馃攳
Learn Excel 2010 - "Variable Rate Loan Payment": Podcast #1438 - YouTube
Channel: MrExcel.com
[0]
MrExcel podcast is sponsored by Easy-XL.
[4]
Learn Excel from MrExcel, episode 1438: variable
rate loan payment.
[9]
Well, hey, welcome back to the MrExcel netcast.
[11]
I'm Bill Jelen and I'm back from my trip to
Milwaukee.
[13]
I did three seminars for the IIA yesterday.
[16]
Some great questions came in.
[17]
I was showing how to do-- how loan payments,
like I normally do the PMT function and Heidi,
[23]
who was in the audience asked the question,
what-- what-- how do you calculate a variable
[27]
loan payment?
[28]
It's funny; they don't have a function for
variable rate loan payments.
[32]
So, this is the-- the best that I can come
up with.
[35]
We have our amount borrowed, you know, this--
well, actually that's the original one.
[40]
All right, so, amount borrowed: 120,000 over
180 months and the interest rate starts at
[44]
this great introductory two and a half percent,
but then after five years goes to six percent,
[49]
after ten years goes to seven percent.
[50]
So, that's kind of my table there.
[52]
I built a table at the bottom that shows the
months from 1 to 180 and I'm just going to
[59]
put in a guess of a payment of 917 for right
now.
[62]
We're going to come up with the real payment.
[63]
So, the payment each month is that 917.
[65]
I'll press F4.
[67]
The rate, the rate is going to be =VLOOKUP--
this is one of those weird VLOOKUP s where
[72]
we're not getting an exact match.
[74]
We are getting a range.
[76]
F4, comma, 2.
[78]
False at the end?
[79]
No, you don't even have to put true; just
leave it blank.
[83]
That's going to-- still need to put which
column area.
[88]
Two and a half percent, let's format that
as a percent.
[93]
Good.
[94]
So, that means the interest this month is
the prior balance, we鈥檙e going to start
[98]
with the prior balance here.
[101]
Our balance equal to prior balance times that
interest rate divided by 12.
[107]
That means the principal is the payment minus
the interest rate.
[110]
So, our balance is the prior balance minus
the principal portion of the payment.
[115]
Is that interest rate-- that's going to change
each month, the principal will change each
[120]
month.
[121]
All right, so, we have that.
[122]
Let's double-click to shoot that whole thing
down and we now have a table.
[126]
I'm going to hit End down arrow and we'll
take a look.
[130]
All right, so right now, that payment that
I have is not correct.
[133]
It is, frankly, too high and so we're paying
too much to the bank.
[139]
Now, to figure out the exact payment, we鈥檒l
go to data, what-if analysis, goal seek.
[143]
Let鈥檚 bring this up here.
[147]
I want to set that final value, that closing
balance, to zero dollars by changing the temporary
[156]
payment number that I put in at the top.
[159]
So, cruise back up there.
[160]
That is in cell C14, click OK, click OK.
[165]
So, there we go.
[167]
Our answer 888 point-- let's call it 59, will
get us the amount.
[174]
Now, the hassle with this is if you would
change something.
[177]
So, let's say we decide that we're going to
borrow $125,000.
[180]
Well, that doesn't recalculate.
[182]
That doesn't recalculate.
[183]
What you鈥檙e going to have to do is go back
and try the whole thing again.
[188]
Choose that last cell, what-if analysis, goal
seek, set that to zero by changing that, click
[198]
OK.
[199]
All right, so, a little bit of training there
that you鈥檙e going to have to go through.
[207]
That set of steps every time that you change
anything.
[209]
Maybe we could even macroise that or something,
but I think this is probably, probably the
[215]
best way.
[216]
Now, what's going to be interesting is I've
never had to deal with variable interest rate
[219]
loans.
[220]
I'm sure a lot of people have to do this all
the time.
[221]
If you have a better way to do this, shoot
me a note: bill @mrexcel.com.
[224]
We'll put it on a future podcast and get you
one of those Excel master pins.
[228]
So, that's my way for right now.
[229]
Let me know if there's something better.
[230]
Well, hey, I want to thank you for stopping
by.
[232]
I want to thank Heidi for that great question
from the IIA seminar.
[235]
See you next time for another netcast from
MrExcel.
Most Recent Videos:
You can go back to the homepage right here: Homepage