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

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

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. 

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:

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

 Financial Functions

 Buy from Amazon

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.

1. Understanding Percentages
   Percentages are something familiar to us all - but they present many pitfalls that need to be avoided.
   
   
2. 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. 
   
   
3. 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.
   
   
4. 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.
   
   
5. Cashflow Continued - Annuities
   We move on to annuities in the second of three chapters devoted to exploring the way in which interest rate affects 
   
   
6. Exploring Repayment Loans
   Repayment loans are the subject of the last of three chapters which look at the effects of regular cashflows.
   
   

7. 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.
   
   

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

9. Advanced Investment Analysis IRR and MIRR
   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. 
   
   

<ASIN:1871962013>

<ASIN:B07S79ZVMQ>

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info

<ASIN:1118490444>

<ASIN:B07S79ZVMQ>