Page 4 of 8
In the days before computers, calculations involving compound interest were considered to be very difficult. Working out powers and performing the necessary multiplications was time consuming and error prone.
To reduce the burden tables and calculating approximations were often employed and an air of mystisism often surrounded financial calculations. With a spreadsheet however there is no need to to fear any calculation - working out a power is just as easy as a multiplication.
In Excel the future value (FV) can be calculated using:
where the principal is invested for n compounding periods at I% per period. Notice that there is a minus sign in front of PV - it is a convention that cash paid in (e.g. PV) is negative while cash paid out (e.g. FV) is positive. Don't worry about the 0 included in the function - in more complicated examples it represents any periodic savings you might make in addition to the lump sum initial value PV.
For example, $100 invested for 5 years at 4% per annum produces a final balance of $121.67.
The formula to type into a spreadsheet to calculate this is:.
or, if you prefer to express the interest rate as a decimal fraction:
Notice the minus sign in front of the initial value. As explained above this is because this represents cash that has been paid in.
The above formula works out compound interest that is added on an annual basis. If you are being paid monthly interest you have to divide the annual interest rate by 12 to give a monthly rate and multiply the number of period by 12 to give the number of months.
Similarly if interest is added daily the formula to use is:
The golden rule is always that the interest rate must be correct for the compounding period.
In fact the compounding period can make quite a difference as shown in Figure 4 which shows the formuals to enter as well as the results.
Figure 4: The effect of different compounding periods
Solving for I, PV or n
The standard compound interest formula calculates the final balance produced when a given sum, the principal or PV, is invested at a given interest rate for a given number of years. Often this is exactly what you want to know but sometimes financial questions are best put in terms of the interest rate or investment term required.
For example, if I need a return of $1000 in 10 years time how much to I need to invest if I can secure a 8% interest rate for the full 10 years?
To answer this and other similar questions what we need are three other rearrangements of the standard compound interest formula to give n, I and PV respectively.
For such a simple calculation these formulas are surprisingly difficult to work out because they involve manipulating powers. However what really matters is what the formula are and this can be seen in the table below along with equivalent financial functions:
|Term (n) in years
The only unusual component of the formulas given is the use of the ln (log natural) function in calculating n. If you haven’t encountered this function before don’t worry too much about it because all spreadsheets support the LN function so you can just use it.
For example, to return to the problem at the beginning of this section but slightly rephrased - what does PV have to be if FV is $1000, I is 8% per annum with interest added monthly and the investment period is 10 years? The answer is;
or using the PV function:
both of which work out to be $450.52.
You can check this by working out the FV of $450.52 at 8% per annum calculated monthly for 10 years:
or using the FV function:
which gives $999.99 or thereabouts. The exact result depends on the number of digits precision that your spreadsheet works to - but 1 cent in $1000 in ten years isn’t an unacceptable error.