Build a spreadsheet you can reuse that shows simple and compound growth side by side for any sum, rate and period.
A spreadsheet is the easiest way to watch simple and compound interest pull apart over many years. You type the formulas once. After that the sheet works out the arithmetic for any sum, rate and period, and you spend your attention on what the result means.
Working out compound interest with a calculator is manageable for 3 years. Doing it for 30 years at several rates is impractical, for example when you are weighing up a fixed deposit, a bond and a savings account. By the tenth line of working you have usually lost track of what you were trying to decide. The sheet takes about 25 minutes to build in Excel or Google Sheets. Each formula points at particular cells, so if you copy the layout described here you can enter the formulas exactly as written.
Put every number you might want to change in the top rows and nowhere else. Then a new scenario only means typing over an input cell, and the formulas are never touched.
Column A holds the labels and column B holds the values:
A1 "Starting sum", with 10000 in B1 (an example sum you'll replace later) A2 "Yearly rate", with 4% in B2 A3 "Years", with 30 in B3 A4 "Periods per year", with 1 in B4
Give B2 a percentage format so it displays as 4% instead of 0.04. Periods per year is how many times a year the interest is credited: 1 means yearly and 12 means monthly. The idea comes from Lesson 2.2, "Monthly compounding beats yearly at the same stated rate".
Leave row 5 blank. Row 6 carries the headings: Year in A6, Simple in B6, Compound in C6 and Gap in D6.
Type 0 into A7. Type `=A7+1` into A8 and fill it down to A37. Column A now counts from year 0 to year 30 in A7 to A37, which is 31 rows.
B7 holds the simple balance: `=$B$1*(1+$B$2*A7)`. This is principal x (1 + rate x years), the formula from Lesson 2.1.
C7 holds the compound balance: `=$B$1*(1+$B$2/$B$4)^($B$4*A7)`. Written out, that is principal x (1 + rate/periods)^(periods x years). The yearly rate is divided by the periods per year, and the power is the number of periods that have passed.
The dollar signs turn each reference into an absolute reference. It stays fixed on the input cells while you fill the formula downwards. Fill B7 and C7 down to row 37. Each row keeps reading B1 to B4 and takes its own year from column A.
Put `=C7-B7` in D7 and fill it down. This column is the gap, the compound balance minus the simple balance. It shows how much more compounding has earned than simple interest by each year. Last, format columns B, C and D as currency with two decimal places.
Read down column D. It shows zero at year 0 and year 1, rises slowly for some years, then grows faster each year. A separate gap column makes this widening easier to see than comparing two balance columns side by side.
Check three of your rows against these figures for S$10,000 at 4%, credited yearly (an invented example). At year 10 you should have simple S$14,000.00, compound S$14,802.44 and gap S$802.44. At year 20 you should have simple S$18,000.00, compound S$21,911.23 and gap S$3,911.23. At year 30 you should have simple S$22,000.00, compound S$32,433.98 and gap S$10,433.98.
Excel and Google Sheets both have a built-in FV (future value) function. Module 6 teaches it in full. For now you only need it as a separate check on your own formulas. It takes four inputs in this order: the rate per period, the number of periods, any regular payment (zero in this sheet) and the starting value. You enter the starting value as a negative because it is money you pay in. FV gives back a positive figure because that is money you receive.
Choose an empty cell such as F1 and enter `=FV(B2/B4, B4*B3, 0, -B1)`. With the example inputs it should read S$32,433.98, which matches C37 to the cent. If the two figures disagree, look at C7 first. The usual cause is a missing dollar sign or a bracket in the wrong place.
Next, type 12 into B4 to switch to monthly crediting. Every value in column B should stay the same, because simple interest doesn't depend on how often interest is credited. If column B is unchanged, the sheet is built correctly. The year 30 compound balance and F1 should both rise to about S$33,134.98. Any change to an input updates all 31 rows at once.
The final step is a chart of the two balances over the 30 years. Rows of numbers hide the upward curve of compounding, and a line chart shows it at a glance.
The finished sheet has four inputs at the top, 31 rows from year 0 to year 30, a gap column, and an FV cell that agrees with the final compound balance. Any input you change updates every row.
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.
Build the table and chart both balances over 30 years. Then write one sentence on 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).