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.