Cash-Flow Reference Library: Reproduce and Understand the Answer
Explore verified IRR, XIRR, NPV and MIRR examples with complete cash flows, mathematical checks, downloadable data and cases you can load into the calculator.
Two calculators receive the same three cash flows. One reports a 10% return. The other reports 20%. You check the entries: neither has a typo.
Both answers can be mathematically correct.
That is the starting point for this collection. A percentage becomes useful when you can trace it back to the money, the dates and the assumptions that produced it. Here you can follow the complete schedule, check the result, and change the inputs yourself. Some cases have one return, some have more than one, and one has no real IRR even though its cash flows change sign twice.
The examples are deliberately small enough to inspect. The paired cases change a specific feature—payment timing, investment size or fees—so you can see which part of the answer moves and why. They are illustrative calculations, with the Microsoft example attributed separately; they are not observations of investment performance.
Choose a question and follow the money#
Start with the puzzle below, or use the case index to find the problem you are investigating. Load this case opens the IRR & XIRR workspace with its exact schedule and rates. The CSV is useful if you want to reproduce it in a spreadsheet. Both routes use the same published inputs.
A return you cannot reduce to one percentage
Pay $100 now, receive $230 in one year, then pay $132 in year two. The final payment changes the shape of the calculation: its NPV crosses zero twice.
| Discount rate | NPV at year 0 |
|---|---|
| 0% | -$2.00 |
| 10% | $0.00 |
| 15% | $0.19 |
| 20% | $0.00 |
| 30% | -$1.18 |
What changed? The last cash flow is another payment, rather than a receipt. A later outflow can produce multiple IRRs. The percentage alone cannot settle the comparison; inspect NPV at the relevant discount rate and the obligations behind the schedule.
Follow the algebra and load this example below →Timing
A quiet year still takes a year
Why must a year with no payment remain in an annual cash-flow schedule?
- Annual IRR
- 10.000000%
- NPV at 10%
- $0.00
- Annual MIRR · 8% finance / 5% reinvestment
- 10.000000%
The investor pays 1,000 at time zero, receives nothing at the end of year one, and receives 1,210 at the end of year two. The 210 gain is spread over two years: 1,000 growing at 10% becomes 1,100 after one year and 1,210 after two. The zero carries timing information even though it contributes no money to the sum.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $0.00 |
| 2 | $1,210.00 |
Watch for this: Deleting the zero moves the receipt into year one and changes the modeled investment. A blank spreadsheet cell can be ignored; use a numeric zero for a real empty period.
Follow the calculation and its assumptions
Mathematical check
- 0 = -1,000 + 0/(1+r) + 1,210/(1+r)^2.
- (1+r)^2 = 1.21, so the admissible rate r > -100% is 10%.
- NPV at 10% = -1,000 + 1,210/1.10^2 = 0.
What is assumed
- Rows represent time zero, year one and year two.
- The final receipt includes all returned capital and income; no omitted payments, fees or taxes.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑The same receipt, one year earlier
What does the accidentally shortened schedule actually calculate?
- Annual IRR
- 21.000000%
- NPV at 10%
- $100.00
- Annual MIRR · 8% finance / 5% reinvestment
- 21.000000%
This is a valid one-year investment with a 21% return. It becomes an error only when it is used to represent the two-year investment in the preceding case. The total gain remains 210, but receiving the money sooner increases both the annual rate and the NPV at the same discount rate.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $1,210.00 |
Watch for this: Treating a different answer as a solver defect before checking whether the schedules represent the same dates.
Follow the calculation and its assumptions
Mathematical check
- 0 = -1,000 + 1,210/(1+r), so r = 1,210/1,000 - 1 = 21%.
- NPV at 10% = -1,000 + 1,210/1.10 = 100.
- Deleting the middle zero raises the reported IRR from 10% to 21%, a change of 11 percentage points.
What is assumed
- The 1,210 receipt really occurs one year after the 1,000 payment.
- Compare with zero-period-retained to isolate timing; the payment amounts do not change.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Ambiguous returns
One schedule, two mathematically valid IRRs
How can both 10% and 20% solve the same cash flows?
- Annual IRR
- 10.000000% / 20.000000%
- NPV at 15%
- $0.19
- Annual MIRR · 8% finance / 5% reinvestment
- 6.438029%
The final payment reverses the cash-flow sign a second time. That creates a different shape from the usual initial payment followed only by receipts: the NPV crosses zero twice. At a 15% discount rate NPV is slightly positive, while it is negative below 10% and above 20%. Neither root alone gives the usual simple rule that every lower hurdle rate must be acceptable.
| Year | Cash flow |
|---|---|
| 0 | -$100.00 |
| 1 | $230.00 |
| 2 | -$132.00 |
Watch for this: Picking the larger root because it looks better, or assuming the first result returned by an iterative spreadsheet function is the only solution.
Follow the calculation and its assumptions
Mathematical check
- Set y = 1+r. Multiplying NPV by y^2 gives -100y^2 + 230y - 132.
- Factor: -100(y-1.10)(y-1.20) = 0, so r = 10% or 20%.
- NPV at 15% = -100 + 230/1.15 - 132/1.15^2 = 0.1890359168.
- With finance 8% and reinvestment 5%, MIRR uses PV of payments 213.1687242798 and FV of receipts 241.5, giving 6.43802917%.
What is assumed
- All three payments are required parts of the same annual schedule.
- IRR means a real rate strictly above -100%; the NPV decision depends on an independently justified discount rate.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: MIRR function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Changing signs does not guarantee an IRR
Can a schedule contain both payments and receipts and still have no real IRR?
- Annual IRR
- No real IRR above −100%
- NPV at 10%
- -$137.19
- Annual MIRR · 8% finance / 5% reinvestment
- -46.833988%
Here the absence of a root can be proved, rather than inferred from a failed numerical search. The discounted middle receipt never offsets the two payments at any real admissible rate. Both signs are necessary for many ordinary IRR calculations, but they are not sufficient. A numerical search that finds nothing cannot establish this general conclusion without the algebra.
| Year | Cash flow |
|---|---|
| 0 | -$100.00 |
| 1 | $50.00 |
| 2 | -$100.00 |
Watch for this: Repeatedly changing the starting guess because both positive and negative amounts are present. This example has no real solution for any guess to find.
Follow the calculation and its assumptions
Mathematical check
- Set y = 1+r > 0. The zero-NPV equation becomes -100y^2 + 50y - 100 = 0.
- The discriminant is 50^2 - 4(100)(100) = -37,500, so there are no real roots.
- Equivalently, -100y^2 + 50y - 100 = -100(y-0.25)^2 - 93.75, which is always negative.
- NPV at 10% is -137.1900826446. MIRR at finance 8% and reinvestment 5% is -46.83398816%.
What is assumed
- Payments occur at three consecutive annual dates.
- No terminal asset value or other receipt has been omitted.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: MIRR function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Return and value
Getting the money back can still have negative NPV
Why is NPV negative when the investment returns every unit of capital?
- Annual IRR
- 0.000000%
- NPV at 10%
- -$173.55
- Annual MIRR · 8% finance / 5% reinvestment
- 0.000000%
The nominal gain is zero and the annual IRR is exactly zero. NPV asks a different question: how much is that year-two receipt worth at the chosen opportunity cost? At 10%, its present value is only 826.4462809917. Returning the same face amount does not compensate for the two-year delay under that assumption.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $0.00 |
| 2 | $1,000.00 |
Watch for this: Reading a negative NPV as proof that less cash comes back than went in. Discounted value and nominal gain are different quantities.
Follow the calculation and its assumptions
Mathematical check
- 0 = -1,000 + 1,000/(1+r)^2 implies r = 0% for r > -100%.
- NPV at 10% = -1,000 + 1,000/1.10^2 = -173.5537190083.
- Nominal gain = -1,000 + 1,000 = 0.
What is assumed
- The discount rate is a hypothetical 10% annual opportunity cost.
- No inflation adjustment is embedded; inputs and discount rate are interpreted consistently in nominal terms.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑A 19% total loss is a 10% annual loss over two years
Why is a two-year 19% loss not the same as minus 9.5% per year?
- Annual IRR
- -10.000000%
- NPV at 10%
- -$330.58
- Annual MIRR · 8% finance / 5% reinvestment
- -10.000000%
Annual compounding applies each year's change to the remaining amount. A 10% decline takes 1,000 to 900; another 10% takes 900 to 810. Dividing the 19% total loss by two would describe a simple average, not the compound annual rate that reproduces the terminal receipt.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $0.00 |
| 2 | $810.00 |
Watch for this: Rejecting every negative IRR as a calculator error, or dividing the total percentage change by the number of years.
Follow the calculation and its assumptions
Mathematical check
- Terminal multiple = 810/1,000 = 0.81.
- Annual IRR = sqrt(0.81) - 1 = -10%.
- NPV at 10% = -1,000 + 810/1.10^2 = -330.5785123967.
What is assumed
- There are no intervening deposits or withdrawals.
- The final 810 is the complete year-two receipt.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Comparing projects
The smaller project wins the percentage comparison
Does a higher IRR necessarily create more discounted value?
- Annual IRR
- 25.000000%
- NPV at 10%
- $6,818.18
- Annual MIRR · 8% finance / 5% reinvestment
- 25.000000%
This project earns 25% on 50,000. It has the higher percentage return in the scale comparison, but its NPV at 10% is 6,818.18. The larger companion project earns a lower rate on much more capital and adds more discounted currency value. The two metrics answer different questions, so their rankings need not agree.
| Year | Cash flow |
|---|---|
| 0 | -$50,000.00 |
| 1 | $62,500.00 |
Watch for this: Assuming that a 25% rate must be preferable without considering the investment amount, capital constraints, risk, or whether both opportunities can be undertaken.
Follow the calculation and its assumptions
Mathematical check
- IRR = 62,500/50,000 - 1 = 25%.
- NPV at 10% = 62,500/1.10 - 50,000 = 6,818.1818181818.
- Nominal gain is 12,500; it is not the same as discounted value.
What is assumed
- Compare with scale-large-lower-rate using the same one-year horizon and hypothetical 10% discount rate.
- For the isolated ranking example, both projects are mutually exclusive, similarly risky and fully fundable; there are no other strategic differences.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑The larger project wins the NPV comparison
How can the 14% project add more value than the 25% project?
- Annual IRR
- 14.000000%
- NPV at 10%
- $18,181.82
- Annual MIRR · 8% finance / 5% reinvestment
- 14.000000%
The additional 450,000 of initial capital buys an additional 507,500 at year one relative to the smaller project. That incremental investment earns 12.77777778%, exceeding the assumed 10% hurdle. Under the stated comparable-risk and funding assumptions, choosing the larger project adds 11,363.64 more NPV despite its lower overall IRR.
| Year | Cash flow |
|---|---|
| 0 | -$500,000.00 |
| 1 | $570,000.00 |
Watch for this: Treating this example as a universal instruction to choose the biggest project. Financing limits, different risks or nonexclusive opportunities change the decision being analyzed.
Follow the calculation and its assumptions
Mathematical check
- IRR = 570,000/500,000 - 1 = 14%.
- NPV at 10% = 570,000/1.10 - 500,000 = 18,181.8181818181.
- Incremental IRR = (570,000-62,500)/(500,000-50,000) - 1 = 12.77777778%.
- Incremental NPV = 18,181.8181818181 - 6,818.1818181818 = 11,363.6363636363.
What is assumed
- Use the same mutually exclusive, comparable-risk assumptions as scale-small-high-rate.
- The additional 450,000 is available; the 10% hurdle applies consistently to the incremental cash flows.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Doubling in two years
What does a 2x multiple leave out?
- Annual IRR
- 41.421356%
- NPV at 10%
- $652.89
- Annual MIRR · 8% finance / 5% reinvestment
- 41.421356%
A 2x receipt-to-payment multiple describes how much money returns, but not how long it takes. With a single initial payment and a single final receipt, the annual IRR is the compound growth rate needed to bridge them. Doubling in two years requires about 41.42% a year; the five-year companion reaches the same total with a much lower annual rate.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $0.00 |
| 2 | $2,000.00 |
Watch for this: Calling a 2x multiple a 100% annual return without stating the holding period and intervening cash flows.
Follow the calculation and its assumptions
Mathematical check
- Receipt-to-payment multiple = 2,000/1,000 = 2x.
- IRR = 2^(1/2) - 1 = 41.4213562373%.
- NPV at 10% = -1,000 + 2,000/1.10^2 = 652.8925619835.
What is assumed
- No interim receipts, additional payments or remaining asset value.
- The companion case changes only the receipt's timing.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Doubling in five years
Why does the same 2x multiple produce a much smaller annual return?
- Annual IRR
- 14.869835%
- NPV at 10%
- $241.84
- Annual MIRR · 8% finance / 5% reinvestment
- 14.869835%
The 1,000 gain is unchanged, but the investor waits three additional years. About 14.87% compounded for five years produces the same final 2,000 that required 41.42% over two years. This is why a multiple belongs next to timing information. Neither number describes risk or guarantees that early receipts can be reinvested at an attractive rate.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $0.00 |
| 2 | $0.00 |
| 3 | $0.00 |
| 4 | $0.00 |
| 5 | $2,000.00 |
Watch for this: Using multiple alone to compare holding periods, or applying the single-payment growth formula to a schedule with intermediate cash flows.
Follow the calculation and its assumptions
Mathematical check
- IRR = 2^(1/5) - 1 = 14.8698354997%.
- NPV at 10% = -1,000 + 2,000/1.10^5 = 241.8426461183.
- Total nominal gain remains 1,000 in both doubling cases.
What is assumed
- Rows retain every year from time zero to year five.
- There are no intermediate payments or receipts; this restriction makes the direct growth formula valid.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Costs and reinvestment
A later repair bill changes the MIRR financing side
When does the MIRR finance rate actually affect the answer?
- Annual IRR
- 17.202459%
- NPV at 10%
- $139.74
- Annual MIRR · 8% finance / 5% reinvestment
- 12.115545%
The 300 year-two payment must be discounted to time zero using the 8% finance rate. Separately, the 700 year-one receipt is compounded for two years at the 5% reinvestment rate; the final 1,000 receipt is already at the endpoint. MIRR then connects those two aggregates over three years. This produces 12.12%, compared with an IRR of 17.20%, under the chosen assumptions.
| Year | Cash flow |
|---|---|
| 0 | -$1,000.00 |
| 1 | $700.00 |
| 2 | -$300.00 |
| 3 | $1,000.00 |
Watch for this: Assuming the MIRR finance rate changes every example. If the only negative payment occurs at time zero, there is no later negative amount for that rate to discount.
Follow the calculation and its assumptions
Mathematical check
- PV of payments = 1,000 + 300/1.08^2 = 1,257.2016460905.
- FV of receipts at year three = 700(1.05)^2 + 1,000 = 1,771.75.
- MIRR = (1,771.75/1,257.2016460905)^(1/3) - 1 = 12.1155452810%.
- For root uniqueness, let x = 1/(1+r) > 0. The NPV polynomial is -1,000 + 700x - 300x^2 + 1,000x^3. Its derivative 700 - 600x + 3,000x^2 is always positive, so it crosses zero only once.
What is assumed
- The year-two payment is an additional required cost, not a withdrawal that can be omitted.
- The 8% finance and 5% reinvestment rates are hypothetical annual assumptions, not observed borrowing terms or promised returns.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: MIRR function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Start with a complete schedule before subtracting fees
What does a gross-to-net comparison need beyond two percentages?
- Annual IRR
- 10.992351%
- NPV at 10%
- $253.94
- Annual MIRR · 8% finance / 5% reinvestment
- 10.580948%
This hypothetical three-year schedule is the starting point for a controlled fee comparison. The final 11,800 includes the last 800 operating receipt and 11,000 of exit proceeds. Nothing in the example establishes a typical market return or a typical fee burden. Its value is that every change in the companion schedule can be traced to a stated charge on a stated date.
| Year | Cash flow |
|---|---|
| 0 | -$10,000.00 |
| 1 | $800.00 |
| 2 | $800.00 |
| 3 | $11,800.00 |
Watch for this: Subtracting a generic number of percentage points from IRR and calling the result a net return.
Follow the calculation and its assumptions
Mathematical check
- NPV at 10% = -10,000 + 800/1.10 + 800/1.10^2 + 11,800/1.10^3 = 253.9444027047.
- Solving that discounted-sum equation for zero gives IRR = 10.9923512079%.
- At reinvestment 5%, terminal receipts aggregate to 800(1.05)^2 + 800(1.05) + 11,800 = 13,522.
What is assumed
- The initial payment is 10,000; operating receipts occur at each year end.
- Before-fee here means before only the four charges specified in after-specified-fees. The example makes no claim about a regulated gross-return reporting definition.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function · Microsoft: MIRR function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Four explicit charges turn positive NPV negative
How much do these fees change the investor's actual result?
- Annual IRR
- 8.291461%
- NPV at 10%
- -$443.43
- Annual MIRR · 8% finance / 5% reinvestment
- 8.118769%
Charge 200 at inception and another 200 at each of the three year ends. The initial outflow becomes 10,200; each subsequent receipt falls by 200. Total nominal charges are 800, but their present value at 10% is 697.3703981968. The resulting IRR is about 8.29%, and the previously positive NPV becomes negative under the unchanged hurdle rate.
| Year | Cash flow |
|---|---|
| 0 | -$10,200.00 |
| 1 | $600.00 |
| 2 | $600.00 |
| 3 | $11,600.00 |
Watch for this: Using the nominal total fees as the NPV reduction, or reusing this example's IRR reduction for investments with different timing and cash flows.
Follow the calculation and its assumptions
Mathematical check
- Fee cash-flow differences from the before-fee case are [-200, -200, -200, -200].
- PV of fees = 200 + 200/1.10 + 200/1.10^2 + 200/1.10^3 = 697.3703981968.
- After-fee NPV = 253.9444027047 - 697.3703981968 = -443.4259954921.
- IRR changes from 10.9923512079% to 8.2914610296%, a difference of 2.7008901784 percentage points.
What is assumed
- Each of the four charges is exactly 200; no tax effects, percentage charges or performance fees are modeled.
- All other amounts and timing match before-specified-fees.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: IRR function · Microsoft: NPV function
Loads annual IRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑Calendar dates
Reproduce Microsoft's dated XIRR example
Can we reproduce a documented dated example without treating rows as years?
- Annualized XIRR
- 37.336253%
- XNPV at 10%
- $1,994.51
These five amounts and dates come from Microsoft's XIRR documentation, which displays 37.34%. Direct discounting reproduces that rounded answer. The elapsed intervals are 0, 60, 303, 411 and 456 days from the first payment, not years zero through four. The extra digits here are the independently calculated root of those specified inputs, not evidence that Excel itself was run in this study.
| Date | Cash flow |
|---|---|
| 2008-01-01 | -$10,000.00 |
| 2008-03-01 | $2,750.00 |
| 2008-10-30 | $4,250.00 |
| 2009-02-15 | $3,250.00 |
| 2009-04-01 | $2,750.00 |
Watch for this: Feeding the five amounts into a periodic IRR function and expecting the dated answer. That substitutes four equal periods for an actual 456-day schedule.
Follow the calculation and its assumptions
Mathematical check
- Solve -10,000 + 2,750/(1+r)^(60/365) + 4,250/(1+r)^(303/365) + 3,250/(1+r)^(411/365) + 2,750/(1+r)^(456/365) = 0.
- The independently calculated rate is 37.3362533519%, rounding to Microsoft's displayed 37.34%.
- At a 10% annual discount rate, the same dated sum gives NPV = 1,994.5100406533.
What is assumed
- Dates are calendar dates with no time-of-day component; year fractions use actual elapsed days divided by 365.
- This is a third-party illustrative fixture, not an observed investment. Periodic MIRR is not reported for dated cases in this collection.
Provenance: Microsoft’s illustrative schedule, with attribution and independently calculated expected values.
Method references: Microsoft: XIRR function and dated example
Loads dated XIRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑A calendar anniversary need not be one XIRR year
Why does a 10% gain across this anniversary produce XIRR slightly below 10%?
- Annualized XIRR
- 9.971359%
- XNPV at 10%
- -$0.26
This interval crosses February 29, 2024, so it contains 366 days. Under the actual-days/365 convention, the receipt occurs 366/365 years after the payment. The total gain is exactly 10%, but the annualized rate is about 9.9714%. The difference is a day-count effect; changing the denominator to 366 would define a different calculation.
| Date | Cash flow |
|---|---|
| 2023-07-01 | -$1,000.00 |
| 2024-07-01 | $1,100.00 |
Watch for this: Rounding every calendar anniversary to one year while claiming to reproduce the documented XIRR convention.
Follow the calculation and its assumptions
Mathematical check
- Elapsed days = 366, so 0 = -1,000 + 1,100/(1+r)^(366/365).
- r = 1.10^(365/366) - 1 = 9.9713585934%.
- NPV at 10% = -1,000 + 1,100/1.10^(366/365) = -0.2610896904.
What is assumed
- The only cash flows are the dated initial payment and final receipt.
- Use actual days divided by a fixed 365, including intervals that cross a leap day; periodic MIRR is not reported.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: XIRR function and dated example
Loads dated XIRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑A zero balance with no elapsed time has no unique rate
Is an equal payment and receipt on the same date a zero-percent annual return?
- Annualized XIRR
- Indeterminate
- XNPV at 10%
- $0.00
Both exponents are zero, so the rate disappears from the NPV equation. Every admissible rate gives the same zero balance. Reporting 0% as if it were a uniquely measured annual return would hide this loss of information. This differs from capital returned after two years: in that earlier case, time has elapsed and zero is the unique admissible IRR.
| Date | Cash flow |
|---|---|
| 2026-01-01 | -$1,000.00 |
| 2026-01-01 | $1,000.00 |
Watch for this: Equating an indeterminate rate with a zero return, or annualizing a result over a zero-length holding period.
Follow the calculation and its assumptions
Mathematical check
- Both elapsed times are zero, so NPV(r) = -1,000/(1+r)^0 + 1,000/(1+r)^0 = 0.
- The equation holds for every r > -100%; there is no unique IRR to report.
- A compound annual rate would require division by a positive duration, which this schedule lacks.
What is assumed
- The calendar-day model has no intraday timing and both entries share the same date.
- The receipt exactly offsets the payment. This is an algebraic edge case; it does not claim a particular spreadsheet error or output.
Provenance: CalculatorFlux illustrative case; this is a constructed calculation, not measured investment performance.
Method references: Microsoft: XIRR function and dated example
Loads dated XIRR mode and the rates above. If you import the CSV manually, select that mode and enter the rates yourself.
Back to case index ↑How to reproduce a case in a spreadsheet#
For an annual CSV, the first row of numbers is year zero. Keep the zero-payment years. If a five-row schedule occupies cells A2:A6, its periodic return is =IRR(A2:A6) and its value at a 10% annual discount rate is =NPV(10%,A3:A6)+A2. The initial amount sits outside the NPV function because that function discounts its first supplied amount by one period. These cases use one-year periods. Microsoft IRR, Microsoft NPV.
For a dated CSV, put genuine spreadsheet dates in column A and amounts in column B, with the earliest date first. Use =XIRR(B2:B6,A2:A6) for five dated rows. In this convention, elapsed time is actual days divided by 365, including across leap years. An amount received one calendar year later can therefore have a slightly different annualized rate when that interval contains 366 days. Microsoft XIRR.
For an annual MIRR comparison, use the case's stated finance and reinvestment rates. With five amounts in A2:A6 and the library's 8%/5% assumptions, the formula is =MIRR(A2:A6,8%,5%). Those two assumptions answer a different question from solving for an IRR. Microsoft MIRR.
These are reproduction instructions, not a report of tests run inside Excel or Google Sheets. A tool returning an error or a different root deserves investigation: check the schedule, timing convention, starting guess and numerical limits before concluding that its arithmetic is wrong.
Read a result without losing its meaning#
Check whose money the schedule represents. A property earns operating income; an equity investor receives what remains after relevant capital spending, financing and sale costs. Mixing those perspectives creates an answer to a question nobody intended to ask. Follow the complete property model to see the reconciliation.
Distinguish amounts received from amounts assumed. A terminal sale value in a forecast can dominate its return. The solver cannot tell whether a forecast is plausible. Replacing that sale value with a lower one is a useful sensitivity test; it is not a probability estimate.
Name the comparison rate. NPV changes when the discount rate changes. A positive NPV says the stated schedule has positive modeled value at that rate. The rate itself still needs a reason. The guide to evaluating an IRR explains how to examine the assumptions behind a headline percentage.
Keep the dollars beside the percentage. Scaling every payment by the same positive factor preserves IRR but changes NPV in dollars. A rate alone therefore cannot tell you how much value a project creates or how much capital it needs. The IRR versus NPV examples develop that comparison.
Verification, precision and reuse#
The displayed answers come from a versioned reference collection. Its checks evaluate the complete published schedules through direct discounting and separate mathematical calculations, then compare applicable results with the calculator. The multiple-root and no-root examples include algebraic explanations; a solver's search result alone would not prove that another root cannot exist.
Money is displayed to cents and return rates to six decimal places in the reference cards. CSV inputs retain their stored precision. Small last-digit differences can come from numerical tolerances or independent rounding. The JSON and accompanying notes preserve the expected values, calculation conventions and verification tolerances for reproducible checks.
The proof and code checks cover these examples. They do not establish whether an actual investment is suitable, certify every possible input, or constitute an independent professional review. Research and drafting used AI assistance; mathematical checks and their limits are described so readers can examine the evidence themselves.
Use the downloads to reproduce the calculations while learning, teaching or testing. When referring to a case, cite its permanent section link and version, retain the Microsoft fixture's attribution, and identify changes you make. If an example is wrong, report its case ID, version and a reproducible correction. A future correction should leave a reader able to tell which answer they used and what changed.
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
