Learn how to use a simple English-language query in order to discover new functions. Episode 409 will show you how to find a function to calculate a monthly car payment. You can also use the Goal Seek function to immediately back into an answer.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well hey, it's the last working day before Christmas, if you are stuck at work today, it's a great day to do something that's not quite work related.
One of my favorite examples of something that Excel is great at, is when you're thinking about buying a new house or a new car, figuring out the loan payments, and I always show this as an example of how to discover new functions.
You know, we have a situation here, there must be a way for Excel to calculate the car payment, but how do we figure it out?
The trick is to use the Fx button, the Insert Function button, right to the left of the formula bar.
If you choose that button, you'll get a search box where you can actually type exactly what you're trying to do.
I'm trying to figure out a car payment, when I click Go, it suggests five or six different functions that might work.
The first function, the PMT function, calculates the payment for a loan based on constant payments and a constant interest rate, that sounds like the right one.
When you click OK, it now takes you through step by step by step, to show you how to build the formula, and every argument has help.
So Rate, the help for Rate is the interest rate per period for the loan, for example, 6%/4.
Well, I want a monthly payment, so I'm going to use my 5.25%/12.
Nper, what's that?
Total number of payments for the loan.
In English, that makes sense!
That would be the 48 months.
Present value, is the total amount that a series of future payments is worth now, that's the loan amount, has to be negative, so I’m going to put –B1, and at this point Excel shows me the result!
You know, I can make sure that's right.
The other tip is if an argument name is in bold is required, if it's not in bold it's optional, so right now I have enough information to get the answer.
Click OK, and there's my car payment, that's probably more than I want to pay.
If I would like to kind of play the What-If game, I can start to change the price or the term or the rate to see how the number changes.
Kind of a bonus tip here before the holidays, if you want to immediately back into a certain value, you can go to Tools, Goal Seek.
And in the Goal Seek dialogue, you can say that you want to set cell B4 to, let's say, 595, by changing cell B1.
Click OK, and in an instant Excel will play the higher-lower game basically, and figure out the exact number: 25710, click OK, and there's your answer.
Now everything is still a live formula, you can still change the numbers or do whatever you need to do.
So hey, if your Christmas shopping list includes a new car, you can use this this weekend to go out and hopefully negotiate a better deal.
Monday is the holiday, will be off Monday and Tuesday, go back on Wednesday for another netcast.
Thanks for stopping by, have a great holiday!
Well hey, it's the last working day before Christmas, if you are stuck at work today, it's a great day to do something that's not quite work related.
One of my favorite examples of something that Excel is great at, is when you're thinking about buying a new house or a new car, figuring out the loan payments, and I always show this as an example of how to discover new functions.
You know, we have a situation here, there must be a way for Excel to calculate the car payment, but how do we figure it out?
The trick is to use the Fx button, the Insert Function button, right to the left of the formula bar.
If you choose that button, you'll get a search box where you can actually type exactly what you're trying to do.
I'm trying to figure out a car payment, when I click Go, it suggests five or six different functions that might work.
The first function, the PMT function, calculates the payment for a loan based on constant payments and a constant interest rate, that sounds like the right one.
When you click OK, it now takes you through step by step by step, to show you how to build the formula, and every argument has help.
So Rate, the help for Rate is the interest rate per period for the loan, for example, 6%/4.
Well, I want a monthly payment, so I'm going to use my 5.25%/12.
Nper, what's that?
Total number of payments for the loan.
In English, that makes sense!
That would be the 48 months.
Present value, is the total amount that a series of future payments is worth now, that's the loan amount, has to be negative, so I’m going to put –B1, and at this point Excel shows me the result!
You know, I can make sure that's right.
The other tip is if an argument name is in bold is required, if it's not in bold it's optional, so right now I have enough information to get the answer.
Click OK, and there's my car payment, that's probably more than I want to pay.
If I would like to kind of play the What-If game, I can start to change the price or the term or the rate to see how the number changes.
Kind of a bonus tip here before the holidays, if you want to immediately back into a certain value, you can go to Tools, Goal Seek.
And in the Goal Seek dialogue, you can say that you want to set cell B4 to, let's say, 595, by changing cell B1.
Click OK, and in an instant Excel will play the higher-lower game basically, and figure out the exact number: 25710, click OK, and there's your answer.
Now everything is still a live formula, you can still change the numbers or do whatever you need to do.
So hey, if your Christmas shopping list includes a new car, you can use this this weekend to go out and hopefully negotiate a better deal.
Monday is the holiday, will be off Monday and Tuesday, go back on Wednesday for another netcast.
Thanks for stopping by, have a great holiday!
