Time zero is discounted by accident
NPV begins with the first future period. Keep B2 outside NPV; include B2 inside IRR and include the complete date/value pairs inside XNPV and XIRR.
Explain a modified return with finance and reinvestment rates
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter separate formulas in E6:E8. Eight signed numeric flows B2:B9 occur at fixed periods 0 through 7 in A2:A9. E2 is the finance rate per period; E3 is the reinvestment rate per period. E6 is the unrounded MIRR over seven intervals. E7 is the positive magnitude of all negative flows discounted to period zero at E2. E8 is all positive flows compounded to period seven at E3. Numeric zero rows, including a terminal zero, still count as periods.
Edit a cash flow or an assumption
Inputs stay numeric. Keep signed cents, real zero periods and full-precision results. Rates are decimals such as 0.08. Dates use YYYY-MM-DD and are stored as numeric dates. Applying one input validates the complete schedule first.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E6, E7, E8. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Separate the present cost of negative flows from the terminal value of positive flows, then reconcile MIRR.
THE QUICK ANSWER01
Enter separate formulas in E6:E8. Eight signed numeric flows B2:B9 occur at fixed periods 0 through 7 in A2:A9. E2 is the finance rate per period; E3 is the reinvestment rate per period. E6 is the unrounded MIRR over seven intervals. E7 is the positive magnitude of all negative flows discounted to period zero at E2. E8 is all positive flows compounded to period seven at E3. Numeric zero rows, including a terminal zero, still count as periods.
E6
=MIRR(B2:B9,$E$2,$E$3)E7
=-SUMPRODUCT((B2:B9<0)*B2:B9/(1+$E$2)^A2:A9)E8
=SUMPRODUCT((B2:B9>0)*B2:B9*(1+$E$3)^($A$9-A2:A9))For the example data
E6 0.06519562136555135E7 2522.4446815336278E8 3924.8879116288003
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Count intervals, not nonzero cash entries
Eight positions from period zero through period seven contain seven compounding intervals. A numeric zero retains a position; deleting a zero changes both flow timing and the terminal horizon.
MIRR makes two growth assumptions explicit
The financing rate discounts costs to the present. The reinvestment rate compounds positive receipts to the terminal period. Their ratio defines a modeled per-period return; it is not a claim about achievable reinvestment or borrowing terms.
Read the full step-by-step walkthrough
- Separate the assumptions
Negative flows are financed at E2. Positive receipts grow at E3. Swapping these assumptions changes the model when rates differ and intermediate costs exist.
- Discount only the negative flows
E7 reports a positive cost magnitude. Each negative flow is discounted by its period index, with the initial cost discounted for zero intervals.
- Compound only the positive flows
E8 compounds receipts to period seven. A later negative cost does not enter this positive-flow terminal value.
- Reconcile the rate
The positive terminal value divided by the positive present cost, raised to 1/7, minus one equals MIRR. Keep all eight entries even if the last flow is zero.
- Retain a zero at the terminal period
A9 is period seven even when B9 is zero. The last zero still fixes the horizon for compounding positive receipts and taking the rate root. Counting only nonzero cash entries changes the model.
Things to watch
A percentage is entered as a whole number
Rates are decimal fractions: 0.08 means eight percent per declared period, or per 365 days in the dated task. Keep full precision; number formatting does not change the calculation.
A rate is treated as a recommendation
These are original cash-flow models with supplied assumptions. A computed yield is not a ranking of projects with different timing, scale or risk. Taxes, fees and changing discount curves are outside the model.
Input domain needs review
Use eight numeric cent flows from -1,000,000 through 1,000,000. The first flow is a cost of at least 100. Periodic and dated IRR tasks require nonnegative later flows and at least one positive receipt, with their unique yield between -0.8 and 2. Discount is -0.25 through 0.5. MIRR admits later costs and uses finance/reinvestment rates from zero through 0.5. Dates are whole numeric serials from 2000 through 2099, the first is earliest, every later date is at least 30 days later and the schedule spans 365 through 3650 days. Arbitrary sign-changing IRR roots and invalid source types require separate rules.
About this example
Original retained cash-flow sources checked with real browser calculations and independent arithmetic. The dated task has an explicit mathematical fallback for the pinned browser XNPV negative-rate boundary. Native Excel/Sheets recalculation, editing and saving remain separate and unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.