There's an NPV function that I'll show you here in a minute. This calculates the net present value of an investment by using a discount rate that the user provides and a series of future payments. The important thing here is the future payments. Remember the real definition of net present value that you learn in business courses, in finance courses, it includes the present value, but the NPV function does not include any present values. So we have negative values, those are payments, and income cash flow are going to be positive values. So the arguments here in the NPV function, we have our interest rate. Again, be sure to divide by 12 if it's an annual percentage rate and you're looking at monthly rates. Value1 is the cash flow in period 1. In the future, this is not a present value. Then you can have optional additional arguments, these are additional cash flows in subsequent periods. It's important here that these are sequential periods. The second argument, other than rate, value1, is the cash flow in period 1. If you don't have a cash flow in period 1, you have to make sure that this is zero, you input zero for value1. So the NPV function does not include any present values, as I just mentioned, those must be added in outside the NPV function, and I'll show you this in the example that I worked through. So the NPV function in Excel is only calculating this portion of the real NPV definition. Let's work through an example in Excel. I have this in a starter file called NPV for net present value. We have our cash flow diagram that I already introduced. We have non-constant cash flows, so that means different values and sporadic cash flows. So it's not a constant payments or expenses, and so we can't use the typical FV, PV, and payment functions to analyze this. Instead, we have to set up something where we can analyze the present value or the net present value. I've got a bunch of cash flows here. Basically, I took the cash flows from this diagram and I put them in here as a function of the month. What we need to do, is we need to calculate a column over here, that's going to be the present value. I'm going to show you how we can do this by setting up a column of present values. But then at the end, I'm going to show you how we can just use the net present value function to do this analysis quite quickly. But I think it'll be educational for you to understand how or what the net present value function is returning in Excel. The present value, we can always take our cash flow for that year. By the way, I've already named cell B3, "int", so we can use that name quite easily. We're going to discount any future cash flows by taking a cash flow and dividing by one plus the interest. So this interest rate is an annual interest rate, I have to divide by 12 because I have monthly cash flows here, and we're going to raise that denominator to the power of the period. Again, what I'm doing is I'm using this equation here to discount any future values to a present value in terms of today's money. So I'm going to take that to the exponent of the month. So I'm going to start with today, so that's month 0, and the present value of a cash flow today is 3,000, so that should be the same. Because I set this up as a relative reference, I can just double-click on this to copy that formula down. What this means, for example, is that in the 11th month, if we have to pay, it's a negative cash flow of $3,000. In today's terms, right now, this is $2,800.65. In other words, if we took $2,800.65 and invested it at this interest rate up here for 11 months, it would be equal to $3,000. So that's another way of looking at this. So we have our present values, we've taken each of our future values and normalize it down to a present value so we can compare apples to apples. Now what I'm going to do is down here in the net present value cell, I'm just going to sum, this is just going to be a sum of everything above it. It's a net, meaning sum of the present values. What this means is the net present value of this situation here, we have our positive cash flows and negative cash flows, is about $3,300.60 in today's money, present value. There's a net present value function. I would encourage you all to set up your spreadsheets so that they have a present value column, I think it's a lot easier to work with and to visualize. Alternatively though, you can just use a single net present value function. I'm just going to replicate this down here. This is our Excel's function, just so we can compare them. I can type in NPV. Now the rate, the rate is going to be our interest rate divided by 12 again, because we have monthly cash flows, and then we have our values. So I can just go in here and I can select the cash flows. Now there's an error with this, and maybe some of you are going to catch this. But if I highlight all of those cash flows and press "Enter", we don't get the same thing. Remember what I said about the NPV function, the first value, value1 of the NPV function is actually the first period after today. So that's not what I did, I actually included the first value as my cash flow today or the zeroth month. So what I have to do is I have to not include that. So I'm going to not include the first cash flow. We start with the first period in the future, and then I can press "Enter". But that only gives me future discounted cash flows. What you have to do is outside of the NPV function, you have to go back and add in the present value of today. So I could use either C6 or D6, I'll just choose C6. So that means the present day value, the NPV, net present value is 3,360, that's exactly what we got by summing all the individual present values. Again, if you just want to use the NPV function down here, we don't rely on column D at all, so that's not even needed. But I would encourage you all to set up an equivalent column here for present value cash flows. Hopefully, you learned a bit about cash flows and net present value in this screencast. Thanks for watching.
