Welcome to Finance in Excel, video number 45.
Hey, if you want to download this workbook for Chapter 6
or the PDF for Chapter 6, click on the link
directly below the video.
And then scroll all the way down to the Finance Class section.
Hey, Chapter 6-- we're going to talk mostly about bonds.
What are bonds?
Debt.
That means you're taking out a loan.
Now, there's always two sides to every transaction.
One person's liability or debt is another person's asset.
So we'll talk a lot about bonds.
And we'll start off by talking about interest
rates and inflation.
All right, interest rates--
we're going to talk about real versus nominal rates.
Now real rates, this is the percentage change
in buying power.
And we'll see a great example of that in just a moment.
These are rates that have been adjusted downward
for inflation.
Nominal rates-- this is like the rate
you earn on your bank account.
These rates have not been adjusted downward
for inflation.
This is the nominal rates, or the percentage change
in the number of dollars that you have.
Now, before we even go in and look at an example,
let's just talk about what money is.
Money is as money does.
Well, that just means the piece of paper isn't the thing.
It's what it can do.
So it's better to define money by its function.
You can buy stuff with it.
You get paid a wage.
You can save it.
So here are the three components,
or the three aspects of money.
Money acts as a medium of exchange.
Right-- without money, what would we do?
We baked bread.
And someone else made shoes.
So if we wanted shoes, we'd have to go
and hunt for someone who was willing to trade bread
for shoes.
That would be hard to do sometimes.
All right, medium of exchange.
A standard of value--
I always think of it as a standard or a gauge of value,
or somehow a measure.
So money is the unit in which the prices of goods
and services are measured.
For example, 20 units for a sweater--
I just use units because money could be anything, pennies,
dollars, pounds, whatever.
So you have 20 units for the sweater.
And this toy over here costs 10 units.
Thus, you know that two toys equals one sweater--
so a standard or a gauge of value.
And finally, it can be a store of value, right?
A person can hold the money and use it later.
Hopefully they're putting it in the bank
and earning an interest rate, which
is what we're interested in.
We want to see what happens when you save it.
Let's first look at an example that concerns milk.
And from this example concerning milk,
we're going to see inflation and the real rate,
which is the change in your buying power.
So here it is--
January 1, 2010, milk costs $3.39.
One year later, it costs 3.56.
Oh, the price went up.
That's called inflation.
So milk inflation, in this simple example-- by the way,
calculating inflation is incredibly complex
if you're doing it for the whole United States.
This is just a simple example to get the idea.
And it will help us illustrate the relationship
between real rates and nominal rates and inflation.
All right, so we started right here.
This was 3.39.
This is where we ended up.
So we're going to use our formula we've been using ever
since Chapter 0.0 in this class, for proportional change
or percentage change--
the end divided by the begin minus 1.
So we get this.
Ah-- so that tells us the inflation rate.
Now, what if we go out on the 10th and buy 100 cartons?
So we take this amount times 100.
And this example here will help us figure out the real rate.
So we have $339.
That's our purchasing power right now,
is to buy 100 cartons.
So what if we didn't buy them, but we put it in the bank
and earned 10%?
That's the nominal rate.
We're going to use N=1, so nominal.
This is what we earn on our savings.
So APR nominal is 10%.
January 1, 2011-- the future value
will be-- that means the amount we have after one year, 372.
Now, the extra interest added in here is nominal, right?
It doesn't take into consideration--
because we've increased our money by 10%.
But what we're interested in is the purchasing power.
So the question is, we earn 10%.
But can we buy 10% more cartons of milk?
Can we buy 110 cartons of milk?
Well, let's just look and see.
Here's the amount we earned, right?
That's our original amount plus all the inflation.
And we're going to divide it by the new milk cost.
This will give us the exact number of units, right?
So now we have 104.
Well, that certainly is 110, right?
So the change in buying power--
we compare this 4.75 extra number of cartons
to our original 100, and we get the change in buying power.
Now what's nice about this example is, it's nice.
It's concrete.
We're doing number of units, right?
We earned a nominal rate of 10%.
But our buying power for milk went up only by 4.75.
Now, we can express this relationship
with something called the Fisher.
Irving Fisher was a great economist
in the early part of the 20th century.
In fact, what he wrote about too much debt and what can result
in a financial crisis exactly predicted--
he wrote this 100 years ago--
exactly predicted what happened in our financial crisis.
Anyway, he came up with this relationship--
one plus the nominal equals one plus the real times
one plus inflation.
And there's our variables there.
So we calculated an H of 0.05, this right here.
So we can go ahead and plug this in to this equation.
And we knew the 10% we earned on our bank account.
And then we can solve for this.
And what do we get?
Exactly the same thing.
This is the same as before when we did our milk example,
and counted the number of cartons we could buy.
Now, what's great about this formula, of course,
is you can solve for any one of these.
So down here--
I'll just show you a little deductive proof.
We start there.
And then we solve for R. So that's the formula for R, real.
And we'll use that in just a moment.
We start here.
We solve for-- and we get this formula for H, and also R. R,
we just take minus one on the other side.
Now, another way to manipulate this
is if you multiply this out here, so one times one is one.
One times H is H. R times one is R. And R times H is there.
Well, then we subtract this from both sides.
We get one minus one.
So we're left with just these.
And these component parts tell you what makes up nominal.
So within our nominal rate that we're earning somewhere,
there's the H. This is the compensation for decrease
in value in original amount invested.
The R, which is the real rate.
And this little teeny extra bit here
is the compensation for decrease in value of interest earned.
Now, when the inflation rate is very small,
this will be very small.
And oftentimes, they use this as an approximation.
Whatever the inflation plus our real rate,
that gives us approximately what the nominal is.
That's our second example.
Let's go over to Excel.
And I want to do this example here.
Here's our same example we did in the PDFs-- milk now
and 100 units.
I want to multiply this and figure out what in the world--
that's the amount we're going to put in the bank today.
Here's our 10%.
So I'm going to say OK, no problem.
I'm going to put that money in.
That buys us 100 cartons.
And our formula for future value given N equals one
is simply one, which always represents the principal,
plus the interest rate.
That always represents the extra interest
added to the principal.
So now we can estimate our inflation.
And this is a slightly different calculation.
I don't think we did this.
Oh yes-- we did do this one over in the PDFs.
But simply, we take end--
that's the amount one year out, the end divided by the
begin, and subtract one.
That's always our formula for proportional-- or in our case,
we have it formatted as percent, so percentage change.
Now we have our H.
Now let's go ahead and--
this I just typed in here, 110 cartons.
But again, all I did is I took the 100 cartons
and I multiplied by the 10%.
That is the amount--
if we could buy 110 new cartons, then our actual buying power
change would have been 10%.
But no, we want to figure out how many actual cartons.
So we say equals the total amount we have in our bank
divided by the new price.
This total amount right here is the 10% interest we earn.
That's the nominal interest.
And Enter-- Control-Enter.
So now we can see, we can buy an actual number of cartons, 104.
So change in buying power is simply
number of cartons divided by--
where's my cartons?
Oh, right there.
That gives us 1.5, sorry, minus one.
What?
I got a different number here?
Oh, don't get tricked.
This is Excel.
So that's just formatting.
So I'm going to go increase the decimals up here.
And we can see, there is our change in buying power.
Now, that's our R, change in buying power, the real rate.
Well, let's use our Irving Fisher formula.
Equals an R-- we simply take one plus big R, which is nominal
divided by one plus inflation.
One plus our nominal divided by one plus our inflation rate.
And then subtract one.
Notice, this looks suspiciously like some end,
some begin, and subtract one.
So it does look suspiciously like our percentage change
or proportional change formula, right?
So with that in mind, let's go back to our PDFs
and look at the same example from a slightly different point
of view.
And we'll see that our Fisher formula actually
does look like a percentage change formula.
Here's our original milk cost.
Here's the inflation.
So milk cost $33.39.
And one year later, it cost $3.56.
In our last example, we took this and multiplied it by 100,
like we were buying 100 cartons of milk.
But what if we just took this amount and just said hey,
we bought one.
Could we figure out what this amount
is if we put it in the bank?
So we multiply it by one plus the normal rate.
We get $3.729.
We could then compare that amount--
this is the milk original cost plus 10%.
This is the milk original cost plus the inflation, which
is the actual ending price.
So here's our one plus the nominal rate.
This is one plus the inflation.
Subtract one, we get the same thing.
So if you can picture that, just one single item,
imagine if the milk cost $1.
Then you could just see this formula.
And this looks just like the Fisher formula, right?
Instead of putting it in dollars I left the dollar off,
because one times anything is just the thing.
And we're left with one plus the nominal rate divided
by one plus the inflation rate minus one.
So that's the way I like to think of the Fisher effect.
And it's the percentage change in buying power.
So if we were to rewrite it, in textbooks
we usually see it as--
we saw it up here, right?
But I always think of it just like a percentage change
formula, at least when I'm thinking about the real rate.
So there it is.
Again, I think of this top part as amount
you would earn in one year at a nominal rate.
And this bottom part is what you could purchase
given an inflation rate.
R is the percentage change in buying power.
So let's look at a couple examples.
Here are two examples.
We're going to look inside the PDFs.
But I'm going to go over to Excel.
And I'm going to click on the sheet three, four, and five.
All right, so let's use our formula, our Fisher formula--
percentage change in buying power.
There is our rate we could earn, nominal.
There's our inflation, equals--
oh, yeah, one plus the nominal divided by one
plus the inflation rate.
Oh, and then subtract one.
And even though this isn't in terms of time
the end amount and the begin amount,
I can picture it that way because this is just
adding just the inflation.
And this is the nominal.
OK, so we do that formula.
And boom-- it tells us the increase in purchasing power.
And of course, this is important,
because if you're investing you want to at least try and beat
the inflation rate.
I'm shooting this in 2010.
And this is an interesting time, because inflation is 1%, right?
So if you want to increase your real purchasing power,
it doesn't take much to beat that about 1% inflation rate.
Now, let's look at two other examples.
What if we could earn 1% on our savings cap,
but inflation went up?
What would the rate be here?
One plus nominal divided by one plus inflation,
and then subtract one.
Whoa-- negative.
So right now, in 2010, some savings accounts
are just barely paying 1%.
And inflation is about 1, maybe a little bit above.
This is 1.5%, right?
So this scenario is real.
It even becomes more real if you think sometimes,
if you're investing in stocks or bonds,
what if you have a terrible year and your return, which is
a nominal return, is negative?
Then this situation definitely could
happen, which means your real purchasing power goes down.
Now, notice, we start at--
we have a nominal rate of one, and then an inflation of 1.5.
But what about slightly reversed?
The nominal rate-- so we can earn 1.5% on our savings
account.
But inflation goes up by 1%.
Notice the difference between both of these is a half
a percent, right?
Yeah, half a percent.
So let's go ahead and see what our change in purchasing power
is, which is the real rate.
So the one plus nominal divided by one,
plus our inflation minus one.
Hmm.
You would think that these would be exactly the same, right?
But the difference is, this is going down.
We have a loss.
And so this number is going to be a little bit smaller
than going up.
We had inflation of one.
But we earned a little bit more in our nominal rate.
And this is always true.
Anytime you're going up, you have the potential
to think of it in huge terms.
Can you increase 100% going up?
Yes.
How about 200%?
Sure, going up.
Over 10 years, your portfolio of investments
could go up by 100 or 200, 300.
But going down, you never can go down further than 100 percent.
So that's why even though the distance
between nominal inflation, nominal inflation
is both a half a percent, this one
has to be smaller than this one because this one is going up.
All right, that's a little bit about interest rates, real
and nominal.
When we come back in our next video,
we'll start talking about bonds.
All right, see you next video.