[MUSIC PLAYING] In the last segment, we saw a table showing the calculations for net present value from a stream of benefits and a stream of costs. We did that for the tree planting example that we've looked at a few times now. Now, this time, I'm going to show you how to do those calculations in a spreadsheet. So it's worth paying attention to these spreadsheet examples and trying to replicate them yourselves because spreadsheets are a remarkably useful tool for an applied economist. And using them well is definitely a skill that's worth acquiring. So let's get started. This spreadsheet is going to have six columns. So I'll start by entering in the headings-- year, benefits and dollars per hectare. Once again, I'm being careful to make sure I put in the units of measure. Next is costs and dollars per hectare, net benefits, and dollars per hectare, discount factor, and then the present value of net benefits, also in dollars per hectare. OK, I'll mark those columns and widen them to fit the size of those labels-- don't need to be quite that wide. And I think for these ones, I'll make them a little bit narrower again-- adjust the widest one that one. Now I'll put in the year-- 0. And then I'll have a formula here-- equals that 0 plus 1-- and then copy it down. Give ourselves 20 years, just like we have in that example. I'm just going to make these headings bold. Now the benefits-- I'm going to cheat a bit here. Rather than stretch out this video by entering in every single benefit and cost number, I'm just going to copy them from another spreadsheet where I have already entered them. There they are-- copied, paste. You could pause here in the video and enter them in to your spreadsheet. And I'm going to carry on now and do the formula for net benefits, which is a very simple one-- equals benefits minus cost. There we go. I can drag that down. Now the discount factor-- now to calculate the discount factor, I need the discount rate. And you might remember in the previous spreadsheet demonstration, I put my parameters over here. Parameters are numbers that don't change as you go down the columns of the spreadsheet, the rows of the spreadsheet. So I'm going to do that for my discount rate. I'm going to use a discount rate of 10%. That would be too high if we were discounting for a government decision. But it's probably realistic for many farmers. Now the formula for the discount rate equals 1 divided by-- open brackets-- 1 plus the discount rate-- press F4 to put the dollar signs in, close brackets-- to the power of the year. There it is. And I can drag that one down. So the discount factor is going to get multiplied by the net benefits to give us the present value of those net benefits. So equals net benefits times discount factor-- there it is. And now, I can copy that down. Now the net present value is simply the sum of all of those present value numbers. So equals sum-- open bracket-- marking all of those-- close brackets. And there it is. So the net present value for this example is minus $107. So this is the example where-- I'm just going to mark this and put a line there to de-markate it. So this is the example where the upfront cost is $600, and the overall net present value is negative. So once you allow for the costs at that level, the costs outweigh the benefits once the benefits get discounted back to the present. But you'll remember-- we looked at another example where the costs were only $450. And the net present value was positive $43. So it's not a very high positive net present value, but it is at least positive. So the benefits do slightly outweigh the costs. And as we saw, when I change the upfront cost-- any cost or benefit in year zero-- that year is not discounted. Remember-- over here, this discount factor is one. Any cost here or benefit here is going to get multiplied by one. And so once it'll end up in there unchanged, then that will get added up. So the number here will go up or down by whatever benefit or cost you include in there. This is $43. If I increase the cost by $150 up to $600, the net present value falls by $150 down to minus $107. So there you have it. Now having done that, we can explore a whole range of potential other changes. So for example, let's just remember that we've got minus $107 as a net present value at the moment. If I was to make this a 5% discount rate instead of 10%, then we've gone from a negative of a bit over $100 up to a positive net present value of $300 per hectare. So the discount rate is a really important consideration. And the value of the discount rate can make quite a large difference to the conclusions that you reach about whether the benefits outweigh the costs, especially if you have a long time frame and the benefits occur a long time after the costs. So in this particular example, most of the benefits are occurring after year 10. And the biggest of the benefits is actually in year 20. And so these are getting discounted quite heavily. So even with a discount rate of only 5%, the discount factor in year 20 is 0.38, say. So that $400 is counting only at about $160 in the present value terms. So there we go. Of course, we can change any of these benefits and any of these costs, and see what effect that has on the net present value. And that's a really valuable thing to do in a spreadsheet, to do sensitivity analysis where you vary your parameters to see, particularly the ones that you're uncertain about, to see whether they have an effect on the result. And if they do have an effect on the result, you can think about how realistic those changes are and how much confidence you've got in any particular result. Now having completed that process and gone through the calculation of net present value in detail, I can show you an easier way. I think it was worth going through the full process to help you understand how net present value is calculated. But we can take advantage of the NPV function that is provided in Excel. But there is one trick to be aware of, so I'll show you that. So I'm going to click here in cell D23 and type in equals NPV-- open brackets. Then it's asking me for the rate. I'll click over here on I2, comma, then mark the range of values. But I'm not going to include the value for year zero. I'm including the range from years 1 to 20. Close brackets, and then plus the value for year zero. That's the trick-- you don't include year zero within the net present value calculation. Because Excel automatically assumes that the first year is discounted, whereas the value in year zero shouldn't be discounted. And you can see that we've got the same value, $106.68 as we calculated going through the whole process in detail-- gives us some confidence that we did it correctly. So normally, that's the way you would do it. But just be aware of that trick with year zero. [MUSIC PLAYING]