MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2024: Discover New Functions by Using fx

June 20, 2024 - by

Excel 2024: Discover New Functions by Using fx

There are 509 functions in Excel. I have room for only 155 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.

A simple model to calculate a car payment. The principal of 25995 is in B1. The Term of 60 months in B2. The interest rate of 5.25% in B3. The payment cell of B5 is selected. You can either choose Insert Function on the Formulas tab, or click the small script fx to the left of the formula bar.
A simple model to calculate a car payment. The principal of 25995 is in B1. The Term of 60 months in B2. The interest rate of 5.25% in B3. The payment cell of B5 is selected. You can either choose Insert Function on the Formulas tab, or click the small script fx to the left of the formula bar.

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 shows functions related to your search term. Click on a function in that box to see the description at the bottom of the dialog.

Caution: A change in Excel Help caused the search to return less results starting in 2019.

In the Insert Functions dialog, use the search box and type Loan Payments. Click Go. Choices such as PMT, PV, IPMT, PPMT, RATE will appear. Click on any search result and a description of the function appears at the bottom of the dialog. Currently PMT is selected and it will Calculates the payment for a loan based on constant payments and a constant interest rate.
In the Insert Functions dialog, use the search box and type Loan Payments. Click Go. Choices such as PMT, PV, IPMT, PPMT, RATE will appear. Click on any search result and a description of the function appears at the bottom of the dialog. Currently PMT is selected and it will Calculates the payment for a loan based on constant payments and a constant interest rate.

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 in each argument box, help appears at the bottom of the window, with specifics on that argument.

The Function Arguments dialog for PMT has boxes for the five arguments: Rate, NPER, PV, FV, and Type. The labels for the first three arguments are bold, indicating they are required. As you click into the rate box, help at the bottom gives an example of 6%/4 for quarterly payments at 6% interest.
The Function Arguments dialog for PMT has boxes for the five arguments: Rate, NPER, PV, FV, and Type. The labels for the first three arguments are bold, indicating they are required. As you click into the rate box, help at the bottom gives an example of 6%/4 for quarterly payments at 6% interest.

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.

With the cursor in Rate, click on B3 and then type /12. Tab to the Nper box, and the help will show Nper is the total number of payments for the loan. Click on B2. Also helpful: to the right of each box is the intermediate result. For example, B3/12 is 0.004375.
With the cursor in Rate, click on B3 and then type /12. Tab to the Nper box, and the help will show Nper is the total number of payments for the loan. Click on B2. Also helpful: to the right of each box is the intermediate result. For example, B3/12 is 0.004375.

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.54065 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.

For the present value argument, type a minus sign and then click on B1. At this point, the three required arguments are entered, so the dialog box will show you the calculated answer of $493.
For the present value argument, type a minus sign and then click on B1. At this point, the three required arguments are entered, so the dialog box will show you the calculated answer of $493.

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 the Function Arguments dialog 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 and then press Ctrl+A. If you press Ctrl+Shift+A, Excel will insert the names of the arguments into the formula.

Thanks to Excel MVP Nabil Mourad from OfficeInstructor.com for the Ctrl+Shift+A technique.

This article is an excerpt from MrExcel 2024 Igniting Excel

Title photo by fernando bacheschi on Unsplash