EME 460
Geo-Resources Evaluation and Investment Analysis

Net Present Value, Benefit Cost Ratio, and Present Value Ratio for project assessment

PrintPrint

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 Worth Revenue or Saving @i*Present Worth Costs @i*

Or

NPV=Net Present Worth Positive and Negative Cash Flow @i*

Or

NPV=Present Worth of All Cash Flows @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).

NPV function in Excel
Click for the transcript of "NPV function in Excel" video.

PRESENTER: In this video, I'm going to explain how to use NPV function in Excel to calculate the NPV of a cash flow. There are two main ways of calling NPV function in Excel. The first method is clicking in this little Fx here. When you click that, this window pops up, and then you can search the NPV function in this box.

You click Go. It finds the NPV function, or you can go to the Financial category here and you will find the NPV function. And then you click OK.

You can see three boxes here. The first one is asking you to enter the Rate. This rate is the interest rate that you're going to discount your cash flow when you calculate the NPV. You can write a number here, which is going to be 10%, or you can rate these from a cell, which I wrote 10% here. Then after, we can click this one or we can push Enter.

And then after, you can enter the cash flow. You can enter the cash flow one by one, in each of these values, or you can choose the cash flow as a series here. Then you click here. If you entered everything correctly, the NPV is going to be calculated and the function shows the NPV here. If something is wrong, you won't see a correct NPV here. Then I click OK. So as you can see, the NPV is calculated and shown.

The other way to call the NPV function is you just write equal sign, and then write NPV open parentheses, the first one, and then you can see did this thing pops up. In the first, you need to enter a rate. You can write a value here-- you can write 10%, or you can read it from this cell. And then you write a comma, and then you enter the values. You start from here, go all the way to the end of the cash flow. And you close the parentheses.

So one thing that you have to be very careful using the NPV function in Excel is NPV function in Excel always considers you are entering your cash flow from year 1. So if you have the cash flow that is happening at the present time, at time 0-- at year 0-- you have to enter that manually. NPV function in Excel does not consider any cash flow at time 0-- it doesn't understand a time 0. NPV assumes that you are entering everything from year 1.

So you can always double-check the result of this NPV function. If you calculate the present value of each of these payments, the summation of that discounted cash flow should be equal to this net present value. Let's quickly calculate that. Let's say we want to calculate the present value of these payments. The first one equals $50,000 divided by 1 plus interest rate. I put a dollar sign behind the column to fix this when I'm going to apply it to the other cells, and power, year.

So as you can see here, this is the present value of this $50,000 of investment. I apply that to the other cells. So the summation of this discounted cash flow, these present values, should be exactly the same as the NPV that we calculated using the NPV function in Excel, which you can see they are exactly the same.

So let's work on another example that has the cash flow at present time. So as you can see in this investment, we are going to have $60,000 of investment at present time, and also $50,000 of investment at year 1. This investment is going to yield the annual income of $24,000 a year, from year 2 to year 10. Let's see how we can calculate the NPV of this cash flow using the NPV function in Excel.

So as you can see here, because we are going to have a payment at present time, we need to enter that payment manually. So what we do is, we write the equal sign and then we add this payment, which is happening at the present time, or year 0. And then after, we use the NPV function for the rest of the cash flow. I write NPV-- I rate the interest rate from here. Then I select the cash flow, starting from the year 1 all the way to the year 10. I close the parentheses, and I press Enter. So this is the NPV, using the NPV function of this cash flow.

Let's double-check our result. Let's see if this is correct or not. So I'm going to calculate the present value of all these payments, and then the summation should be exactly same as this NPV-- using the NPV function. So present value equals this payment-- it is happening at the present time, so it doesn't need to be discounted. So it is equal same amount-- present value of this, $50,000, equals $50,000 divided by open parentheses 1 plus interest rate. I fix the column to make sure it doesn't change-- to make sure interest rates sale doesn't change when I'm going to apply to the other cells. I close the parentheses, and power, year. So this is the present value of this-- $50,000 happening at year 1.

So I apply this to the rest of cash flow, and the summation of this discounted cash flow should give me the exact same value as the NPV-- that I used the NPV function in Excel. As we can see, these are exactly the same.

So these are two ways of calculating NPV using Excel, and how we can double-check the other one.

Credit: Farid Tayari

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).

IRR function in Excel
Click for the transcript of "IRR function in Excel" video.

PRESENTER: In this video, I'm going to explain how to calculate rate of return for a given cash flow using IRR function in Excel. There are two ways that we can call IRR function. First, we can click on this fx icon. This window pops up, and we can write IRR in this box, which is a search box. Go, it finds it. Or, we can choose the financial from this list and find the IRR.

When we choose IRR, this window opens up. And it has two boxes. The first box needs you to enter the cash flow. There's a very important point in calling Excel IRR function. That requires you to enter the cash flow, and it assumes your cash flow starts from year zero. So you should be very careful using IRR function in Excel. It assumes your cash flow starts from year zero or present time.

So I select the cash flow starting from year zero. And I click here or press Enter. So here, it already calculated the IRR. But sometimes, the IRR function cannot find a rate of return. So you need to give it an initial guess. If you don't have any assumption in your mind, just give it 10%. If still it doesn't calculate it, give it 20%, and so on. And you press OK. So as you can see here, the rate of return for this cash flow, starting from year zero, is 14.06%.

The other way to call the IRR function in Excel is just writing the IRR function. You write the equals sign, and then you write IRR. You can see Excel shows this here, shows the function here. You open the parentheses. You select the cash flow from starting from year zero all the way to the year 10. A comma, and the initial guess, which is going to be 10%. And the result.

So we can always double-check this result that we calculated rate of return using Excel IRR function. As you know, rate of return is a rate that makes NPV equal zero. So if I calculate the NPV for this rate, it should be exactly zero, or very close to zero. So let's see. Let's calculate the NPV for this rate.

So because we are going to have a payment at present time, I have to enter that payment manually. And then I have to calculate the NPV of the rest of the cash flows that start from year one using the NPV function.

NPV, open parentheses. Rates. I choose the rate. Comma, and then I select the cash flow that starts from year one. And I close parentheses. And you can see the NPV equals zero. So it shows that the rate of return that I calculated for this cash flow is correct.

Credit: Farid Tayari

Example 3-6:

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

i*=10%: NPV=60,00050,000*( P/ F 10%,1 )+24,000*( P/ F 10%,1 )*( P/ A 10%,9 )=$20,196.88 i*=15%: NPV=60,00050,000*( P/ F 15%,1 )+24,000*( P/ F 15%,1 )*( P/ A 15%,9 ) =$3,897.38

If using spreadsheet, following method can be more convenient:

i*=10%: NPV=60,00050,000*( P/ F 10%,1 )+24,000*( P/ F 10%,2 )+24,000*( P/ F 10%,3 )+... +24,000*( P/ F 10%,10 )=$20,196.88 i*=15%: NPV=60,00050,000*( P/ F 15%,1 )+24,000*( P/ F 15%,2 )+24,000*( P/ F 15%,3 )+...  +24,000*( P/ F 15%,10 )=$3,897.38

Figure 3-5 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.

Screenshot of Excel doc illustrates calculation of NPV function
Figure 3-5: Calculating NPV in Microsoft Excel
Credit: Farid Tayari

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*.

Benefit Cost Ratio=PV of Net Positive Cash Flow/PV of Net Negative Cash Flow
Equation 3-1

Equation 3-1

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*.

Present Value Ratio ( PVR )=NPV/PV of Net Negative Cash Flow
Equation 3-2

Equation 3-2

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 3-7

Calculate the B/C ratio and PVR for the cash flow in Example 3-6.

i*=10%: B/C Ratio=24,000*( P/ F 10%,1 )*( P/ A 10%,9 )/[ 60,000+50,000*( P/ F 10%,1 ) ]=1.19  project is economically satisfactory at i* = 10% PVR = NPV/[ 60,000 + 50,000*( P/ F 10%,1 ) ]=0.19  project is economically satisfactory at i* = 10%

Figure 3-6 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.

Screenshot of Excel doc illustrates calculation of NPV function
Figure 3-6: Calculating B/C in Microsoft Excel
Credit: Farid Tayari

Figure 3-7 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.

Screenshot of Excel doc illustrates calculation of NPV function
Figure 3-7: Calculating PVR in Microsoft Excel
Credit: Farid Tayari

i*=15%: B/C Ratio=24,000*( P/ F 15%,1 )*( P/ A 15%,9 )/[ 60,000 + 50,000*( P/ F 15%,1 ) ]=0.96  project is not economically satisfactory at i*=15% PVR = NPV/[ 60,000 + 50,000*( P/ F 15%,1 ) ]=0.04  project is not economically satisfactory at i*=15%