Advanced Functions

PMT

The PMT is a function used to calculate the payments due on a specified regular period as subject to a loan.

The formula of the PMT function is PMT(Rate; DPER; PV; FV; F)

The PMT function takes four arguments, two of which are optional.

The Rate specified the interest rate of the loan.

The DPER represents the total number of payments on the set period.

The PV represents the present value of the loan.

The FV represents the future value intended to achieve after the last payment of the loan. As an optional argument, if you don’t specify it, the function considers it as 0.

The F, also an optional argument, indicates when the payments are due. The default value is 0 which indicates that the payments are due at the end of the period. Otherwise, a value of 1 indicates that the payments are due at the beginning of the period.

Practical Learning: The PMT Function

1. Start a new workbook.
2. In Sheet1, in cell B2, type MORTGAGE PAYMENT CALCULATOR FOR 15-YEAR LOANS
3. In cell A5, type 50000 and press Enter
4. In cell A6, type 55000
5. Select both cells A5:A6
6. On the Object bar, click the Number Format: Currency button .
7. Still on the Object bar, click the Number Format: Delete Decimal Place button twice to remove the decimal values.
8. With cells A5:A6 still selected, drag their Fill Handle down to cell A27 which will produce $160,000.
9. Click cell B4 and type 4%
10. Press Tab and, in cell C4, type 4.5%
11. Select cells B4:C4 and drag their Fill Handle to O4 which will produce 10.50%.
12. To implement the PMT function and calculate the periodic payments, click cell B5 and type =PMT(
13. Click cell B4
14. Type /12;15*12;
15. Click cell A5 and press Enter. Notice the value of the monthly payment. It is in red and in parentheses because the value is negative
16. Click cell B5 to give it focus.
17. In the Formula Bar, click between B and 4.
18. Press Shift + F4 to make B4 an absolute reference. This is because, for the same amount of loan, in this particular scenario, the rate doesn’t change, only the loan amount does. Notice that the function now displays $ signs on the first argument
19. Press Enter.
20. Click cell B5 to select it.
21. Drag its Fill Handle to C5. Notice the error value in cell C5
22. Click cell C5 to make it active.
23. While cell C5 is selected, in the Formula Bar, change the content to display as follows: =PMT($C$4/12;15*12;A5)
24. Press Enter
25. Select cells B5:C5.
26. On the main menu, click Format -> Cells…
27. Click the Numbers property sheet.
28. In the Category list, click Currency.
29. In the list under Format, click the 2nd value $1,234.00
30. In the Format Code text box, the second - and delete it:
     
    
31. Click OK.
32. While cells B5 and C5 are still selected, drag their Fill Handle down to row 27.
33. Save the file as Mortgage Payments