You will build a reusable workbook with tabs for future value, present value, monthly payment, rate and number of periods.
Every money question in this module comes down to five numbers: what you start with, what you add each period, the rate, how long, and what you end up with. Know any four and a spreadsheet will find the fifth. The trouble is that each function wants its arguments in a slightly different order, and the minus signs catch everyone out. Rebuilding the formula from memory each time you face a decision is how mistakes get in.
So in this project you build the workbook once, test it, and keep it. Allow about 45 minutes in Excel or Google Sheets.
Build one workbook with six tabs. Five of them each answer one question:
FV: what will this grow to? PV: what is a future sum or stream worth today? PMT: how much do I need to pay in, or pay off, each period? RATE: what rate turns this sum into that one? NPER: how many periods will it take?
The sixth tab compares a lump sum now with a stream of payments later, at two discount rates. Every tab has its inputs clearly marked, one tested example, and a note on signs. Every figure below is an example from this course, so you can check your formulas against answers you already know.
Use the same layout on every tab so you never have to hunt. Put labels in column A and inputs in column B, rows 1 to 5, in the order the function expects: rate per period, number of periods, payment per period, present value, future value. Shade the input cells in one colour. Put the formula in B7, with a label in A7. In row 9 write the sign note: "Money paid in is negative. Money received is positive."
On each tab, one of those five rows is the number you are solving for. Leave that cell blank and write "answer in B7" beside it, so you never type an input into the wrong row.
FV tab. Formula =FV(B1, B2, B3, B4). Test with the lump sum from lesson 6.2, Future value: what a sum or monthly saving grows to: rate 3%, periods 10, payment 0, present value -10000. You should see S$13,439.16. Then try Priya's saving from the same lesson: rate =3%/12, periods 60, payment -200, present value 0, which should give S$12,929.34.
PV tab. Formula =PV(B1, B2, B3, B5). Test with lesson 6.3, Present value: what a future promise is worth today: rate 3%, periods 10, payment 0, future value -10000. You should see S$7,440.94.
PMT tab. Formula =PMT(B1, B2, B4, B5). Reverse Priya's saving to find the monthly amount: rate =3%/12, periods 60, present value 0, future value 12929.34. The answer is minus S$200.00, negative because it is money you pay in.
RATE tab. Formula =RATE(B2, B3, B4, B5). Test with the lump sum again: periods 10, payment 0, present value -10000, future value 13439.16. You should see 3.00%. For a second test, use the flat rate loan from lesson 3.4, Calculate the EIR of a flat rate offer with the RATE function: periods 60, payment -191.67, present value 10000, future value 0. Multiply the result by 12 and you get about 5.64%.
NPER tab. Formula =NPER(B1, B3, B4, B5). Ask how long S$10,000 takes to double at 6% a year: rate 6%, payment 0, present value -10000, future value 20000. The answer is about 11.9 years. The rule of 72 from lesson 2.3, Estimate doubling time in your head with the rule of 72, said about 12, so this also checks the rule.
If a tab shows an error or the wrong sign, the cause is nearly always the same: two inputs that should have opposite signs have the same one. RATE and NPER cannot find an answer if you pay in and get back money with the same sign.
This tab answers the question from lesson 6.3: take a sum now, or a series of payments later? Put the lump sum in B1, the yearly payment in B2, the number of years in B3, a low discount rate in B4 and a higher one in B5.
In B7 type =PV(B4, B3, -B2) and in B8 type =PV(B5, B3, -B2). Each gives the stream's value today at one rate. In C7 and C8, subtract the lump sum to see the gap, with =B7-B1 and =B8-B1. In D7 and D8, let the sheet say which wins, with =IF(B7>B1, "Stream", "Lump sum") and the same for row 8.
Test it with the motorcycle sale from lesson 6.3: lump sum 5000, payment 1100, years 5, rates 3% and 5%. At 3% the stream is worth S$5,037.68 and wins. At 5% it is worth S$4,762.42 and the lump sum wins. If your tab shows both, it works.
A finished workbook has six tabs, the same layout on each, shaded inputs, a sign note on every tab, and a test example that gives the answer shown above. Change any input and the answer moves.
Then it has one more thing: a real question of yours, answered. Pick something you are deciding this year where time is part of the choice. It could be how much to set aside each month for a goal, how long your savings would take to reach a target, or whether an offer of instalments beats a smaller sum now. Copy the right tab, give it your own inputs, and write the question and the answer in a line at the top.
Build the workbook, test every tab against the module examples, and use it to answer one real question about your own money.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).