Page 2 of 3
The workings of a repayment loan or annuity are actually easier to understand if we first consider the closely related situation of a regular saving plan.
In this case a regular sum S (a payment usually denoted PMT) is deposited each period and the total sum accumulated attracts interest at I% (Rate) at the end of each period.
It is a convention that the money is deposited at the end of each period and money that you pay out is negative. Thus after the first period the balance is simply the first regular payment:
After the second period the amount is the first regular payment plus the interest it has earned:
=S*(1+I) + S
After the third period the amount is:
=[S*(1+I)+S]*(1+I) + S
and so on.
You can see that as each period passes the amount already on deposit is increased by being multiplied by (1+I) and has S added to it.
To work out the Future Value of the savings plan after n periods you could simply construct a spreadsheet that calculates the repeated multiplication by (1+I) and adding S but there is a simpler way of working out the same thing.
After a little math it can be shown that the Future Value is given by:
(1+I)n - 1
FV= S* ------------
This is such a complicated looking formula that nearly all spreadsheets provide a single financial function, usually called FV, to calculate the same result.
This is just the function we met in Chapter Two in the calculation of compound interest only now we can specify an initial deposit - the Present Value PV - and a regular payment S.
The common spreadsheet function:
will calculate the Future Value FV of a regular saving of S, at I% for nper periods with a starting blance of PV which is often zero.
If you don't specify the PV then it is assumed to be zero by default.
Notice that as with nearly all financial functions it is best to regard the sums specified in the function, i.e. S and PV, as flowing from the depositor. So a deposit of $100 is -$100 and a payment of $10 per month into the account is -$10 per month.
However the result of the function is the status of the account - postive when in credit and negative when in debt.
The cashflows are -PV and -S.
As with all financial calculations the interest rate has to be expressed in terms of the regular payment period and for a savings plan the money paid into the acount is negative.
For example, if you start a savings plan that calls for $10 per month at 6% per annum paid monthly then after 5 years the Future Value will be
This is a horrible looking formulae and one that you are very likely to make mistakes in when trying to enter it into a spreadsheet. How much simpler the equivalent financial functions appears:
and this of course gives exactly the same result, $697.70.
Notice that there is a minus sign in front of the payment - it is a convention that cash paid in (e.g. S) is negative while cash paid out (e.g. FV) is positive.
If you make an initial deposit of PV at the start of the savings plan to get it going then the formula have to be altered to:
(1+I)n - 1
FV= S* ------------ + PV*(1+I)n
That is, the compounded interest earned by the initial deposit has to be added to the Future Value.
In most spreadsheets you simply include the initial deposit as the optional Present Value (PV) parameter at the end of the previous formula.
For example, if deposit $100 and save $10 per month at 6% per annum paid monthly then after 5 years the Future Value will be:
which works out to $832.59 which is more than the previous result by $134.89 - the amount of interest earned by the initial $100 at 6% for 5 years.
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 :
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:
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:
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:
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.