Excel Finance Class 103: 2 Ways To Calculate Geometric Mean

Welcome to Finance in
Excel video number 103. OK. If you want to download this
workbook for Chapter 11, click on the link
directly below the video and scroll all the way down
to the Excel Finance Class section. Now this is chapter 11, but
before we do chapter 11, I just want to do a little video
on the historical stock data that we saw in chapter 10 and
talk about geometric means– see a second way to
calculate geometric mean. Now we saw from history cap,
small cap stocks return, and that arithmetic
mean of 16.4%– large stocks return of 11.7%. That's from the year 2005
to 2008, an 83-year span.

Also, we saw, based
on this historic data, a dollar invested in 1925. In 2008, it grew to
$9,554, et cetera. I want to show
you, in this video, how to calculate geometric
mean based on just a begin value, an end value,
and the number of periods. Here's our formula,
pretty straightforward. Equals the end
divided by the begin. Now I'm going to do this
formula, but if the begin is 1, you all only need
the end amount. But in case the amount you're
starting with is not $1, then you want to do that. And you have to
raise it to this. So this is an exponent,
1 divided by the end– the number of periods. So 83. That's the difference
between those three.

In essence, we're
taking the 83rd root. Now that would give me 1
plus the geometric mean, so I'm going to subtract 1. No. Actually I'm going to lock this
cell reference right there, so I'm going to hit that– click right there, and hit
the F4 key, and then Enter– Control Enter. And double click
and send it down. So we can see the
geometric mean. The geometric mean, as we
talked about last chapter, is always going to be less
than the arithmetic mean unless all the rates are exactly
the same, which they're not. Now we could check. So different than last chapter–
and let's remind ourselves what we did last chapter. Here is all of the data,
and I actually typed this in from the textbook. I couldn't find the
file or anything, and there's maybe a
couple-decimals error there. But nevertheless, 83
years of data, we can– this is how we calculated
geomean before, right? Here we calculate geomean
from just two values and the number of
periods, but here's how we calculate from
all the actual data– the changes per year, the
return per year, period returns.

So as you saw, we have to
use the SUMPRODUCT function and then the GEOMEAN. The GEOMEAN. The geometric mean needs
1 plus all of these rates. Since we actually
have the rates, I'm going to click there and
then Control Shift Down Arrow. Scroll back up. And we have to add 1. Now, as we saw last
chapter, the reason why we have to use
SUMPRODUCT is because we're doing an array operation,
and normal formulas can't handle it. So if we slap it into
the SUMPRODUCT– that's an argument that
says array– it'll actually handle the array. And then we can subtract 1. That's how we did it before,
and that gives us [? 9.54. ?] That's for the large
company stocks. Now, again, there's going
to be a slight difference between here and here,
because we did this.

I typed this in. There's probably a
couple errors there in [? 83. ?] Nevertheless,
that was the geomean formula, and you can see it there for
all of the actual returns, or values, or percentage
change, or proportional changes. And this was the formula
for just [? to ?] beginning, and end, and the
number of periods. So pretty darn close. You also want to know–
yeah, the geomean is always less than
the arithmetic mean. We could also check this, right? Let's see if we did
our calculation right. I'm going to say this times,
in parentheses, 1 plus. This is the geomean. Now remember the geomean is
different than arithmetic mean, because it is the true
compounding rate [? to ?] [? you. ?] So we simply say
1 plus and then raise it, caret to the 83. And I'm going to
hit F4 to lock that. Control Enter. And we'd better get
exactly the same value for each one of these,
and we do right here.

All right, so geomean formula
number one based on two values and geomean formula
number two based on all the actual percentage
of proportional changes– all right, see you next video..

test attribution text

Add Comment