MIRR Function in Excel
Syntax
Parameters
The details of the parameters used in the MIRR formula in Excel are as follows:
- Values: Values are an array of cash flow representing a series of payment amounts, range of references, or set of income values, including the initial investment amount.Finance_rate: The finance rate is a rate of interest paid on the amount used during cash flow.Reinvest_rate: Reinvest rate is the interest rate earned from the reinvested profit amount during cash flow.
How to use the MIRR Function in Excel? (with Examples)
Example #1
Consider an initial loan amount of 25,000 as an initial investment amount (loan amount) with an interest rate of 5% yearly. You have earned an interest rate of 8% from the reinvested income. In MIRR, the loan or initial investment amount is always considered as (-ve) value.
Below is the table that shows the income details after a regular interval. For example, the cash flow for the 1st, 2nd, 3rd, 4th, and 5th years are as follows: 10,911, 14,716, 19,635, 18,700, and 18,477.
Now calculate MIRR in Excel (Modified internal return rateInternal Return RateInternal rate of return (IRR) is the discount rate that sets the net present value of all future cash flow from a project to zero. It compares and selects the best project, wherein a project with an IRR over and above the minimum acceptable return (hurdle rate) is selected.read more) after two years:
=MIRR(B4:B6,B10,B11) and output MIRR is 3%.
Similarly, calculate MIRR (Modified internal rate of return) after 3 and 5 years:
MIRR, after three years, will be =MIRR(B4: B7, B10, B11), and output is 25%.
MIRR, after five years, will be=MIRR(B4: B9, B10, B11), and output is 31%.
Example #2
Consider an initial loan amount of 10,000 as an initial investment with an interest rate of 5% yearly, and you have earned an interest rate of 8% from the reinvested income.
Below is the table showing the income details after a regular interval. For example, the cash flow for the 1st, 2nd, 3rd, 4th, and 5th years are as follows: 7,505, 5,338, 9,465, 5,679, and 6,004.
Now calculate MIRR after 2 years:
=MIRR(B15:B17,B21,B22)and output MIRR is 16%.
Similarly, calculate MIRR after 3 and 5 years:
MIRR after three years will be =MIRR(B15: B18, B21, B22), and output is 34%.
MIRR after five years will be =MIRR(B15:B20,B21,B22) and output is 32%.
Things to Remember
The loan amount is always considered a negative value.After regular intervals, the modified internal return rate is always calculated on a variable cash flow.Error handling:
#DIV/0!: MIRR excel will return #DIV/0Excel Will Return #DIV/0#DIV/0! is the division error in Excel which occurs every time a number is divided by zero. Simply put, we get this error when we divide any number by an empty or zero-value cell.read more! The exception occurs when the supplied error does not contain at least one negative or positive value.
#VALUE!: MIRR will return this kind of exception when any supplied value is non-numeric.
Recommended Articles
This article is a guide to MIRR Function Excel. We discuss using the MIRR Excel function, practical examples, and a downloadable template. Also, you can learn more about Excel from the following articles: –
- Examples of IRRExamples Of IRRInternal rate of return (IRR) is the discount rate that sets the net present value of all future cash flow from a project to zero. It compares and selects the best project, wherein a project with an IRR over and above the minimum acceptable return (hurdle rate) is selected.read moreIRR vs. ROIIRR Vs. ROIThe value that IRR seeks is the rate of discount which makes the NPV of the sum of inflows equal to the initial net cash invested. ROI is a metric that calculates the percentage increase or decrease in return for a particular investment over a set time frame.read moreExcel NPER FunctionExcel NPER FunctionNPER, commonly known as the number of payment periods for a loan, is a financial term and an inbuilt financial function in Excel that can be used to calculate NPER for any loan. This formula takes rate, payment made, present value and future value as input from a user.read moreRANKX Function in Power BIRANKX Function In Power BIRANKX is a type of built-in function in Power BI that is widely used for sorting data under various conditions. The syntax for this function is as follows, RANKX(
