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..