How to Calculate IRR: Verified Examples, XIRR and CSV Downloads
Calculate IRR from cash flows, reproduce two property examples, and download CSVs. Includes NPV checks, dated XIRR, multiple roots and MIRR assumptions.
IRR is a discount rate that makes the net present value of a cash-flow schedule zero. With one row per year, it is an annual rate. With monthly rows, ordinary IRR is monthly; the effective annual equivalent is (1 + monthly IRR)^12 − 1. Use XIRR when the actual dates are irregular.
The IRR & XIRR calculator accepts annual rows or dated spreadsheet/CSV cash flows, shows NPV and possible multiple roots, and exports an audit. This guide provides two reproducible property examples and downloadable schedules. These are hypothetical outputs of stated assumptions, not forecasts or market benchmarks.
The IRR formula and timing#
NPV(r) = CF0 + CF1 / (1+r) + CF2 / (1+r)^2 + ... + CFn / (1+r)^n
Find r such that NPV(r) = 0.
CF0 occurs immediately; later rows are equally spaced. Money invested is usually negative and money received positive. Include a zero row for a period with no cash flow: deleting that row changes the timing. Include fees, follow-on investment and final proceeds when they occur. State whether amounts are before or after tax and financing.
Simple schedules sometimes have an algebraic solution; general schedules are normally solved numerically. A schedule can have one root, multiple roots, no real root in the permitted rate domain, or an indeterminate result. Failure to find a root is not a return of 0%.
Reproduce an IRR in a spreadsheet#
- Put every amount, including the initial investment, in time order.
- For equal periods, use
=IRR(A2:A7)for six rows. - Check with
=NPV(rate,A3:A7)+A2. Excel NPV discounts its first supplied amount by one period, so add the time-zero amount separately. - For dated flows, use
=XIRR(amounts,dates)and=XNPV(rate,amounts,dates). XIRR annualizes on actual elapsed days divided by 365. - Inspect the schedule and NPV at your required return. A percentage alone does not establish that the inputs are realistic.
Example 1: a five-year cash purchase
A hypothetical property costs $250,000 in cash at time zero, including purchase costs. There is no loan. Net rent arrives at each year end. Year five includes $19,500 net rent and $310,000 net sale proceeds. Taxes and costs beyond these stated net cash flows are excluded.
| Year | Net cash flow |
|---|---|
| 0 | -$250,000.00 |
| 1 | $18,000.00 |
| 2 | $18,500.00 |
| 3 | $19,000.00 |
| 4 | $19,500.00 |
| 5 | $329,500.00 |
Annual IRR: 11.3521%. Discounting these flows at that rate gives an NPV approximately equal to zero.
| Discount rate | NPV |
|---|---|
| 10.0000% | $13,840.21 |
| 12.0000% | -$6,296.91 |
| 15.0000% | -$32,897.44 |
At a 12% required return the NPV is negative, so these forecast cash flows do not meet that hurdle. This conclusion depends on the assumptions; it is not a recommendation about a real property.
Example 2: a seven-year financed property
Purchase price $500,000; down payment $125,000; loan $375,000 at a nominal annual 7.25% with 300 equal monthly payments. First-year rent is $3,800/month, increasing 2.5% each year. Operating expenses are 35% of rent. The model excludes initial fees, income taxes, vacancy beyond those expenses, and extra capital spending. Net operating cash is aggregated at each year end.
Monthly payment: $2,710.53. Annual debt service: $32,526.31. Loan balance immediately after payment 84: $326,501.62. Calculations retain unrounded values until display; an actual lender's cent rounding can differ slightly.
| Year | NOI | Debt service | Operating cash | Total equity cash |
|---|---|---|---|---|
| 1 | $29,640.00 | $32,526.31 | -$2,886.31 | -$2,886.31 |
| 2 | $30,381.00 | $32,526.31 | -$2,145.31 | -$2,145.31 |
| 3 | $31,140.52 | $32,526.31 | -$1,385.78 | -$1,385.78 |
| 4 | $31,919.04 | $32,526.31 | -$607.27 | -$607.27 |
| 5 | $32,717.01 | $32,526.31 | $190.71 | $190.71 |
| 6 | $33,534.94 | $32,526.31 | $1,008.63 | $1,008.63 |
| 7 | $34,373.31 | $32,526.31 | $1,847.00 | $189,165.58 |
The sale uses trailing year-seven NOI divided by a hypothetical 6.5% exit cap rate: $528,820.20. After the remaining loan and $15,000 selling costs, net sale proceeds are $187,318.58. That amount is added to year-seven operating cash. A forward-NOI valuation would give a different result.
Annual equity IRR: 5.4514%. MIRR at a 7% finance rate and 5% reinvestment rate is 5.4708%. Here MIRR is slightly higher than IRR; it is not inherently a lower number. Neither reaches a hypothetical 10% hurdle.
Download and reproduce the examples
For shorter cases that isolate a single idea, explore the cash-flow reference library. Each case includes its schedule, expected answer, a mathematical check and a link to load it into the workspace.
Download a CSV, open the IRR & XIRR workspace, and import it. Select annual mode for yearly rows or dated mode for the Microsoft schedule. For the financed example's MIRR, enter 7% financing and 5% reinvestment. CSVs retain more precision than the tables.
- Five-year cash purchase CSV — IRR 11.352111%; NPV at 12% -6296.91.
- Seven-year financed property CSV — IRR 5.451406%; MIRR at 7% finance / 5% reinvestment 5.470811%.
- Two valid IRRs CSV — IRRs 10% and 20%; compare NPV at a stated rate.
- Microsoft XIRR example CSV — XIRR approximately 37.336253%; actual days / 365.
Download timing, formulas and verification notes. The dated fixture comes from Microsoft's XIRR documentation.
Comparing IRR, NPV and MIRR#
| Measure | Question it answers | Main limitation |
|---|---|---|
| NPV | What is the discounted net value at my required return? | Depends on the selected discount rate and forecast cash flows. |
| IRR | At what rate does this schedule have zero NPV? | Can be ambiguous and does not show investment scale. |
| XIRR | What annualized zero-NPV rate uses these actual dates? | Retains IRR's root and scale limitations. |
| MIRR | What rate links discounted outflows and compounded inflows at stated rates? | Requires finance/reinvestment assumptions and a specified horizon. |
For a conventional investment with an initial outflow followed only by inflows, a unique IRR above the required return corresponds to positive NPV at that required return. Do not generalize that rule to every schedule with later negative flows. When mutually exclusive projects differ in scale or timing, compare their NPV under consistent assumptions instead of choosing solely by the highest IRR.
Two one-year projects costing $10,000 and $1,000,000 and returning $14,000 and $1,150,000 have IRRs of 40% and 15%. Their undiscounted profits are $4,000 and $150,000. The percentages and dollar values describe different aspects; available capital and required return still matter.
Multiple roots, absent roots and reinvestment#
The annual schedule [-100,230,-132] has two IRRs: 10% and 20%. Substituting either rate in the NPV equation gives zero. A spreadsheet may return one depending on its starting guess. Multiple sign changes permit multiple roots but do not guarantee them. The workspace includes this example and explains its finite search range; it cannot establish that no root exists outside that range.
IRR is a property of the stated cash flows. Its zero-NPV equation does not explicitly simulate reinvesting distributions. Interpreting IRR as a realized compound return on all money until the final date requires an additional reinvestment assumption. Early distributions can be consumed or earn a different rate, so IRR alone does not describe final wealth.
MIRR makes the assumptions explicit:
MIRR = (terminal value of positive flows / absolute present value of negative flows)^(1/n) − 1
Compound inflows to the final period at the reinvestment rate; discount outflows to time zero at the finance rate. Use =MIRR(values,finance_rate,reinvest_rate) for equally spaced spreadsheet rows. MIRR can be higher or lower than IRR. Neither measure repairs unrealistic rent, sale, cost or financing assumptions.
What was checked and what remains uncertain#
The tables are generated from their cash-flow inputs. Verification compares loan balances against month-by-month amortization and checks NPV by direct discounting. Tests cover expected example roots, the two-root schedule and downloadable files. These are arithmetic checks, not independent valuation advice or a forecast review.
The cap-rate tool explains income yield, while the private-equity IRR guide discusses contributions and distributions. To report a discrepancy, include the schedule, timing convention and expected result through Contact; avoid private account details.
No. Ordinary IRR is a rate per equally spaced period. Annual rows give an annual rate; monthly rows give a monthly rate. XIRR gives an annualized rate using actual dates and an actual-days/365 convention.
For flows in A2:A7 with A2 at time zero, use =NPV(IRR(A2:A7),A3:A7)+A2. Check for a duplicated initial amount, mismatched cash-flow ranges or timing, and a rounded rate. Passing the entire range to NPV shifts every flow by one period; that changes NPV away from a root but still gives approximately zero at the same IRR. Different valid IRR roots also each give zero for the same schedule.
Yes. A loss-making conventional schedule can have a negative IRR. The schedule -100, 230, -132 has roots at 10% and 20%. Other schedules have no root in the permitted rate domain. Inspect the cash flows and NPV at a stated rate.
No. MIRR depends on both finance and reinvestment rates. In the seven-year property example here, MIRR is about 5.4708% at rates of 7% and 5%, compared with an IRR of about 5.4514%.
No. IRR does not show dollar scale, liquidity, uncertainty or whether forecast cash flows are achievable. Compare NPV at a suitable required return and examine risk, timing, fees, tax and alternative uses of capital.
References
Written by
Hassaan Rasheed
Web Developer & Content Researcher
Hassaan builds calculators and writes source-linked guides across the site's subject areas. Calculator methods and reference data are documented in each guide so readers can verify the underlying sources.
About the author and methods
