Prof. Dr. Ulrich Anders


2019-09-28

Project Calculation Formulae

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}

Be careful with Excel

  • 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 later 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 later. 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.

© Prof. Dr. Ulrich Anders

Revision: Oct 04, 2020 (04:38)
Browser: undefined

Imprint · Data Protection