Cashflow Continued - Annuities
Cashflow Continued - Annuities
Written by Janet Swift   
Article Index
Cashflow Continued - Annuities
Annuity calculator

Present Value

You can find the Present Value, i.e. the amount needed to be deposited to provide a payment S for n periods with interest rate I, of regular payments using the formula


Most spreadsheets provide a financial function to find PV


this gives the amount needed to generate a payment of S for n periods given and interest rate of I and leaving FV in the account at the end of n periods. Usually FV is set to zero because you need to know the minimum amount needed to fund the cash flow without leaving a residual sum in the account. 

For example, if you need a cash flow of $200 per month for 7 years at 10% per annum then the amount you need to deposit is:


which works out to -£937.68 which is a little less than the $1000 in the previous examples.

Payments needed

Finding the regular amount that can be taken given the value of the annuity and the interest rate produces a fairly simple looking formula -


In this case nearly all spreadsheets have a simple financial function that will work out payments. In most spreadsheets it is =PMT,  the same one that works out the payments for a savings plan:


where Rate is the interest rate (which is normally denoted I in this book), nper the number of periods, PV (Present Value) is the initial deposit FV (Future Value) is the final sum of money in the account.

For example, how much can you take for 7 years if the initial deposit is $1000 and the interest rate is 10% per annum:


which works out to $205.41, which again should be compared to the result given earlier that taking $200 leaves a little in the account after 7 years. The sum quoted reduces the account to exactly zero after exactly 7 years. 

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 function:


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: 


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 4.

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


=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.


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.


Financial Functions



Spreadsheets take the hard work out of calculations, but you still need to know how to do them. Financial Functions with a spreadsheet is all about understanding and reasoning, using a spreadsheet to do the actual calculation.

  1. Understanding Percentages
    Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.

  2. Interest Simple and Compound
    We explore the idea of borrowing money for a specified rate of interest or earning interest on an investment. The ideas of Present and Future Value PV and FV are introduced. 

  3. Effective Interest Rates
    We explore the idea of the `effective’ annual interest rate and then on to the Effective Interest Rate/Annual Percentage Rate, the much quoted EIR or APR.

  4. Introduction to Cashflow - Savings Plans
    In the first of three chapters covering the way in which interest rate affects cashflow we explore savings - but first we introduce some general ideas that apply equally to annuities and repayment loans.

  5. Cashflow Continued - Annuities
    We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects 

  6. Exploring Repayment Loans
    Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.

  7. Present and Future Values
    The principles of present and future value apply even if the cash flow is irregular. The calculations are just a matter of breaking down the cash flow calculations into simple steps.

  8. Investment Analysis
    How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.

  9. Advanced Investment Analysis IRR and MIRR
    The IRR is perhaps the most complicated of the measures of the value of an investment with an irregular cash flow. Understanding exactly what it means is a good step toward making correct use of it. 


To be informed about new articles on I Programmer, install the I Programmer Toolbar, subscribe to the RSS feed, follow us on, Twitter, FacebookGoogle+ or Linkedin,  or sign up for our weekly newsletter.


blog comments powered by Disqus

<ASIN: 0470475366>







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