MIRR
The MIRR function in Excel calculates the modified internal rate of return for a series of cash flows that may have different reinvestment and finance rates. It is a useful tool in financial analysis for evaluating investment returns under varying conditions.
Syntax
=MIRR(values, finance_rate, reinvest_rate) Arguments
| Argument | Required | Description |
|---|---|---|
| values | Yes | An array or reference to a range of cells containing cash flows. |
| finance_rate | Yes | The interest rate paid on money used in financing the investments. |
| reinvest_rate | Yes | The interest rate received on the reinvestment of cash flows. |
About
Delve into the world of investment analysis with the MIRR function in Excel. When dealing with cash flows over time, especially where reinvestment rates differ from financing rates, MIRR stands out as a key tool for determining the modified internal rate of return. This metric aids in assessing the profitability of investments considering both incoming and outgoing cash flows, along with reinvestment conditions and financial costs incurred along the way. Essentially, MIRR provides a comprehensive view of the true return on an investment, factoring in varying rates pertinent to reinvestment and financing. By utilizing the MIRR function, you gain insights into the overall performance of your investments, allowing for more informed decision-making in financial matters.
Examples
Suppose you have a series of cash flows from an investment project: -$100, $20, $30, $50, and $40 over five periods. The finance rate is 6%, and the reinvestment rate is 8%. To calculate the MIRR for these cash flows, the formula would be: =MIRR(-100, 20, 30, 50, 40, 0.06, 0.08)
Consider a scenario where an initial investment of $500 generates future cash flows of $100, $150, and $200 at the end of three periods. The finance rate is 5%, and the reinvestment rate is 7%. To find the MIRR for this investment, use the formula: =MIRR(-500, 100, 150, 200, 0.05, 0.07)
Consider a scenario where an initial investment of $500 generates future cash flows of $100, $150, and $200 at the end of three periods. The finance rate is 5%, and the reinvestment rate is 7%. To find the MIRR for this investment, use the formula: =MIRR(-500, 100, 150, 200, 0.05, 0.07)
Tips & notes
Ensure that the cash flows and rates are appropriately organized when using the MIRR function. The values should be input as an array or reference to a range of cells. Additionally, keep in mind that the finance_rate and reinvest_rate must be consistent with the time periods of the cash flows for accurate calculations.
Common questions
How does the MIRR function differ from the IRR function in Excel?
While both MIRR and IRR are used to evaluate the potential profitability of investments, MIRR considers different reinvestment and finance rates. IRR assumes that all cash flows are reinvested at the same rate, which may not always be realistic in practical financial scenarios.
What does a higher MIRR value indicate?
A higher MIRR value indicates a more favorable return on an investment, considering both reinvestment and finance rates. It suggests that the investment may be more profitable, factoring in the costs and benefits associated with cash flows and reinvestments.
Can the MIRR function handle irregular cash flow patterns?
Yes, the MIRR function in Excel can handle irregular cash flow patterns by accurately calculating the modified internal rate of return based on the provided cash flows, finance rate, and reinvestment rate. It offers flexibility in analyzing investments with varying cash flow timings.