This function calculates the modified internal rate of return (MIRR), evaluating negative cash flows (disbursements) and positive cash flows (deposits) at different interest rates.
Syntax:
MIRR(Values; Investment; Reinvestment)
Arguments
- Values (required)
- A range of cash flows (disbursements and deposits) arranged chronologically.
- Each value represents the end of a period (e.g., yearly).
- Must include at least one positive and one negative value.
- Non-numeric or empty cells are ignored.
- Investment (required)
- The discount rate applied to negative cash flows (borrowing cost).
- Reinvestment (required)
- The interest rate applied to positive cash flows (reinvestment yield).
Background
The MIRR method improves upon the standard IRR() by:
- Separate rates for financing (negative flows) and reinvestment (positive flows).
- Eliminating multiple IRR issues that arise with irregular cash flows.
- Providing a realistic reinvestment assumption (unlike IRR, which assumes reinvestment at the IRR itself).
Advantages:
✔ Clear reinvestment rate – Reflects realistic earnings on deposits.
✔ No time horizon limitation – Unlike NPV, which requires a fixed rate.
Disadvantages:
✖ Fixed rates – Assumes constant borrowing and reinvestment rates.
Example
An investor buys a 5-year government bond with:
- Annual coupon rate: 4.25%
- Reinvestment rate: 2% (due to market conditions)
IRR vs. MIRR:
| Metric | Calculation | Result |
| IRR() | Standard return | 4.25% |
| MIRR() | Adjusted for reinvestment | 4.08% |
Interpretation:
- Initial investment: $100
- Future value (FV):
- Coupons reinvested at 2% → $122.12 after 5 years.
- MIRR (4.08%) reflects the true annualized return after accounting for lower reinvestment yields.
Key Takeaway
MIRR provides a more realistic measure of profitability by:
- Using separate rates for costs and reinvestment.
- Avoiding the overstated returns of IRR when reinvestment rates differ.