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

We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects cashflow.

 Financial Functions


 Buy from Amazon

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. 


Chapter 5

Although a savings plan (see Chapter 4) is just a special case of an annuity it has become common practice to apply the term to a particular type of investment where a lump sum is exchanged for a cash flow (contrast with a savings plan where a cash flow is exchanged for a lump sum). In the case of an annuity the cash flow reduces the principal but interest still acts to increase it.

Annuities explored

If the amount deposited is PV and the amount periodically withdrawn is S (which is positive as it represents cash flowing to you) then at the end of the first time period the balance stands at:


and at the end of the second:

  PV*(1+I)*(1+I) - S*(1+I) -S

and so on.

Each month the balance earns I% interest and so increases by (1+I) but then S is removed from the account.

In general after n time periods the balance stands at:

PV*(1+I)^n - S*(1+I)^(n-1)-
                  S*(1+I) ...-S

If you compare this situation with the savings plan you should notice that it is the same but with the regular payment positive (i.e. a cash inflow) and the initial deposit negative (i.e. a cash outflow).



An annuity - the deposit decreases as cash is withdrawn but interest still acts to increase it.


We already have a formula that gives the future value, i.e. the balance at time n, and it is just the formula used for the savings plan when there is an initial deposit PV :


and as long as we regard the cash flow as positive and the initial deposit as negative (i.e. money you paid out) then we don’t even have to change the sign of S in the formula.

That is, the function:


will calculate the Future Value FV of an annuity consisting of a regular payment of S, at I% for nper periods with a starting blance of PV. (Note that in Excel's terminology PMT is used for S and Rate for I.)

In the case of an annuity PV is usually negative and the payment S is positive. For example, suppose we set up an annuity with a PV of -$1000, then at an interest rate of 10% per annum if we withdraw $100 each year the FV or balance at the end of one year is:


which gives $900.

The reason is that the interest on $1000  is $100 making the balance at the end of the year $1100 and then we withdraw $200 reducing the balance to $ 900. The following year the balance will be:


which works out to $790 - i.e. plus $90 of interest and minus the $200 withdrawal and so on. 

Number of periods to zero

The FV function can be used to give the balance after any number of periods but what usually interests us is not the balance after n periods but when will the balance be reduced to zero.

The reason is that when the balance is reduced to zero the cash flow stops and thus this is the lifetime of the annuity.

We can also ask other obvious questions such as given a sum of money and an interest rate how much can be taken given that the cash flow has to last n time periods.

The first formula that we need is the number of periods it takes to reduce the Future Value to 0 -


solving this equation for n gives:



If this looks like an unappealing formula then you will be pleased to know that most spreadsheets supply a suitable financial function to make it unnecessary to struggle with the real thing.

To calculate the number of periods needed to reduce the Future Value of an annuity to zero you can use the NPER function that has already been introduced but this time with the FV set to zero:

n = NPER(I,S,PV,0,type)

For example, how long will an annuity paying $200 with an interest rate of 10% and an initial deposit of $1000 take to exhaust the capital? The answer is:


which works out to approximately 7.27 years. If you use the FV function to work out the balance after 7 years:


you will discover that there is only just over $51 left in the account - not enough to pay the annuity in the following year.

It this is a guaranteed annuity, i.e. one that is paid until the holder dies, then this is the point at which the annuity provider starts to make a loss.

You can also see that with a table that gives the probability of dying, an actuarial table, you can easily work out the expected return on a guaranteed annuity. 


<ASIN: 0470475366>