How to calculate debt-to-income ratio in Excel

Step 1: List the monthly payments

Put each monthly payment on its own row: housing, car loan, student loan, the minimum payment on each credit card, personal loans and any other loan with a fixed payment. Use the minimum payment from the statement for cards, not what you usually pay or the balance.

Day-to-day bills such as groceries, utilities, phone and insurance that isn't part of a housing payment are usually not in DTI. Lenders decide exactly what counts, and it varies by lender and loan type, so if you're preparing for an application, use the list they give you.

Give each row a Type (Housing or Debt) and an Include? column. Both make the what-if questions below one change instead of a rewrite.

Step 2: Divide by gross monthly income

DTI uses gross income, before tax and deductions, not your take-home pay. Put yearly gross income in G2 and divide by 12 in G3: $78,000 / 12 = $6,500.

$2,620 / $6,500 = 40.3%. If you divide by take-home pay instead (say 75% of gross, $4,875), you get 53.7%. That's a useful number for your own budget, but it isn't DTI, and it won't match what a lender calculates.

If your income changes from month to month, average a longer period, such as the last 12 or 24 months of gross income, and say which in a note. One good month makes the ratio look better than it is.

Housing-only (front-end) vs total (back-end) DTI

Mortgage lenders often look at two ratios. Front-end DTI is the housing payment alone (mortgage or rent, with property tax, insurance and HOA if they apply) divided by income. Back-end DTI is all debt payments, housing included, which is the 40.3% above.

SUMIFS on the Type column gives the housing part: $1,650 / $6,500 = 25.4%. The other debts add $970 a month, 14.9% of income. Showing the two parts side by side tells you whether the ratio is high because of housing or because of everything else.

What if a debt is paid off?

Switch the total to SUMIFS on the Include? column: =SUMIFS(B2:B7,D2:D7,"Yes")/G3. With every row set to Yes it matches the 40.3% above. Change the car loan's D3 to No and it falls to 34.4%, because $385 a month leaves the total.

Paying off both credit cards instead (D5 and D6) only brings it to 38.2%, since their minimum payments are small. DTI looks at payments, not balances, so the debt with the largest monthly payment moves it most. That's a different question from which debt costs the most interest, which the debt payoff calculator in the personal-finance hub compares.

Room under a limit you set

If a lender has told you a limit, or you've set one for yourself, put it in G4 (36% here is a sample; use your own). =IF(SUM(B2:B7)/G3<=G4,"Within your limit","Above your limit") labels the result: Above your limit, since 40.3% is over 36%.

Turn it around to see the room left. The limit x income is the total monthly payment that fits: 36% of $6,500 is $2,340. Take away the other debts ($970) and up to $1,370 a month is left for housing. With the current $1,650 housing payment, the total is $280 over. These are calculations on your own inputs, not a lending decision or financial advice.