You will be able to rebuild a money calculation from an advert, a quote or an assistant's answer and check whether it's right.
Joanne is borrowing money for a renovation. Their contractor hands them a brochure from a lender the contractor works with. It offers S$10,000 at 3% a year over five years, repaid at S$191.67 a month (all of Joanne's figures are examples). A line of small print mentions an effective interest rate. The two rates don't agree, and Joanne can't tell which one to believe.
Every figure you are given, whether in a quote, a projection or a chat reply, came out of a calculation that somebody else ran. You can pull that calculation apart and rebuild it. Then you can see whether the result holds up, and work out the reason when it doesn't.
Three kinds of figure work well. One is a loan quote that shows an instalment. Another is a savings or insurance projection that gives a value some years from now. The third is an assistant's answer to a calculation question. If you asked an assistant to calculate something in Lesson 5.1, Why the numbers in a paragraph can be wrong, you can use that answer. Whatever you choose needs a clear result to compare against, such as a monthly payment, a total repaid or a value at a future date. Leave out anything that depends on unknown future returns, such as a projection at an assumed growth rate, unless the document tells you the rate it used.
Before you open a spreadsheet, write down every input and assumption. For a loan, the inputs are the amount, the rate, the type of rate, the term, how often you pay, and any fees. For a savings projection, they are the starting sum, the regular saving, the rate, how often interest is added, the term, and whether any fees or charges are taken off. Next to each one, note whether the document states it, you assumed it, or it is missing.
Joanne picks the brochure and goes through it:
Amount, S$10,000: stated Rate, 3% a year: stated Type of rate: missing, because the brochure never uses the words "reducing balance" Term, five years (60 months): stated Payment frequency, monthly: stated Fees: missing, because the brochure mentions a processing fee but gives no amount
When an input is missing, ask the provider for it in writing. Keep working with the inputs you have while you wait for a reply. Joanne emails the lender with two questions: is the 3% a flat rate, and how much is the processing fee. Then they carry on.
Put each input in its own cell with a label next to it, the same setup you used in Lesson 5.2, Ask for the formula. Then try one formula at a time until the result matches the figure you were given.
For a loan, start with PMT, the standard loan formula. PMT assumes a reducing balance, which means interest is charged only on the amount you still owe. Joanne enters S$10,000 in B1, 3% in B2 and the term in years in B3. They then type =PMT(B2/12, B3*12, -B1), which returns S$179.69. The brochure says S$191.67. Because PMT doesn't match the quote, the quote is not using a reducing balance.
Joanne moves on to the next method, a flat rate, where interest is charged on the full original amount for the whole term. How money works lesson 3.2, Why a flat rate loan costs nearly double what it looks like, explains this calculation. The interest is S$10,000 x 3% x 5 years = S$1,500. Adding the loan gives a total repaid of S$10,000 + S$1,500 = S$11,500. Spread over the term, S$11,500 / 60 months = S$191.67, the same as the brochure figure. Joanne now knows what the lender means by 3%.
Once you have confirmed a flat rate, work out the effective interest rate (EIR). MoneySense describes the EIR as the true cost of a loan, which can be higher than the advertised rate because of the way interest is calculated, and it suggests using the EIR to compare loan packages and find the one that costs least. How money works lesson 3.4, Calculate the EIR of a flat rate offer with the RATE function, shows how. Joanne enters =RATE(60, -191.67, 10000)*12 and gets about 5.64% a year. This is the rate the brochure's small print referred to.
Before you trust a function's result, compare it with a rough figure you work out by hand, using the method in Lesson 5.3, Sanity-check results with a rough estimate. For a flat rate loan, divide one year's interest by about half the loan. You use half because, averaged over the term, that is roughly how much you owe.
For Joanne, S$1,500 / 5 years = S$300 a year in interest. About half of S$10,000 is S$5,000, and S$300 / S$5,000 = 6%. The RATE figure of 5.64% is in the right range of that 6% estimate, so Joanne can rely on it. With any flat rate quote, the true cost is much higher than the stated rate, and a fee raises it further.
Put your rebuilt figure next to the original and write a short note saying where the two differ and why. The note has to name the assumption that explains the gap. Giving only the size of the gap is not enough.
Joanne's note says that the 3% is a flat rate charged on the full S$10,000 for all five years, and that S$191.67 is the correct instalment on that basis. Worked out on a reducing balance, the true cost is about 5.6% a year. That figure is before the processing fee, which the lender has not yet disclosed, and any fee will push the cost higher.
If your figure came from an assistant, earlier in this module covered the usual causes of a gap. The assistant may have treated a monthly figure as a yearly one, compounded interest at the wrong frequency, or treated regular deposits as one lump sum. Sometimes you won't be able to explain a gap because an input is still missing. That still counts as a result, as long as you write down which input you need and who can give it to you.
When you have finished, your sheet holds the labelled inputs, your rebuilt figure, the original figure next to it, the rough estimate and your note. Keep the sheet, because when a similar quote comes along you only need to change the inputs.
Find your figure now. The best ones to check are those you were about to act on.
Rebuild one calculation in a spreadsheet and compare your result with the original. Write two sentences explaining any difference.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).