You will be able to get a working spreadsheet formula or short script for a money calculation.
When you need loan repayments worked out, ask an AI assistant for a spreadsheet formula and run it yourself. You can check a formula and reuse it with any figures, which you can't do with a number the assistant types into the chat.
Joanne lives in Singapore, and last year Joanne and their husband collected the keys to an HDB flat. Joanne is now pricing a renovation from a contractor's quote and comparing two options: taking a loan, or paying part of the cost from savings. To compare them, Joanne wants the monthly repayment for several loan amounts. Lesson 5.1, "Why the numbers in a paragraph can be wrong", taught Joanne not to take a figure from the chat, so Joanne asks the assistant for a formula instead.
A good formula request has three parts: what you know (the inputs), what you want (the output), and where it will run, such as Google Sheets. You don't need to give your real figures. The assistant is writing a formula that works with any figures, which is a language task it does well.
Joanne's request covered these points:
where Joanne lives (Singapore) and the app the formula will run in (Google Sheets) the task: a monthly loan repayment the inputs: loan amount, yearly interest rate on a reducing balance, and number of years the output: the monthly payment as a positive number which cell each input should go in, and an explanation of each part of the formula
Joanne left out the actual figures.
Say whether the loan uses a reducing balance rate or a flat rate, because each is calculated differently. How money works lesson 3.2, "Why a flat rate loan costs nearly double what it looks like", explains the difference. If you're unsure which kind of rate applies to your loan, ask the lender.
The assistant replied with a formula built on PMT.
Three spreadsheet functions handle most loan and savings questions, and 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; it is the function used in lesson 5.1. RATE works backwards from the payments to find the interest rate, which is how you find the true cost of a flat rate loan. How money works lesson 3.4, "Calculate the EIR of a flat rate offer with the RATE function", uses it for that.
All three use 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 divide a yearly rate by 12 and multiply the number of years by 12.
These functions also treat money you receive and money you pay as opposite signs. PMT on a loan you receive returns a negative number. Putting a minus sign in front of the loan amount makes the payment come out positive.
Each input gets its own cell and its own label, and this is what makes the sheet useful. In A1 Joanne types "Loan amount" and puts the amount in B1. A2 is "Yearly rate", with the rate as a percentage in B2. A3 is "Years", with the term in B3. A5 is "Monthly payment", and B5 holds the formula:
`=PMT(B2/12, B3*12, -B1)`
B2/12 turns the yearly rate into a monthly one, B3*12 turns years into months, and the minus sign before B1 makes the payment positive.
With S$20,000 at 4% a year over five years, Joanne gets a monthly payment of S$368.33 (an invented example). Changing an input cell updates the payment automatically. With B1 at S$10,000 the payment is S$184.17, and at S$30,000 it is S$552.50. Changing B3 from 5 to 7 shows what a longer term does to the monthly amount.
To check the formula is behaving, double the loan and change nothing else. The payment should double too.
Don't hard-code numbers inside a formula. =PMT(0.04/12, 60, -20000) works, with 60 standing for 5 years x 12 months, but later neither you nor anyone else can tell what each number was for.
Some questions are too long for one cell, such as a repayment schedule covering all sixty months, or a comparison of three loan offers with different fees. For these, ask for a short Python script. Describe the inputs and outputs the same way you would for a formula, and ask for the inputs to be set at the top of the script with clear names. Use example-style inputs like the ones in your spreadsheet, and don't paste personal financial details into the script.
Read the script before you run it. You don't need programming skill to check that the rate is divided by 12 and that the loop runs for the right number of months. If a line is unclear, ask the assistant to explain it. Then run the script yourself in a notebook you control, such as Jupyter or Google Colab.
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).