Ordinary and due

You may be puzzled as to why in the calculation of the Future Value of the savings plan the first payment was made at the end of the first time period and so earned no interest.

The reason for adopting this convention is that it is used by nearly every spreadsheet’s financial functions relating to annuities.

In fact there are two types of annuities:

• ordinary annuities where the payment is at the end of each period
  
  
• annuities due where payment is at the start of each period

In practice savings plans are usually calculated as annuities due so that when the plan has been running for n years it is indeed exactly n years since the first payment was made.

Nearly all spreadsheets include an optional type parameter as part of their financial functions that is set to zero to indicate that the payment is made at the end of each period and one to indicate that the payment is made at the end of the period.

By default, i.e. if you omit the parameter as we have done so far, then it is assumed that the payment is made at the end of each time period.

At first the distinction between the two types of payment might seem confusing but you can usually determine which you should use by asking yourself a simple question.

Is there any interest involved in the first time period?

That is, if in month one you deposit $10 and the amount in the account at the end of month one is $10 then no interest has been added. This is an ordinary annuity and the formulas relating to it should have the type parameter set to zero or omitted. 

If on the other hand the amount in the account at the end of the first month is $10 plus the interest then we have an due annuity and the type parameter should be set to one to calculate interest in the first period.

This sounds easy but it can still be confusing. 

For example, if you open a savings plan that you have to pay in yearly it is difficult to remember that the first payment represents the first year of an ordinary annuity and attracts no interest.

So, if you pay $100 per year at 10% per annum into a savings plan then the standard FV formula gives that at the end of the first year you have :

=FV(10%,1,-100)

which works out to $100. By default an ordinary annuity has been calculated where the payment is at the end of the first time period and so no interest is due.

However, for a savings plan you would expect to  earn interest in the first year and so the correct formula is with the type parameter set to one:

=FV(10%,1,-100,0,1)

Notice that we now have to specify PV as zero because we need to specify the last parameter to the function and hence need to enter some value for the one before.  In this case the formula returns the result $110 to indicate that the payment was made at the start of the period and so earned interest.

There is also a the potential to confuse the role of any lump sum that starts a savings plan. The inital deposit is assumed to earn interest during the first period in both types of annunity calcuation.

That is, if you start the savings plan used above with an initial deposit of $100 then the ordinary annuity calculation gives for the first year:

=FV(10%,1,-100,-100,0)

which works out to $210 i.e. the intial $100 made $10 interest plus the payment of $100 at the end of the year which did not make any interest. For a due annuity the calculation is:

 =FV(10%,1,-100,-100,1)

which works out to $220 because the initial $100 made $10 interest and the first payment made at the start of the year also made $10 interest giving $220.

In most cases you can work out which type of calculation you need to do by considering the situation at the end of the first time period.

Notice that most of the financial functions concerned with periodic payments have a final type parameter that works in more or less the same way.

Payment, period and rate

Payment

Once we have a formula that gives the Future Value given the interest rate and the term the next obvious question is what the corresponding formulae are for the interest rate given the Future value and the term and for the term given the interest rate and the Future Value.

If you want to save an amount of money equal to FV in n time periods with corresponding interest rate I then you have to save

       FV*I
S = ------------
    (1+I)n - 1

per time period.

As you might expect for such a complicated formula most spreadsheets have an equivalent financial function PMT:

S=PMT(I,n,PV,FV,type)

where I is the interest rate, n the number of periods, PV the intial deposit i.e. the Present Value and FV the final or Future Value of the acccount. The final parameter type is zero or one depending on whether the payments are at the start or end of each time period. If the savings scheme starts from a zero balance then the present value PV is zero.

For example, if you want to save for a final sum of $697.7 in 5 years at an interest rate of 6% per annum paid monthly then you would use:

=PMT(0.06/12,5*12,0,697.7)

which returns a result of -$10 per month which agrees with the previous example.

The FV is regarded as positive because it is the value of the account in 5 years time. Notice that the payment is negative indicating that you have to pay into the savings scheme. You can, of course, include an inital sum in the saving scheme and specify if the payments are to be at the start or the end of each period.

For example, suppose that we have an initial deposit of $100 then the at the same interest rate over five years you need to deposit:

=PMT(0.06/12,5*12,-100,697.7)

which works out to only -$8.07 per month.

Notice that the PV is a negative cashflow. If you make a mistake and enter a positive PV then you should spot the error by noticing that the monthly payment goes up!