"I was in Milwaukee this week at the IIA Regional Conference when Heidi asked me about calculating a loan payment for a loan, with variable interest rates." Understanding that "PMT" makes this easy for a fixed rate, in today's podcast, Episode #1438, Bill shows us how to use Goal Seek to back into this answer.
Transcript of the video:
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.
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.
