馃攳
Excel Finance Class 28: Relationship Between APR, Period Rate and Effective Annual Rate - YouTube
Channel: unknown
[0]
welcome to Excel and Finance video
[1]
number 28 hey if you want to download
[4]
this workbook for chapter 5 or our PDF
[9]
notes for chapter 5 click on the link
[12]
below the video and then scroll down to
[15]
the finance section you can download
[16]
these files hey this video 28 we got to
[19]
talk about interest rates and we got to
[22]
talk about APR which is required by the
[27]
Truth in Lending Act annual percentage
[30]
rate and then the actual real rate or
[33]
effective annual rate all right we'll
[37]
stand we're going to see a bunch of cool
[40]
problems where knowing how to go from
[43]
APR to effective rate or effective to
[45]
APR or period rate also back and forth
[48]
can help us solve particular problems
[50]
let's start with period rate so far in
[53]
this class we've always referred to
[55]
annual percentage rate as I but now we
[59]
can refer to it as APR APR it's usually
[63]
required by law on loan contracts for
[67]
example truth' required by the Truth in
[71]
Lending Act this is Congress saying that
[74]
APR has to appear in certain contracts
[78]
now APR we're given that 12% or 0.12
[84]
anytime we have compounding periods
[87]
greater than 1 then annual percentage
[91]
rate and effective annual rate will be
[94]
different let's go ahead and first
[95]
calculate our period rate we've done
[97]
this you know 30 times already in this
[99]
class we take our annual rate divided by
[101]
a number of compounding periods so
[105]
there's our period right now it's
[106]
interesting the APR and you're
[109]
percentage rate is defined this way take
[113]
the period rate that's I divided by n
[115]
and multiply it by the number of periods
[117]
right so as an example if we know the
[120]
monthly rate is this and we know n is 12
[128]
we can go ahead and calculate the annual
[130]
percentage rate this times twelve and
[134]
that'll give us six percent or point
[136]
zero six now here if this is the rate
[141]
this know let's go down here sorry let's
[143]
just say APR is eighteen percent right
[146]
on a credit card compounded monthly now
[150]
you always got to look at your contract
[151]
because it could be compounded all sorts
[153]
of different ways but in this example n
[155]
is 12 now this is the one required by
[160]
law right but anytime you have an APR
[163]
with an N greater than one then the
[165]
actual effective annual rate is greater
[169]
the effective annual rate is the rate
[172]
that when multiplied times some amount
[178]
gives you the future value but it's if
[181]
this is twelve times four you know we
[183]
have to do our formula we have to figure
[184]
out the period rate add one and then
[186]
raise it to the N and multiply that by a
[190]
prince apresentava effective annual rate
[192]
will in essence show us the real rate
[194]
which is always going to be greater than
[195]
eighteen percent when n is greater than
[197]
one and it will allow us to multiply
[200]
just this effective rate times our
[204]
original principal and that will give us
[206]
the interest we won't have to mess
[207]
around with all the N and everything so
[209]
let's see how to calculate the effective
[212]
annual rate equals and this should look
[216]
familiar one plus our period rate that's
[219]
the annual rate divided by number of
[222]
compounding periods and since we're
[224]
doing interest rates here so far in the
[226]
class we've always had an exponent of n
[228]
times X but this is just the annual rate
[231]
so we don't need X we don't need years
[234]
we just take caret n now that'll give us
[239]
the amount one plus the actual rate and
[244]
if we're using this to multiply times
[247]
the original present value or principal
[251]
amount that one gives us the original
[253]
amount and the extra little bit their
[255]
point one nine five six gives us the
[258]
extra interest earned
[260]
so to actually figure out the rate what
[263]
do you do you subtract one and that's
[266]
our formula now we can compare these two
[268]
and clearly see I think I have something
[270]
written over here but we can clearly see
[272]
that the when n is greater than one
[279]
we're always going to get a not fixing
[283]
this correctly anyway any time you have
[289]
an greater than one this rates always
[291]
going to be bigger now we use the if we
[296]
used our math formula but there's a
[298]
function called effect so we're going to
[300]
try that effect there's a little
[301]
drawback to the effect though it just
[303]
wants the nominal rate and by the way
[305]
APR can be called nominal rate quoted
[309]
quotes rate that supposed to be quoted
[311]
rate stated rate annual interest rate
[313]
and also others so in excel nominal
[316]
means APR so I'm clicking there comma
[320]
and it NP ery oh that's different
[324]
we've seen NPR so this is slightly
[326]
different I said before that all these
[328]
arguments in these financial functions
[331]
are all the same occasionally they're
[332]
slightly different but NP ery just means
[335]
number of compounding periods and
[338]
that'll do it that'll just give us our
[340]
19 now the one problem and we'll run
[342]
into this in just a little bit when we
[345]
do our money tree example this argument
[350]
right here has to be an integer if it's
[353]
not it truncates it so if we were to
[356]
give this twenty four point four
[359]
actually put that number in there it
[361]
would only take the twenty four so
[362]
sometimes we get a slight error with
[366]
this function if this is supposed to be
[369]
include a decimal it just won't do it
[372]
it truncates it in this case C twelve is
[376]
a integer so there's no problem
[379]
whatsoever
[380]
now some notes never do ei are divided
[384]
by n only a PR so when we're given an a
[386]
PR you know we're given our rates in the
[388]
contract or in the ad it says four
[392]
percent compound
[393]
did 365 times a year or or 10%
[397]
compounded monthly
[398]
that's APR in the end never do this this
[401]
doesn't work APR
[403]
are effective annual rate only one n
[406]
equals one and ei R is always going to
[408]
be greater than a PR one n is greater
[411]
than 1 now let's go over and look at an
[413]
example I want to prove that we could
[417]
take this rate and multiply it by our
[419]
original present value or our original
[424]
investment amount and get the same exact
[427]
answer as if we did our fancy formula
[429]
with APR and n so let's come over here
[432]
the sheet six point five let's calculate
[434]
our period rate equals and I'm going to
[439]
calculate my ei R one plus the period
[444]
rate carrot 12 minus one I actually
[449]
overtime have tended to always do this
[451]
formula so when I run into a situation
[453]
where this is a decimal which we'll see
[456]
later I just use a formula because it
[460]
won't give me an error whereas the
[462]
effect function will so there it is a PR
[468]
annual rate now our goal here is I want
[471]
to do future value with APR and N equals
[473]
12 so I'm going to go equals and we'll
[477]
use the future value functions how about
[479]
that so the rate I'm going to do our
[485]
period rate sorry come our nper comma
[491]
and this present value now here I just
[493]
said we'll do we'll do it leave dollars
[495]
in for one year so the the number of
[499]
compounding periods will be twelve
[501]
because there's twelve months in one
[502]
year all right so there's our future
[506]
value we get a one hundred and twenty
[507]
six dollars and eighty three cents now
[510]
let's do it again
[515]
but now the rate we're going to use the
[518]
ear comma and the nper is one now here's
[524]
this is the example of why the
[525]
definition of e a are the effective
[528]
annual rate is given these two inputs a
[531]
PR and n it will give us the rate that
[535]
will calculate any future value with a
[537]
compounding period of one comma comma
[541]
and then the present value is this now
[545]
what's so nice about effective annual
[547]
rate as compared to a PR is what it
[551]
actually shows you the true rate this
[554]
one it's kind of hidden because there's
[556]
some interest in there that we're either
[558]
going to earn or for you know if it's
[560]
debt we're pain right this shows you a
[562]
more truthful number and that's why it's
[564]
kind of ironic that the APR is required
[568]
by law well who made the law Congress
[570]
who influenced Congress people well the
[573]
bank so that's why a PR is required on
[576]
loan documents not this one because this
[579]
one will show you the true rate and it
[581]
will always be more when there's more
[582]
compounding periods we have an example
[585]
in a couple I think number ten here
[587]
we'll do the effective annual rate for a
[591]
money tree loan one of those short-term
[594]
loans and we'll see that there's a huge
[596]
difference between this one and APR but
[599]
here we can see there is a difference
[600]
it's not that much now our next example
[603]
we want to go over to nine and we want
[605]
to we want to compare so we have two
[608]
savings accounts we have an APR of 11%
[611]
compounded four times a year or ten
[614]
point seven five compounded 365 times a
[617]
year so which one do you actually want
[620]
well we want the one that will give us
[622]
the most interest right so the way you
[625]
can do this is you calculate ear because
[628]
ear you can compare they're both one
[631]
compounding period per year this is for
[634]
365 so it's hard to tell so I'm going to
[637]
come here and do my formula for each one
[639]
of these one plus
[644]
and remember this is just for one year
[648]
we're comparing rates so I did one plus
[650]
the comp the period rate raised to the
[652]
number of periods per year minus one
[656]
okay so eleven point four six we'll do
[659]
this one here for this next rate I just
[663]
love being able to do this right so now
[664]
I can go in and not get tricked by
[666]
whatever advertising I can just
[668]
calculate it myself and say okay I know
[672]
which one I want and I want this one
[674]
because this one's a little bit bigger
[676]
now in this circumstance we can use the
[679]
effect because there's no decimal here
[682]
and again in our next example we will
[684]
see a decimal so I'm going to say this
[687]
that's the nominal rate APR comma NP ery
[692]
that one right there and then this one
[699]
so whether you use the formula or the
[702]
function the built-in function you can
[704]
see we get the same number so our
[706]
conclusion if we want to earn more
[707]
interest use this one right here this
[710]
okay so now let's go over to the most
[713]
brutal example brutal means this is
[716]
really terrible this is not what you
[719]
want to do you do not want to go to you
[721]
know whatever they're called like I call
[723]
this money tree our us loaning you know
[725]
where you go in and you give them a
[728]
check amount we write a check for two
[730]
hundred and twenty-five dollars and they
[732]
give you cash today of two hundred and
[734]
then 15 days later they cash the check
[737]
now think about that how much interest
[739]
did you pay 25 dollars well let's go
[743]
ahead and use our basic knowledge of
[747]
comparing the part to the whole to
[750]
figure out what our interest rate is we
[753]
can we can I adhere two hundred and
[755]
twenty five dollars extra interest
[757]
compared to this two hundred but the
[760]
thing is when we calculate this this
[762]
will give us the period rate this will
[763]
give us 15 day period rate so I'm going
[767]
to do this all one formally and remember
[769]
we earlier in Chapter zero zero did our
[772]
formulas for change what is the
[775]
cent change we have an end amount and a
[778]
begin amount so I'm simply going to take
[779]
the end / they begin minus one and in
[784]
Chapter zero zero we saw the the
[786]
deductive proof for why that's true
[790]
12.5% hmm that seems like a lot but in
[796]
annual rate it wouldn't be that much
[798]
right if it was an annual rate for a
[800]
credit card it wouldn't be so bad right
[802]
unless you went to BECU you know then
[804]
that would be bad beat you see you only
[806]
tart charges what six seven percent okay
[810]
so 15 day rate hmm there's 365 days in a
[815]
year before we can before we can
[819]
actually figure out actually we can
[822]
figure out the APR right off the bat
[826]
except for we don't know how many
[829]
periods there are right 15 days we know
[833]
that there's 15 days but how many
[834]
fifteen day periods are there in 365 day
[839]
year well to figure out how many periods
[841]
that's the number of fifteen day periods
[843]
in one year we say equals 365 divided by
[847]
15 that tells us how many periods are
[852]
because you need to know how many
[853]
periods in order to calculate APR and ei
[857]
are here's our decimal and that's why
[860]
sometimes our using the effect function
[865]
where this argument right here is
[867]
truncated what this will do and I'll
[869]
we'll look at it we'll calculate what
[871]
the effect and it just it's going to
[872]
take the 24 not the 0.33 all right APR 8
[887]
2014 4 and I'm going to 1 plus and what
[905]
is our period rate
[907]
12.5 12.5% or 0.125 close parentheses
[913]
and our carat and our number of periods
[916]
minus 1.so 1656 percent that doesn't
[925]
seem legal does it happens all the time
[929]
pretty terrible too bad there's a lot of
[933]
law that prevents that any way if there
[935]
was a law that banks did figure a way to
[937]
get around it let's go ahead and do
[939]
effect the the moral of the story is
[944]
just don't go don't go do this if it
[946]
exists out there and they always seem to
[947]
figure out ways to do this just don't go
[949]
out and take a short-term loan because
[951]
you're losing money okay the nominal
[954]
rate that's our APR so right here
[958]
nominal is a PR and nper sorry it's
[962]
going to be approximately true but not
[964]
quite because it'll take the 24 so still
[968]
that's pretty terrible this is a little
[970]
bit more terrible all right and this is
[973]
not an uncommon example so it should
[975]
bother you when you you see this but
[977]
knowing finance is good information
[981]
alright let's look at our last example
[985]
sometimes we know ear effective annual
[989]
rate and we need to figure out APR now
[993]
we're going to show you the math just
[995]
briefly I don't even not even quite sure
[997]
why actually we could go over to the the
[1000]
PDFs I have all of this here and with
[1003]
some good notes all the examples we just
[1005]
did the loaning tree example is a little
[1011]
bit different I chose 25 days here and
[1014]
you can see we got two thousand five
[1016]
hundred percent when we did it that way
[1018]
here's our example here and you can see
[1021]
I'd show you the the math here of how to
[1024]
do it with math the problem is is unless
[1029]
n is less than five if it's less than
[1034]
five you can do the math otherwise you
[1035]
have to do something called iterating
[1037]
which means you have to plug in numbers
[1039]
until
[1040]
you zero in on the answer and that's
[1042]
what our function that we'll see we'll
[1045]
do called nominal but just for a moment
[1048]
I want to type in this will do it real
[1050]
quick you can just see it once and then
[1052]
forget it
[1053]
here's our and there's the proof for it
[1055]
we went from our formula right this is a
[1058]
formula given and then we went from that
[1063]
and solved it just for the eye or the
[1065]
APR all right so we're going to take our
[1069]
ear add one closed parenthesis and then
[1074]
caret open parenthesis one divided by N
[1078]
and then from that we have to subtract
[1081]
one and do a closed parenthesis now
[1088]
let's make sure I did that right not
[1090]
quite
[1090]
oh oh that gives us the period rate and
[1095]
so to calculate from that the period
[1097]
rate we have to multiply it times our
[1100]
number of periodic remember the
[1102]
definition of APR is period rate times
[1104]
and now the nominal function nominal oh
[1112]
look it's just like effective except for
[1114]
it says effective rate and NPR and guess
[1117]
what this function also truncates that
[1123]
so there you go and sometimes you know
[1126]
that you have this effective annual rate
[1128]
and actually lay in a later video we
[1131]
will use this trick to great effect that
[1134]
we're going to have an investing
[1136]
scenario that's quite complicated and
[1138]
we'll need to go back and forth between
[1140]
solving for APR and ei R so here this is
[1144]
just the the math of it here's the ER a
[1149]
and we solve for APR again an example
[1153]
later we'll have to use the nominal
[1155]
because our n will be greater than 5 all
[1159]
right that is a bunch of cool things
[1162]
about interest rates I'm going to come
[1165]
back in our next video we will start
[1168]
talking about annuities cash flow
[1171]
streams that are of equal amount
[1174]
and the distance between each payment is
[1176]
equal in time all right we'll see you
[1178]
next video
Most Recent Videos:
You can go back to the homepage right here: Homepage