馃攳
Build your own Financial Planning Spreadsheet (part 6) - CAGR vs average return - YouTube
Channel: Lars Kroijer
[0]
hi welcome back to this mini video
[2]
series where I help you build a
[4]
financial spreadsheet from scratch VM
[7]
done so already please go back and start
[9]
with the first video in this video I'm
[11]
going to be introducing the compound
[13]
annual growth rate or the kegger and and
[16]
explain the difference between that and
[17]
the average annual growth rate this is
[20]
frankly something I should have done
[21]
when I introduced risk to the financial
[23]
model a couple of videos ago but but I
[27]
forgot so there you have it my name is
[30]
Lance Crower
[31]
I'm a former hedge fund manager who's
[33]
written a couple books about finance and
[34]
I'm not doing these videos as a hobby
[36]
I'm not a financial advisor as a police
[39]
make sure you do your own work before
[41]
you do anything I say in this or in any
[43]
other video but let's get back to the
[45]
spreadsheet so here we are back at the
[48]
old spreadsheet I want to start by
[50]
pointing out a mistake that a couple of
[52]
very observant readers that found in the
[55]
spreadsheet if you see down in the data
[57]
table in the both adings retirement and
[60]
adding scenarios case you see the data
[62]
say blade refers to t2 to cell number f9
[66]
up here it should have been f8 which is
[69]
where the standard deviation was it ends
[71]
up not actually mattering because we're
[74]
using a random function and the
[76]
important numbers the input here the 25%
[79]
that we have in column B but but good
[84]
catch in any case but as before I want
[87]
to start by just creating a copy of the
[90]
sheet so we get a new sheet for every
[94]
video and let's call this adding CAG ah
[100]
ok gone and so adding hey god this is
[104]
super critical what we saw in in the
[106]
earlier video when I added the risk to
[111]
the model I kept using the old
[114]
compounded annual growth rate of 5% and
[118]
in reality I should really then I simply
[122]
forgot but I should have had changed the
[125]
number so that we used k-car and for for
[131]
the whole period but for each individual
[133]
a year we should have used the average
[137]
annual growth rate so let me add go back
[140]
to this let me do this
[143]
almost by proof by example and let's
[147]
create a cheat sheet and cheat sheet M
[150]
and that's just just make this copy this
[155]
these cells I find it easier to copy
[160]
some cells from elsewhere then then to
[166]
send them to reformat it's slightly lazy
[168]
so I find it for example easy to copy in
[171]
50% and call it Phi M but so here let's
[177]
make this copy let's do this for three
[179]
years
[179]
and so what I want to see is if you
[182]
start with this one plus the growth rate
[188]
and and you copy this down you see what
[191]
we would end up with so let's see what's
[193]
the average growth rate well this is
[196]
simply the average of the year and of
[200]
the years which is going to be at 5% oh
[203]
then what is the K gar the compound
[206]
annual growth rate compound annual
[209]
growth rate and and what that is is that
[211]
a ending year divided by the starting
[216]
years ^ 1 divided by the number of yes 1
[224]
by 3 minus 1 so this is what you make
[228]
every year in this case it was also 5%
[231]
but I want to show you something kind of
[233]
interesting so let's call this case 1
[236]
now let's do a case 2 let's just copy
[241]
and ok let's copy also the calculation I
[245]
don't have to even copy those over case
[248]
2
[249]
now let's make this for example 10% 10%
[253]
and then minus 5% what you see is again
[258]
the average is 5% but now the k-car is
[260]
lower so the idea that this that that
[263]
the risk in the risk of each individual
[266]
year
[267]
I introduced a lower net effect now of
[270]
course we care a great deal about what
[272]
the ending number is so actually the
[274]
main thing we care about what we see is
[276]
that although our annual average return
[278]
was the same the amount we made from
[280]
start to finish on an annualized basis
[283]
was lower this will become even more
[285]
obvious if we introduce far more extreme
[288]
cases so that's called this case three
[291]
and let's say we do 100% one year then
[295]
we do - fifty percent and then we would
[298]
have to do minus thirty five percent so
[303]
now we see that the average was still
[305]
five percent but you actually lost money
[308]
your compound average gross annual
[311]
growth rate was negative thirteen
[313]
percent so that that's you hope you're
[317]
appreciating that this is super super
[318]
important stuff so when we talked about
[320]
the average equity return which on back
[323]
they had a compound annual growth rate
[325]
of 5% ish the average growth rate in any
[329]
one year if you looked at the margins
[331]
any one year would have been higher
[332]
this is also important because it's
[334]
quite confusing in a lot of sort of
[336]
marketing talk of financial products you
[339]
know these three can this third guy can
[342]
easily say well I earn an average of 5%
[346]
a year to which the Kanner's yeah but if
[349]
I'd given you money and asked how I was
[352]
doing after three years I would have
[353]
lost money so I don't care that the
[354]
average return is 5% I care about the
[356]
compound annual growth rate this is
[358]
super super important stuff and so how
[362]
do we get let's go back to the sheet
[364]
here well course what we want to get is
[366]
really we want to what we want is
[370]
obviously to figure out from start to
[373]
finish how we were doing so let's let's
[376]
do couple cells here and and then I'll
[381]
take this up one and then copy this so
[386]
this is the keg R so this is T C ad R
[391]
and that's let's say my that - then
[397]
and so that's the same as before right
[399]
now what we want is what is the average
[402]
the annual average return and there you
[409]
go and your charges and you have rush
[414]
now here's the formula for that I
[417]
recommend that you google how this is
[421]
done if you're not clear but it's
[422]
basically one plus the keg I squared
[429]
plus the standard deviation as you saw
[432]
before the higher the standard deviation
[435]
the bigger the difference M squared is
[439]
equal to one plus the average return
[446]
squared so if we wanted to do so this is
[452]
the 1 plus the cake R squared plus the
[453]
standard deviation squared equals one
[455]
plus the average search on squared so if
[457]
we want to figure out if we want to
[459]
solve for the average return we can do
[463]
it as follows so this gets a little
[464]
complicated so please try to stay with
[466]
me and so we need two parentheses it's 1
[470]
plus this guy squared plus the standard
[479]
deviation which in this case is 25%
[482]
squared and then if we do the square
[485]
root of that whole thing so that's to
[487]
the power of 0.5 that should get us that
[491]
if we got a minus 1 plus F 7 squared F
[496]
10 squared we got a minus 1 that looks
[503]
wrong so let's see here so that is
[509]
clearly enough I forgot to the power of
[515]
2 there you go
[517]
so that's right so let's copy this into
[519]
the minimum risk so what we see now is
[521]
that the minimal risk asset there is no
[524]
difference because there's no standard
[526]
deviation in the return
[529]
the annual average return the difference
[532]
is about 3% so the kegger is fine
[535]
the the annual average is almost 8 what
[538]
we'll see if we change the standard
[539]
deviation to for example 15% the
[541]
difference would be smaller for me if
[543]
this serial up there would be there
[545]
would be no difference right so so that
[548]
kind of makes sense so this is important
[550]
because what we'll see out here in the
[552]
equity return they depend on the annual
[556]
average what are you expecting any one
[559]
year and if you have a keg of 5% and a
[562]
risk of 2010 deviation of 25% your
[564]
annual average would be about would be
[567]
about 8 so I hope I know this is tricky
[570]
stuff but it's critically important also
[573]
next time someone tries to tell you and
[575]
go back here try to tell you that they
[577]
make an average of 5% or 10% per year if
[580]
you really ask them what their keg are
[582]
the combine annual growth rate is over a
[584]
long period of time and so the only one
[588]
last thing this is really cleaning up
[591]
stuff and but what we should also
[593]
emphasize is that this case here is
[596]
really the engine of the model the main
[600]
case really is in all the iterations and
[603]
main case you can call the calculated
[606]
case and but really what we care about
[609]
is remove this out of there we'll really
[612]
care about is what happens this not if
[615]
you do it one time but if you do this a
[617]
thousand times and so make this equal to
[621]
this so now you see these are all the
[626]
iterations so with all this with a
[630]
scenario output we call it that such
[636]
good soaps and this is the underlying on
[641]
the second Bowl so this is really the
[643]
core of the model so so there we are and
[647]
now we've incorporated the kegger
[649]
correctly we're saying 8% a year what
[652]
you see is let's do just one recalculate
[655]
over all this and what you see is that
[657]
the numbers are now substantially
[659]
different than they were before you see
[661]
the mean
[662]
number is 800,000 it's going to vary
[665]
every iteration it's not going to vary
[666]
that much because we're doing it a
[668]
thousand times you see every time I
[669]
press it it does change but it hasn't
[671]
changed dramatically to call that round
[673]
about seven seven hundred and seventy
[676]
five thousand in the case before we saw
[679]
the mean down here was four hundred and
[681]
sixty seven press that a couple of times
[684]
will see it change a little bit but
[686]
obviously makes it a material difference
[688]
that in any one year and we expect
[691]
instead of a five percent return we're
[694]
expecting an eight percent return which
[696]
is the which is the right number for for
[700]
this calculation so thanks for watching
[704]
I hope that was interesting and useful
[706]
you can subscribe to my channel if you
[708]
want to hear about future videos or
[710]
share this video with your friends on
[712]
social media if you think they would
[714]
benefit from watching it but in any case
[715]
I hope to see you in the next video
Most Recent Videos:
You can go back to the homepage right here: Homepage