You will build a reusable spreadsheet that shows simple and compound growth side by side for any sum, rate and period.
Working out compound interest by hand for three years is fine. Working it out for thirty years, at three different rates, to compare a fixed deposit with a bond with a savings account, is not. By the time you finish, you have forgotten why you started. A spreadsheet that does the arithmetic for any sum, rate and period means you only have to think about the answer.
In this exercise you build that sheet in Excel or Google Sheets. It takes about 25 minutes. The formulas below use the cell positions given, so if you lay your sheet out the same way, you can type them in as written.
Keep every number you might change in one place, so you never have to edit a formula to try a new scenario. In column A, type four labels, and put the values beside them in column B:
A1 "Starting sum", B1 10000 A2 "Yearly rate", B2 4% A3 "Years", B3 30 A4 "Periods per year", B4 1
Periods per year is how often interest is credited: 1 for yearly, 12 for monthly, as in lesson 2.2, Monthly compounding beats yearly at the same stated rate. Format B2 as a percentage so 4% shows as 4%, not 0.04. The values here are an example; you will change them later.
Leave row 5 empty and put headings in row 6: Year in A6, Simple in B6, Compound in C6 and Gap in D6.
In A7 type 0. In A8 type =A7+1 and fill it down to A37, so the column runs from year 0 to year 30.
In B7, the simple interest balance, type =$B$1*(1+$B$2*A7). This is principal times (1 plus rate times years), the formula from lesson 2.1.
In C7, the compound balance, type =$B$1*(1+$B$2/$B$4)^($B$4*A7). The rate is divided by the number of periods per year, and the power is the number of periods that have passed.
The dollar signs fix the reference to the input cells, so when you fill the formulas down, each row still reads from B1 to B4 but uses its own year from column A. Fill B7 and C7 down to row 37.
Last, in D7 type =C7-B7 and fill it down. This is the extra money compounding has made compared with simple interest. Format columns B to D as currency with two decimal places.
Read down column D. It sits at zero for year 0 and year 1, then creeps up, then climbs faster and faster. Seeing the gap as its own column makes the widening visible in a way that two balances side by side do not.
Every spreadsheet has a built-in function for this, and you should use it to check your own formulas. In an empty cell, say F1, type =FV(B2/B4, B4*B3, 0, -B1).
FV takes the rate per period, the number of periods, any regular payment (zero here) and the starting value. The starting value is negative because it is money you pay in; the result comes back positive because it is money you receive. Module 6 uses FV properly.
The FV result should match the compound figure in your last row, C37, to the cent. If it does not, the usual culprits are a missing dollar sign or a bracket in the wrong place in C7.
With the inputs above, S$10,000 at 4% credited yearly, the sheet should show these rows. In year 10, simple S$14,000.00, compound S$14,802.44 and a gap of S$802.44. In year 20, simple S$18,000.00, compound S$21,911.23 and a gap of S$3,911.23. In year 30, simple S$22,000.00, compound S$32,433.98 and a gap of S$10,433.98. FV in F1 shows S$32,433.98.
Now change B4 to 12 for monthly crediting. Simple interest does not change, because it ignores crediting. The compound balance in year 30 rises to about S$33,134.98, and FV gives the same figure. If both of those happen, your sheet works.
A finished sheet has the four inputs at the top, 31 rows from year 0 to year 30, the gap column, and an FV cell that matches the final compound balance. Changing any input updates every row at once.
Before you put this away, set the inputs to a scenario you care about, perhaps the balance of your largest savings account and the rate it really earns, from your money map in lesson 1.4. Rows of numbers hide the shape of compounding. A picture of the two balances over thirty years shows it at a glance, and that is the last thing your sheet needs.
Build the table, chart both balances over 30 years, and write one sentence about what the chart shows that the numbers alone did not.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).