IRR vs MIRR Formula Explained: Choose the Right Metric for Your Investments

You should use MIRR over IRR when you want a more accurate reflection of an investment’s profitability, especially if you’re considering the realistic reinvestment of cash flows and the cost of financing.

This article will explain both the Internal Rate of Return (IRR) and Modified Internal Rate of Return (MIRR) formulas in detail, comparing how each metric works and when to apply them. You’ll also find a worked example showing how to calculate and interpret both IRR and MIRR, helping you make more informed investment decisions.

Understanding the Dangers of IRR: A Simple Investment Opportunity

To illustrate the risks associated with IRR and why Modified IRR is a superior method, let's consider a simple investment opportunity: investing in a frozen banana stand.

IRR and Modified IRR Example

Imagine you are presented with an opportunity to purchase a franchise in a frozen banana stand. The seller is asking for a purchase price of $100,000 and promises potential annual profits of $40,000. You plan to hold onto the franchise for 4 years and then sell it.

However, you learn that in 3 years, an expected redevelopment of the esplanade where the banana stand is located will mean minimal to no profits for that year before recovering the next year.

Question: What is the annual return you can expect to make from the frozen banana stand?

Answer: Your IRR is 32%, but your Modified IRR is 23%. Let's examine the calculations below.

Investment

First, include the initial investment of $100,000 in year 0 and assume that you can sell the franchise at the same price you paid at year 4.

Profit

Assume you achieve $40,000 in annual profits, except for year 3 when the esplanade closes, forcing you to shut down for most of the year.

Cashflows

Sum the profit and investment to calculate the net cash flows for each year.

IRR Formula

The IRR formula determines the rate of return needed to make the present value of cash flows from the investment equal to the initial investment. IRR can be calculated using Excel or Google Sheets with the formula =IRR(cashflows), resulting in 32%.

In simple terms, an IRR of 32% means that to earn the same return as the banana stand investment, you would need to invest $100,000 in a 4-year term deposit with a very high compound annual return of 32%.

However, the major shortcoming of IRR is that it assumes the cash flows earned throughout the investment are reinvested back at the same rate of return, such as investing in another banana stand and earning a high 32% return. This is rarely the case.

Modified IRR Formula

The Modified IRR formula improves upon the IRR by including the actual rate of reinvestment you can earn with the cash flows of the investment.

For example, if there are no new banana stand investment opportunities, you would realistically invest the cash flows in a term deposit earning a 5% annual rate for the remainder of the 4 years. By using a realistic reinvestment rate of 5% instead of 32%, the Modified IRR decreases from 32% to 23%.

In simple terms, a Modified IRR of 23% based on a reinvestment rate of 5% means our banana stand investment is equivalent to a 4-year term deposit earning a high compound annual return of 23%.

Conclusion

Our Modified IRR of 23% is materially lower than the advertised potential annual returns of 40%, but still significantly above a safe investment of 5% in a term deposit.

Investing in the banana stand does come with risks. We must assume that the $40,000 can be achieved in the years the esplanade is open, and that someone else would be willing to pay the full $100,000 we paid for the franchise.

Lower profits would hurt cash flows and negatively impact our resale price. This is the risk premium we are demanding in our purchase price.

But, as George Bluth Snr says:

There’s always money in the banana stand.