Welcome to Finance and Excel class number 30.
Hey, if you want to download this workbook for chapter 5,
click on the link directly below the video
and scroll way down to the Excel Finance Class section.
Oh, we have a great video here.
Here's the situation.
We have a savings plan that compounds interest 365 times
of year, but you put money in only 12 times a year, right?
So you go to the bank.
And oftentimes your basic savings accounts
are compounding interest 365 times a year.
And we love that, right?
Compound interest.
The more times they compound it, the more time we're
getting interest on interest.
But we only put money at the end of each month
when we get our paycheck in, right?
So our situation is we're going to put in $250 each month.
We're planning to save for 25 years.
We get compounding that often.
Here's our APR.
So the trick is what?
Well, they're giving us interest 365 times a year.
But we only put money in 12 times a year.
There is a mismatch between the n for interest earned
and money going in.
No problem.
We can translate our APR, 8% compounded 365 times a year,
n equals 365, into an annual rate compounded once
using our effective annual rate.
We then can divide that by 12 to get the period rate
for our money being put in.
So the whole trick is when we have
a mismatch between our n's, the transition from one
to the other so we can actually calculate
our future value is going to be the effective annual rate.
And remember, effective annual rate
takes some annual rate with an n greater than 1
and tells us what it would be if n equals to 1.
All right.
So step 1, ERA.
Well, we could do it the long way, right?
We saw this formula a number of times already.
Well, as long as our n is not an integer--
I'm sorry, is an integer, meaning
it doesn't have decimals, then we have no problem.
We can use the EFFECT.
We saw how we got into trouble using the EFFECT on our money
tree example, because the n--
the n was a decimal.
So no problem here.
We just take our nominal rate, 8, comma, our NPERY,
periods per year.
And boom, we get our effective annual rate.
That's the annual rate as if we were
compounding one time a year.
Now from this, we need to go from our effective annual rate
to the APR for our money in.
That means 12 times a year.
Well, we could-- we just set it up like this.
We just calculated our EAR.
So here's our base formula.
We plug in EAR.
And we need to solve for that.
Now, you could do it longhand here.
And you could see the math there.
I even built the formula there.
But why bother?
If we're in-- you know, do it one or two times
to see how the math works.
But then after that, what do we use?
We can use NOMINAL.
Here we used EFFECT to go from APR or nominal to effect.
And we can use NOMINAL to go from effective annual rate
back to nominal or APR.
So we take that.
That's the effective annual rate compounding one time, right?
And we say our NPER for money into our account is 12.
And that will tell us our APR.
All right.
So that's all of this right here.
And we get that number.
Now from the APR, we need to find our period rate,
because remember, if we're putting money in 12 times
a year, $250, we need the period rate.
Well, our translator from this down to something we can use
is going to be EFFECT, NOMINAL, and then divide by 12.
Now we can solve for our future value.
We're going to use the future value function.
Our period rate is going to be that.
Our NPER-- well, we didn't calculate it specifically.
But no problem.
12 times a year times 25 years, comma, our PMT,
it is going to be negative.
From our point of view, it's going out of our wallet
and into the bank.
And comma, we don't need present value.
We don't have any money in the bank account
we're assuming when we start.
And we don't need TYPE, because it's
the end of the period by default, so we can just Enter.
And there is our future value with a mismatch between
our n's.
Finally, it pays to figure out how much we put in in total.
So we say 12 times 25 times our $250.
So I'm going to apply like a formatting
like that-- oh, not like that.
I'm going to Control-1.
Control-1 opens Format Cells.
I'm going to say Currency and then this one here.
Right.
So we've put in $75,000.
But we get $238,000.
All right.
That's fun with a 365 times a year earning interest,
but we're only putting in money monthly, and how
to calculate future value.
See you next trick.