馃攳
Excel Finance Class 79: Investment Criteria: NPV, IRR, Payback, AAR, Profitability Index - YouTube
Channel: unknown
[0]
welcome to finance and Excel video
[1]
number 79 hey if you want to download
[4]
this workbook for chapter 8 click on the
[6]
link directly below the video in scroll
[8]
way down to the finance excel section
[10]
hey yeah this is the last video in
[12]
chapter 8
[13]
talking about in vestment criteria
[16]
should we buy a particular asset we've
[20]
looked at individually different methods
[22]
we've talked about the net present value
[23]
internal rate of return pay back
[25]
profitability index and average
[28]
accounting return we're going to look at
[29]
them all together in one example so
[32]
here's our time here's our cash flows
[35]
and here's the net income for each
[38]
period well net present values we've
[41]
talked about throughout this chapter is
[44]
the best method and we have our lucky in
[47]
Excel we have the net present value
[48]
function we give it our required rate of
[52]
return this is our discount rate this is
[54]
the rate which includes the risk comma
[56]
and we're going to discount back our
[58]
estimated future cash flows when you're
[62]
using net present value start at time
[64]
one and go forward do not include time
[66]
zero and then you subtract the cost so
[71]
this is the net present value and
[72]
subtract the Casso because that's
[74]
negative I put a plus
[82]
now the IRR is closely related and we
[86]
can use it here because we do we have
[87]
conventional cash flows which means
[89]
there's a negative and all the rest are
[91]
positive and we're not comparing
[93]
mutually exclusive projects so we can
[95]
simply highlight all of the cash flows
[98]
and get this IRR is great because you
[100]
can include x zero and all the ones for
[103]
it and it tells you the internal rate
[105]
that's the rate innate to these
[108]
particular cash flows that parallel in
[111]
our bond chapter we said hey here's our
[113]
cash flows from our bond what is the
[115]
yield to market that meant the rate
[118]
inherent in those particular cash flows
[120]
so it means we'd earn fourteen percent
[122]
right well if our hurdle rate is
[129]
required rate is 15 percent are we going
[133]
to invest in these in this project no by
[136]
the way here - it was what this is
[139]
negative net present value is negative
[141]
that means if you took on this project
[143]
you would decrease the value of the firm
[144]
so know now payback we're gonna have to
[149]
calculate payback and it's our in
[154]
advance we said if this project pays
[157]
back within three years then we will
[159]
take it right this method doesn't take
[164]
into consideration time value money or
[166]
risk or even whether value is added but
[168]
nevertheless people do use it it's
[170]
oftentimes used for smaller projects and
[177]
what we need to do is figure out how
[180]
quickly it pays back
[181]
well there's $150,000 outlay and then
[184]
each year we get this period so I'm
[185]
gonna start by going negative this and
[187]
I'm gonna say minus this so the first
[193]
year after the first year we recover
[195]
thirty eight thousand five hundred and
[197]
that's how much we have left to collect
[198]
now I'm going to say from that I want to
[202]
subtract this second cash flow in this
[204]
formula I can copy down because it's
[205]
always going to look from the period
[207]
before and subtract the appropriate
[209]
amount so I'm going to drag this all the
[211]
way down and the first negative says
[213]
well somewhere during this
[215]
year I'm going to get paid back now
[218]
that's how I did it in the video before
[220]
I'm going to show you an alternative
[221]
here equals minus this and now I'm gonna
[228]
say minus the sum of this one and I'm
[234]
gonna hit shift colon and close
[236]
parentheses now that's a funny formula
[238]
b3 to b3 but watch this if I click right
[241]
there and hit the f4 key that locks it
[244]
that means now this is got one range
[247]
that's lot one cell reference in the
[249]
range that's locked on one that's not
[250]
this is called an expandable range let's
[254]
see how this works
[255]
control-enter and i drag it down that
[258]
means I got to go up here and lock this
[264]
one too
[265]
OOP control-enter double click and send
[269]
it down I get the same answers with a
[271]
little bit less trouble and creating my
[273]
phone it's now let's see how this works
[274]
b3 remain locked but this one is allowed
[278]
to move relatively so in its expanding
[281]
range when I come down here and hit f2
[283]
you can see the green is expanding hit
[286]
here f2 f2 to put in edit mode the green
[289]
range is expanding alright that's a
[292]
better formula for this particular
[294]
calculation alright now we know it got
[297]
paid off almost in year 3 but somewhere
[299]
into your 4 so the way we do this would
[302]
say hey that's year 3 and how much cash
[306]
what amount was left to pay back after
[310]
year 3 I'm going to add well this amount
[314]
and we're gonna divide by which means
[316]
compare to and this is year four we need
[319]
so we come up here to four and click
[321]
there right it assumes an an even pay
[324]
back which name it may not necessarily
[326]
be true but for estimation purposes we
[330]
can say that it took three point nine
[332]
years to pay this back so again while
[338]
this is no because no we got to pay back
[340]
within three years so three point nine
[342]
no way the profitability index equals
[348]
net present value cause we calculate the
[350]
net present value at 15% , and all of
[354]
the cash flows 1 and forward but for
[356]
profitability index unlike net present a
[359]
straight net present value we don't
[361]
subtract the cost we divide by the cost
[364]
and since it's negative I need to do a
[366]
negative sign there 0.98 remember the
[372]
profitability index rule says less than
[374]
1 don't accept greater than 1 we accept
[377]
now I usually like to go and do
[382]
profitability index minus 1 so I do
[384]
equals and control-v whoops and then
[389]
minus 1 and this tells us the decimal
[394]
equivalent or the proportion or if we
[396]
had a percent the percentage value added
[399]
so we lose in essence two pennies for
[402]
every one dollar of cost finally we have
[405]
our accounting return we already have
[406]
our net our net income for each year so
[409]
we need to average that and then we need
[411]
to figure out what the average Book
[415]
value is and again we're assuming
[418]
straight-line salvage value of zero so
[422]
that means we can just take the original
[423]
cost divided by two and that gives us
[425]
the average Book value so I'm going to
[427]
say equals average in earlier video we
[431]
did this and we did it in multiple cells
[434]
and showed you how it was all calculated
[435]
but I'm going to do it all together here
[436]
so those are all that's the net income
[438]
and that's the denominator sorry that's
[442]
the numerator the one on top divided by
[444]
and now we need to say in parentheses
[448]
negative this so I don't okay so I get a
[451]
positive divided by two and in earlier
[453]
video I showed you why that calculation
[456]
works so three point four is our average
[460]
accounting return now if we've set in
[463]
advance just like we did with a payback
[465]
right we set this in advance for
[466]
accounting average accounting return our
[469]
required is 0.3 so we say yes for this
[473]
measure here but this one all the rest
[476]
are no and as we mentioned in earlier
[478]
videos the accounting one does not just
[481]
like the payback
[482]
neither one of these consider the time
[485]
value of money they don't consider the
[487]
risk and they don't tell us really how
[489]
much value is added so no surprise that
[492]
one gave us a yes but this is the last
[495]
video and we've looked at all these
[496]
different methods there are many
[499]
different methods this one is the
[501]
preferred method because it looks at
[504]
time value money risk and tells us how
[506]
much value is added that one's the best
[508]
but out in the working world people use
[510]
all of these and often times they do
[513]
multiple calculations right and if all
[515]
of these are saying no and this one's
[517]
saying yes you probably are going
[518]
probably not a good idea to invest in
[521]
this project all right lots of fun in
[524]
chapter 9 we'll see a next chapter
Most Recent Videos:
You can go back to the homepage right here: Homepage