This article was originally published here in April 2003
In a previous article I showed you some useful financial spreadsheet functions. In this article I am going to use a spreadsheet to show you the power of compound growth.
Stakeholder pensions were introduced in April 2001. They brought with them some great features compared to earlier personal pension schemes. One such feature is that stakeholder pensions can be opened for children. Money saved in a stakeholder pension (even a child’s) receives tax relief at the basic rate of tax. This means that for every 78p saved the government adds 22p. To hit the £3,600 annual savings limit of a stakeholder pension you only need to save £2,808, the taxman adds the remaining £792. I bet you never thought that Gordon Brown could be so generous!
Of course money saved in a pension is inaccessible until you retire so this gives time for compound growth to show its power.
Run your spreadsheet application and create a new document. In cell B1 enter the value £3,600. This will be the starting sum, the amount initially saved. In cell B2 enter 0.05 (i.e. 5%). This will be the annual growth rate.
In cell A1 enter the formula B1*(B2+1). The calculated value of this cell should be £3,780. In cell A2 enter the formula A1*($B$2+1). Replicate cell A2 into cells A3 through A65. Click on cell A65 and check that the formula in it is A64*($B$2+1). Because we referenced cell B2 with dollar prefixing it stayed fixed whilst the A cell was copied relatively. These formulas replicate the compounding, growth-on-growth of an investment.
Cell A65 should show the value £85,823, the value of £3,600 compounded at 5% for 65 years. I feel that an annual growth rate of 5% is a reasonable figure to expect, allowing for charges and inflation. If you are feeling more optimistic or pessimistic you can change the number in cell B2 to see the effect on growth. It is really quite instructive to see the effect of even a small change in the growth rate compounding over a long time.
Now £85,000 is not a lot to retire on and £3,600 is a lot to save in a year, especially with the additional expenses of a baby! Let’s use the spreadsheet to investigate a gentler savings regime. Say that you can afford to save £1,000 a year from birth to the child’s 16th birthday. Change the value of cell B1 to 1000 and append "+$B$1" to the formulas in cells A1 through to A15. If you scroll down to cell A65 you will see that the final value will be over a quarter of a million. Not a bad pension fund to receive having never saved a penny yourself.
Most people don’t save for their pension until their early thirties. We can use the spreadsheet to see how well these people do. Let’s say that our example person saves £1,000 a year from the age of 30 until 65. Continue appending "+$B$1" to cells A16 through to A34. Cell A35 will contain the value of the retirement fund at age 65. Our late starter has roughly £95,000, £176,000 less than our child starter, even though the child put just £17,000 into their fund compared to the £35,000 the adult saved. The extra thirty years of compounded growth really stands out.
I hope that this article and any experimenting that you have done with the spreadsheet has been useful. Do play with the spreadsheet to see the effect of changing the starting sum and the growth rate - it is very eye-opening.