Net Present Value (NPV)
As explained in the first lesson, Net Present Value (NPV) is the cumulative present worth of positive and negative investment cash flow using a specified rate to handle the time value of money.
$$NPV=Present\text{}Worth\text{}Revenue\text{}or\text{}Saving\text{}@i*Present\text{}Worth\text{}Costs\text{}@i*$$Or
$$NPV=Net\text{}Present\text{}Worth\text{}Positive\text{}and\text{}Negative\text{}Cash\text{}Flow\text{}@i*$$Or
$$NPV=Present\text{}Worth\text{}of\text{}All\text{}Cash\text{}Flows\text{}@i*$$If the calculated NPV for a project is positive, then the project is satisfactory, and if NPV is negative then the project is not satisfactory.
The following video, NPV function in Excel, explains how NPV can be calculated using Microsoft Excel (8:04).
In the video NPV and IRR in Excel 2010 (8:59) you can find another useful video for calculating NPV using Excel NPV function. In this video cash flow is formatted in the vertical direction (there is absolutely no difference between vertical and horizontal formatting, using spreadsheet).
In the following video, IRR function in Excel, I'm explaining how to calculate the Rate of Return for a given cash flow using Microsoft Excel IRR function (4:19).
Example 36:
Please calculate the NPV for the following cash flow, considering minimum discount rate of 10% and 15%.
C=60,000  C=50,000  I=24,000  I=24,000  ...  I=24,000 


0  1  2  3  ...  10 
C: Cost, I:Income
$$\begin{array}{l}i*=10\%:\text{}NPV=60,000\u201350,000*\left(P/{F}_{10\%,1}\right)+24,000*\left(P/{F}_{10\%,1}\right)*\left(P/{A}_{10\%,9}\right)=\$20,196.88\\ i*=15\%:\text{}NPV=60,000\u201350,000*\left(P/{F}_{15\%,1}\right)+24,000*\left(P/{F}_{15\%,1}\right)*\left(P/{A}_{15\%,9}\right)=\$3,897.38\end{array}$$If using spreadsheet, following method can be more convenient:
$$\begin{array}{l}i*=10\%:\text{}NPV=60,000\u201350,000*\left(P/{F}_{10\%,1}\right)+24,000*\left(P/{F}_{10\%,2}\right)+24,000*\left(P/{F}_{10\%,3}\right)+...\\ +24,000*\left(P/{F}_{10\%,10}\right)=\$20,196.88\\ i*=15\%:\text{}NPV=60,000\u201350,000*\left(P/{F}_{15\%,1}\right)+24,000*\left(P/{F}_{15\%,2}\right)+24,000*\left(P/{F}_{15\%,3}\right)+...\text{}\\ +24,000*\left(P/{F}_{15\%,10}\right)=\$3,897.38\end{array}$$Figure 35 illustrates the calculation of the NPV function in Microsoft Excel. Please note that in order to use the NPV function in Microsoft Excel, all costs have to be entered with negative signs.
Benefit Cost Ratio
Benefit Cost Ratio (B/C ratio) or Cost Benefit Ratio is another criteria for project investment and is defined as present value of net positive cash flow divided by net negative cash flow at i*.
Equation 31
For the project assessment:
If B/C >1 then project(s) is economically satisfactory
If B/C =1 then project(s) the economic breakeven of the project is similar to other projects (with same discount rate or rate of return)
If B/C <1 then project(s) is not economically satisfactory
Present Value Ratio
Present Value Ratio (PVR) can also be used for economic assessment of project(s) and it can be determined as net present value divided by net negative cash flow at i*.
Equation 32
If PVR>0 then project(s) is economically satisfactory
If PVR=0 then project(s) is in an economic breakeven with other projects (with same discount rate or rate of return)
If PVR<0 then project(s) is not economically satisfactory
Example 37
Calculate the B/C ratio and PVR for the cash flow in Example 36.
$$\begin{array}{l}i*=10\%:\\ B/C\text{}Ratio=24,000*\left(P/{F}_{10\%,1}\right)*\left(P/{A}_{10\%,9}\right)/\left[60,000+50,000*\left(P/{F}_{10\%,1}\right)\right]=1.19\text{}\\ \text{projectiseconomicallysatisfactoryat}i*\text{}=\text{}10\%\\ PVR\text{}=\text{}NPV/\left[60,000\text{}+\text{}50,000*\left(P/{F}_{10\%,1}\right)\right]=0.19\text{}\\ \text{projectiseconomicallysatisfactoryat}i*\text{}=\text{}10\%\end{array}$$Figure 36 illustrates the calculation of the B/C function in Microsoft Excel. Please note that you need to use the absolute value in the denominator or multiply the answer by 1.
Figure 37 illustrates the calculation of the PVR function in Microsoft Excel. Please note that you need to use the absolute value in the denominator or multiply the answer by 1.