Introduction to Cashflow
Article Index
Introduction to Cashflow
Savings plans
Ordinary and due
Payment, period and rate
A savings plan calculator

 

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.

Banner

First enter the text in columns A and B  and rows 1,2, 4, 5, 6, 7 and 8 as shown in Figure 5.

fig4

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

and

 =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 
FV(I,n,S,PV,type)
S regular saving
PMT(I,n,PV,FV,type)
n number of periods
NPER(I,S,PV,FV,type)
I interest rate
RATE(n,S,PC,FV,type,guess)

For a savings plan type is usually set to 1 and PV is often 0.

Banner

More Financial Functions:

<ASIN:0470044039>

<ASIN:0470044020>



 
 

   
RSS feed of all content
I Programmer - full contents
Copyright © 2014 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.