Modified internal rate of return MIRR Excel function
Written by:Abraham A.Learn to find modified internal rate of return MIRR with MS Excel function, download easy to use Excel template. On this web page we explain how to use MIRR formula in MS Excel to calculate Modified Internal Rate of Return.
What is MIRR
MIRR is a discounted cash flow technique used in financial analysis to evaluate capital investment proposals. It is suggested that MIRR is a preferred measure than the IRR. The reason for this has to do with the fact that in certain cases the regular IRR may not exists or there may exist multiple IRR values. MIRR has the vantage that it's value is unique and you get results for it in all cases. MIRR is the rate at which initial expense equals the present value of expected cash flows that are appreciated at the WACC.
MIRR Formula in Excel
MS Excel provides a built in formula to find modified internal rate of return, to use this formula you type in
=MIRR(values,discount_rate,reinvestment_rate)
Here the values are a range of cells such as A1:B10 that carry the net cash flows, one thing to keep in mind is that at least one of these values must be a negative number and other may be positive, or mixed. The second argument the formula expects is the discount rate or the WACC. The last of the arguments is the reinvestment rate which in most cases will be the same as the discount rate or the WACC.
MIRR MS Excel Example
WABC TV station in NYC is planning an installation of Coffee Vending machine for its employees. The machine will cost $12,000 and is expected to generate sales in amounts of $3500, $3600, $3700, and $3800 each of the next four years. WABC has a WACC of 10%. Compute the MIRR
In Cell A1, Type the text WACC. In Cell B1 enter the value 10%.
In Cell A2, Type the text Time, In Cell B2 type 0, in cell C2 type 1, in cell D2 type 2, in cell E2 type 3 and in cell F2 type 4.
In Cell A3, Type the text Cash Flows, In Cell B3 type -12000, in cell C3 type 3500, in cell D3 type 3600, in cell E3 type 3700 and in cell F3 type 3800.
In Cell A4, Type the text MIRR. In Cell B4 enter the following function
=MIRR(B3:F3,B1,B1)
MS Excel MIRR Template
Download this MS Excel Template to find Modified Internal Rate of Return| A | B | C | D | E | F | |
| 1 | WACC | 10% | ||||
| 2 | Time | 0 | 1 | 2 | 3 | 4 |
| 3 | Cash Flow | -12000 | 3500 | 3600 | 3700 | 3800 |
| 4 | MIRR | 15.15% |
Related Investment Analysis Excel Templates
Following is a list of related Excel templates that cover other 5 commonly used investment analysis methodsMIRR Calculator
|
type in the authorization code in the box located below:
|
|
ResultsMIRR = 15.15%
|
|
Input DataPlease enter the reinvestment rate (WACC) aka discount rate: %Please enter the finance rate: % Please enter the net cash flows in the space below: |
|
Instructions
- Enter the series of cash flows in the text box where each of the cash flows is separated by a space. Ensure there is at least 1 positive and at least 1 negative cash flow
- Enter the discount rate referred to as the reinvestment rate (WACC)
- Enter the finance rate which in most cases is the same as the reinvestment rate
