You will be able to get a working spreadsheet formula or short script for a money calculation.
Joanne is pricing a renovation for the HDB flat she and her husband collected the keys to last year. A contractor has given her a quote, and she's comparing a loan with paying part of it from savings. She wants to know the monthly repayment on a few different loan amounts, and she's learned from lesson 5.1, Why the numbers in a paragraph can be wrong, not to take the figure from the chat.
So this time she asked the assistant for something else: the formula.
A good formula request has three parts. What you know, what you want, and where it will run. Joanne wrote:
"I'm in Singapore. I want a formula for Google Sheets that works out the monthly repayment on a loan. My inputs are the loan amount, the yearly interest rate on a reducing balance, and the number of years. The output is the monthly payment as a positive number. Tell me which cell each input should go in, and explain what each part of the formula does."
Notice what's missing: her actual figures. She doesn't need the assistant to see them. She needs it to write a formula she can use with any figures, which is a language job it does well.
The phrase "reducing balance" matters. If the lender quotes a flat rate, the calculation is different, and How money works lesson 3.2, Why a flat rate loan costs nearly double what it looks like, explains why. Tell the assistant which kind of rate you have. If you're not sure, ask the lender.
The answer came back built on PMT, one of three spreadsheet functions that handle most loan and savings questions. They work the same way in Excel and Google Sheets.
PMT gives the regular payment on a loan, or the regular saving needed to reach a target. FV gives the future value of a lump sum or a series of regular payments, which is the one from lesson 5.1. RATE works backwards from the payments to the interest rate, which is how How money works lesson 3.4, Calculate the EIR of a flat rate offer with the RATE function, finds the true cost of a flat rate loan.
All three take the same building blocks: the rate per period, the number of periods, the payment, and the present value. The commonest mistake is mixing periods. If payments are monthly, the rate must be monthly too, so a yearly rate is divided by 12 and the number of years is multiplied by 12.
The other surprise is the sign. These functions treat money you receive and money you pay as opposite signs, so PMT on a loan you receive comes back negative. Putting a minus in front of the loan amount makes the payment come out positive.
Here is how Joanne set it up. In A1 she typed "Loan amount" and in B1 the amount. In A2, "Yearly rate", and in B2 the rate as a percentage. In A3, "Years", and in B3 the term. In A5 she typed "Monthly payment", and in B5:
=PMT(B2/12, B3*12, -B1)
Every input has its own cell and its own label. That's what makes the sheet useful. Change B1 and the payment updates. Change B3 from 5 to 7 and you see what a longer term does to the monthly amount.
With figures that are an example, a loan of S$20,000 at 4% a year on a reducing balance over five years gives a monthly payment of S$368.33. She then changed B1 to S$10,000 and got S$184.17, and to S$30,000 and got S$552.50. Doubling the loan doubled the payment, which is what you'd expect when nothing else changes, and is a quick sign the formula is behaving.
Never put a number directly inside the formula, such as =PMT(0.04/12, 60, -20000). It works, but the next time you look at it, or someone else does, there's no way to tell what each number was for.
Some questions are too long for one cell. A full repayment schedule showing the interest and principal in each of sixty months. A comparison of three loan offers with different fees. A savings plan where the monthly amount rises each year.
For these, ask for a short Python script instead. Describe the inputs and outputs the same way, and ask for the inputs to be set at the top of the script with clear names. Then run it yourself in a notebook you control, such as Jupyter or Google Colab. Don't paste your personal financial details into the script: use the same example-style inputs you'd put in a spreadsheet.
Read the script before running it. You don't need to be a programmer to check that the rate is divided by 12 and that the loop runs for the right number of months. If something looks unclear, ask the assistant to explain that line.
Set up your own sheet now, with the loan amount, rate and term in labelled cells, and try it with three different amounts.
Get a formula for the monthly payment on a loan, set it up with labelled input cells, and test it with three different loan amounts.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).