MIRR v IRR
LED Potlights Compared with Halogen Ones
No measure of financial return is absolutely accurate. I’ve argued extensively that before you greenlight any project, your head, your heart and your gut must all be in alignment, and that everything humans do–whether they are deciding to get married or build their next great startup–is, ultimately, an act of faith.
Having said this, some financial analysis (not of getting married, of launching a new project) is necessary to satisfy your head.
I typically use IRR, Internal Rate of Return, but some of my students argue that MIRR, modified IRR is better. Maybe.
MIRR assumes that a project’s positive cashflows are reinvested at the organization’s cost of capital. However, in my experience, the reinvestment of +ve cashflows is a fiction.
Certainly, for individual investors, they consume it more often than save it or invest it. The same can be also true of corporations, not-for-profits or charities and is almost certainly true of most NGOs and government departments.
Look at Google and Apple: they have tens of billions of dollars of cash on their balance sheets getting 0.7% pa returns on average. If you use 0.7% pa, it will skew MIRR… in fact, MIRR and IRR will be almost exactly the same.
I don’t like formulas where you are arbitrarily asked to specify exogenous data. If you are using excel, it will ask you to spec the finance rate and reinvestment rate before it renders a value for MIRR. Then guess what? Try it again with a different value for your finance rate or even failing to specify one. MIRR doesn’t change. What gives?
I was working with a young engineer I coach in real estate. He works for Natural Resources Canada in green technology. We were trying to establish ROI for various green initiatives yesterday including comparing LED potlights verus halogen ones.
What we found is that while LED potlights are (currently) more expensive (at least in Canada)–$46.50 each versus $31.48–they save a huge amount of power. In my home office, each type of potlight produces 90 watts of lighting, but while the halogen source consumes 90 watts, the LED potlight only uses 7 w. So over a year, the halogen lamp is using 252 kw-hrs while the LED potlight is only consuming 19.6.
At 17.5 cents per kw-hr, running each halogen potlight costs $44.10 per annum. The LED version? Just $3.43.
Here are Ontario Hydro rates:
So what’s your ROI on the higher initial cost of LED v halogen lamp?
Well according to my calculations, your return on LED potlight installation is a ridiculous 271.9% pa.
This sort of look s right. If I told you you could invest $15 and get an annualized series of payments of more than $40, you’d: a) jump for joy, b) understand at a molecular level that you are seeing more than a two hundred percent return.
How does MIRR perform? Not well. If I spec a 2.5% finance rate and a reinvestment rate of 6%, MIRR tells me my ROI is just 61.1%, which just doesn’t jibe with what I am seeing in terms of cashlfow shown above.
If I use a 0% finance rate or don’t specify one at all (the green highlighted area above), MIRR doesn’t change at all.
I’m sticking with IRR, folks.
Anyway, again my three tests before you start a project or launch a new product are:
-do some analysis to satisfy your head (using IRR, MIRR if you prefer or at least do a cap rate calculation or estimate the project’s NPV, net present value)
-make sure you are passionate about it, ie, your heart is in it
-trust your instincts; if your gut is telling you, “Whoa,” listen!
@ profbruce @ quantum_entity
postscript: I wrote more about IRR here, https://www.eqjournal.org/?p=2462. You can also read more about MIRR v IRR here, https://www.brighthubpm.com/project-planning/96355-irr-v-mirr-valuation-methods/.
postscript: these calculations do change somewhat if you include bulb replacements. But since LED lamps last a lot longer than halogen ones (see table below), more bulb changes just make LEDs look better and better. If you have 20 halogen potlights in your home now, you will get $5,853 in benefits (ie, cash!) over the next seven years if you replace them (today!) with LEDs.
Finally, to settle the MIRR v IRR debate, I turned for help to former student Robin Chahal. Here’s what he had to say:
I think you figured out how to break MIRR, Prof Bruce! … or at least the MIRR
code in MS Excel.
I noticed a really minor issue with your MIRR calculation but fixing it
didn’t help.
I suspect MIRR calculations aren’t working when IRR is over some
threshold.
I tried another example scenario:
Prof Bruce lends $100 and
Robin pays Prof Bruce 100% interest annually. On the 10th year, he pays 100%
interest plus the principle.
IRR is 100%
MIRR is 27.1%
???
I’ll need to debug this one. 🙂
0
$(100.00)
1
$100.00
2
$100.00
3
$100.00
4
$100.00
5
$100.00
6
$100.00
7
$100.00
8
$100.00
9
$100.00
10
$200.00
IRR
100.00%
MIRR @ 0%
27.10%
MIRR @ 100%
100.00%
I think we need a simpler example to look at.
Let’s take two
scenarios to test the difference between IRR and MIRR.
In both scenarios, Prof Bruce lends Robin $100 for 10 years at 10% annual interest.
Scenario #1:
Robin pays Prof Bruce back the $100 plus interest at the end of the 10 year
term.
0
$(100.00)
1
$-
2
$-
3
$-
4
$-
5
$-
6
$-
7
$-
8
$-
9
$-
10
$259.37
IRR
10.00%
MIRR @ 0%
10.00%
MIRR @ 10%
10.00%
In scenario #1, there is no
cashflow, so all the returns are being re-invested and the interest is
compounding.
IRR is 10%, MIRR is 10%.
Scenario #2: Robin pays Prof Bruce annual installments for the interest so that the interest doesn’t
compound, and the principle is paid back at the end of the term.
0
$(100.00)
1
$10.00
2
$10.00
3
$10.00
4
$10.00
5
$10.00
6
$10.00
7
$10.00
8
$10.00
9
$10.00
10
$110.00
IRR
10.00%
MIRR @ 0%
7.18%
MIRR @ 10%
10.00%
With MIRR, we can calculate the
return with different re-investment rates for the positive cashflow.
We’ll
do one calculation with a re-investment rate of 0% (assuming the money is
consumed and not re-invested) and with a re-investment rate of 10% (simulating
the theory that IRR assumes the re-investment rate is the same as IRR).
At a
0% re-investment rate, there is no compounding effect and MIRR is 7.18%.
At
10% re-investment reate, the MIRR is 10%, the same as the IRR.
In both
scenarios, the IRR is 10%, indicating that the IRR calculation can’t
differentiate between the two.
I was going to try to do a
mathematical proof but I quickly realized that I’ve completely forgotten how to
do them. 🙂
Happy Easter!
Robin
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.