In this post let’s find the answer to how to calculate the modified internal rate of return using the MIRR function in Google Sheets.
I have already explained about the IRR function (see FINANCIAL FUNCTIONS inside my function guide). MIRR is the modified version of this function. As the name suggests the M in MIRR stands for modified.
Do you know where the difference lies in these two (IRR and MIRR) financial functions for the internal rate of return calculation in Google Sheets?
IRR – The IRR function assumes the interim cash flows are reinvested at the internal rate of return aka IRR itself as that of the project that generated them.
MIRR – The MIRR accepts both the cost of the investment (financing rate) and a reinvestment rate (reinvestment return rate) for cash flows received.
In short, the MIRR function in Google Sheets returns the MIRR for a series of periodic cash flows and the difference between the interest rate paid on financing and the return received on income that reinvested.
MIRR Function – Syntax and Arguments
Syntax:
MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)Arguments:
cashflow_amounts – An array or cell range that contains a series of income or payments occurring at regular periods associated with the investment.
The cell range must contain at least one -ve and one +ve cash flow to calculate the modified rate of return aka MIRR.
financing_rate – The interest rate that you pay on the funds invested.
reinvestment_return_rate – The interest rate you receive on reinvestment of the income received (cash flows) from the investment.
MIRR Formula Example in Google Sheets
Please copy the below table and paste it in the range A1:B7 in a new Google Sheets file (we only require the numbers in the second column for the MIRR calculation though).
| Description | Cash Flow Amounts |
| Initial Outlay | -15000 |
| 1st Cash Flow Amount | 2000 |
| 2nd Cash Flow Amount | 3000 |
| 3rd Cash Flow Amount | 3500 |
| 4th Cash Flow Amount | 6000 |
| 5th Cash Flow Amount | 6500 |
Assume the financing_rate (the annual interest rate of 15000) is 10% (0.1) and the reinvestment_return_rate (the annual interest rate for the reinvested profits) is 12.5% (0.125).
We can use the below MIRR formula in any cell other than in the above range (A1:B7) in the same Google Sheets file to return the modified interest rate of return.
=mirr(B2:B7,0.1,0.125)or
=mirr(B2:B7,10%,12.5%)
The MIRR would be 11%. Let’s change the reinvestment_return_rate to 11%.
=mirr(B2:B7,0.1,0.11)The result would be 10%.
Points to be Noted
Cash flows should be periodic and enter it in the correct sequence that occurs in the array or cell range.
In the earlier example, the cash flow amounts are in the cell range B2:B7. Here in this formula, the same values are in an array.
=mirr({-15000;2000;3000;3500;6000;6500},10%,12.5%)Further the array can be in any orientation (horizontal or vertical).
=mirr({-15000,2000,3000,3500,6000,6500},10%,12.5%)The formula would return #DIV/0! error if there is no negative value in the cash flow amount.
I hope you could understand how to use the MIRR function in Google Sheets. Thanks for the stay, enjoy!
Related Functions:
