Welcome to Excel in finance video number 28.
Hey, if you want to download this workbook for chapter 5
or our PDF notes for chapter 5, click on the link
below the video, and then scroll down to the finance section.
Then you can download these files.
Hey, this video 28, we got to talk about interest rates,
and we got to talk about APR, which is required by the Truth
in Lending Act.
Annual Percentage Rate.
And then the actual real rate, or effective annual rate.
And then we're going to see a bunch of cool problems
where knowing how to go from APR to effective rate
or effective to APR or period rate also
back and forth can help us solve particular problems.
Let's start with period rate.
So far in this class, we've always
referred to annual percentage rate as i,
but now we can refer to it as APR.
APR.
It's usually required by law on loan contracts, for example.
Required by the Truth and Lending Act.
This is Congress saying that APR has
to appear in certain contracts.
Now, APR-- we're given that 12%, or 0.12.
Anytime we have compounding periods greater than 1,
then annual percentage rate and effective annual rate
will be different.
Let's go ahead and first calculate our period rate.
We've done this 30 times already in this class.
We take our annual rate divided by a number of compounding
periods.
So there is our period rate.
Now, it's interesting the APR, Annual Percentage Rate,
is defined this way.
Take the period rate-- that's i divided by n--
and multiply it by the number of periods.
So as an example, if we know the monthly rate is this,
and we know n is 12, we can go ahead and calculate
the annual percentage rate.
This times 12.
And that'd give us 6%, or 0.06.
Now, here, if this is the rate, this--
no, let's go down here.
Sorry.
Let's just say APR is 18% on a credit card, compounded
monthly.
Now, you've always got to look at your contract
because it could be compounded all sorts of different ways.
But in this example, n is 12.
Now, this is the one required by law.
But anytime you have an APR with an n greater than 1,
then the actual effective annual rate is greater.
The effective annual rate is the rate
that, when multiplied times some amount,
gives you the future value.
But if this is 12 times--
you know, we have to do our formula.
Have to figure out the period rate,
add 1, and then raise it to the n,
and multiply that by our present value.
But effective annual rate will, in essence,
show us the real rate, which is always
going to be greater than 18% when n is greater than 1.
And it will allow us to multiply just this effective rate
times our original principal.
And that'll give us the interest.
We won't have to mess round with all the n and everything.
So let's see how to calculate the effective annual rate.
Equals-- and this should look familiar--
1 plus our period rate.
That's the annual rate divided by number
of compounding periods.
And since we're doing interest rates here-- so far
in the class, we've always had an exponent of n times x.
But this is just the annual rate, so we don't need x.
We don't need years.
We just take caret n.
Now, that'll give us the amount 1 plus the actual rate.
And if we're using this to multiply
times the original present value or principal amount,
that 1 gives us the original amount.
And the extra little bit there, 0.1956,
gives us the extra interest earned.
So to actually figure out the rate, what do you do?
You subtract one.
And that's our formula.
Now, we can compare these two and clearly see--
I think I have something written over here,
but we can clearly see that when n is greater than 1,
we're always going to get a--
I am not fixing this correctly.
Anyway, anytime you have n greater than 1,
this rate's always going to be bigger.
Now, we used our math formula, but there's
a function called EFFECT, so we're going to try that.
EFFECT.
There's a little drawback to the EFFECT, though.
It just wants the nominal rate.
And by the way, APR gonna be called nominal rate.
Quoted-- quotes rate.
That supposed to be quoted rate.
Stated rate, annual interest rate, and also others.
So in Excel, nominal means APR, so I'm clicking there.
Comma, and NPERY.
Oh, that's different.
We've seen NPR, so this is slightly different.
I said before that all of these arguments
in these financial functions are all the same.
Occasionally they're slightly different.
But NPERY just means number of compounding periods.
And that'll do it.
That will just give us our 19.
Now, one problem, and we'll run into this
in just a little bit when we do our money tree example.
This argument right here has to be an integer.
If it's not, it truncates it.
So if we were to give this 24.4, actually
put that number in there, it would only take the 24.
So sometimes we get a slight error with this function.
If this is supposed to include a decimal, it just won't do it.
It truncates it.
In this case, see, 12 is a integer.
So there's no problem whatsoever.
Now, some notes.
Never do EAR divided by n.
Only APR.
So when we're given an APR, we're given our rates
in the contract, or in the ad it says, 4% compounded 365 times
a year.
Or 10% compounded monthly.
That's an APR in the n.
Never do this.
This doesn't work.
APR equals EAR, Effective Annual Rate, only one n equals 1.
In EAR is always going to be greater than APR
when n is greater than 1.
Now, let's go over and look at an example.
I want to prove that we could take this rate
and multiply it by our original present value,
or our original invested amount, and get the same exact answer
as if we did our fancy formula with APR and n.
So let's come over here.
This sheet, 6.5.
Let's calculate our period rate equals--
and I'm going to calculate my EAR.
1 plus the period rate caret 12 minus 1.
I actually, over time, have tended
to always do this formula.
So when I run into a situation where
this is a decimal, which we'll see later,
I just use the format because it won't give me an error,
whereas the EFFECT function will.
So there it is.
APR, effective annual rate.
Now, our goal here is I want to do future value with APR
in n equals 12.
So I'm going to go equals.
And we'll use the future value functions, how about that?
So the rate, I'm going to do--
or period rate, sorry.
Comma, our NPER, comma, and this present value.
Now, here I just said we'll leave dollars in for one year.
So the number of compounding periods
will be 12 because there's 12 months in one year.
All right, so there's our future value.
We get $126.83.
Now, let's do it again.
But now the rate.
We're going to use the EAR, comma, and the NPER is 1.
This is the example of why the definition of EAR,
the Effective Annual Rate, is given these two inputs, APR
and n.
It'll give us the rate that will calculate any future value
with a compounding period of 1, comma, comma, and then
the present value is this.
Now, what's so nice about effective annual rate
as compared to APR is what?
It actually shows you the true rate
this one is kind of hidden because there's
some interest in there that we're even going to earn
or if it's debt we're paying.
This shows you a more truthful number.
And that's why it's kind of ironic that the APR is
required by law.
Well, who made the law, Congress?
Who influenced Congress people?
Well, the banks.
So that's why APR is required on loan documents, not this one,
because this one will show you the true rate.
And it will always be more when there's more compounding
periods.
We have an example in a couple--
I think number 10 here.
We'll do the effective annual rate
for a money tree loan, one of those short-term loans.
And we'll see that there's a huge difference
between this one and APR.
But here we can see there is a difference.
It's not that much.
Now, our next example.
We want to go over to nine.
We want to compare.
So we have two savings accounts.
We have an APR of 11% compounded four times a year,
or 10.75 compounded 365 times a year.
So which one do you actually want?
Well, we want the one that will give us
the most interest, right?
So the way you can do this is you calculate EAR,
because EAR you can compare.
They're both one compounding period per year.
This is 4 365, so it's hard to tell.
So I'm going to come here and do my formula
for each one of these.
1 plus-- and remember, this is just for one year.
We're comparing rates.
So I did 1 plus the period rate raised to the number of periods
per year minus 1.
So 11.46.
We'll do this one here.
For this next rate, I just love being able to do this.
So now I can go in and not get tricked
by whatever advertising.
I can just calculate it myself, and say, OK.
I know which one I want.
I want this one, because this one's a little bit bigger.
Now, in this circumstance, we can use the EFFECT
because there's no decimal here.
Again, in our next example, we will see a decimal.
So I'm going to say this.
That's the nominal rate, APR, comma, NPERY.
That one right there.
And then this one.
So whether you use the formula or the function,
the built-in function, you could see we get the same number.
So our conclusion-- if we want to earn more interest,
use this one right here.
OK,
So now, let's go over to the most brutal example.
Brutal.
It means this is really terrible.
This is not what you want to do.
You do not want to go to whatever they're called.
I called this Money Tree R Us Loaning, where you go in,
and you give them a check amount.
You write a check for $225, and they give you
cash today of $200.
And then, 15 days later, they cash the check.
Now, think about that.
How much interest did you pay?
$25.
Well, let's go ahead and use our basic knowledge
of comparing the part to the whole to figure out
what our interest rate is.
We can eye it here.
$225 extra interest compared to this $200.
But the thing is, when we calculate this,
this will give us the period rate.
This will give us 15-day period rate.
So I'm going to do this all in one formula.
And remember, we earlier in chapter 00 did our formulas
for change.
What is the percent change?
We have an end amount and a begin amount.
So I'm simply going to take the end, divided by they begin,
minus 1.
And in chapter 00, we saw the deductive proof for why
that's true.
12.5%.
That seems like a lot.
But an annual rate, it wouldn't be that much, right?
If it was an annual rate for a credit card,
it wouldn't be so bad, right?
Unless you went to BECU, then that would be bad.
BECU only target charges, what, 6%, 7%.
So a 15-day rate.
There's 365 days in a year.
Before we can actually figure out--
actually, we can figure out the APR right off the bat.
Except for we don't know how many periods there are, right?
15 days.
We know that there's 15 days, but how many 15-day periods
are there in a 365-day year?
Well, m figure out how many periods,
that's the number of 15-day periods in one year,
we say equals 365 divided by 15.
That tells us how many periods there are,
because you need to know how many periods in order
to calculate APR and EAR.
Here's our decimal.
And that's why sometimes using the EFFECT function,
where this argument right here is truncated,
what this will do-- and we'll look at it.
We'll calculate with the EFFECT.
And it's going to take the 24, not the 0.33.
All right, APR.
Now we can calculate our APR.
There's our period rate times the number of periods.
And that's pretty terrible, right?
That alone there, you're like, 304%?
Isn't that against the law?
I guess not.
They're doing it, right?
But here's the more painful truth.
Let's calculate EAR equals--
and I'm going to 1 plus--
and what is our period rate?
12.5%, or 0.125.
Close parentheses, and our caret and our number
of periods minus 1.
So 1,656%.
That doesn't seem legal, does it?
Happens all the time.
Pretty terrible.
Too bad there's not a law that prevents that.
Anyway, if there was a law, thanks
that figure a way to get around it.
Let's go ahead and do EFFECT.
The moral of the story is just don't go do this.
If it exists out there, and they always
seem to figure out ways to do this,
just don't go out and take a short-term loan
because you're losing money.
OK, the nominal rate.
That's our APR, so right here.
Nominal is APR.
And NPER.
Sorry, it's going to be approximately true
but not quite because it'll take the 24.
So still, that's pretty terrible.
This is a little bit more terrible.
All right, and this is not an uncommon example.
So it should bother you when you see this.
But knowing finance is good information.
All right, let's look at our last example.
Sometimes we know EAR, Effective Annual Rate,
and we need to figure out APR.
Now, I'm going to show you the math just briefly.
I'm not even quite sure why.
Actually, we could go over to the PDFs.
I have all of this here with some good notes.
All the examples we just did.
The loaning tree example is a little bit different.
I chose 25 days here.
And you can see we got 2,500% when we did it that way.
Here's our example here, and you can
see I show you the math here, of how to do it with math.
The problem is unless n is less than 5, if it's less than 5,
you can do the math.
Otherwise, you have to do something called iterating,
which means you have to plug in numbers until you zero in
on the answer.
And that's what our function that we'll see will do,
called nominal.
But just for a moment, I wanted to type in this.
We'll do it real quick.
You can just see it once and then forget it.
And there's the proof for it.
We went from our formula--
this is a formula given and then we went from that
and solved it just for the i or the APR.
All right, so we're going to take our EAR,
add 1, close parentheses.
And then caret open parentheses 1 divided by n, and then from
that we have to subtract 1 and do a close parentheses.
Now, let's make sure I did that right.
Not quite.
Oh, that gives us the period rate.
And so to calculate from the period rate,
we have to multiply it times our number of periods.
Remember, the definition of APR is period rate times n.
Now, the nominal function.
Nominal.
Oh, look-- it's just like EFFECT, except for it
says Effective Rate and NPR.
And guess what?
This function also truncate that.
So there you go.
And sometimes you know that you have
this effective annual rate.
And actually in a later video, we
will use this trick to great effect.
We're going to have an investing scenario that's
quite complicated, and we'll need
to go back and forth between solving for APR and EAR.
So here, this is just the math of it.
Here's the ERA, and we solve for APR.
Again, in the example later, we'll
have to use the nominal because our n will be greater than 5.
All right, that is a bunch of cool things
about interest rates.
When we come back in our next video,
we will start talking about annuities.
Cash flow streams that are of equal amounts,
and the distance between each payment is equal in time.
All right, we'll see you next video.