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

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.

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.