MIRR
The MIRR function calculates the modified internal rate of return for a series of cash flows with different reinvestment and finance rates. It is used in financial analysis to evaluate investment returns under varying conditions.
Syntax 🔗
=MIRR(values, finance_rate, reinvest_rate)
values | An array or reference to a range of cells containing cash flows. |
finance_rate | The interest rate paid on money used in financing the investments. |
reinvest_rate | The interest rate received on the reinvestment of cash flows. |
About MIRR 🔗
Use the MIRR function in Excel to analyze investment cash flows over time, especially when your reinvestment rates differ from financing rates. The MIRR function helps you determine the modified internal rate of return, which considers both incoming and outgoing cash flows, reinvestment conditions, and financial costs. This gives you a clearer view of the return on your investment, factoring in different rates for reinvestment and financing. With MIRR, you can better assess the performance of your investments and make more informed financial decisions.
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, use the formula: =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, enter the formula: =MIRR(-500, 100, 150, 200, 0.05, 0.07)
Notes 🔗
When using the MIRR function, make sure your cash flows and rates are well-organized. Input the values as an array or reference to a range of cells. Also, ensure that the finance_rate and reinvest_rate align with the time periods of the cash flows for precise calculations.
Questions 🔗
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.