Learn Excel 2010 – “Variable Rate Loan Payment”: Podcast #1438


MrExcel podcast is sponsored by Easy-XL. Learn Excel from MrExcel, episode 1438: variable
rate loan payment. Well, hey, welcome back to the MrExcel netcast. I’m Bill Jelen and I’m back from my trip to
Milwaukee. I did three seminars for the IIA yesterday. Some great questions came in. I was showing how to do– how loan payments,
like I normally do the PMT function and Heidi, who was in the audience asked the question,
what– what– how do you calculate a variable loan payment? It’s funny; they don’t have a function for
variable rate loan payments. So, this is the– the best that I can come
up with. We have our amount borrowed, you know, this–
well, actually that’s the original one. All right, so, amount borrowed: 120,000 over
180 months and the interest rate starts at this great introductory two and a half percent,
but then after five years goes to six percent, after ten years goes to seven percent. So, that’s kind of my table there. I built a table at the bottom that shows the
months from 1 to 180 and I’m just going to put in a guess of a payment of 917 for right
now. We’re going to come up with the real payment. So, the payment each month is that 917. I’ll press F4. The rate, the rate is going to be=VLOOKUP–
this is one of those weird VLOOKUP s where we’re not getting an exact match. We are getting a range. F4, comma, 2. False at the end? No, you don’t even have to put true; just
leave it blank. That’s going to– still need to put which
column area. Two and a half percent, let’s format that
as a percent. Good. So, that means the interest this month is
the prior balance, we’re going to start with the prior balance here. Our balance equal to prior balance times that
interest rate divided by 12. That means the principal is the payment minus
the interest rate. So, our balance is the prior balance minus
the principal portion of the payment. Is that interest rate– that’s going to change
each month, the principal will change each month. All right, so, we have that. Let’s double-click to shoot that whole thing
down and we now have a table. I’m going to hit End down arrow and we’ll
take a look. All right, so right now, that payment that
I have is not correct. It is, frankly, too high and so we’re paying
too much to the bank. Now, to figure out the exact payment, we’ll
go to data, what-if analysis, goal seek. Let’s bring this up here. I want to set that final value, that closing
balance, to zero dollars by changing the temporary payment number that I put in at the top. So, cruise back up there. That is in cell C14, click OK, click OK. So, there we go. Our answer 888 point– let’s call it 59, will
get us the amount. Now, the hassle with this is if you would
change something. So, let’s say we decide that we’re going to
borrow $125,000. Well, that doesn’t recalculate. That doesn’t recalculate. What you’re going to have to do is go back
and try the whole thing again. Choose that last cell, what-if analysis, goal
seek, set that to zero by changing that, click OK. All right, so, a little bit of training there
that you’re going to have to go through. That set of steps every time that you change
anything. Maybe we could even macroise that or something,
but I think this is probably, probably the best way. Now, what’s going to be interesting is I’ve
never had to deal with variable interest rate loans. I’m sure a lot of people have to do this all
the time. If you have a better way to do this, shoot
me a note: bill @mrexcel.com. We’ll put it on a future podcast and get you
one of those Excel master pins. So, that’s my way for right now. Let me know if there’s something better. Well, hey, I want to thank you for stopping
by. I want to thank Heidi for that great question
from the IIA seminar. See you next time for another netcast from
MrExcel.

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Post navigation

5 thoughts on “Learn Excel 2010 – “Variable Rate Loan Payment”: Podcast #1438

  1. I am trying to figure out how to calculate student loan payments in excel. With payments being made on the monthly or biweekly bases. 

  2. Hi, great tutorial. Aren't the payments supposed to change as well when the rate changes? They will not stay consistent throughout the life of the loan. How would you show that in this particular scenario? Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *