You will be able to build a worksheet that applies LTV, MSR and TDSR to your income and gives your maximum loan and price.
Gather three things before you open a spreadsheet: your latest payslips, a list of every debt you pay each month, and the current limits and rates from MAS and HDB. Set aside about 35 minutes.
The last four lessons gave Farah and Hakim three separate limits. One came from the value of the home (LTV), one from their instalment (MSR) and one from all their debts (TDSR). None of these limits on its own answers the question an agent asked them in lesson 4.1: "How much is your loan approved for?" The worksheet joins all three. It gives you one maximum loan, the rule that sets it, and the highest price that loan supports.
At the top of the sheet, give every input its own cell. Next to each one, note where the figure came from and the date you checked it. You need:
gross fixed monthly income for each borrower variable income for each borrower the haircut on variable income the monthly payment on every existing debt each borrower's age loan tenure in years the LTV limit the MSR cap the TDSR cap the stress-test rate
Farah and Hakim's sheet uses an LTV limit of 70%, an MSR cap of 28%, a TDSR cap of 50%, a 30% haircut on variable income and a stress-test rate of 4.5% (all invented figures, so replace each one with the current figure from MAS or HDB). Their fixed incomes are S$5,000 and S$5,500. Hakim also earns S$1,000 in commission. They pay S$650 a month on a car loan. Their ages are 31 and 33, and they want a 25-year tenure, which is 300 months.
Below the inputs, add a cell for assessed income: fixed income plus variable income after the haircut. The 30% haircut brings Hakim's S$1,000 commission down to S$700, so their assessed income is S$5,000 + S$5,500 + S$700 = S$11,200.
Before going further, check the tenure against their age. Banks use the borrowers' average age, weighted by income, and a loan that runs past a set age gets a lower LTV limit. Farah and Hakim's weighted average age is about 32, so a 25-year loan ends in their late fifties. Look up the current age and tenure limits that would cut your LTV. If your plan crosses them, put the lower LTV in the LTV cell.
MSR and TDSR are caps on the monthly instalment, so you work out each instalment first.
The MSR instalment is the MSR cap times assessed income. For Farah and Hakim that is 28% of S$11,200, which is S$3,136.
The TDSR instalment is the TDSR cap times assessed income, minus existing debt payments. 50% of S$11,200 is S$5,600, and taking off the S$650 car loan leaves S$4,950.
Next, convert each instalment to a loan amount at the stress-test rate, the rate the bank uses to test whether you can afford the loan. The spreadsheet PV function does this from a monthly payment, a rate and a number of months. The formula =PV(4.5%/12, 300, -3136) returns the loan that S$3,136 a month would repay over 300 months at 4.5% a year, which is about S$564,200. The same formula with S$4,950 gives about S$890,600 under TDSR.
Which loan counts depends on the type of home. For an HDB flat bought with a bank loan, both MSR and TDSR apply, so the limit is the lower of the two. For Farah and Hakim that is about S$564,200, set by MSR. For a private home only the TDSR applies, so their limit for a condo is about S$890,600.
The LTV limit works the other way round. It doesn't give you a loan amount on its own. It caps the loan at a percentage of the home's value. To find the supported price, the highest price your loan limit supports, divide the loan limit by the LTV.
For Farah and Hakim's HDB flat that is S$564,200 / 0.70, or about S$806,000. For a private home it is S$890,600 / 0.70, or about S$1.27 million.
Above the supported price, the loan you can get stays the same, so every extra dollar of price is extra downpayment. Below it, the LTV limit is the one that binds, and the loan is the LTV percentage of the lower of price and valuation.
Write down which rule gives the lowest loan, because that rule shows what would change your limit. For the flat, MSR sets Farah and Hakim's limit, and only more income would raise it. For a condo, TDSR sets the limit, and either more income or less debt would raise it, as lesson 4.4, "Variable income, other debts and guarantors shrink your limit", showed.
If your plan is over the limit, the worksheet shows you by how much and which rule you need to work on.
The last line of the worksheet compares your actual plan with these limits. Work out the instalment on your planned loan at the stress-test rate and compare it with the instalment limit. Then compare your planned price with the supported price.
Farah and Hakim plan to buy a flat in Bedok with a S$427,000 loan on a S$610,000 valuation. At the stress-test rate the instalment is S$2,373.40, against their MSR limit of S$3,136. They pass with about S$760 a month to spare, and the planned price is well under the S$806,000 their income supports.
Room under the limit is not money to spend. A high supported price does not mean you should shop at that price. It means the rules are not what holds you back, so the price you pay is set by your own budget, your cash and the ten-year comparison from lesson 1.5, "Compare two homes over ten years".
If you take an HDB loan, HDB does its own assessment, using its own income rules and its own affordability test, and states the result in your HDB Flat Eligibility (HFE) letter. Put that figure next to the worksheet. If you are still choosing between an HDB loan and a bank loan, keep both figures. Lesson 5.1, "HDB loan or bank loan", compares the two.
A finished worksheet has a dated inputs block with sources, an MSR instalment and a TDSR instalment, and a loan amount for each at the stress-test rate. It names the lower of the two loans as the limit, shows the price that limit supports through the LTV, and ends with a line comparing all of this with your plan. Build it with your own numbers in the activity below.
Build the worksheet with your own numbers, then write down your maximum loan, the rule that limits you and the price it supports.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).