Blog

One of my students and her husband were going thru the process to buy a car at one of the local dealers. By coincidence, I had just taught the class how to use the PMT function with Excel. The next day after they had completed the transaction, she would tell us an anecdote about it. It results that she had obtained the calculation using the PMT function, based on the values given, principal, interest, rate, and interest period (number of payments/months). This calculation allowed her to learn the maturity value or the total amount comprised by the principal + interest, they would have paid at the end, after finished making payments. Then, when the salesman presented the documentation for them “just to sign”, she noticed a discrepancy between the results she obtained and those by the salesman. Therefore, she asked the salesman to explain where that extra amount had come from, and so she found out the salesman had added charges they were not aware of. He, not happy at all, had to remove them, finally matching the amounts.

These things happen very commonly during this specific type of purchase. Salesmen make the buyers wait, usually for hours, with the purpose of wearing down their patience, turning them so anxious that they want just to sign the work papers and go home. Strategy that usually works. It should not happen to you, learn how to use the PMT Excel function. Let’s do it.

With Excel opens, enter the following data:

In cell C2 enter Rate:
In cell C3 enter # of payments:
In cell C4 enter Principal:
In cell C5 enter Payment:
In cell D2 enter 7%
In cell D3 enter 24
In cell D4 enter $5,000.00

No data is entered in cell D5; just have it selected, with the focus remaining on it.

Note: You may change the rate, the number of payments/months, and/or the principal for any value you want now. However, once we have obtained the result, you can change the values too, and the formula will update the value of the previous result.

At this point, we have paralleled values on column C and D, except for cell D5, where we will have our seek value produced by the formula, which is the amount of the monthly payments.

Make sure the focus is still on cell D5. Click on the Formulas tab, following by a click on the “Financial” group. A functions list is displayed; scroll down until you see the “PMT” function and click to select it. Its dialog box will open. Follow the steps below:

• Click on the Rate box to place the cursor  in it.
• Click on cell D2 in the spreadsheet. Its reference will appear.
• After the D2 reference, type a forward slash ( / ) followed by the number 12 – in the Rate line of the dialog box. This gives you the interest rate per month.
• Click on the Nper box to place the cursor there.
• Click on cell D3 in the spreadsheet.
• Click on the Pv box.
• Type a minus sign ( – ) and then click on cell D4 in the spreadsheet.
• Click OK in the dialog box.
• The answer $223.86 appears in cell D5.

When you click on cell D5 the complete function = PMT (D2/12, D3, – D4) appears in the formula bar above the worksheet.

You can save the work, so you can reuse it anytime in the future. Let us know what you think about this post, or post your comment.

Uriel Algaba