Insert Functions & Loan Payments
September 08, 2017 - by Bill Jelen
How to discover new calculation functions in Excel. Say you need to calculate a loan payment in Excel. This article will show you how to learn which formula to use and how Excel will walk you through the steps.
There are 400+ functions in Excel. I only have room for 40 tips this book, so there is no way I can cover them all. But instead of taking 450 pages to describe every function, I am going to teach you how to find the function that you need.
The Excel 2007 formulas tab introduced a huge fx Insert Function icon. But you don’t need to use the one on the Formulas tab; the same icon has been to the left of the formula bar ever since I can remember.
If you are trying to figure out how to calculate a loan payment, the Insert Function dialog will help. Click the icon next to the formula bar. In the Search for a Function box, type what you are trying to do. Click Go. The Select a Function box will show functions related to your search term. Click on a function in that box to see the description at the bottom of the dialog.
When you find the correct function and click OK, Excel takes you into the Function Arguments dialog. This is an amazing tool when you are new to a function. As you click into each argument box, help appears at the bottom of the window, with specifics on that argument.
Personally, I could never get the PMT function to work correctly because I always forgot that the Rate had to be the interest rate per period. Instead of pointing to the 5.25% in B3, you have to point to B3/12. Below, the help for Nper explains that it is the total number of payments for the loan, also known as the Term, from B2.
PV is the loan amount. Since I never write a check for negative $493, I want the answer from PMT to be positive instead of negative. That is why I always use –B1 for the PV argument. If you use B1 instead, you will get the correct $493.45065 answer, but it will appear as negative in your worksheet. Think of the original $25,995 as money leaving the bank; that is why the PV is negative.
Notice in the above figure that three argument names are bold. These are the required arguments. Once you finish the required arguments, the Function Arguments dialog shows you the answer in two places. I always use this as a sanity check. Does this answer sound like a typical car payment?
This one topic really covered three things: how to calculate a loan payment, how to use the fx icon to discover new functions, and how to use Function Arguments to get help on any function. If you are in a situation where you remember the function name but still want to use the Function Arguments dialog, type =PMT( with the opening parenthesis then press Ctrl + A.
Thanks to Tony DeJonker and Cat Parkinson for suggesting the function arguments.
Watch Video
- 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.
Video Transcript
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!
Download File
Download the sample file here: Podcast2022.xlsx
Title Photo: Pixabay
