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.