MIRR Formula in Excel (Table of Contents)
Introduction to MIRR Formula in Excel
Modified Internal Rate of Return (MIRR) is a Microsoft Excel function categorized under financial function, which takes into consideration the initial capital cost (which the investor borrowed with a certain rate of interest) and the reinvestment rate of cash for investment with given time intervals.
Suppose you borrowed (took loan) an amount with a certain rate of interest and willing to invest it somewhere else. If you invest that amount and get the returns with the same rate of interest you borrowed, you can assume that you have a standard Internal Rate of Return (IRR). However, if you invest that loan amount with a different interest rate somewhere else, it can be considered a Modified Internal Rate of Return.
Basically, MIRR is associated with a situation in which you take a loan of some amount and invest it somewhere else with a different rate of interest for a specified period of time.
An argument in the MIRR Formula
- values – Is a required argument containing an array of cells representing the series of payments or loan amount (negative) and income (positive) amount for a given time period.
- finance_rate – Is a required argument that specifies the rate of interest you pay for the loan amount over the period of time.
- reinvest_rate – Is the required argument that specifies the rate of interest you receive through the investments made over time (The time should be the same/uniform through the course for the proper calculations of MIRR).
How to Use MIRR Formula in Excel?
MIRR Formula in excel is very simple and easy. Let’s understand how to use the MIRR Formula in excel with an example.
Example of MIRR Excel Function
Mr. Shaw has taken a loan of 20,000 from the bank with a 10% rate of interest. He then reinvested the same amount with a reinvestment rate of interest of 12.43%. Suppose the income values after the 1st, 2nd, 3rd, and 4th year are 9000, 12398, 15000, and 18690, respectively, which are also shown in the screenshot below. We are keen to know the Modified Internal Rate of Return (MIRR) Mr. Shaw got through the 2nd year, 3rd year, and 4th year (We are not calculating the return for first-year because it will always be negative because the return value will be lesser than the investment value).
One more thing to have a note here is the value of the Investment Amount. As Investment Amount is always considered as a cash outflow, it will have a negative value. Make sure that there is at least one negative value in your data while processing with the MIRR function. Otherwise, you will not get the desired results.
Let’s see step by step how to calculate the MIRR for the 2nd, 3rd, and 4th year.
In cell E2, start typing the formula for MIRR.
Give an array containing investment value and 1st as well as 2nd-year returns as the first argument under the MIRR formula.
We will need the value for an interest rate on the investment amount stored in cell B7.
The final value we will be in need of is a value of interest earned on reinvestments. It is stored in cell B8.
Complete the formula by closing the parentheses and press Enter Key to see the output.
After two years, Mr. Shaw will get returns with a MIRR of 6%. While calculating the MIRR for the 3rd year, select array B2:B5 as the first argument under the MIRR formula with rest arguments as it is (i.e. finance_rate and reinvest_rate as the same used in the previous example). You’ll get the output as below:
Again for the calculations of MIRR for 4 years, select value array as B2 to B6 (from Investment Amount till Income After 4 Year) and keep other arguments like finance_rate and reinvest_rate as it is. You will get an output as below:
This is how we can calculate the Modified Internal Rate of Return through excel with the help of the MIRR formula.
- MIRR has wide applications in the field of Financial Modeling, specifically in investment banking and private equity or mortgage.
- This works as a compound rate of interest there and considers all the previous effects of the reinvestment. However, a commonly used IRR (Internal Rate of Return) considers single reinvestment at a time, evading the effect of other reinvestment values on the overall earnings.
- One of the common drawbacks of the MIRR function is nothing but complexity it makes in calculations by considering the effect of all previous returns. Though it seems much easier here in excel, in real-time theoretical calculations, the formula gets trickier for MIRR.
- Apart from this, it is not a widely practiced method to calculate the Investment Rate of Return and need more socialization in the organizations for being popular.
This is from this article. Let’s wrap things up with some points to be remembered.
Things to Remember
- value argument in the MIRR formula must contain at least one investment value (negative value) and one income value (positive value) to get the Modified Internal Rate of Return. Otherwise, this formula will throw #DIV/0! error towards you.
- If the value argument in MIRR contains any text, logical or blank values, those will be ignored. However, zeros are kept in calculations.
- MIRR uses the order of values to interpret the order payment/income. Make sure you have mentioned payment and investment values in sequence (payment value at first with a negative sign and then the income/return value with all positives).
- The loan amount should always be provided as a negative value as this is the outflow of the cash or investment we are making (It’s going through our pocket hence negative or debt).
- The Modified Internal Rate of Return (MIRR) is calculated based on variable returns over the period of the same time. This means you can’t calculate MIRR if all of the returns are not made under the same period of time.
- #VALUE! the error occurs when any one of the supplied arguments, i.e. value, finance_rate, reinvest_rate, are non-numeric.
This is a guide to MIRR Formula in excel. Here we discuss how to use MIRR Formula and MIRR function in excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
- REPLACE Formula in Excel
- Poisson Distribution in Excel
- LOOKUP Formula in Excel
- SUBTOTAL Formula in Excel