MIRR in Excel: Separate Financing and Reinvestment in Periodic Returns

=MIRR(values, finance_rate, reinvest_rate)

All three arguments are required. Values is an ordered range or array of regularly spaced cash flows. Payments are negative and receipts positive from the same perspective. At least one of each sign is required.

Finance_rate describes financing of negative flows, while reinvest_rate describes compounding positive flows. Both rates must use the same interval as the cash-flow sequence. Neither is a numerical guess for a root search.

MIRR differs from IRR because it explicitly uses these two supplied assumptions. A MIRR result is not a prediction that future reinvestment or financing will actually be available at those rates.

Put periods 0, 1, 2 and 3 in A2:A5 and flows −1000, 400, 500, 600 in B2:B5. Put finance rate 8% in E1 and reinvestment rate 10% in E2.

Use =MIRR(B2:B5,E1,E2) in E3. The independent terminal value of positive flows at period three is =400*1.1^2+500*1.1+600, or 1634.

The magnitude of the present value of negative flows is 1000, since the only payment is at time zero. The equivalent growth calculation is =(1634/1000)^(1/3)-1, independently 0.1778338119.

There are four cash-flow entries but three intervals. Using a fourth root would annualize over the wrong horizon. The period labels make the n−1 interval count visible.

Use a separate schedule −1000, −100, 600, 700 at times zero through three with the same rates.

The independent present-value magnitude of payments is =1000+100/1.08, or 1092.5925925926. Positive flows compound to =600*1.1+700, or 1360 at the final period.

=MIRR for that range independently targets 7.5705903830%, from (1360/1092.5925925926)^(1/3)-1 when calculated without intermediate rounding.

Unlike the first example, a later negative payment must be discounted using finance_rate. In the first example changing only the financing rate leaves the time-zero payment unchanged, so its MIRR is unchanged under the mathematical construction.

Microsoft says numeric zero cells are included, while text, logical values and empty cells in arrays/references are ignored. An explicit zero therefore preserves an interval in the cash-flow timeline.

Do not delete a zero year merely because it contributes no direct currency amount. It affects how long earlier positive flows compound, when later negative flows are discounted and the total number of intervals in the root.

Keep dates or period numbers next to values even though MIRR itself only uses their order. If spacing is irregular, this periodic function does not automatically adjust for that; a separate dated model is required.

Microsoft uses −120000, 39000, 30000, 21000, 37000, 46000, finance rate 10%, and reinvestment rate 12%. Independent five-year MIRR is 12.6094130366%, which the source displays as 13%.

For the initial cost and first three receipts, the independent three-year value is −4.8044655250%, displayed as −5%.

With all six flows and reinvestment rate 14%, the independent rate is 13.4759110828%, also displayed as 13%. Two identical rounded percentages can therefore conceal materially different mathematical values. Increase display precision before concluding that an assumption has no effect.

The dedicated reference explicitly documents #DIV/0! if values lack either a positive or a negative flow. Check signs, numeric inclusion and interval count before changing rates to suppress an error.

Use ordinary rates above −1 for the real-valued discount/compound model illustrated here. The page does not enumerate every native rate-bound or type-coercion error, and none is invented from algebra alone.

The supplied finance and reinvestment assumptions are model inputs, not recommendations. Compare results only when timing, cash-flow scope and rate conventions agree. All outputs in this guide are independently calculated rather than native Excel observations.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft MIRR reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.