Page 4 of 5
Payment, period and rate
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
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:
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:
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:
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!
Number of periods
Similarly if you want to know how long you will have to wait before a savings plan produces a given sum i.e. the FV then you have to work out
n = ----------
This too is a horrible looking formula and so there is corresponding financial function -
Once again I is the interest rate, S the periodic sum, PV the Present value, which can be zero, and FV the Future Value. The final parameter type is zero or one depending on whether the payments are at the start or end of each time period.
For example, if you want to save for a final sum of $697.7 at an interest rate of 6% per annum paid monthly and you can save $10 per month then then you would use:
which returns a result of 59.999 months which agrees with the previous example i.e. roughly 5 years. Notice that in this case it is vital that you get the sign on the payment correct. If you enter a positive value then this means that the savings plan pay you $10 at the end of each month and to reach the final FV you would have to "save" for -86 months. A result that is mostly nonsense!
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.
Now we come to the interesting calculation of the interest needed to make a regular payment of S grow to FV in n periods. This problem has no easy solution. There literally is no way of juggling the values in the formula to solve for I. The best we can do is make a guess at I and see if the value of FV computed using it is too high or too low. Using this information a new guess can be made that is closer to the correct value. The process is repeated until the guessed value of I is close enough to the value that we are looking for.
This guessing process is generally called ‘iteration’ and some spreadsheets do provide a financial function that will solve for I.
For example, Excel and Open Office have the RATE function which can be used to find the interest rate for a savings plan as
I= RATE(n, S,PV,FV,type,guess)
where n is the number of periods, S the payment, PV the inital balance, FV the final balance, type is zero or one depending on whether the payments are the start or the end of the time periods and guess is a guess at what the interest rate is. If you don't specify a guess then a random value is used as the starting value.
For example, if you want to accumulate $697.7 by saving $10 per month for five years you would need to find an interest rate of:
which is 6% per annum paid monthly. Notice the need to multiply by 12 to convert the monthly rate to a per annum rate.
The question remains what should be done in the case of a spreadsheet that doesn’t have a financial function to calculate I? The solution is to implement the iterative method directly. Using standard techniques (Newton's method) it is relatively easy to work out that if I is a guess at the interest rate then
Inew = I - ------------------
is closer to the true value.
This is another horrible looking formula but once entered into a spreadsheet it can be worked out without effort. The big difference is that it has to be worked out more than once to repeatedly improve the guess until it is close enough to the true value to make no practical difference.
Each time the new I obtained from the use of the formula is fed back into the equation to give another new value of I and so on until the change in the value with each iteration is very small. This may sound very complicated but in fact it is very easy to incorporate into a spreadsheet.