Prof. Dr. Ulrich Anders

Project Investments based on NPV, IRR, MIRR, or ROI

Project Investment Sheet

Investment decisions and risk

Many text books on project management suggest, that a project investment is advantageous if the net present value (NPV) is positive or if the [modified] internal rate of return ([M]IRR) is above an internally set hurdle rate. However, this statement is utterly flawed. The statement may be true in theory but in practice it completely ignores the risk of a project.

The longer a project runs, the later the return cash flows arrive, the higher the investment sum is, the larger is the risk of a project. In longer running project the likelihood simply increases, that something unforeseen is happening, which will negatively impact the project. Therefore, a better (but very uncommon) measure for a project investment is ROI, the return on investment. The NPV of the expected return cash flows is compared with the NPV of the expected investments. The ROI ratio suggests that large investments need to yield corresponding high returns. The ROI should be high enough to cover the risk from unforeseen extra investments or the absence or later arrival of expected returns.

Path dependency and an accounting perspective on projects

Over time many projects become quite unsuccessful and much more expensive than ever expected, believed, or promised. Nevertheless, they struggle on and more good money is invested into such project. You may wonder why? Text books do not give an explanation for this observation. Is this irrational behavior?

No, it is not:

  • The first reason is a concept called path dependency. If you still want or need to achieve the project goal, to cancel the whole project and start from scratch maybe even more expensive than to carry on. The mathematical reason is that costs already invested into a project are sunk costs if they cannot be recovered (which is usually the case). Because sunk costs are lost anyway, they are not taken into consideration when you are re-calculating all the project investment measures at the point in time where you are now. Or in other words: because sunk costs must not be considered, the measures usually get better the closer you come to the project end, even though the project may have a large cost overrun. Therefore, it is perfectly rational to continue a project even though it has become much more expensive than expected or even NPV negative in hindsight. Because otherwise with all the money gone you would have nothing.

  • The second reason results from an accounting perspective. Many project costs are not booked into the Income Statement. Instead they are capitalized in the Balance Sheet as an asset and amortized (depreciated) over the project result’s useful lifetime. The longer the useful lifetime is, the lower is the impact in the Income Statement. A simple example may explain this. Assume project costs of 1 million Euros. This would be the impact in the Income Statement if you book the costs into there. If you, however, capitalize the project costs in the Balance Sheet as an asset and amortize this asset over its useful life time of, say, 10 years, you will have only a 100 thousand Euros impact in your next Income Statement. When possible, most projects are booked into the Balance Sheet because then the project costs do not reduce the current profit so much. However, there is a price for it. Not only are project costs pushed into the future, but one can only have an asset in the Balance Sheet if it has a value. If the project fails and yields no project result, i.e. an asset with zero value, you have to write down your asset all at once. This will then again be a big blow to the Income Statement and can cause even losses in this very year. Typically, no CEO or CFO wants this. So, they carry on the project only to avoid the one time single write down.

Calculations with Excel — be mindful of the formulae

  • Be careful if you use NPV in Excel. Normally, net present value (NPV) means that you are netting the present value (PV) of the return CFs from period 1 and above with the investment in period 0. However, the NPV-function in Excel only calculates the present value of the return cash flows from period 1 and above. You have to net this PV with the investment in period 0 yourself in order to get the NPV.

  • Be careful if you use the definition of MIRR from Excel. Not all cash flows that are negative can be considered investments. You also should not net negative investments with positive return cash flows from the same period. This distorts the MIRR.

Formulae

Please find the formulae for calculating the project investment ratios and the used definitions below:

PV=t=0TCFt×DFtNPV=t=0T(RCFt+ICFt)×DFtFVT=t=0TCFt×AFtDFt=1(1+it)tAFt=(1+it)(Tt)NPVIRR=!0=t=0TCFt×1(1+IRR)tExcel: MIRR=FVT,r[positive CFs]PVf[negative CFs]T1better: MIRR=FVT,r[RCFs]PVf[ICFs]T1ROI=PV[RCFs]PV[ICFs]1\begin{aligned} \text{PV} &= \sum_{t=0}^T { \text{CF}_t \times \text{DF}_t} \\\\ \text{NPV} &= \sum_{t=0}^T { (\text{RCF}_t + \text{ICF}_t) \times \text{DF}_t} \\\\ \text{FV}_T &= \sum_{t=0}^T { \text{CF}_t \times \text{AF}_t} \\\\ \text{DF}_t &= { 1 \over (1 + \text{i}_t)^t} \\\\ \text{AF}_t &= (1 + \text{i}_t)^{(T-t)} \\\\ \text{NPV}_\text{IRR} \stackrel{!}{=} 0 &= \sum_{t=0}^T { \text{CF}_t \times {1 \over (1 + \text{IRR})^t}} \\\\ \text{Excel: } \text{MIRR} &= \sqrt[T]{\frac{\text{FV}_{T,r} \text{[positive CFs]}}{-\text{PV}_f\text{[negative CFs]}}} - 1 \\\\ \text{better: } \text{MIRR} &= \sqrt[T]{\frac{\text{FV}_{T,r} \text{[RCFs]}}{-\text{PV}_f\text{[ICFs]}}} - 1 \\\\ \text{ROI} &= \frac{\text{PV}\text{[RCFs]}}{-\text{PV}\text{[ICFs]}} -1 \\\\ \end{aligned}
CF=Cash FlowRCF=Return Cash Flow (positive)ICF=Investment Cash Flow (negative)PV=Present ValueNPV=Net Present ValueFV=Future ValueDF=Discount FactorAF=Accumulation FactorIRR=Internal Rate of ReturnMIRR=Modified Internal Rate of ReturnROI=Return on Investmenti=interest ratef=financing rater=re-investment ratet=time periodT=final time period\begin{aligned} \text{CF} &= \text{Cash Flow} \\ \text{RCF} &= \text{Return Cash Flow (positive)} \\ \text{ICF} &= \text{Investment Cash Flow (negative)} \\ \text{PV} &= \text{Present Value} \\ \text{NPV} &= \text{Net Present Value} \\ \text{FV} &= \text{Future Value} \\ \text{DF} &= \text{Discount Factor} \\ \text{AF} &= \text{Accumulation Factor} \\ \text{IRR} &= \text{Internal Rate of Return} \\ \text{MIRR} &= \text{Modified Internal Rate of Return} \\ \text{ROI} &= \text{Return on Investment} \\ i &= \text{interest rate}\\ f &= \text{financing rate}\\ r &= \text{re-investment rate} \\ \text{t} &= \text{time period} \\ \text{T} &= \text{final time period} \\ \end{aligned}