Interest by iteration

Once again, as in the case of the savings plan,  solving for I turns out to be impossible and iteration has to be used. In most cases you can find the interest rating using the Excel function:

 =RATE(nper,PMT,PV,FV,type,guess)

For example, if you take $200 for 7 years from an initial deposit of $1000 what interest rate to you need to make the final balance zero: 

 =RATE(7,200,-1000,0)

which works out to 9% per annum.

An annuity calculator

Now that we have the formula to calculate any one of PV, I, S and n from any of the other three it is quite easy to construct an annuity calculator along the same lines as the savings plan calculator given in Chapter Four.

First enter the text as shown in Figure 2 and the following formulas

=PV(B7/12,B9*12,-B8)      in B6

=RATE(C9*12,C8,-C6,0)*12  in C7

=PMT(D7/12,D9*12,-D6,0)   in  D8

and

=NPER(E7/12,E8,-E6,0)/12   in E9

As in the case of the savings plan entering the three appropriate values into the spreadsheet gives you the fourth in the cell coloured yellow.

Figure 2: The annuity calculator

Formula summary

For an ordinary annuity type is set to 0 or omitted and FV is often 0.

<ASIN:0470044039>

More Financial Functions: