Welcome to Highline BI348 Class Video Number 22.
If you want to download this workbook BI348Chapter02start
or the finished file, click on the link below the video.
We've got a great video here about geometric mean,
which is a way to calculate the average compounding rate.
Now this is another one of those calculations
that we did in our prereq class, but it
is just too common and useful not to go over again.
Now here's the situation.
We have an investment.
And we put an initial amount in, $15,000 for Investment 1
and $5,000 for Investment 2.
And we waited 10 years.
And then boom.
We got $25,447 for the investment 1,
about $11,300 for Investment 2.
And we need to know which one had the better return.
Well, first off, we can calculate
for both of these investments the growth rate
or the percentage change.
You take the end amount-- notice this
is at the end of one year-- divided by the begin amount,
the initial amount we invested.
Another way to think of these two terms, End and Begin,
in terms of financial terminology.
This is the future value.
That's the present value.
We take those two, divide them, and then subtract 1.
That's our formula for percentage change.
Now there's other formulas for percentage change.
They will give you the same exact answer,
and this formula is shorter and easier to create.
So there we go.
I'm going to copy it down.
I go to the last cell and hit F2, just to verify.
End divided by Begin.
Subtract 1.
And boom, there it is.
Now we can do the same calculation over here.
End divided by Begin, subtract 1.
Control Enter.
So we have two columns of percentage changes or growth
rates.
Now you might think that we could
use the mean to calculate this.
But compounding is a multiplicative process, not
an additive process.
This means the average function, or the mean calculation,
will always overestimate.
And actually, our textbook does a good job of mentioning this.
You've got to watch out.
If you're in the financial industry
and someone tries to use the arithmetic mean
to represent an investment, it's always going to overestimate.
The proper calculation is the geometric mean.
Now I'm going to show you two ways.
If you know the Begin and the End amount,
and you know the total number of periods,
then we have two different ways we
can calculate the actual geometric mean
or average compounding rate.
And also, if you only have the percentage changes or growth
rates, there is a separate formula we can use for that.
Now our formula-- and this is straight
from our prerequisite class, Business 210-- for calculating
this geometric mean, if we know the end, which is boom,
and the begin, we take that amount.
And we actually have to raise it to an exponent.
But the exponent is 1 divided by the number of total periods.
So in parentheses, 1 divided by total number
of periods, close parentheses.
And from that, we subtract 1.
And when I enter it, there's the true average compounding
rate or geometric mean.
And we can clearly see that it is slightly smaller.
Now we can do this for Investment Number 2.
We take the End amount divided by our Begin amount.
Close parentheses.
And by the way, we're forcing this division
to calculate before our exponent.
Because in Excel exponents are calculated before division.
That's not just Excel, that's regular math.
And then we need to do another division, 1 divided
by the total number of periods.
And then subtract 1.
So Investment Number 2, 0.08501.
And we can clearly see this is smaller
than the arithmetic mean.
And now we have a measure that will
allow us to compare our two investments in terms of return.
Investment Number 2 had a better return.
Now that's a pretty complicated formula.
There is a brand new awesome function, in Excel 2013
and later, called Equals RRI.
And it wants the total number of periods,
which is our 10, comma.
And then it calls it present value and future value.
The present value was the original amount invested,
comma.
The future value is the amount that we got after 10 periods.
Now if you know financial functions in Excel,
they've been around a long time.
And there's a lot of functions that
use present value and future value as their arguments.
This function is different.
In all other financial functions,
you have to represent cash flow out with a minus.
But for some reason in 2013, when
they invented this function, they
didn't follow that same convention.
So we just put the two numbers in as positives,
close parentheses.
And you've got to be kidding me.
That is a lot easier than this formula right here.
We can do the same thing our.
RRI for Investment Number 2.
Total number of periods is 10.
Comma.
Present value, that's the amount we invested.
Future value, that's the amount we withdrew after 10 periods.
Close parentheses.
Control Enter.
Now both of these formulas work when
you have the begin and end amount
in total number of periods.
But oftentimes we don't have those numbers.
We only have the percentage change or growth rate.
And by the way, we're doing an investment example here.
This works for population growth rates and all sorts
of other rates of change.
So think about that.
When we calculate average, right, we actually
just add them all up and divide by the count.
So usually we're dealing with all of the individual numbers.
Well, there's definitely a formula for that.
The one caveat, though, is that we
can't use the geometric mean function on the actual growth
rates.
We have to add 1 to each one of these to get the growth factor.
So Equals GEOMEAN.
And I would like to simply highlight all of these,
but I really need to add 1.
And if I can't add an extra column, watch this.
I'm going to do an array calculation, plus 1.
Now an array calculation in Excel
means you're doing some calculation,
whether it's math or comparative operator,
on more than one item.
And that is a range of values.
By doing plus 1, you're telling Excel
to add 1 to each one of the numbers in this range.
Now this is an array formula.
So in order for Excel to understand correctly,
we have to use that special keystroke, Control Shift
and Enter.
Now for a second we have a 1, but you
could see the decimal part is exactly correct.
But remember, when we enter an array formula with Control
Shift Enter, we can only guarantee
that Excel understood, and that we entered correctly,
by looking up to the formula bar.
When you see your curly brackets,
you know Excel understood this was an array calculation.
Now I'm going to hit F2, because really we
need to subtract 1 from it.
And that's the complete formula.
Now this minus 1 is not an array calculation.
It's just internally, because Geometric Mean is spitting out
a single number.
And then we subtract 1.
We still have to do Control Shift, Enter,
so the inside part of the Geometric Mean function
understands.
Control Shift and Enter.
Now I can do the same calculation over here, GEOMEAN.
I highlight all the numbers.
I need to add one.
I'm going to be tricky.
I'm going to do an array calculation.
Close parentheses, minus 1.
Control Shift Enter.
Now there we did an array calculation.
Oftentimes you do have space to just add a 1.
So you can simply add an extra column and add 1, copy it down.
And now, of course, equals GEOMEAN.
We simply highlight the percentage changes or growth
rates already have 1 added, minus 1.
There's no array calculation here,
so just Enter or Control Enter.
Now I want to prove that these are actually
the correct average compounding rates.
And the way we can do that is guess what?
If I take the initial amount, multiply it
times this average compounding rate or geometric mean,
and compound it the correct number of times
I should get exactly the ending amount.
So you ready?
Equals-- here's the start amount--
times in parentheses, 1 plus our compounding rate,
close parentheses.
And then we have to compound it, so we carry it 10 times.
Enter.
Sure enough, I get exactly the correct answer.
Now we can also use the built-in function, FutureValue.
It wants to know the period rate-- that's
this amount here-- per year.
The average compounding rate.
Comma.
Total number of periods, 10.
Comma.
Payment is like when you're making monthly payments.
We didn't.
Instead we threw in a lump sum and just let it grow.
So we skip this argument with a comma.
And there's that present value.
And you've got to know cash flow.
When you put the money into the bank, it is out of your wallet
and into the bank.
So you have to put a minus.
That's our initial investment.
We do not need the type argument here.
The type argument is only when you have periodic payments.
So we simply close parentheses and Control Enter.
We get exactly the same answer.
Boom.
Now we can do it for our second investment.
Equals our Begin amount times in parentheses, 1
plus our Geometric Mean.
And we have to raise it to the total number of periods.
And boom.
We get exactly the same answer.
If we want to use Excel FutureValue
function, our compounding rate-- that's actually a period rate,
our period is a year-- comma.
NPER 10.
Skip payment.
The present value, the cash is going out when we initially
invested in the bank.
5,000 as a negative.
Close parentheses.
And Control Enter.
So the Geometric Mean.
We saw one, two ways to do it, when
we have to begin end amount and number of compounding periods.
And we actually saw two different ways to do it,
if we have all of the growth rates or percentage change
amounts.
Next video we'll talk about variation.
We'll see you next video.