Cashflow Continued - Annuities
Article Index
Cashflow Continued - Annuities
Balance exhausted
Annuity calculator

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.

fig5

Figure 2: The annuity calculator

 

Formula summary

FV FV(I,n,S,PV,type)
S PMT(I,n,PV,FV,type)
n NPER(I,S,PV,FV,type)
I RATE(n,S,PC,FV,type,guess)

 

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

<ASIN:0470044039>

Banner

More Financial Functions:



 
 

   
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.