Use Excel 2010 to calculate your next car payment or loan payment; Episode #1233 shows the steps.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
...This episode is the video podcast companion to the book, "Excel 2010 In Depth", by Bill Jelen a.k.a. MrExcel.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Excel 2010 In Depth, Chapter 13 - Loan Payments Oh 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 want to create a loan payment calculator.
I have a price, maybe a car 25,995 pay forward over 60 months, five and a quarter percentage or assigning 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 if 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.
In this case, the first one "PMT" calculates the payment for a loan based on constant payments in 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 with that intermediate result is .004375 I don't particularly care in this case.
All right, next one is Nper.
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 long, 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 formulas All right, hey I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel 2010 In Depth, Chapter 13 - Loan Payments Oh 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 want to create a loan payment calculator.
I have a price, maybe a car 25,995 pay forward over 60 months, five and a quarter percentage or assigning 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 if 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.
In this case, the first one "PMT" calculates the payment for a loan based on constant payments in 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 with that intermediate result is .004375 I don't particularly care in this case.
All right, next one is Nper.
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 long, 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 formulas All right, hey I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
