A modified IRR calculator finds the modified internal rate of return of a project. It takes the cash flows, a finance rate for the money paid out and a reinvestment rate for the money coming in, and gives a single rate of return that avoids the problems of the ordinary IRR. It also shows the ordinary IRR for comparison.
How to use the modified IRR calculator
- Enter the cash flows from period 0 onward, one per line.
- Enter the finance rate and the reinvestment rate.
- Press Calculate.
The formula
The modified internal rate of return is the future value of the positive cash flows at the reinvestment rate, divided by the present value of the negative cash flows at the finance rate, raised to the power of one over n, minus one [1]. n is defined as the number of equal periods at the end of which the cash flows occur, not the number of cash flows [1]. The present value is taken at the start of the first period and the future value at the end of the last [1].
A worked example
The example has cash flows of −1,000 in year 0, −4,000 in year 1, 5,000 in year 2 and 2,000 in year 3, with a finance rate of 10 percent and a reinvestment rate of 12 percent [1]. The present value of the negatives is −1,000 − 4,000 ÷ 1.10, which is −4,636.36. The future value of the positives is 5,000 × 1.12 + 2,000, which is 7,600. The MIRR is (7,600 ÷ 4,636.36)^(1/3) − 1, which is 17.91 percent [1]. The ordinary IRR for the same flows is 25.48 percent, which is significantly different [1].
Why use it
The ordinary IRR assumes that every cash flow is reinvested at the IRR itself, which can be unrealistic. The modified rate lets you state the rate at which the money can really be reinvested, and the rate at which you borrow. MIRR is used in capital budgeting to rank alternative investments of unequal size, and that it finds only one value [1], where the ordinary IRR can have several when the flows change sign more than once.
Choosing the rates
The finance rate is the interest rate you pay on the money used in the cash flows, such as the rate on a loan. The reinvestment rate is the interest rate you receive on the cash flows as you reinvest them [2]. It is said that interim cash flows should generally be reinvested at the weighted average cost of capital [1], so a company often uses that figure for both. Enter your own rates, per period, in percent.
Timing
The calculator treats the first flow as period 0 and each following one as one period later. If your periods are years, the rates are annual. If they are months, use monthly rates. The number of periods n is one less than the number of cash flows. A project with four cash flows therefore has n = 3.
A simple check
With two cash flows, −100 now and 110 one period later, the MIRR is 10 percent, the same as the ordinary IRR, because there is nothing to reinvest. The two measures differ only when there are interim flows. Try changing the reinvestment rate in the worked example and watch the MIRR move, while the IRR stays at 25.48 percent.
Limits
MIRR, like the IRR, can mislead when projects have different sizes, since a larger project with a smaller MIRR may have a larger net present value [1]. Variants have been proposed to handle this. Use the MIRR with the net present value, and not alone. Microsoft's documentation also requires at least one positive and one negative value [2], and the calculator needs a negative flow first.
Using the results
Compare the MIRR with your required return. If it is higher, the project adds value at your assumed rates. Compare projects of the same size and length, or use the net present value for different sizes. Test your answer by changing the two rates, to see how much it depends on your assumptions.
Entering cash flows
Write each period's net cash flow on its own line, with the outlay at the start as a negative number. If a period has both inflows and outflows, enter the net amount. Periods with no cash flow need a zero, so that the timing is right. For a project that runs for five years, enter six lines, one for year 0 and one for each year after it.
Example to try
Try the flows −500, 200, 200 and 200 with both rates at 8 percent. The future value of the inflows at 8 percent is 200 × 1.1664 + 200 × 1.08 + 200, which is 649.28, and the present value of the outlay is 500, so the MIRR is (649.28 ÷ 500)^(1/3) − 1, about 9.1 percent. The ordinary IRR is a little higher, at about 9.7 percent.
Common mistakes
- Counting n as the number of flows. n is the number of periods.
- Using annual rates for monthly flows. Match the rates to the periods.
- Ranking projects of different sizes by MIRR alone. Use NPV as well.
- Starting with a positive flow. The first flow should be an outlay.
What next
Use this often? Add Modified IRR to your Home screen.
Frequently asked questions
What is a modified IRR calculator?
A tool that finds the MIRR of a project from its cash flows, a finance rate and a reinvestment rate.
What is the MIRR formula?
MIRR = (FV of the positive flows at the reinvestment rate ÷ PV of the negative flows at the finance rate)^(1/n) − 1.
How is MIRR different from IRR?
The IRR assumes reinvestment at the IRR itself. The MIRR uses a reinvestment rate you choose and gives one answer.
What is n in the MIRR formula?
The number of periods, which is one less than the number of cash flows.
What is the MIRR in the worked example?
17.91 percent, against an ordinary IRR of 25.48 percent.
Was this tool helpful?
What went wrong?
We only receive the reason you pick, never what you typed into the tool.
Thanks for your feedback.