You will be able to spot a calculated result that is in the wrong range before you rely on it.
A formula that runs without an error message is not necessarily correct. A spreadsheet will return a figure to two decimal places from a formula with the wrong rate or the wrong number of periods, so a precise-looking result can still be wrong, and its precision tells you nothing about whether it is right. The protection is a habit: know roughly what the answer should be before you look at it.
Joanne, the learner from lesson 5.2, "Ask for the formula, then run it yourself", built a loan sheet there that worked first time. Joanne's friend Kumar copied a formula from a chat into Kumar's own sheet to price a S$20,000 loan at 4% over five years (an invented example). The formula ran without error and gave a monthly payment of S$884.04. Kumar nearly accepted it, because the number was precise, the formula ran, and Kumar had no feel for what the answer should be.
An estimate, or ballpark, is a rough answer worked out from rounded inputs before you see the result. Its job is to confirm the right range, so it does not need to be close. Round the inputs to easy numbers and do the sum in your head or on paper.
Start with the principal. S$20,000 over five years is S$4,000 a year, or about S$333 a month. Then estimate the interest. Kumar's loan is a reducing balance loan: interest is charged only on what is still owed, which starts at the full loan and falls to nothing. On average about half the loan is outstanding, so use S$10,000 as the balance. 4% of S$10,000 is about S$400 a year, or S$2,000 over five years. Add that to the principal and Kumar repays about S$22,000 over 60 months, roughly S$367 a month.
The correct payment with these example figures is S$368.33, within a couple of dollars of the estimate. S$884.04 is more than twice the estimate. A few seconds of rounding would have shown Kumar the error immediately.
Do the same every time, before you read a result. Round the inputs. Work out the principal per month by dividing the loan by the number of months. Estimate the interest as about half the loan, times the annual rate, times the years. Add the interest to the principal and divide by the number of months. Write the estimate down before you look at the result, so you can't adjust it afterwards. Then compare: if the formula and your estimate are within ten or twenty percent, carry on. If they are a factor of two, ten or a hundred apart, stop and investigate.
Kumar's formula used 4% as the per-period rate without dividing by 12, so it charged 4% a month instead of 4% a year, close to 48% a year. Mixing up monthly and yearly rates in this way is the most common error in money calculations. It produces results that are wildly off, which a ballpark estimate catches at once. When a result is off by a large factor, check first that the rate per period matches 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%. In a sheet like Joanne's, which divides the rate cell by 12, that gives a payment of about S$6,667 a month. If the rate cell shows 4 rather than 4% or 0.04, that is the problem. Monthly versus yearly rates. Some quotes give a monthly rate and most give a yearly one. Read the quote and label the cell. Months versus years. If the number of periods is entered as years (5) when payments are monthly, the formula treats the loan as paid off in five months. With Kumar's figures that comes to about S$4,040 a month.
An estimate checks one answer. To check the formula itself, feed it an edge case, which is an input where you already know the exact answer.
Set the interest rate to zero. The payment must equal the loan divided by the number of months: S$20,000 divided by 60 is S$333.33. Any other result, or an error, means the formula is wrong. Some versions of PMT handle a zero rate fine, but a hand-written formula may divide by zero, which is worth knowing.
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 about a minute and test the structure of the formula as well as a single 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 a ballpark tool for growth: years to double is roughly 72 divided by the annual rate. At 4% a year, money roughly doubles in 72 divided by 4 = 18 years. Combined with the loan estimate, it lets you sanity-check most savings and loan results in under a minute.
Apply the habit to any calculated figure, whether it comes from a spreadsheet, a chat or a salesperson, and write your estimate down before you see 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 down a rough estimate for your loan formula from lesson 5.2. Compare it with the spreadsheet result for all three amounts.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).