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!

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

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

This too is a horrible looking formula and so there is corresponding financial function -

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

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:

=NPER(0.06/12,-10,0,697.7)

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.

Interest

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.

Most spreadsheets have a 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:

=RATE(5*12,-10,0,697.7)*12

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

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.

A savings plan calculator

Using the formulae derived in the previous section it is fairly simple to create a savings plan calculator that will supply the missing value given any three of FV, S,I and n.

First enter the text in columns A and B and rows 1,2, 4, 5, 6, 7 and 8 as shown in below.

The savings spreadsheet

The actual work of calculation is done in each of the columns B, C, D and E. Each column calculates one of FV,I, S and n as indicated by its heading.

The relevant formulas are:

=FV(B7/12,B9*12,-B8,1) in B6

=RATE(C9*12,-C8,0,C6,1)*12 in C7

=-PMT(D7/12,D9*12,0,D6,1) in D8

and

=NPER(E7/12,-E8,0,E6,1)/12 in E9

Now if you enter some values into each of the empty cells (yellow in the figure) you will see the formula in the same column work out the missing value.

The type parameter is set to 1 in each case because you usually want to calculate a savings plan so that the payments are at the start of each time period.

Notice that the use of negative signs to indicate which values are paid out from the user. The PMT function automatically gives the correct answer as a negative value but as all of the other payments in the row have been entered using the convention that payments are positive a minus sign is added.

Also notice that the NPER function automatically returns the answer in months but the spreadsheet is working in years - hence the division by 12 - and the Rate function returns a monthly interest rate - hence the need to mulitply by 12 to give a per annum rate. Generally it's the attention to detail such as signs and units of time that makes a financial spreadsheet trickier than you might initially think!.

A formula summary

The formulas introduced in this chapter:

FV current balance

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

S regular saving

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

n number of periods

NPER(I,S,PV,FV,type)

I interest rate

RATE(n,S,PC,FV,type,guess)

For a savings plan type is usually set to 1 and PV is often 0.

Financial Functions

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.

Understanding Percentages Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.

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.

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.

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.

Cashflow Continued - Annuities We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects

Exploring Repayment Loans Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.

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.

Investment analysis How is it possible to evaluate investments that generate irregular cashflows? We explore how NPV can be used to make investment decisions.

IRR The Internal Rate of Return 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.