How to discover which Excel calculation function to use. This episode shows how to use the PMT function to calculate a loan payment and how the Function Arguments helps with tricky arguments like dividing the interest rate by 12. Episode recap:
There are over 400 calculation functions in Excel.
If you don't know what function to use, click the fx button.
This button is on the Formulas tab and also to the left of the formula bar.
Type what you are trying to do and Search.
The results will show you the functions related to your search term.
When you click OK, you are taken to the Function Arguments dialog.
This dialog provides help for each argument.
Bold arguments are required.
Once the bold arguments are completed, you will see the answer in two places.
If you know the name of the function, type =PMT( and then press Ctrl+A
Note: Thanks to Tony DeJonker and Cat Parkinson for suggesting the function arguments.
There are over 400 calculation functions in Excel.
If you don't know what function to use, click the fx button.
This button is on the Formulas tab and also to the left of the formula bar.
Type what you are trying to do and Search.
The results will show you the functions related to your search term.
When you click OK, you are taken to the Function Arguments dialog.
This dialog provides help for each argument.
Bold arguments are required.
Once the bold arguments are completed, you will see the answer in two places.
If you know the name of the function, type =PMT( and then press Ctrl+A
Note: Thanks to Tony DeJonker and Cat Parkinson for suggesting the function arguments.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2022 - Discover Functions with the Fx Icon!
I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist!
Alright, so we're trying to calculate a loan payment today, I want to buy a new car, 25995, pay for over 60 months, 5.25% interest, how do I calculate the payment?
Oh, well you know, there's all kinds of questions like this in Excel.
How do I do this?
How do I do that?
What's the formula for?
And out here on the Formulas tab there is a huge button called Insert Function.
You don't have to use this one though, because it's been right here to the left of the formula bar for the last 30 or 40 years.
Choose Insert- it can't be 40 years, for the last 30 years, alright, so we're in a search for a function.
I'm going to search for loan payments, car payments, and when I click Go or just press Enter, it shows me all of the functions related to loan payments.
For each one I can see what arguments they need, and it tells me what it's going to do, “Calculates the payment for a loan based on constant payments and a constant interest rate.” That's the one that I need, alright?
But the best part is, other than finding the name of the function, because I came in through Insert Function, when I click OK, they take me to the Function Arguments.
And this is really important because there's 5 arguments here, the 3 bold arguments are required, the 2 non-bold arguments are optional.
But as I click in, it gives me help for each of the arguments, “Rate is the interest rate per period for the loan.
For example, use 6%/4 for quarterly payments at 6% APR.” This is why I always screw this formula, because I just use 5.25%, but that's an annual rate, so I have to /12.
And look, they even show me that's .004375, and today I don't care at all, but someday, that might be useful.
Nper, what is Nper?
Just click in, “Total number of payments for the loan.” That's what I call the term.
What's this, Pv, Present Value, in other words the price of the car.
Now one thing I've learned over time is that, if I put a positive number here, the payment’s going to be negative, but I never write a check for -$425, I always write a check for +$425.
This is money coming out of the bank, so I put a minus sign here, and then click on 25995.
Future value is a residual value, where might be, but it's not factored into the loan.
Are we paying on the first of the period or the end of the period, I'll just leave it blank and say that I'm paying at the end of the month, alright.
And you see, as soon as you get the three bold arguments in, the required arguments, you have an answer, 493, how awesome is that, click OK.
Alright so, you don't know what function to use, come up here, click the Fx button.
And by the way, if you do know what function to use, if you do know that's payment, as soon as you type that ( you can press Ctrl+A, and it will bring you into Function Arguments.
Alright, so if you know the name of the function, you just need help getting through it, then just type that FUNCTIONNAME( and Ctrl+A to get there as well.
This tip, and many more tips, are in “MrExcel XL - The 40 Greatest Excel Tips of All Time”.
Click the “i” on the top-right hand corner, and you will have lots of ways to be more efficient in Excel, have more fun at work, all kinds of good things.
Alright, thanks to my friend Tony DeJonker in the Netherlands and Cat Parkinson for suggesting the Function Arguments dialog box, it’s one of their favorite tips.
There are over 400 calculation functions in Excel, if you don't know what function to use, click the Fx button, it's right there to the left of the formula bar.
Type what you're trying to do and click Go to do a search, results show you the function related to your search term.
When you click OK, you're not just left to figure it out, they take you through the Function Arguments dialogue, it gives you help on each argument.
Get all the bold arguments in, and you'll see the answer in two places in the dialog.
If you already know the name of the function, type the = name of the function, open parentheses, and then press Ctrl+A.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist!
Alright, so we're trying to calculate a loan payment today, I want to buy a new car, 25995, pay for over 60 months, 5.25% interest, how do I calculate the payment?
Oh, well you know, there's all kinds of questions like this in Excel.
How do I do this?
How do I do that?
What's the formula for?
And out here on the Formulas tab there is a huge button called Insert Function.
You don't have to use this one though, because it's been right here to the left of the formula bar for the last 30 or 40 years.
Choose Insert- it can't be 40 years, for the last 30 years, alright, so we're in a search for a function.
I'm going to search for loan payments, car payments, and when I click Go or just press Enter, it shows me all of the functions related to loan payments.
For each one I can see what arguments they need, and it tells me what it's going to do, “Calculates the payment for a loan based on constant payments and a constant interest rate.” That's the one that I need, alright?
But the best part is, other than finding the name of the function, because I came in through Insert Function, when I click OK, they take me to the Function Arguments.
And this is really important because there's 5 arguments here, the 3 bold arguments are required, the 2 non-bold arguments are optional.
But as I click in, it gives me help for each of the arguments, “Rate is the interest rate per period for the loan.
For example, use 6%/4 for quarterly payments at 6% APR.” This is why I always screw this formula, because I just use 5.25%, but that's an annual rate, so I have to /12.
And look, they even show me that's .004375, and today I don't care at all, but someday, that might be useful.
Nper, what is Nper?
Just click in, “Total number of payments for the loan.” That's what I call the term.
What's this, Pv, Present Value, in other words the price of the car.
Now one thing I've learned over time is that, if I put a positive number here, the payment’s going to be negative, but I never write a check for -$425, I always write a check for +$425.
This is money coming out of the bank, so I put a minus sign here, and then click on 25995.
Future value is a residual value, where might be, but it's not factored into the loan.
Are we paying on the first of the period or the end of the period, I'll just leave it blank and say that I'm paying at the end of the month, alright.
And you see, as soon as you get the three bold arguments in, the required arguments, you have an answer, 493, how awesome is that, click OK.
Alright so, you don't know what function to use, come up here, click the Fx button.
And by the way, if you do know what function to use, if you do know that's payment, as soon as you type that ( you can press Ctrl+A, and it will bring you into Function Arguments.
Alright, so if you know the name of the function, you just need help getting through it, then just type that FUNCTIONNAME( and Ctrl+A to get there as well.
This tip, and many more tips, are in “MrExcel XL - The 40 Greatest Excel Tips of All Time”.
Click the “i” on the top-right hand corner, and you will have lots of ways to be more efficient in Excel, have more fun at work, all kinds of good things.
Alright, thanks to my friend Tony DeJonker in the Netherlands and Cat Parkinson for suggesting the Function Arguments dialog box, it’s one of their favorite tips.
There are over 400 calculation functions in Excel, if you don't know what function to use, click the Fx button, it's right there to the left of the formula bar.
Type what you're trying to do and click Go to do a search, results show you the function related to your search term.
When you click OK, you're not just left to figure it out, they take you through the Function Arguments dialogue, it gives you help on each argument.
Get all the bold arguments in, and you'll see the answer in two places in the dialog.
If you already know the name of the function, type the = name of the function, open parentheses, and then press Ctrl+A.
Alright hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
