How to Calculate XIRR for an Investment Portfolio

1. What XIRR measures

XIRR is the internal rate of return for cash flows that occur on specific, potentially irregular dates. The result is annualized. It is often called a money-weighted return because the timing and size of the money you add or remove affect the result.

That makes XIRR useful for a personal portfolio with contributions, withdrawals or transfers across the reporting boundary. A simple percentage change in the account balance cannot separate those external cash flows from investment performance.

XIRR is not the same as a time-weighted return. A money-weighted result reflects the investor's cash-flow timing; a time-weighted result is designed to reduce the effect of external contributions and withdrawals. Use the measure that matches the question you are trying to answer.

2. Build a clean portfolio cash-flow schedule

Create a separate tab named XIRR. Use one reporting currency and three columns: Date, Cash Flow and Description. Enter actual spreadsheet dates—not date-looking text.

Use the investor's perspective for signs:

What should not appear as an external cash flow?

• A purchase or sale inside the portfolio, because money only moves between cash and an investment.
• A dividend retained inside the portfolio, because it is already included in portfolio value.
• A transfer between two accounts that are both inside the same measured portfolio.
• An internal fee entered a second time when the account value already reflects that fee.

The boundary is crucial. A transfer from checking into your only tracked brokerage account is an external contribution. A transfer from Broker A to Broker B is internal if both accounts are included in the same combined portfolio.

If your transactions and holdings are not yet organized, begin with our guide to tracking an investment portfolio in Excel. It explains account boundaries, transactions, cash and month-end values before you add a return calculation.

3. Enter the XIRR formula in Excel or Google Sheets

Assume dates are in cells A2:A5 and signed cash flows are in B2:B5. The basic formula is identical:

=XIRR(B2:B5,A2:A5)

The optional third argument is an initial guess:

=XIRR(B2:B5,A2:A5,10%)

Both Microsoft and Google document 10% as the default guess. Usually you can omit it. A different guess may help the iterative calculation converge, but it should not be used to force a preferred answer.

The function requires at least one negative and one positive cash flow. Each amount must have a corresponding date. Microsoft calculates day differences on a 365-day basis, so XIRR is an annualized rate even when your measurement period is shorter or longer than one year.

4. Worked example with irregular contributions and withdrawals

Suppose an investor measures one combined portfolio from January 15, 2025 to January 15, 2026. They begin with $10,000, add $2,000 in April, withdraw $800 in August and finish with a portfolio worth $12,600.

=XIRR(B2:B5,A2:A5)

This is not calculated by dividing the $1,400 difference between ending value and net invested cash by the starting value. XIRR discounts each dated flow, so the April contribution and August withdrawal receive different time weights.

The ending value is included once as a positive terminal flow. If the portfolio is still open, you do not need to pretend that every holding was sold in the transaction log. Use the market value on the selected end date. Do not add both that ending value and hypothetical sale proceeds for the same holdings.

5. Check whether your XIRR result makes sense

A valid formula can still be fed the wrong data. Before using the result, run these checks:

1. Reconcile starting and ending values. Use the same account boundary and reporting currency on both dates.
2. Match external flows. Tie contributions and withdrawals to statements or bank records.
3. Review signs. Money invested is negative; money returned and ending value are positive.
4. Check dates. Confirm there are no shifted years, locale errors or text-formatted dates.
5. Compare with investment gain. Ending value minus starting value minus contributions plus withdrawals gives a currency gain. It will not equal XIRR, but an obviously conflicting direction can reveal an input problem.

For the worked example, the gain is $12,600 − $10,000 − $2,000 + $800 = $1,400. Gain is an amount; XIRR is an annualized percentage. Keep both because they answer different questions.

6. Fix common XIRR errors and suspicious results

Do not hide an error with IFERROR(XIRR(...),0). A displayed 0% looks like a valid return and can conceal missing or malformed data. Show a clear review message instead:

=IFERROR(XIRR(B2:B5,A2:A5),"Check cash-flow signs and dates")

Microsoft notes that dates before the first date are invalid and that mismatched value and date ranges produce an error. Keep the schedule compact and auditable rather than feeding entire columns full of blanks and unrelated entries into the formula.

7. XIRR vs. total gain, simple return and time-weighted return

No single number explains an entire portfolio. A useful monthly or quarterly review can show ending value, net external flows, investment gain, XIRR and allocation together. Keep the label and date range visible so readers do not confuse a partial-period result with a full-year return.

8. Add XIRR to a repeatable portfolio workflow

Once your calculation works, turn it into a controlled process:

1. Choose a reporting period and freeze the start and end dates.
2. Reconcile holdings and cash to brokerage statements.
3. Record only external contributions and withdrawals in the XIRR schedule.
4. Convert every amount to one base currency using a documented rate and date.
5. Add the ending market value as the final positive flow.
6. Calculate XIRR, check gain in currency units and note any data exceptions.
7. Save a dated snapshot so live prices do not rewrite historical results.

For several accounts, calculate a combined XIRR only after defining which accounts are inside the portfolio. You can also calculate account-level results, but a transfer between two included accounts must not become a contribution to one combined schedule and a withdrawal from the other without clear reconciliation.

Prefer a ready-made portfolio tracker?

XIRR can be built manually with the schedule above. If you also want a structured workbook for transactions, holdings, allocation and portfolio review, browse our Investment Portfolio Trackers collection.

Stock Portfolio Tracker

Track stock purchases and sales, transaction fees, profit and loss, dividends and multiple currencies. Review the product page for version-specific features.

View Stock Portfolio Tracker

ETF & Index Fund Tracker

Organize multiple accounts, transactions, cash, currencies, allocation and dividend information for ETF and index-fund portfolios.

View ETF & Index Fund Tracker

The product pages specify Microsoft 365 for the Excel files or a Google account for Google Sheets. Mobile editing may be limited. This article teaches a separate DIY XIRR method and does not claim that every product uses this exact calculation.

XIRR for investment portfolios: FAQ