Mortgage Affordability in Excel: What a $620 Car Payment Costs in House
Debt-to-income limits work backwards from income to a maximum payment, then from that payment to a price. On one household the car loan costs $80,000 of buying power and a 1% rate rise costs about $29,000. Here is the model.
Two Ratios Decide the Answer
Lenders test affordability with two ratios. The front-end ratio caps housing costs as a share of gross income. The back-end ratio caps all debt payments together. Whichever binds first sets your maximum payment, and for most households with any existing debt it is the back-end.
From that maximum payment, a price follows. Building the chain in a spreadsheet lets you see what each existing debt, and each rate movement, actually costs in house.
Try the home affordability calculatorEnter income, debts, and a rate to get a maximum price without building the sheet.The two ratios
Swipe sideways to compare columns.
| Guideline | Front-end | Back-end |
|---|---|---|
| Conservative rule of thumb | 28% | 36% |
| Common conventional underwriting | 28% to 31% | Up to 43% |
| Some programmes, with compensating factors | 31% to 33% | Up to 50% |
The household
Swipe sideways to compare columns.
| Item | Value |
|---|---|
| Gross monthly income | $9,400 |
| Car loan payment | $620 |
| Student loan payment | $410 |
| Credit card minimums | $210 |
| Total other debt | $1,240 |
| Down payment | 20% |
| Mortgage rate | 6.75%, 30 years |
| Property tax rate | 1.1% of value |
| Home insurance | $145 a month |
Step one: the maximum payment
Swipe sideways to compare columns.
| Test | Allowance | Less other debt | Max PITI |
|---|---|---|---|
| Front-end at 28% | $2,632 | n/a | $2,632 |
| Back-end at 36% | $3,384 | −$1,240 | $2,144 |
| Back-end at 43% | $4,042 | −$1,240 | $2,802 |
| Back-end at 50% | $4,700 | −$1,240 | $3,460 |
Step two: from payment to price
This is the circular part. Property tax depends on the price, and the price depends on how much payment is left after tax. Two ways to solve it: algebra, or Goal Seek.
The Goal Seek route is easier to maintain. Build the sheet forwards from a price to a PITI, then use Data, What-If Analysis, Goal Seek to set the PITI cell to $2,144 by changing the price cell. It handles any extra cost line without rewriting the formula.
Swipe sideways to compare columns.
| Line | Amount |
|---|---|
| Maximum price | $327,400 |
| Down payment at 20% | $65,480 |
| Loan amount | $261,920 |
| Principal and interest | $1,699 |
| Property tax | $300 |
| Insurance | $145 |
| Total PITI | $2,144 |
| Back-end ratio | 36.0% |
What moves the answer
Swipe sideways to compare columns.
| Change | Maximum price | Difference |
|---|---|---|
| Rate 5.75% instead of 6.75% | $357,900 | +$30,500 |
| Rate 7.75% | $300,700 | −$26,700 |
| Car loan paid off | $407,300 | +$79,900 |
| All other debt cleared | $407,300 | +$79,900 |
| Limits 31% and 43% instead of 28% and 36% | $435,200 | +$107,800 |
| Income $10,400 instead of $9,400 | $386,400 | +$59,000 |
The rate rows are worth reading together. A one point rate move is worth about $29,000 of buying power on this household, roughly 9% of the price. Rate movements and debt payoff are the two levers that actually work; income changes slowly and the down payment mostly changes the loan rather than the price.
Try the debt-to-income calculatorCheck where your ratios stand today before working out what they allow.The costs the ratios do not include
PITI is not what a house costs. Both ratios are underwriting tests, and both ignore the running costs of ownership.
Swipe sideways to compare columns.
| Cost | Annual | Monthly |
|---|---|---|
| Maintenance at 1% of value | $3,274 | $273 |
| Utilities beyond a rental | $1,200 | $100 |
| Capital reserves for major systems | $2,400 | $200 |
| Total not in PITI | $6,874 | $573 |
That $573 a month is 27% on top of the $2,144 PITI, and it is the difference between a household that qualifies and a household that copes. Add these lines to the affordability sheet as a separate block below the ratio test.
What the model does not decide
- Whether you will be approved. Credit score, employment history, reserves, and the appraisal all matter, and none of it is a ratio.
- Mortgage insurance, which applies below a 20% deposit and adds to PITI without adding to equity. Model it explicitly rather than assuming 20% down.
- HOA and condominium fees, which count fully in the ratios and can consume several hundred dollars of allowance.
- Variable-rate loans, where the qualifying payment may be based on a stressed rate rather than the initial one.
- Closing costs and moving costs, which reduce the deposit available rather than the payment.
- Property tax reassessment on purchase. Many jurisdictions reassess at the sale price, so the tax on the listing is not necessarily the tax you will pay.
- Whether the price is a good one. Affordability is a constraint, not a recommendation.
What is the difference between front-end and back-end DTI?
Front-end counts only housing costs against gross income. Back-end counts housing plus all other required debt payments. For anyone with a car loan or student debt, the back-end usually binds first and is therefore the ratio that sets the answer.
Why does the maximum price calculation need Goal Seek?
Because property tax is a percentage of the price, so the price appears on both sides of the equation. The algebra is solvable, and Goal Seek is easier to maintain once you add HOA fees, mortgage insurance, or a second tax line.
How much does paying off a car loan increase what I can borrow?
On this household, clearing a $620 payment raises the maximum price by $79,900, roughly 129 times the monthly payment, at which point the front-end ratio becomes the binding limit and further payoff adds nothing. The multiple depends on the rate and the tax rate, and it is always large enough to be worth modelling before buying a car.
Should I borrow the maximum a lender allows?
Generally not. Underwriting limits test whether the loan will perform, not whether you will be comfortable. The gap between the conservative limits and the 31% and 43% pair is $108,000 of house and about $658 a month of payment that has to come from somewhere.
Do the ratios use gross or net income?
Gross, before tax. That is why a 36% back-end ratio consumes a considerably larger share of take-home pay, commonly 45% to 50% depending on your tax rate. Compute both and look at the net figure before deciding.
How much should I budget for maintenance?
One percent of value a year is the common rule and it is a rule, not a measurement. Older properties and larger ones run higher. Whatever figure you use, keep it in the affordability sheet, since it is real money that PITI does not include.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team