The PMT function
The =-PMT(C3/12,C4*12,C5) formula in cell C6 of the PMT-CUMIPMT functions worksheet, as shown in Figure 6.1, returns $578.57 as the monthly payment for a $25,000 loan paid off over 4 years at 5.25% interest:
Figure 6.1 – The PMT, CUMIPMT, and CUMPRINC functions
Before I get to the PMT function, let me share a trick I used to display the word years in cell C4. Visually, you see 4 years in the cell, but if you look in the formula bar, only the number 4 appears. This is because I used a custom number format to add the word years. This is a way of displaying text and numbers in the same cell while maintaining the cell as a numeric input. To do so, perform the following steps:
- Select the cell(s) you wish to format, in this case, cell C4.
- Choose Format | Format Cells or press Ctrl + 1 (
+ 1 in Excel for macOS). - Choose Custom on the Number tab.
- Enter
0" years"in the Type field. In this case,0represents our numeric...
