0:13

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.

Â 2:03

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.

Â 3:07

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.

Â 4:11

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.

Â 6:04

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.

Â