MS Excel: MIRR function to assume realistic rate

MIRR stands for Modified Internal Rate of Return. It improves the traditional IRR by assuming reinvestment at a realistic rate (not at the IRR itself) and separately considering borrowing costs.

MIRR gives a more accurate picture of an investment’s profitability.

MIRR Syntax

MIRR(values, finance_rate, reinvest_rate)

• values = Cash flows (a range of cells, like A2:A8)

• finance_rate = Interest rate you pay on borrowed money (cost of capital)

• reinvest_rate = Interest rate you earn on reinvested cash flows

  
  

Beginner Example: Simple Investment

Year	Cash Flow
0	-10000
1	3000
2	4000
3	5000

Assume:

• finance_rate = 10%

• reinvest_rate = 12%

Formula:

=MIRR(A2:A5, 10%, 12%)

Result: 11.75%

Interpretation: Your project gives an 11.75% return, considering real-world financing and reinvestment.

Intermediate Example: Including Negative and Positive Cash Flows

Year	Cash Flow
0	-15000
1	4000
2	4500
3	7000
4	3000

Assume:

• finance_rate = 8%

• reinvest_rate = 10%

  
  

Formula:

=MIRR(A2:A6, 8%, 10%)

Result: 10.16%

Now, MIRR properly adjusts for both financing costs and reinvestment returns across multiple periods.

Advanced Example: Complex Cash Flows

Year	Cash Flow
0	-20000
1	0
2	7000
3	-3000
4	12000
5	5000

Assume:

• finance_rate = 9%

• reinvest_rate = 11%

Formula:

=MIRR(A2:A7, 9%, 11%)

Result: 12.05%

Even with gaps (zero cash flows) and negative cash flows during the project, MIRR still correctly calculates the realistic return.

Tips and Common Mistakes

Mistake/Tip	Explanation
Only one negative cash flow allowed	MIRR needs one initial investment (negative) followed by positives.
Rates must be in decimal	Use 0.08 for 8%, not just 8.
Don't confuse IRR and MIRR	IRR assumes reinvestment at IRR, MIRR at your reinvestment rate.
Useful for projects with fluctuating cash flows	MIRR smooths returns for better comparisons.

Conclusion

The MIRR function is a powerful tool in Excel to evaluate investment projects more realistically. It adjusts for real-world financing costs and realistic reinvestment returns, unlike the basic IRR.

Mastering MIRR helps you:

• Avoid overestimating returns

• Better compare different investment opportunities

• Make smarter financial decisions 📈