The Excel 2016 PMT function on the Financial button's drop-down menu on the Formulas tab of the Ribbon calculates the periodic payment for an annuity, assuming a stream of equal payments and a constant rate of interest. The PMT function uses the following syntax:
=PMT(rate,nper,pv,[fv],[type])
As with the other common financial functions, rate is the interest rate per period, nper is the number of periods, pv is the present value or the amount the future payments are worth presently, fv is the future value or cash balance that you want after the last payment is made (Excel assumes a future value of zero when you omit this optional argument as you would when calculating loan payments), and type is the value 0 for payments made at the end of the period or the value 1 for payments made at the beginning of the period. (If you omit the optional type argument, Excel assumes that the payment is made at the end of the period.)
The PMT function is often used to calculate the payment for mortgage loans that have a fixed rate of interest.
The figure shows you a sample worksheet that contains a table using the PMT function to calculate loan payments for a range of interest rates (from 2.75 percent to 4.00 percent) and principals ($150,000 to $159,000). The table uses the initial principal that you enter in cell B2, copies it to cell A7, and then increases it by $1,000 in the range A8:A16. The table uses the initial interest rate that you enter in cell B3, copies to cell B6, and then increases this initial rate by 1/4 of a percent in the range C6:G6. The term in years in cell B4 is a constant factor that is used in the entire loan payment table.
To get an idea of how easy it is to build this type of loan payment table with the PMT function, follow these steps for creating it in a new worksheet:
Enter the titles Loan Payments in cell A1, Principal in cell A2, Interest Rate in cell A3, and Term (in years) in cell A4.
Enter $150,000 in cell B2, enter 2.75% in cell B3, and enter 30 in cell B4.
These are the starting values with which you build the Loan Payments table.
Position the cell pointer in B6 and then build the formula =B3.
By creating a linking formula that brings forward the starting interest rate value in B3 with the formula, you ensure that the interest rate value in B6 will immediately reflect any change that you make in cell B3.
Position the cell pointer in cell C6 and then build the formula =B6+.25%.
By adding 1/4 of a percent to the interest rate to the value in B6 with the formula =B6+0.25% in C6 rather than creating a series with the AutoFill handle, you ensure that the interest rate value in cell C6 will always be 1/4 of a percent larger than any interest rate value entered in cell B6.
Drag the Fill handle in cell C6 to extend the selection to the right to cell G6 and then release the mouse button.
Position the cell pointer in cell A7 and then build the formula =B2.
Again, by using the formula =B2 to bring the initial principal forward to cell A7, you ensure that cell A7 always has the same value as cell B2.
Position the cell pointer in A8 active and then build the formula =A7+1000.
Here too, you use the formula =A7+1000 rather than create a series with the AutoFill feature so that the principal value in A8 will always be $1,000 greater than any value placed in cell A7.
Drag the Fill handle in cell A8 down until you extend the selection to cell A16 and then release the mouse button.
In cell B7, click the Insert Function button on the Formula bar, select Financial from the Or Select a Category drop-down list, and then double-click the PMT function in the Select a Function list box.
The Function Arguments dialog box that opens allows you to specify the rate, nper, and pv arguments. Be sure to move the Function Arguments dialog box to the right so that no part of it obscures the data in columns A and B of your worksheet before proceeding with the following steps for filling in the arguments.
Click cell B6 to insert B6 in the Rate text box and then press F4 twice to convert the relative reference B6 to the mixed reference B$6 (column relative, row absolute) before you type /12.
You convert the relative cell reference B6 to the mixed reference B$6 so that Excel does not adjust the row number when you copy the PMT formula down each row of the table, but it does adjust the column letter when you copy the formula across its columns. Because the initial interest rate entered in B3 (and then brought forward to cell B6) is an annual interest rate, but you want to know the monthly loan payment, you need to convert the annual rate to a monthly rate by dividing the value in cell B6 by 12.
Click the Nper text box, click cell B4 to insert this cell reference in this text box, and then press F4 once to convert the relative reference B4 to the absolute reference $B$4 before you type *12.
You need to convert the relative cell reference B4 to the absolute reference $B$4 so that Excel adjusts neither the row number nor the column letter when you copy the PMT formula down the rows and across the columns of the table. Because the term is an annual period, but you want to know the monthly loan payment, you need to convert the yearly periods to monthly periods by multiplying the value in cell B4 by 12.
Click the Pv text box, click A7 to insert this cell reference in this text box, and then press F4 three times to convert the relative reference A7 to the mixed reference $A7 (column absolute, row relative).
You need to convert the relative cell reference A7 to the mixed reference $A7 so that Excel won't adjust the column letter when you copy the PMT formula across each column of the table, but will adjust the row number when you copy the formula down across its rows.
Click OK to insert the formula =PMT(B$6/12,$B$4*12,$A7) in cell B7.
Now you're ready to copy this original PMT formula down and then over to fill in the entire Loan Payments table.
Drag the Fill handle on cell B7 down until you extend the fill range to cell B16 and then release the mouse button.
After you've copied the original PMT formula down to cell B16, you're ready to copy it to the right to G16.
Drag the Fill handle to the right until you extend the fill range B7:B16 to cell G16 and then release the mouse button.
After copying the original formula with the Fill handle, be sure to widen columns B through G sufficiently to display their results. (You can do this in one step by dragging through the headers of these columns and then double-clicking the right border of column G.)
After you've created a loan table like this, you can then change the beginning principal or interest rate, as well as the term to see what the payments would be under various other scenarios. You can also turn on the Manual Recalculation so that you can control when the Loan Payments table is recalculated.