Excel Tutorial: How to Create a Formula with a Function in Excel

Creating a formula with a function by typing the function name or using the Function Library in Excel

Creating formulas with functions is very similar to creating regular formulas, with the exception that each function follows a specific syntax. All functions consist of the equal sign followed by the function name and the arguments within a set of parentheses. For instance, the formula to calculate the average in a range of data would be similar to: =AVERAGE(A1:A50).

You can create a formula using a function by typing the function name and arguments yourself, or you can use the Function Library to build the function step by step. In the following example, we will use the NPER function to determine how long it will take to pay back a loan of $15,000 if monthly payments of $100 are made and the interest rate is 2.5% per year.

1 In Book1, click in cell A8.

2 Choose Financial from the Formulas tab.

3 Select NPER from the Function list. In the formular bar Excel displays a description and the syntax for the NPER function, which calculates the number of payment periods to pay off a loan. The Function Arguments dialog box for the selected function NPER is displayed.

4 Enter .025/12 in the Rate box; enter 100 in the Pmt box; and 15000 in the Pv box.

The Function Arguments dialog box helps you build a formula step by step.

5 Click OK. Excel enters the formula in cell A8 and calculates the result of 130 pay periods.