A Savings Plan

What if you are interested in saving rather than borrowing? The PMT function continues to be a useful tool.

Suppose you want $3000 in 2 years time and you can get a fixed interest rate of 5.45% for making regular monthly deposits.

This time we want the title "Savings Plan" in A1, the labels “Amount” in A3, “Years” in A4, “Interest Rate” in A5, and “Monthly Deposit” in A6 and the values 3000 in B3, 2 in B4 and 5.45% in B5 - and this should now display correctly because of the previous formatting change that set the number of decimal places to 2.

Start the formula in B6 by typing =PMT( and then click the fx ito open the Function Arguments box. Click on B5 to place it in the Rate box, then type “/12” to convert from the annual rate to a monthly one.

Tab to the next box, Nper, click on B4 then type “*12” to convert from years to months. Skip over the next box, Pv, then when you get to the Fv box click on B3. Finally we come to the Type box. For this example a savings deposit is going to be made at the beginning of each month so enter 1 in this box.

With all the entries made, click OK. The result, $118.06, is as explained above shown as a negative number in B6.

Imagine you have already saved $2000 and want to achieve $5000 at the end of another two years. What monthly deposit do you need to make?

Enter the label “Existing savings” in A7and put 2000 in B7. Notice however that this, following the convention for the monthly deposit, is a negative value. Type in the minus sign besides it and use cell formatting to display it in currency format in red as well as with the minus sign.

Next change the value in B3 to 5000. Then edit the PMT formula by placing the cursor in B6, pressing the F2 function key on the keyboard and clicking the fx icon to reopen the Function Arguments box.

Go to the Pv box and click on B7 then click on OK.

You now only have to save $109.02 monthly instead of $118.06.

<ASIN: 0273714066>

<ASIN:0143115766>