How does the MIRR Really Work?

Introduction

The MIRR is the Modified Internal Rate of Return method that has had its own Excel function for quite a while now. What is it, though and how does it work?

The MIRR helps us to program a capital budgeting case in which the cost of capital and the reinvestment rate of a project are different. That is, the IRR function assumes that all finance is invested and reinvested at the WACC (weighted average cost of capital), whereas MIRR says that the WACC is one thing but the reinvestment rate is probably lower and it gives rise to further cash inflows.

Look at this basic example: as with many of my examples, they are small and uncomplicated so that we don’t get lost in a massive tangle of formulas. If your problem is bigger, my example should help you to work on that by yourself.

A very short lived project with an initial investment of 100 and cash flows of 160 and 70 for the following two years respectively. Assuming initially that the WACC (r) and the reinvestment rate (k) are the same:

NPV 103.31, IRR 95.76% and MIRR 5684%

The information in column D shows us how to derive the MIRR. Work down that column to see the initial cost, year 0, then year one’s cash flow is compounded at r for one year, since it has been received at the end of year one in a two year project and year two’s cash flow becomes year one cash flow just derived plus year two’s cash flow of 70. Note year two’s cash flow in not compounded since it has been received at the end of that year.

I then show the CAGR (compound annual growth rate) by using the formula you can see explained in cell F70.

Let’s introduce k to the example now:

We put the 7% value of k in the MIRR formula and we also put it in the formula for cell D66 and then we see that both MIRR and CAGR have changed to 55.31% from 56.84%.

Finally, here is the fully worked solution to a three year project for the purchase of Laptops … with two further elements for you to try for yourself. Note, I have used k in this example and not shown the results using r alone.

These examples are deliberately simple so recreating them should not be a problem. Hence no file available for downloading

Duncan Williamson

16th March 2020