Hands on: Using Excel's PMT function

When Lord Polonius first said to Laertes, ‘Neither a borrower nor a lender be’, it was before the introduction of savings institutions and the safety they can offer. Now we have the choice of renting money to or from banks.

The interest can go into their pockets or ours. Everything we buy can carry a bank surcharge or a bank contribution. If you’ve never thought of it that way, let’s use a spreadsheet to make a comparison and, in the process, investigate PMT – one of Excel’s financial functions.

A number of banks offer special ‘Christmas saver’ accounts these days to encourage you to save up for large purchases you make during the festive season. But in fact you can choose any ordinary savings account and save up for any desired purchase costing any amount.

The example here uses £2,000. Enter that amount in cell B2 of a new worksheet. We will calculate how much you would need to save each month to be able to buy the item for cash in a year. In B3 enter the annual interest rate paid on the savings account.

You decide what that will be, but the illustration shows four per cent. The financial functions of most spreadsheets are very similar, but in this case we will use Excel.

To calculate how much you need to deposit every month, in cell B4 enter:

=-PMT(B3/12,12,0,B2,1)

The minus sign at the front is used because Excel considers cash you pay out to be negative and cash you receive, such as dividends or credit interest, to be positive.

In the first argument of the payment function, the interest rate is divided by 12 here, because this example account is compounded monthly.

If it were compounded quarterly, you would divide by four, and so on. The second argument is the number of payment periods – in this case 12.

The third argument is zero because the account starts with nothing in it until the first of our 12 deposits. The fourth argument is the amount we wish to end up with: 2,000.

And the fifth argument is a 1, indicating the deposits are made at the beginning of each period.
In B6 enter:

=12*ROUND(B4,2)

to see how much you have contributed. The payment is rounded because of all the decimal points calculated but not displayed. In B7 enter: =B2-B6 to see how much the bank has chipped in.

The lure of plastic
Now let’s see what happens if you succumb to the seductive credit card. The annual interest rate may be quoted as an APR (annual percentage rate) or an APY (annual percentage yield).

Payments are usually made and interest compounded monthly. If the interest rate is quoted as APY, enter that rate in cell E3. If it’s quoted as APR, and that rate is, for example, ‘19.9% typical’, enter that rate in E2 and in E3 enter:

=NOMINAL(E2,12)

to convert it into the nominal rate.

To calculate the necessary monthly payments, in cell E4 enter

=-PMT(E3/12,12,B2,0,0)

The first three arguments are as before. The fourth argument here is zero as that’s the amount we wish to end up with.

The fifth argument is zero because the payment is made at the end of the period.
In E6 enter:

=12*ROUND(E4,2)

to see how much you have paid. In E7 enter =E6-B2 to see how much the bank has charged you.

This shows that with this credit card you would pay out £183.63 a month, a total of £2,203.56 (£183.63*12) of which £203.56 goes to the bank.

If you save the £2,000, you pay out only £163.09 a month, a total of £1,957.08 (£163.09*12), and the bank chips in £42.92 in credit interest to make up the needed £2,000.

Overall you are £246.48 ahead of the game (£2,203.56 – £1,957.08). Chances are you’ll even get a newer model of whatever you are purchasing, because you waited a year.