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.

thanks for teaching! this is just too amazing

You helped me with my homework! You are the best!

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

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

do you have one for weekly payments?