# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-mortgage-affordability-and-dti
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 11 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the home affordability calculator](https://dothecalculation.com/calculators/home-affordability-calculator) — Enter income, debts, and a rate to get a maximum price without building the sheet.

## The two ratios

**Front-end and back-end**

```
Front-end = PITI ÷ Gross monthly income    ·    Back-end = (PITI + other debt payments) ÷ Gross monthly income
```
- PITI is principal, interest, taxes, and insurance, plus HOA fees and mortgage insurance where they apply.
- Other debt payments means the minimum required payments on cards, car loans, and student loans.
- Both use gross income, before tax, which is why the resulting payment can feel high.

**Common limits**
| 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% |

> **What a lender will allow is not what you should borrow** — A 50% back-end ratio means half of gross income goes to debt before tax, food, transport, childcare, or saving. The 36% figure is a rule of thumb about living comfortably. The 43% and 50% figures are about the loan performing. Model all three and choose deliberately.

## The household

**Inputs**
| 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

**What each limit allows**
| 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 |

**The binding constraint**

```
=MIN(Income*FrontEndLimit, Income*BackEndLimit − OtherDebt)
```
- At the conservative limits, the back-end binds: $2,144 rather than $2,632.
- The MIN is the whole point. A model that applies only one ratio will overstate affordability for anyone with debts.
- With no other debt, the front-end binds instead and the answer is $2,632.

## 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 algebraic form**

```
Price = (MaxPITI − Insurance) ÷ ( (1 − DownPct) × PaymentFactor + TaxRate ÷ 12 )
```
- PaymentFactor is the monthly payment per dollar borrowed: =PMT(rate/12, 360, -1), which is 0.00648587 at 6.75%.
- (2,144 − 145) ÷ (0.80 × 0.00648587 + 0.011 ÷ 12) = 1,999 ÷ 0.00610537 = $327,415
- Add HOA fees to the insurance term and mortgage insurance to the payment factor if the deposit is under 20%.

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.

**The resulting purchase**
| 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

**One change at a time, from a $327,400 base at the 28% and 36% limits**
| 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 |

> **A $620 payment costs $79,900 of house** — That is 129 times the monthly payment, which is what a mortgage does to any competing obligation. Note also that clearing the car makes the front-end ratio the binding constraint, so the remaining $620 of student and card debt buys no further capacity at all. Anyone within a year of buying should model the car loan against the house before assuming the car is the smaller decision.

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.

Tool: [Try the debt-to-income calculator](https://dothecalculation.com/calculators/debt-to-income-ratio-calculator) — Check 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.

**On a $327,400 house**
| 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-mortgage-affordability-and-dti). Quote freely with attribution and a link to this page._