MIRR

In this comprehensive guide, we will explore the MIRR (Modified Internal Rate of Return) formula in Excel. The MIRR is a financial function that calculates the profitability of an investment by taking into account the costs of the investment, the revenues generated, and the interest rates involved. This formula is particularly useful for evaluating projects with varying cash flows and helps in making better investment decisions. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the MIRR function.

MIRR Syntax

The syntax for the MIRR function in Excel is as follows:

=MIRR(values, finance_rate, reinvest_rate)

Where:

• values (required) – This is an array or range of cells containing the cash flows for the investment. The first value represents the initial investment, which should be a negative number, and the subsequent values represent the cash flows for each period.
• finance_rate (required) – This is the interest rate you pay on the money used to finance the investment. It should be expressed as a decimal (e.g., 0.1 for 10%).
• reinvest_rate (required) – This is the interest rate you earn on the cash flows as they are reinvested. It should also be expressed as a decimal (e.g., 0.08 for 8%).

MIRR Examples

Let’s look at some examples of how to use the MIRR function in Excel:

Example 1: You have an investment with the following cash flows: -$10,000 (initial investment), $3,000, $4,000, $5,000, and $6,000. The finance rate is 10%, and the reinvest rate is 8%. To calculate the MIRR, you would use the following formula:

=MIRR(A1:A5, 0.1, 0.08)

Where A1:A5 contains the cash flow values. The result would be 0.1261 or 12.61%, which is the modified internal rate of return for this investment.

Example 2: You have an investment with cash flows in cells B1:B6, a finance rate in cell C1, and a reinvest rate in cell C2. To calculate the MIRR, you would use the following formula:

=MIRR(B1:B6, C1, C2)

This will return the MIRR based on the cash flows and interest rates provided in the specified cells.

MIRR Tips & Tricks

• When entering cash flow values, make sure the initial investment is a negative number, as it represents an outflow of cash.
• Ensure that the finance_rate and reinvest_rate are expressed as decimals, not percentages (e.g., 0.1 for 10%, not 10).
• Use the MIRR function to compare different investment opportunities with varying cash flows and interest rates. A higher MIRR indicates a more profitable investment.
• Keep in mind that the MIRR function assumes that all cash flows are reinvested at the reinvest_rate, which may not always be the case in real-life scenarios.

Common Mistakes When Using MIRR

• Not using a negative value for the initial investment, which can result in an incorrect MIRR calculation.
• Entering the finance_rate and reinvest_rate as percentages instead of decimals, which can lead to inaccurate results.
• Not including all cash flows in the values argument, which can cause the MIRR to be based on incomplete data.
• Using the IRR function instead of the MIRR function when dealing with investments that have varying cash flows and interest rates. The IRR function does not account for the finance_rate and reinvest_rate, which can lead to misleading results.

Why Isn’t My MIRR Working?

If you’re having trouble with the MIRR function, consider the following troubleshooting steps:

• Double-check that the initial investment value is negative, as this represents an outflow of cash.
• Ensure that the finance_rate and reinvest_rate are entered as decimals, not percentages.
• Verify that all cash flows are included in the values argument and that there are no errors in the data.
• Make sure you’re using the MIRR function and not the IRR function if your investment has varying cash flows and interest rates.
• If you’re still having issues, check for any errors in the formula syntax or cell references.

MIRR: Related Formulae

Here are some related formulae that can be useful when working with the MIRR function:

• IRR: The IRR (Internal Rate of Return) function calculates the internal rate of return for an investment based on a series of cash flows. However, it does not account for the finance_rate and reinvest_rate, making it less suitable for investments with varying cash flows and interest rates.
• NPV: The NPV (Net Present Value) function calculates the net present value of an investment based on a series of cash flows and a discount rate. This can help determine the profitability of an investment over time.
• PV: The PV (Present Value) function calculates the present value of an investment based on a series of future cash flows, a discount rate, and the number of periods. This can help determine the current value of an investment.
• FV: The FV (Future Value) function calculates the future value of an investment based on a series of cash flows, an interest rate, and the number of periods. This can help determine the potential value of an investment in the future.
• XIRR: The XIRR (Extended Internal Rate of Return) function calculates the internal rate of return for an investment with irregular cash flows and periods. This can be useful for investments that do not have consistent cash flows or time intervals.

By understanding the MIRR function and its related formulae, you can make more informed investment decisions and better analyze the profitability of various investment opportunities. Remember to always double-check your data and formula syntax to ensure accurate results.