 So in this video, I'm going to show you how to set up a very simple discounted cash flow analysis and Calculate a net present value in Excel Two different ways we're going to go through two different ways of calculating the net present value one way will be basically by brute force which will show you how to calculate the present discounted value of Costs and benefits in each year and then you add them up by brute force to get NPV The second way is using a built-in function in Excel called the PV function. So we're going to go through both of these the Example or the situation that we're going to use is a power plant so that we have an example of a hypothetical power plant okay, and It has some capital cost so some cost to build and it has some and then once it's built it has some annual costs and revenues and so the question is in Present value terms does this plant make any money? Does this plant pass the cost benefit test? Okay, are the present discounted value of all of the benefits equal or all of the revenues really Equal to the present discounted value of all of the costs or the bigger or they small Okay, so I'm going to start off by typing in some numbers here And this spreadsheet will also be posted up on angel if you want to take a look at it So I'm going to assume that the capital cost is Five hundred thousand dollars Okay, so that's how much it costs to build the plant Okay, I'm going to assume that the interest rate here is 10% I'm going to assume that the relevant decision horizon here is five years Okay, so that's the time period over which we're evaluating the profitability of the plant I'm going to assume that its annual output is 4500 megawatt hours of electrical energy Okay, I'm going to assume that it's variable cost. So the cost to produce one megawatt hour is $60 and so the total annual Operating cost where I hear written total variable cost. It's going to be equal to $60 per megawatt hour times the number of megawatt hours, okay, and so what we get is 270 thousand dollars per year that's so much it costs to operate this plant Assuming that it produces 4500 megawatt hours each year Okay, so the sales price for each megawatt hour. I assume it's going to be $90 per megawatt hour and So then we can calculate the annual revenue is equal to the sales price $90 per megawatt hour times the number of megawatt hours and so what we get is annual revenues of $405,000 per year the the stuff below the cost and revenue figures in the spreadsheet is The disk is basically the simple version of the cash flow balance sheet for this power plant. Okay, so you have a Column for capital cost the column for operating cost a column for revenue a column for the annual undiscounted cash flow Okay, or the net benefit a column for the discounted cash flow and then a column here which I have labeled as cumulative net present value which just shows which will show the the Overall net present value of this power plant after each year Okay, and we have Year zero was so the current year All the way down through five years from now Okay, so now we have to fill in these table with numbers so the capital cost Okay, we assume is all incurred only in years zero So you only have to spend money this year to build the plant. Okay, so that's going to be equal to Minus $500,000 you have to be a little careful here when you're doing these things in itself in Excel that costs You have to be careful that costs are negative numbers and Revenues are positive numbers otherwise Excel will mess up your calculations because it doesn't know which one is which Okay, so this plant has a capital cost of $500,000 in year zero and then no capital cost in years one through five Okay, so we're going to assume here that the plant is built in year zero and operates in year one through five So in each of years one through five We're going to have an operating cost equal to Minus two hundred and seventy thousand dollars and that's going to be the same for each year Revenues in each year are going to be equal to positive $405,000 and those are going to be the same for each year oops so the annual undiscounted cash flow is going to be the sum of capital cost operating cost and revenue for each year So I'm going to use the sum function here in Excel Okay summing over those three columns and then so this in Undiscounted terms this plant costs the owner $500,000 in the first year and Then brings in benefits equal to $135,000 a year for five years So now we need to discount this okay, so remember that to Discount some future value back to the present we take that future value and divide by one plus the Interest rate raised to the teeth power where T is the year that we're talking about okay, so we're going to take this and then divide it by One plus And I'm going to lock this cell by putting dollar signs around it Okay, raised to The zero power, okay, and so this is just going to give us minus five hundred thousand dollars because We're raising something to the zero power, so it's just going to be one okay, so and then in This cell here we have next year's future value of a hundred thirty five thousand dollars discounted by one year So this is a hundred thirty five dollars that we would enjoy in one year and that's equivalent to a hundred and twenty two thousand seven hundred and twenty seven dollars and twenty seven cents this year We can do the same thing for years two through five okay, okay, so What the discounted cash flow column tells us is the present discounted value of each year's costs and benefits Okay, what we sometimes want to know is after some number of years How is the plant doing what are what are its? cumulative Present discounted costs and benefits up to that year okay, so to do that We start with year zero minus five hundred thousand dollars okay, and then the cumulative net present value of the power plant after the first year is equal to its net present value after zero years or minus five hundred thousand plus Whatever the present discounted value of the first year's net revenues are okay the cumulative net present value in year two is equal to Whatever the cute whatever the plant was worth Cumulatively at the end of year one which would be this three hundred and seventy seven thousand two hundred and seventy two dollars and seventy three cents plus it's the present discounted value of its net revenues in year two and We keep going here What we find is that at the end of year five the plant the At the at the at the end of year five in present discounted value terms The plant is worth eleven thousand seven hundred and fifty six dollars and twenty one cents Okay, so That's the same thing that you would get if you were to add up all of the discounted cash flows So I'll do that here Okay, so if you add up all of the discounted cash flows right Just following the formula that was in the reading and in the lecture notes Okay, you'll get the same thing as if you count as What you would get in year five if you calculated the cumulative net present value of this plant after every year so That's how you would use Excel to calculate The net present value were to do what we would call discounted cash flow analysis By brute force. Okay, so what we did was we Figured out the cost and revenues each year These cells over here Calculated the undiscounted cash flow Okay Discounted appropriately back to present value terms and then added everything up In Excel, there's a way to do this with a shortcut okay without having to Without without having to Do all of these calculations manually and It is called the P the present value or PV function okay, and so the way that you Would call the PV function is to type equals PV Okay, and then parentheses and then the first thing that it asks you for is the annual interest rate Okay, the number of periods okay, and then the And then the Undiscounted cash flow each year in years One through T so here in years one through five, okay, so We give it that Okay, one of the things about using the PV function is that it Does not understand year zero so it it does all of its discounting starting from year one and So you have to then have to add in year zero okay, oops and The other thing about the the PV function is that it assumes that all numbers are negative so you have to Put a negative in front of the PV sign and When you do that you will see that you get the same answer that we did using the brute force method Okay, there's Another function Okay, called in PV Which you can use to in much the same way that you use? PV except You would use in PV when your annual Undiscounted cash flows are not the same in every year Okay here in our example here the annual undiscounted cash flows were basically the same every year Okay, so the way that you call so here I'll label this Okay, so the way that you call this is NPV okay, and then The the discount rate and then your stream of future Undiscounted cash flows Okay, and just like The PV function the NPV function does not understand year zero So you have to add in anything in year zero separately and you get exactly the same answer