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 different rate of interest somewhere else, it can be considered as 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.
Argument in the MIRR Formula
values – Is a required argument containing an array of cells that represent the series of payments or loan amount (negative) as well as 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 which you receive through the investments made over the period of time (The time should be same/uniform through the course for the proper calculations of MIRR).
4.5 (898 ratings)
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 as 12.43%. Suppose the income values after 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 be having a negative value. Make sure that, there is at least one negative value in your data while processing with MIRR function. Otherwise, you will not get the desired results.
Let’s see step by step how to calculate the MIRR for 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 MIRR formula.
We will need the value for an interest rate on investment amount which is stored in cell B7.
The final value we will be in need 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 MIRR as 6%. While calculating the MIRR for the 3rd year, select array B2:B5 as the first argument under 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 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 at the overall earnings.
- One of the common drawbacks for 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 the things up with some points to be remembered.
Things to Remember
- value argument in 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. Means, you can’t calculate MIRR if all of the returns are not made under the same period of time.
- #VALUE! error occurs when any one of the supplied argument 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 downloadable excel template. You can also go through our other suggested articles –