How to Use the MIRR Function in Excel
The MIRR function in Excel and Google Sheets is designed to calculate the Modified Internal Rate of Return for a series of cash flows. This function is particularly helpful in assessing the performance of various investment opportunities, as it incorporates both the costs of financing and the returns on reinvested cash. In this article, we will delve into the functionality of the MIRR function in both Excel and Google Sheets, and provide practical examples of how to use it.
Excel and Google Sheets MIRR Function Syntax
The syntax for the MIRR function in both Excel and Google Sheets is:
=MIRR(values, finance_rate, reinvest_rate)- values: An array or a range of cash flows corresponding to a sequence of disbursements and earnings.
- finance_rate: The interest rate charged for borrowing capital.
- reinvest_rate: The interest rate earned on reinvesting the cash flows.
Examples of Using the MIRR Function
Let’s explore a simple example to understand how the MIRR function calculates the modified internal rate of return for a sequence of cash flows. Consider the following cash flows from an investment:
| Period | Cash Flow |
|---|---|
| 0 | -$100,000 |
| 1 | $30,000 |
| 2 | $40,000 |
| 3 | $50,000 |
| 4 | $60,000 |
Assume a finance_rate of 8% and a reinvest_rate of 6%. To calculate the Modified Internal Rate of Return using the MIRR function in Excel or Google Sheets, input the following formula:
=MIRR({-100000, 30000, 40000, 50000, 60000}, 0.08, 0.06)The calculation will yield the Modified Internal Rate of Return for the specified cash flows.
In summary, the MIRR function in Excel and Google Sheets serves as a powerful tool for evaluating investment opportunities by accounting for both the financing costs and the returns on cash reinvestments. With the syntax and examples outlined in this article, you can effectively harness the MIRR function for comprehensive financial analysis.
More information: https://support.microsoft.com/en-us/office/mirr-function-b020f038-7492-4fb4-93c1-35c345b53524