Finding modified internal rate of return with Excel MIRR function is restricted as you can only use start of period payments with interest that is compounded periodically or discretely. This limitation forces you to make use of 3rd party add-on to Excel that offer financial functions that are more robust and feature rich. tadMIRR is one such Excel MIRR function that permits you to specify the timings of payments and selection of either discrete or continuous compounding of interest. I will begin this discussion by have a brief look at definition of modified internal rate of return followed by example calculation with tadMIRR Excel function.
Excel MIRR function
Excel MIRR function takes three values the first of which is a series of cash flows, and the second one is the finance rate and the last one being the reinvestment rate. The cash flows are entered with a range of cells such as B1:F1 or as an array of values. The finance rate and reinvestment rates are entered as either a decimal value of say 0.04 or as a percentage such as 4% The following sample data shows you that Excel has calculated a MIRR of 5.450%. As stated earlier this is the periodic rate where interest is compounded discretely and cash flows make start of period payments. Please move down to the second example where I have used tadMIRR function to calculate four different rates of return. This is a 3rd party user defined Excel function that is part of a large library of financial functions called TADXL.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | Excel MIRR | =MIRR(B1:F1, 2%, 2%) | 5.450% | |||
How to use tadMIRR function
Excel MIRR function calculates modified internal rate of return for periodic compounding of interest, in comparison tadMIRR function finds four different rates of return for start and end of period payments and when interest is compounded either discretely or continuously. The following example data that uses the same cash flows from our previous examples shows how tadMIRR has calculated a rate of 5.450% for periodic compounding with start of period payments, a second rate of 5.458% for start of period payments and continuous compounding of interest. The third rate calculated by this function is 4.751% for end of period payments and discrete compounding of interest and the last rate of 4.761% for infinite compounding of interest.
| A | B | C | D | E | F | |
| 1 | Cash Flow | -10000 | 3000 | 3000 | 3000 | 3000 |
| 2 | TADXL MIRR | =tadMIRR(B1:F1, 2%, 2%, 0, 0) | 4.751% | =tadMIRR(B1:F1, 2%, 2%, 0, 1) | 4.761% | |
| 3 | TADXL MIRR | =tadMIRR(B1:F1, 2%, 2%, 1, 0) | 5.450% | =tadMIRR(B1:F1, 2%, 2%, 1, 1) | 5.458% |
Excel MIRR calculator
You can download this Excel template to find modified internal rate of return by using tadMIRR Excel function.
