calculate-loan

01 Loan payments

Debt-to-Income Calculator in Excel: Formula and Example

Calculator No. 01-19117 · Updated · Formula and assumptions shown below

Debt-to-income ratio 42.50 %
Total monthly debt payments
$2,550.00
Level-payment formula, rounded to the cent
Gross income after listed debts
$3,450.00
From the formula below, to the cent
Gross monthly income
$6,000
Input
You enter
Gross monthly income ($), Housing payment ($), Auto loan payment ($), Credit card payments ($), Other monthly debt payments ($)
You get
Debt-to-income ratio (%), Total monthly debt payments ($), Gross income after listed debts ($)
Rounding
2 dp

Estimate for education, not financial advice. The result follows from the numbers you enter and the formula shown. It is not a quote or an offer, and it does not include fees, taxes or insurance unless the calculator asks for them. Ask your lender for the exact figures.

A debt-to-income (DTI) calculator in Excel compares your monthly debt payments with your gross monthly income and returns a percentage. Enter your income and the monthly payments included in your calculation to see the debt total and DTI; the estimate is arithmetic, not a loan approval decision.

Formula

The calculator uses this basic formula:

DTI (%) = total monthly debt payments ÷ gross monthly income × 100

  • Gross monthly income (inc): income before taxes and other payroll deductions, in dollars per month.
  • Housing payment (mort): the monthly housing amount you choose to include, in dollars. For a mortgage estimate, this may include principal, interest, property taxes, homeowners insurance, and any applicable housing charges; use a consistent definition.
  • Auto loan payment (auto), credit card payments (card), and other monthly debt payments (other): the monthly amounts included in your own DTI calculation, in dollars.
  • Total monthly debt payments (debt): the sum of the listed payment inputs, in dollars.
  • Gross income after listed debts (rem): gross monthly income minus those listed payments. This is not take-home pay or a complete spending budget.

In Excel, if monthly income is in B2 and the four payment amounts are in B3:B6, an equivalent formula is =SUM(B3:B6)/B2. Format the result cell as a percentage. Excel’s percentage format multiplies a decimal by 100 for display, so do not also multiply by 100 in the cell formula. Alternatively, use =SUM(B3:B6)/B2*100 and format the result as a regular number with a percent sign in the label.

Worked Example

Using the calculator’s defaults, gross monthly income is $6,000. Listed monthly payments are $1,800 for housing, $400 for an auto loan, $200 for credit card payments, and $150 for other debts.

  1. Add the listed payments: $1,800 + $400 + $200 + $150 = $2,550 per month.
  2. Divide payments by gross income: $2,550 ÷ $6,000 = 0.425.
  3. Convert the decimal to a percentage: 0.425 × 100 = 42.50%.
  4. Subtract the listed debt payments from gross income: $6,000 − $2,550 = $3,450.

The calculator therefore returns a DTI of 42.50%, total listed monthly debt payments of $2,550, and $3,450 of gross income remaining after those payments. That last figure does not deduct taxes, food, utilities, savings, or other expenses.

Assumptions

  • Income and payment inputs are monthly amounts in dollars; convert weekly, biweekly, or annual figures before entering them.
  • Income means gross income before taxes and deductions.
  • Payments are entered as amounts due per month. This calculator does not estimate a loan payment or add interest.
  • Only the payment categories entered are counted. Fees, taxes, insurance, and other housing costs are not added automatically; include relevant amounts in the housing input if appropriate to the calculation you are making.

How to Build the Calculator in Excel

A simple worksheet needs one income cell, payment cells, and a formula cell. Label the rows clearly, enter monthly figures, and use a formula that sums the debt inputs before dividing by income. For example, with income in B2 and payments in B3:B6, enter =SUM(B3:B6)/B2 in B7 and format B7 as a percentage with two decimal places. A separate total cell can use =SUM(B3:B6).

To avoid misleading results, include a visible note that income must be gross and all inputs must use the same monthly period. If the sheet will be reused, add data validation to prevent negative payment entries and a warning when income is blank or zero; division by zero cannot produce a meaningful ratio. Keep a separate copy or clearly labeled cells for alternative scenarios rather than overwriting the figures you want to retain.

What DTI Includes and How to Read It

DTI is a ratio, not a dollar amount. It expresses the portion of gross monthly income represented by the monthly debt payments included in the calculation. A 30% result means the entered payments equal 30 cents for each dollar of gross monthly income. It does not mean that 70% of take-home pay is available to spend: taxes, payroll deductions, everyday living costs, and unlisted obligations have not been accounted for.

Definitions of included payments can differ between calculations. Some versions focus on housing-related costs; others compare broader monthly obligations with income. A lender’s own calculation may use specific documentation, payment definitions, and income treatment. This worksheet uses only the categories you enter and should not be treated as a lender’s determination.

For an annual income figure, divide by 12 to estimate a monthly amount only if that is an appropriate representation of the income. For weekly pay, multiplying by 52 and dividing by 12 converts an annualized weekly amount to a monthly average. If income varies, a single month may not represent the period or method used in another calculation; keep the source and period of each figure clear.

Common Excel Errors and Limitations

  • Using net income: DTI is commonly expressed against gross income. This calculator explicitly asks for gross monthly income; do not mix it with take-home income without relabeling the result.
  • Mixing periods: Do not divide a monthly debt total by annual income. Convert both sides to the same time period first.
  • Double-formatting the percentage: Use either a decimal formula with Excel percentage formatting or a formula multiplied by 100 with a regular numeric format. Doing both makes the displayed result 100 times too large.
  • Omitting or duplicating payments: Review which housing costs and debt obligations you included. Count each item once and use the same definition when comparing results.
  • Reading the remainder as disposable income: The subtraction output only removes the payments entered from gross income. It is not a household budget or a measure of affordability.

The formula is a straightforward ratio, but it cannot verify income, determine which debts another party will count, or predict approval. Get the applicable definitions and figures from the lender if you are comparing your worksheet with an application or disclosure.

Frequently Asked Questions

What is the Excel formula for debt-to-income ratio?
Divide the sum of monthly debt payments by gross monthly income. For example, use =SUM(B3:B6)/B2 and format the result as a percentage.

Should I use gross or net income for DTI?
This calculator uses gross income before taxes and deductions. Other calculations may define inputs differently, so check the method you are trying to match.

Do I include rent in a DTI calculation?
It depends on the purpose and definition of the calculation. If you include rent or another housing payment, enter it once in the housing field and label your worksheet so the result’s scope is clear.

Why does my Excel DTI show as 4,250% instead of 42.50%?
You may have multiplied the formula by 100 and also formatted the cell as a percentage. Remove the multiplication by 100 or change the cell to a regular number format.

Can a DTI calculator tell me whether I will be approved?
No. It calculates a ratio from the figures entered. A lender makes its own assessment using its process and information; this worksheet does not model that decision.

This result is an estimate, and the lender’s disclosure gives the exact figures.

Related

Formula used
in inc | Gross monthly income | $ | 6000 | 0.01 | 100000000
in mort | Housing payment | $ | 1800 | 0 | 100000000
in auto | Auto loan payment | $ | 400 | 0 | 100000000
in card | Credit card payments | $ | 200 | 0 | 100000000
in other | Other monthly debt payments | $ | 150 | 0 | 100000000
out dti | Debt-to-income ratio | % | (mort + auto + card + other) / inc * 100 | 2
out debt | Total monthly debt payments | $ | mort + auto + card + other | 2
out rem | Gross income after listed debts | $ | inc - mort - auto - card - other | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.

More in this section

The whole Loan payments section →