If you’re an investor or finance professional, you’ve likely heard of the term Modified Internal Rate of Return (MIRR). The MIRR serves as a more accurate measure of an investment’s profitability than the traditional Internal Rate of Return (IRR). While it may be a less familiar concept, calculating MIRR in Excel is simple and straightforward. In this article, we’ll go through the step-by-step process of doing so.

1. Understand MIRR

Before diving into the actual calculation process, it’s essential to understand what MIRR represents. Unlike IRR, which assumes that reinvested cash flows earn at the same rate as the original investment, MIRR accounts for the cost of capital and assumes that reinvested cash flows generate returns at a different rate. This consideration can lead to more accurate depictions of an investment project’s viability.

2. Prepare your data

To calculate MIRR in Excel, you’ll need to have your investment data at hand. Typically, this information includes:

– Initial investment cost

– Cash inflows (profits) for each period

– Finance rate

– Reinvestment rate

Organize these values chronologically in contiguous cells within different columns or rows.

3. Use the MIRR function in Excel

Excel offers a built-in function for calculating MIRR: `=MIRR(values, finance_rate, reinvestment_rate)`. This formula takes three arguments:

– `values`: The range of cells containing cash flow values (including initial investment cost and profits for each period).

– `finance_rate`: The annual interest rate paid on borrowed funds.

– `reinvestment_rate`: The annual interest rate earned on reinvested cash flows.

4. Apply the MIRR function

After gathering your data and becoming familiar with the function, apply it in an empty cell:

1. Select a blank cell where you want the result.

2. Type `=MIRR(`.

3. Select or type the range of cash flow values (including initial cost and profits).

4. Press comma `,`.

5. Insert your finance_rate, like 5% (as a decimal: 0.05).

6. Press comma `,`.

7. Insert your reinvestment_rate, like 10% (as a decimal: 0.10).

8. Close the function with a parenthesis `)` and press enter.

After executing the formula, the result should display the MIRR percentage for your investment project.

5. Interpret the results

Now that you’ve calculated MIRR in Excel, it’s time to analyze the result:

– If MIRR > cost of capital (finance_rate), this suggests that the investment is worthwhile.

– If MIRR < cost of capital, it’s likely that the investment is not a sound financial decision.

– If MIRR is significantly higher than IRR, this might indicate that IRR overestimated the project’s profitability.

By using Excel to calculate MIRR, you can make more informed decisions about investment projects and gain a more realistic understanding of an investment’s potential profitability. With this step-by-step guide at your disposal, calculating MIRR can be an easy and efficient part of your financial toolkit.