Use Excel to calculate your next car payment or loan payment
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL Excel 2010 in-depth Chapter 13; Loan Payments Hey, all right, welcome back to the MrExcel netcast. I'm Bill Jelen.
The example from chapter 13, in the excel in-depth book is talking about how to discover new functions.
All right, so I wanna create a Loan Payment Calculator.
I have a price, maybe, a car 25,995 pay for over 60 months, 5.25% interest.
I need to figure out what the monthly payment is going to be?
So, when you don't know what function to use, You come here to the left of the formula bar and use this tiny little script letter fx.
you also see that it's here, on the formulas tab, in a large version and then in most of the other drop downs, here at the bottom.
So it's about 13 different places, you can tell that Microsoft really likes this icon and what you do here is you type what you're trying to figure out.
So, I'm trying to figure out a Loan Payment.
Click Go and they shortlist the 400 plus functions down, to just the functions related to loan payments.
And this for case, the first one PMT calculates the payment for a loan based on constant payments in a constant interest rate...
at a constant interest rate, that's the one that I want, so, I click OK.
And they very nicely now take me through step-by-step.
Rate: interest rate per period for the loan.
For example, use 6%/4 for quarterly payments.
Okay well, we have monthly payments, so I need to take the Rate field, which is B3 and then type divided by 12, and they even show me what that intermediate result is: 0.004375.
I don't particularly care in this case.
All right, next one is Nper, and hey by the way, notice that three of these functions are bold.
The other two are not bold that means the top three are required to make the function work.
The other two have optional information.
So Nper, total number of payments for loan, okay well that's my term, and then PV that's the Present Value.
In other words, the price of the car and since, this is money coming out of the bank, we are going to type a minus sign and then click on the price of the car.
If you didn't do that the payment would show up as minus, negative, as in its money leaving your wallet.
Okay so as soon as, we get those bold arguments done.
We have an answer here 493. 493 sounds about right, click OK.
And we now have our loan payment of course, then if you want to start to do some what-if analysis, you know, what if I get 48 month loan...
or you know different price of the car, different interest rate, you can do all kinds of great what-if analysis, once you get the formula setup.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
The example from chapter 13, in the excel in-depth book is talking about how to discover new functions.
All right, so I wanna create a Loan Payment Calculator.
I have a price, maybe, a car 25,995 pay for over 60 months, 5.25% interest.
I need to figure out what the monthly payment is going to be?
So, when you don't know what function to use, You come here to the left of the formula bar and use this tiny little script letter fx.
you also see that it's here, on the formulas tab, in a large version and then in most of the other drop downs, here at the bottom.
So it's about 13 different places, you can tell that Microsoft really likes this icon and what you do here is you type what you're trying to figure out.
So, I'm trying to figure out a Loan Payment.
Click Go and they shortlist the 400 plus functions down, to just the functions related to loan payments.
And this for case, the first one PMT calculates the payment for a loan based on constant payments in a constant interest rate...
at a constant interest rate, that's the one that I want, so, I click OK.
And they very nicely now take me through step-by-step.
Rate: interest rate per period for the loan.
For example, use 6%/4 for quarterly payments.
Okay well, we have monthly payments, so I need to take the Rate field, which is B3 and then type divided by 12, and they even show me what that intermediate result is: 0.004375.
I don't particularly care in this case.
All right, next one is Nper, and hey by the way, notice that three of these functions are bold.
The other two are not bold that means the top three are required to make the function work.
The other two have optional information.
So Nper, total number of payments for loan, okay well that's my term, and then PV that's the Present Value.
In other words, the price of the car and since, this is money coming out of the bank, we are going to type a minus sign and then click on the price of the car.
If you didn't do that the payment would show up as minus, negative, as in its money leaving your wallet.
Okay so as soon as, we get those bold arguments done.
We have an answer here 493. 493 sounds about right, click OK.
And we now have our loan payment of course, then if you want to start to do some what-if analysis, you know, what if I get 48 month loan...
or you know different price of the car, different interest rate, you can do all kinds of great what-if analysis, once you get the formula setup.
Hey, I want to thank you for stopping by.
See you next time for another netcast from MrExcel.
