How to use the Excel MIRR function?
The term MIRR (Modified Internal Rate of Return) is commonly used in corporate financial or financial management to indicate the profitability of an investment, and is therefore commonly used in making business decisions when choosing between investments. The MIRR calculation uses a series / payment schedule (which includes the initial outflow along with net income / gains), when calculating the MIRR of interest capitalization, it is assumed that the Net Present Value of the investment is zero.
Basics of the MIRR Function
MIRR, which stands for “Modified Internal Rate of Return,” is employed in corporate finance and financial management to evaluate investment profitability. It overcomes certain limitations of the IRR calculation by accounting for the initial investment cost and the reinvestment rate of cash flows, assuming that the Net Present Value (NPV) of the investment is zero.
Syntax of the MIRR function in Excel
The MIRR function in Excel is used with the following syntax:
=MIRR( range, finance_rate, reinvestment_rate )
Parameters:
- range – is a range of cells that represent a series of cash flows (investment values and net income) that occur at regular time periods. Must have at least one negative value (representing payment) and at least one positive value (representing income)
- finance_rate – This is the interest rate you pay on cash flow amounts.
- reinvestment_rate – This is the interest rate you get on the cash flow amounts when they are reinvested.
Examples of the Mirr function
Choosing Between Two Projects
Imagine you need to decide between two investment projects: one for constructing a railway line and the other for building a motorway. Both projects span three years and have the same cash flows. To account for a potential economic slowdown and lower reinvestment rates, calculate the MIRR for both projects.
In this case, the MIRR function is used to calculate the MIRR for both projects with a financing rate of 10% and a reinvestment rate of 8%. Based on the MIRR approach, the motorway project is the preferred choice.
Simple MIRR formula
A client wishes to determine the future value of an investment after five years with a specified interest rate. The MIRR function is used to provide a straightforward calculation of future value for the client’s investment.
More Detailed MIRR Calculation
In this scenario, the client desires to explore investment details further. A more comprehensive MIRR formula is employed to accommodate the client’s request for in-depth information.
Flexibility in MIRR
A client wants to assess how different interest rates would impact their investments. MIRR is utilized to determine the potential earnings under varying interest rate scenarios, demonstrating its flexibility.
MIRR and Average
A company evaluates its investment policy, considering both the average performance and MIRR for the entire investment portfolio. Both the AVERAGE and MIRR functions are used for comprehensive assessment.
Average and MIRR
In this case, the situation is completely different. The client is stunned by the circumstances and wants to know what’s best for him. This time, we would like to know what the rate has been over the last year, which has given us productive data. Therefore, we will use the formula AVERAGE and MIRR in one formula.
MIN and MIRR
In this case, a company with a long history of diverse interest rates seeks to select the minimum rate for its circumstances. The MIN and MIRR functions work in tandem to identify the most suitable rate.
What is MIRR of 350,000?
We have a client who has 350,000 and is trying to figure out how much the money would be worth after fixing the interest rate. This allows you to confirm that we need to format the cell to currency after entering the formula.
IF and MIRR
The client asks a question that involves us, confirming that we need to find a way to see how the show will work. The client asks for an interest on the investment and we would know that the client must have a certain amount in the investment account for this MIRR to be possible. Therefore, we use both IF and MIRR formulas to find the answer.
MAX and MIRR
The company evaluates how to use the information that is currently in business, and we prefer to use the MAX and MIRR formulas to find answers. All our interest rates have been fixed, but we rather use the MAX formula.
MIRR, MAX and MIN
The company faces a unique situation where it needs to balance customer and business interests effectively. MIRR, along with the MAX and MIN functions, is employed to achieve the optimal outcome.
These practical examples illustrate how the MIRR function in Excel can be applied in various financial scenarios to evaluate investment profitability, make informed decisions, and address the complex dynamics of cash flows, financing, and reinvestment rates.
Leave a Reply