How to Calculate IRR in Excel: Verified IRR, XIRR and MIRR Examples

Copy a complete cash-flow table into Excel, verify IRR and MIRR, and check dated XIRR. Fix blank periods, date errors, multiple roots and #NUM! results.

Use =IRR(values) for equally spaced cash flows and =XIRR(values,dates) for actual transaction dates. IRR returns a rate per input period; XIRR returns an annualized rate. MIRR answers a different question by using specified finance and reinvestment rates.

The IRR & XIRR calculator lets you paste annual or dated schedules, inspect NPV and detected roots, and export the calculation. It does not infer dates from an annual list. The worked spreadsheet examples below make the timing and assumptions explicit so you can reproduce each result.

Choose the function before entering the cash flows#

For monthly IRR, the effective annual equivalent is (1 + monthly_IRR)^12 - 1. For example, a 1% monthly rate annualizes to approximately 12.68%. A monthly MIRR calculation also needs monthly finance and reinvestment rates; do not insert annual percentages unchanged.

A complete annual IRR example: 22.06%#

This hypothetical investment pays $500,000 initially, receives four annual distributions, and receives $750,000 at the end of year five. The final amount includes every year-five receipt, including any sale proceeds. No other cash flows are assumed.

Paste this table starting at cell A1:

Year	Cash flow
0	-500000
1	80000
2	90000
3	95000
4	100000
5	750000

Then enter:

=IRR(B2:B7)
Result: 22.06377908% per year

=NPV(10%,B3:B7)+B2
Result: 252474.681933

=NPV(IRR(B2:B7),B3:B7)+B2
Result: approximately 0

The independent check discounts each row by its year:

NPV(r) = -500000 + 80000/(1+r) + 90000/(1+r)^2
         + 95000/(1+r)^3 + 100000/(1+r)^4 + 750000/(1+r)^5

At the unrounded IRR, the sum is zero to numerical precision. At a 10% required return, it is $252,474.68. Excel's NPV function starts discounting its first supplied value one period ahead, which is why the immediate investment B2 is added separately.

Keep zero periods. If year three has no payment, enter numeric 0. Excel IRR ignores empty cells in a referenced range; a blank can remove a period and change subsequent timing. The first cash flow is usually negative for an investment, but the mathematical requirement is at least one positive and one negative amount, with signs consistent with the perspective being modeled.

A complete dated XIRR check: 37.34%#

This is the five-payment example from Microsoft's XIRR documentation, also available through the calculator's Microsoft XIRR example button. Enter the amounts in A2:A6 and the date formulas in B2:B6:

=XIRR(A2:A6,B2:B6)
Result: 37.33625335% per year

These payments are not five annual periods. Ordinary IRR applied to the amounts alone answers a different timing question. XIRR uses the day differences from the first date divided by 365, including when a leap day falls within the schedule.

Use =ISNUMBER(B2) to check whether a date cell stores a number, and inspect the intended date as well. Applying a date display format to arbitrary text does not by itself convert it to a valid Excel date. Put the earliest date first when building the worksheet, and ensure every amount has a corresponding date. The CalculatorFlux dated mode sorts valid entered dates and uses the earliest as its valuation point.

MIRR on the annual example: 18.35%#

Return to the six-row annual example. Choose a 7% annual finance rate and 5% annual reinvestment rate purely for this illustration:

=MIRR(B2:B7,7%,5%)
Result: 18.35448783% per year

The calculation can be audited without an IRR solver:

Future value of inflows at year 5:
80000*1.05^4 + 90000*1.05^3 + 95000*1.05^2 + 100000*1.05 + 750000
= 1161164.25

Present value of outflows = 500000
MIRR = (1161164.25/500000)^(1/5) - 1
     = 18.35448783%

Here the finance rate has no effect because the only negative flow occurs at time zero. It would affect the result if there were later outflows. The rates are modeling inputs, not claims about current borrowing costs or obtainable investment returns.

IRR's zero-NPV equation does not explicitly model reinvestment of distributions. Treating the IRR as compound growth of all proceeds through the final date requires an additional assumption about what happens to interim cash. MIRR makes that assumption explicit. It can be above or below IRR depending on the schedule and rates; neither percentage alone establishes the better investment.

Diagnose errors without hiding the cash-flow problem#

For [-100,230,-132] at years 0, 1 and 2, 10% and 20% both make NPV zero. Different spreadsheet guesses can select different roots. Multiple sign changes allow multiple IRRs but do not guarantee them. MIRR produces a separate result under its own valid rate and cash-flow assumptions; it does not identify which IRR to choose.

The workspace reports detected roots and its finite search range, -99.99% to 1,000,000%. A numerical search may miss roots, particularly in nonconventional schedules. Always retain the amounts, timing and NPV check with a reported percentage.

For a partial-year sale, enter the actual closing and receipt dates with XIRR. Prorating the last amount in ordinary IRR changes its size, not its payment time, so it does not reproduce a fractional-period calculation.

For additional reproducible schedules, use the verified property examples and CSV downloads.