You will be able to tell when a calculated result is in the wrong range before you rely on it.
Joanne's sheet from lesson 5.2, Ask for the formula, then run it yourself, worked first time. Her friend Kumar's didn't. He copied a formula from a chat into his own sheet to price a S$20,000 loan at 4% over five years, and it told him the monthly payment was S$884.04. He nearly accepted it. The number was precise, the formula ran without an error, and he had no feel for what the answer should be.
A formula that runs is not the same as a formula that's right. This lesson gives you the habit that would have stopped Kumar in a few seconds: knowing roughly what the answer should be before you look at it.
Before you read the result, work out a ballpark. Round the inputs to easy numbers and do the sum in your head or on paper.
For Kumar's loan: S$20,000 over five years is S$4,000 a year of principal, or about S$333 a month. The interest on a reducing balance is charged on what's still owed, which starts at S$20,000 and falls to nothing, so on average about half the loan, S$10,000, is outstanding. At 4% that's about S$400 a year, or S$2,000 over five years. Add it to the principal: about S$22,000 repaid over 60 months, or roughly S$367 a month.
The correct payment, with these example figures, is S$368.33. The estimate is within a couple of dollars. Kumar's S$884.04 is more than twice it. Something was wrong, and he would have seen it immediately if he'd had the estimate in front of him.
You don't need the estimate to be close. You need it to tell you the right range. If the formula and the estimate are within ten or twenty percent, carry on. If they're a factor of two, ten or a hundred apart, stop and look.
Kumar's formula used 4% as the rate per period without dividing it by 12. It charged 4% a month instead of 4% a year, which is close to 48% a year. That's the most common error in money calculations, and it produces results that are wildly off in a way that a ballpark catches at once.
If a result is off by a large factor, check this first. Is the rate per period matched to the payment period? A monthly payment needs a monthly rate.
Three unit mistakes cause most of the remaining trouble.
Percent versus decimal. In a spreadsheet, 4% and 0.04 are the same, but typing 4 into a rate cell means 400%. If the rate cell shows 4 rather than 4% or 0.04, that's the problem. In a sheet like Joanne's, which divides the rate cell by 12, a 4 typed without the percent sign gives a payment of about S$6,667 a month.
Monthly versus yearly rates, as above. Some quotes give a monthly rate; most give a yearly one. Read the quote and label the cell.
Months versus years. If the number of periods is 5 when payments are monthly, the formula thinks the loan is paid off in five months. With Kumar's figures that gives about S$4,040 a month. Again, the estimate catches it.
The last check is to feed the formula an input where you already know the answer exactly.
Set the interest rate to zero. With no interest, the monthly payment on S$20,000 over five years must be S$20,000 divided by 60, which is S$333.33. If your formula gives anything else, or an error, it's wrong. Some versions of PMT handle a zero rate fine; a formula written by hand may divide by zero, which is worth knowing too.
Set the term to one month. The payment should be the whole loan plus one month's interest. Set the loan to zero. The payment should be zero.
These checks take a minute and they test the structure of the formula, not just one answer. Once a formula passes them, you can trust it with figures you can't check by hand.
How money works lesson 2.3, Estimate doubling time in your head with the rule of 72, gave you one ballpark tool for growth: at 4% a year, money roughly doubles in 72 divided by 4, or 18 years. Combine that with the loan estimate here and you can sanity-check most savings and loan results in under a minute.
Make it the habit before you look at any calculated figure, from a spreadsheet, a chat or a salesperson. Write your estimate down first, so you can't adjust it after seeing the answer.
Your loan sheet from lesson 5.2 is the place to practise, so open it and cover the result cells before you start.
Write a ballpark estimate for your loan formula from lesson 5.2 and check it against the spreadsheet result for all three amounts.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).