Explain a modified return with finance and reinvestment rates
Your goal: Separate the present cost of negative flows from the terminal value of positive flows, then reconcile MIRR.
Full 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.
Intermediate costs and two distinct assumptionsFinance the negative-flow pool
A/B holds the timeline and signed flows. D/E holds the labelled rate controls. Keep physical zero-flow periods. Calculated results appear after your formulas pass.Scroll the worksheet horizontally to inspect later source columns.
| # | ||||
|---|---|---|---|---|
| 1 | ||||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 |
Step 1 of 3
Finance the negative-flow pool
Rules for this step
Select every negative amount with its period and the finance-rate control. Discount these outflows to period zero and report their positive magnitude.