The **PMT** function provides an easy way to calculate loan payments and savings plans in Excel. More specifically, it can be used to determine the constant periodic payment required to pay off (or partially pay off) a loan or a savings plan that will result in saving a set amount in a specific length of time.

This tutorial is compatible with most versions of Excel including 2007, 2010, 2013, 2016, and Office 365.

### PMT Function Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, comma separators, and arguments. The syntax for the **PMT** function is:

=PMT(Rate, Nper, Pv, Fv, Type)

**Rate** (required): The annual interest rate for the loan. If payments are made monthly, divide this number by 12.

**Nper** (required): The total number of payments for the loan. Again, for monthly payments, multiply this by 12.

**Pv** (required): The present or current value or the amount borrowed.

**Fv** (optional): Future value. If omitted, Excel assumes the balance will be $0.00 at the end of the time period. For loans, this argument can generally be omitted.

**Type** (optional): Indicates when payments are due. The number 0 indicates the end of the payment period, and 1 indicates the beginning of the payment period.

### Excel PMT Function Examples

The image above includes several examples of the PMT function being used to calculate loan payments and savings plans.

- The first example (
**cell D2**) returns the monthly payment for a $50,000 loan with an interest rate of 5% to be repaid over 5 years. - The second example (
**cell D6**) returns the monthly payment for a $15,000, 3-year loan, an interest rate of 6% with a remaining balance of $1,000. - The third example (
**cell D11**) calculates the quarterly payments to a savings plan with a goal of $5,000 after 2 years at an interest rate of 2%.

### Steps for Entering the PMT Function

Let's look at how the **PMT** function is used in the first example. Options for entering the function and its arguments into a worksheet cell include:

- Typing the complete function into
**cell D2**. - Selecting the function and its arguments using the
**Formula Builder**.

Although it is possible to type the complete function manually, many people find it easier to use the Formula Builder as it takes care of entering the function's syntax including brackets and commas between arguments.

- Select
**cell****D2**to make it the active cell. - Select the
**Formulas tab**of the**ribbon**. - Choose
**Financial functions**to open the function drop-down. - Select
**PMT**in the list. - Select the
**Rate**line. - Select
**cell****B2**to enter this cell reference. - Type a forward slash (
**/**) followed by the number**12**in the **Rate**line. - Select the
**Nper**line. - Select
**cell****B3**to enter this cell reference. - Select the
**Pv**line. - Select
**cell****B4**in the spreadsheet. - Select
**Done**to complete the function.

=PMT(B2/12,B3,B4)

The answer (in this case **$943.56**) should appear in **cell** **D2**. When you select **cell** **D2**,** **the complete function appears in the **formula** **bar** above the worksheet.

### Additional Formula Modifications

**Calculating Loan Repayment Total**

Find the total amount of money paid over the duration of a loan by multiplying the **PMT** value by the value of the **Nper*** *argument. In the first example, that would mean modifying the formula to follow such a mathematical form:

$943.56 x 60 = $56,613.70

**Formatting Negative Numbers in Excel**

In the example image, the answer **$943.56** in **cell** **D2** is surrounded by parenthesis and has a red font color to indicate that it is a negative amount because it is a payment. The appearance of negative numbers in a worksheet can be altered using the Format Cells dialog box.