Welcome to Finance in

Excel, video number 79. Hey, if you want to download

this workbook for Chapter 8, click on the link

directly below the video. And scroll way down to

the Finance Excel section. Hey, this is the last

video in Chapter 8, talking about

investment criteria. Should we buy a

particular asset? We’ve looked at, individually,

different methods. We’ve talked about the net

present value, internal rate of return, payback,

profitability index, and average accounting return. We’re going to look at them

all together in one example. So here’s our time. Here’s our cash flows. And here’s the net

income for each period. Well, net present

value, as we’ve talked about throughout this

chapter, is the best method. And we are lucky. In Excel, we have the net

present value function. We give it our required

rate of return. This is our discount rate. This is the rate which

includes the risk, comma. And we’re going to discount

back our estimated future cash flows. When you’re using net present

value, you start at time one and go forward. Do not include time zero. And then you subtract the cost. So this is the

net present value. And subtract the cost. So because that’s

negative, I put a plus. Now, the IRR is closely related. And we can use it here, because

we have conventional cash flows, which means

there’s a negative and all the rest are positive. And we’re not comparing

mutually exclusive projects. So we can simply highlight

all of the cash flows. And get this– IRR is great, because you

can include time zero and all the ones forward. And it tells you

the internal rate. That’s the rate innate to

these particular cash flows. The parallel in our

bond chapter– we said, hey, here’s our cash

flows from our bond. What is the yield to market? That meant the rate inherent

in those particular cash flows. So it means we’d earn 14%. If our hurdle rate,

required rate is 15%, are we going to invest

in this project? No. By the way, here,

too, it was what? This is negative. Net present value is negative. That means if you

took on this project, you would decrease

the value of the firm. So no. Now payback– we’re going to

have to calculate payback. And in advance we said, if this

project pays back within three years, then we will take it. This method doesn’t take

into consideration time value of money or risk, or

even whether value is added. But nevertheless,

people do use it. It’s oftentimes used

for smaller projects. And what we need to do is figure

out how quickly it pays back. Well, there’s a $150,000 outlay. And then each year

we get this period. So I’m going to start

by going negative this. And I’m going to say minus this. So the first year– after the

first year, we recover 38,500. And that’s how much we

have left to collect. Now I’m going to

say from that, I want to subtract

this second cash. So on this formula

I can copy it down, because it’s always going

to look from the period before, and subtract

the appropriate amount. So I’m going to drag

this all the way down. And the first

negative says, well, somewhere during this year

I’m going to get paid back. Now, that’s how I did

it in the video before. I’m going to show you

an alternative here. Equals minus this,

and now I’m going to say minus the

sum of this one. And I’m going to hit

Shift-colon, and close parentheses. Now, that’s a funny formula– B3 to B3. But watch this. If I click right there and

hit the F4 key, that locks it. That means now this has

got one range, one cell reference in the range that’s

locked and one that’s not. This is called an

expandable range. Let’s see how this

works Control-Enter, and I drag it down. That means I’ve got

to go up here and lock this one too– boop! Control-Enter, double-click

and send it down. I get the same answers, with

a little bit less trouble in creating my formulas. Now, let’s see how this works. B3 remained locked. But this one is allowed

to move relatively, so in its expanding range. When I come down

here and hit F2, you can see the

green is expanding. Hit here, F2, to

put in [INAUDIBLE].. The green range is expanding. All right, that’s

a better formula for this particular calculation. All right, now, we know it got

paid off, almost in year three but somewhere into year four. So the way we do this, we would

say, hey, that’s year three. And how much cash– what amount was left to

pay back after year three? I’m going to add–

well, this amount. And we’re going to divide

by, which means compare to. And this is year four. We need to come up

here to year four. And click there. It assumes an even payback,

which may not necessarily be true. But for estimation

purposes, we can say that it took 3.9

years to pay this back. So again– well, this

is no, because we’ve got to pay back within three years. So 3.9, no way. The profitability index

equals net present value, because we calculate

the net present value at 15%, comma, and all of the

cash flows, one and forward. But for profitability

index, unlike a straight net present value,

we don’t subtract the cost. We divide by the cost. And since it’s negative, I need

to do a negative sign there. Oh– 0.98. Remember the profitability

index rule says less than one, don’t accept. Greater than one, we accept. Now, I usually like

to go copy and do profitability index minus one. So I do equals and

Control-V– whoops– and then minus one. And this tells us the decimal

equivalent or the proportion– or if we had a percent,

the percentage– value added. So we lose, in essence,

two pennies for every $1.00 of cost. Finally, we have our

accounting return. We already have our net

income for each year. So we need to average that. And then we need to figure out

what the average book value is. And again, we’re assuming

straight line salvage value of zero. So that means we can just

take the original cost divided by two. And that gives us the

average book value. So I’m going to

say, equals average. In an earlier

video, we did this. And we did it in multiple

cells and showed you how it was all calculated. But I’m going to do

it altogether here. So that’s the net income. And that’s the denominator– sorry, that’s the numerator,

the one on top, divided by. And now we need to say, in

parentheses, negative this. So I get a positive

divided by two. In an earlier video, I showed

you why that calculation works. So 3.4 is our average

accounting return. Now, if we’ve set in advance– just like we did

with the payback, we set this in advance for

average accounting return. Our required is 0.3. So we say yes for

this measure here. But this one, all

the rest are no. And as we mentioned

in earlier videos, the accounting one does not– just like the payback,

neither one of these consider the time

value of money. They don’t consider the risk. And they don’t tell us, really,

how much value is added. So no surprise that

one gave us a yes. But this is the last video. And we’ve looked at all

these different methods. There are many

different methods. This one is the

preferred method, because it looks at time

value of money, risk, and tells us how

much value is added. That one’s the best. But out in the working world,

people use all of these. And oftentimes, they do

multiple calculations. And if all of these are saying

no and this one’s saying yes, you probably are going,

probably not a good idea to invest in this project. All right, lots of

fun in Chapter 9. We’ll see you next chapter.

very clear! Thank you.

Its great! its very clear and easy to understand. Thank you,

Thank you it was really easy to understand!!!

I can't find the xls file on the website! Please write out the specific url! I could not find busn233ch08.xls

Thank You

Thank you. but how to calculate the irr for this problem. discount rate is 10% ( you have an investment of 100000$ (year 0 or base) and for 1st year cost 10000 , benefit 25000 . 2nd year cost 10000, benefit 50000. 3rd year cost 10000, benefit 50000.4th year cost 10000, benefit 50000. 5th year cost 10000, benefit 50000. thank you

Awesome video, very clear. Saved me a lot of trouble!

excellent explanation, thank you!

mdikfjwp;jgrejfjd

wfwg