馃攳
Excel Finance Class 103: 2 Ways To Calculate Geometric Mean - YouTube
Channel: unknown
[0]
welcome to finance an excel video number
[1]
written 3 hey if you want to download
[4]
this workbook for chapter 11 click on
[6]
the link directly below the video and
[8]
scroll all the way down to the Excel
[9]
finance class section now let's chapter
[12]
11 but before we do
[13]
chapter 11 I just want to do a little
[14]
video on the historical stock data that
[19]
we saw in chapter 10 and talk about
[21]
geometric mean see a second way to
[23]
calculate geometric mean now we saw from
[26]
history cap small cap stocks return an
[29]
arithmetic mean of 16 point 4 large
[31]
stocks a return of eleven point seven
[35]
that's from the year 2005 to 2008 and 83
[39]
year span also we saw based on this
[43]
historic data a dollar invested in 1925
[47]
in 2008 it grew to nine thousand five
[51]
hundred and fifty-four etc I want to
[55]
show you in this video how to calculate
[56]
geometric mean based on just a begin
[59]
value an end value and the number of
[62]
periods here's our formula pretty
[64]
straightforward equals the end divided
[69]
by the begin now I'm gonna do this
[70]
formula but if the begin is one you all
[72]
only need the end amount but in case the
[78]
amount you're starting with is about one
[81]
dollar then you want to do that and you
[83]
have to raise it to this so this is an
[87]
exponent one divided by the end the
[91]
number of periods so eighty three that's
[93]
the difference between those three in
[95]
essence we're taking the 83rd root now
[98]
that'll give me one plus the geometric
[103]
mean so I'm going to subtract one now
[106]
actually I'm gonna lock this cell
[108]
reference right there so I'm hit that
[109]
click right there and hit the f4 key and
[111]
then enter control enter and double
[114]
click and send it down so we can see the
[116]
geometric mean and the geometric mean as
[118]
we talked about last chapter is always
[120]
going to be less than the arithmetic
[121]
mean unless all the rates are exactly
[125]
the same which they're not now we could
[127]
check so different than last chapter and
[131]
let's remind ourselves what we did last
[132]
chapter
[133]
here is all of the data and I actually
[136]
typed this in from the textbook I didn't
[139]
find a file or anything and there's
[141]
maybe a couple decimals error there but
[143]
nevertheless 83 years of data we can
[146]
this is how we calculated Geo mean
[148]
before right here we calculate Geo mean
[150]
from just two values in the number of
[152]
periods but here's how we calculate from
[154]
all the actual data
[156]
the changes per year that return per
[159]
year period returns so as I saw we have
[163]
to use the sum-product function and then
[165]
the Geo mean the Geo mean the geometric
[170]
mean needs 1 plus all of these rates
[172]
since we actually have the rate so I'm
[174]
going to click there and then control
[175]
shift down arrow scroll back up and we
[179]
have to add one now as we saw last
[181]
chapter the reason why we have to use
[183]
some product is because we're doing an
[185]
array operation and normal formulas
[188]
can't handle it so if we slap it into
[190]
the sum product that's it that's an
[193]
argument that says array it'll actually
[195]
handle the array and then we can
[197]
subtract 1 that's how we did it before
[201]
that gives us nine point nine point five
[204]
four that's for the large company stocks
[206]
now again there's going to be a slight
[207]
difference between here and here because
[210]
we did this I type this in and it's
[212]
probably a couple of errors there on 83
[214]
values nevertheless that was the Geo
[217]
mean form and you can see it there for
[219]
all of the actual returns or values or
[222]
percentage change or proportional
[224]
changes and this was the formula for
[227]
just two beginning and ending the number
[230]
of periods so pretty darn close you also
[232]
want to know yeah the geo means always
[234]
less than the arithmetic mean we could
[236]
also check this right see if we did our
[239]
calculation right I'm going to say this
[241]
times in parentheses 1 plus this is the
[247]
Geo mean now remember the Geo mean is
[248]
different than arithmetic mean because
[250]
it is the true compounding rate to you
[255]
so we simply say 1 plus and then raise
[257]
it caret to the 83 and I'm going to hit
[260]
f4
[262]
to lock that control-enter and we better
[267]
get exactly the same value for each one
[269]
of these and we do right here all right
[271]
so geo mean formula number one based on
[275]
two values and geo mean formula number
[279]
two based on all the actual percentage
[281]
of proportional changes alright see you
[283]
next video
Most Recent Videos:
You can go back to the homepage right here: Homepage