Page 5 of 5
A savings plan calculator
Using the formulae derived in the previous section it is fairly simple to create a savings plan calculator that will supply the missing value given any three of FV, S,I and n.
First enter the text in columns A and B and rows 1,2, 4, 5, 6, 7 and 8 as shown in Figure 5.
Figure 5: The savings spreadsheet
The actual work of calculation is done in each of the columns B, C, D and E. Each column calculates one of FV,I, S and n as indicated by its heading.
The relevant formulas are:
=FV(B7/12,B9*12,-B8,1) in B6
=RATE(C9*12,-C8,0,C6,1)*12 in C7
=-PMT(D7/12,D9*12,0,D6,1) in D8
=NPER(E7/12,-E8,0,E6,1)/12 in E9
Now if you enter some values into each of the empty cells (yellow in the figure) you will see the formula in the same column work out the missing value. The type parameter is set to 1 in each case because you usually want to calculate a savings plan so that the payments are at the start of each time period.
Notice that the use of negative signs to indicate which values are paid out from the user. The PMT function automatically gives the correct answer as a negative value but as all of the other payments in the row have been entered using the convention that payments are positive a minus sign is added. Also notice that the NPER function automatically returns the answer in months but the spreadsheet is working in years - hence the division by 12 - and the Rate function returns a monthly interest rate - hence the need to mulitply by 12 to give a per annum rate. Generally it's the attention to detail such as signs and units of time that makes a financial spreadsheet trickier than you might initially think!.
A formula summary
The formulas introduced in this chapter:
|FV current balance
|S regular saving
|n number of periods
|I interest rate
For a savings plan type is usually set to 1 and PV is often 0.
More Financial Functions: