A debt-to-income (DTI) calculator in Excel compares your monthly debt payments with your gross monthly income and returns the ratio as a percentage. Enter the monthly amounts below to calculate DTI and see the debt and income totals used in the calculation.
Formula
The DTI calculation is:
DTI (%) = total monthly debt payments ÷ gross monthly income × 100
- mort is the monthly mortgage or rent-related debt payment, in dollars.
- car is the monthly auto-loan payment, in dollars.
- other is the combined monthly payment for other debts included in your calculation, in dollars.
- income is gross monthly income before taxes and other deductions, in dollars.
- debt is the sum of the listed monthly debt payments, in dollars.
- dti is the result expressed as a percentage.
The calculator uses the same arithmetic you can put in Excel. If the debt amounts are in cells B2 through B4 and gross monthly income is in B5, an Excel formula is =SUM(B2:B4)/B5. Format the result as a percentage, or multiply by 100 and display it as a regular number with a percent sign. Do not do both: Excel’s percentage format already multiplies the displayed value by 100.
Worked Example
Using the calculator’s default amounts:
- Add the monthly payments: $1,500 + $400 + $300 = $2,200 in monthly debt.
- Divide by gross monthly income: $2,200 ÷ $6,000 = 0.3666667.
- Convert to a percentage: 0.3666667 × 100 = 36.66667%.
- Round to two decimal places: the DTI result is 36.67%.
- Subtract listed payments from gross income: $6,000 − $2,200 = $3,800.
The last figure is only gross income less the debts entered. It is not take-home pay or a complete household budget.
Assumptions
- Income and debt payments are monthly amounts in dollars.
- Income means gross income before taxes and deductions.
- Payments are entered at their regular monthly amount; the calculator does not annualize irregular income or debts.
- The listed debt categories are only examples. Include the obligations relevant to the DTI definition you intend to use, and avoid counting the same payment twice.
- Taxes, living expenses, utilities, insurance, and savings are not included unless you add them to a payment category. The result does not determine eligibility or approval.
Building a DTI Formula in Excel
A basic worksheet can keep each payment on a separate row and make the inputs easy to review. For example, put a label in column A and the monthly amount in column B. Use separate rows for housing debt, auto debt, student debt, credit-card minimums, and other recurring debt payments. Put gross monthly income in its own cell.
If payments occupy B2:B6 and gross monthly income is in B7, enter =SUM(B2:B6)/B7 in the result cell. Choose Percentage formatting and set the displayed decimal places as needed. To show two decimal places without percentage formatting, use =SUM(B2:B6)/B7*100 and label the result as a percent. Keeping the formula and its input cells visible makes it easier to find omissions or accidental changes.
The calculator above combines some categories for simplicity. You can adapt the Excel layout by adding or removing debt rows, provided the numerator remains the total monthly payments you intend to count and the denominator remains gross monthly income for the same period.
What Counts and How to Read the Result
DTI is a comparison, not a dollar budget. A result of 36.67% means the entered monthly debt payments equal about 36.67 cents for each dollar of gross monthly income. It does not mean that this share of take-home pay is available for other spending, because taxes and payroll deductions have not been subtracted.
Definitions can vary by purpose. A housing-related ratio may compare housing costs with income, while a total-debt ratio may include housing plus other recurring debt. Some calculations count particular payment types and exclude others. For a specific application or assessment, use the definition and figures requested by the relevant organization rather than assuming every DTI calculation uses an identical list.
For a quick check, compare the result with your own records and inspect the inputs. The percentage can look precise even when an income estimate is uncertain or a payment is missing; the displayed decimals do not make the underlying data more exact.
Common Excel Errors and Limitations
- Using net income: The standard DTI calculation uses gross monthly income, not the amount deposited after deductions. If a particular process specifies a different measure, follow its stated definition.
- Mixing time periods: Do not divide monthly debt by annual income. Convert annual gross income to a monthly figure by dividing by 12, or convert both sides to annual amounts.
- Double-counting: If a total already includes a payment, do not add that payment again in another category.
- Formatting the result incorrectly: With Excel percentage formatting, the underlying result should be a decimal such as 0.3667. Multiplying by 100 as well would display 3,666.67%.
- Dividing by zero or a blank: The denominator must be positive. The calculator requires an income amount above zero; in Excel, check the income cell before relying on the result.
- Treating remaining gross income as disposable: The subtraction output excludes taxes, essential expenses, and any payments not entered.
This simple ratio does not assess savings, variable expenses, payment history, assets, or other details. It is a calculation aid, not a prediction of a decision by a lender or another organization.
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 debts in B2:B6 and income in B7, use =SUM(B2:B6)/B7, then format the result as a percentage.
Should I use gross or net income for DTI?
The commonly used DTI formula uses gross income before taxes and deductions. A specific calculation may define its inputs differently, so check the instructions for the purpose at hand.
Do I multiply by 100 in Excel?
Not if you format the formula result as a percentage. Excel will display 0.3667 as about 36.67%. Multiply by 100 only when you intend to display the numerical percent without percentage formatting.
Which debts should I include?
That depends on the DTI definition being used. List the monthly obligations requested for that calculation, use consistent monthly amounts, and do not count any payment twice.
Can DTI tell me whether I will be approved?
No. DTI is one arithmetic measure and cannot establish whether an application will be approved. Decision criteria and definitions vary, and this calculator does not model them.
Why is my DTI different from another calculator’s result?
Calculators may count different obligations, use different income figures, or handle payment timing and rounding differently. Compare the inputs and the stated definition before comparing percentages.
This result is an estimate for educational use, and the lender’s disclosure gives the exact figures.
Related
Formula used
in mort | Mortgage or rent-related debt | $ | 1500 | 0 | 100000000 in car | Auto loan payment | $ | 400 | 0 | 100000000 in other | Other monthly debt payments | $ | 300 | 0 | 100000000 in income | Gross monthly income | $ | 6000 | 0.01 | 100000000 out dti | Debt-to-income ratio | % | (mort + car + other) / income * 100 | 2 out debt | Total monthly debt payments | $ | mort + car + other | 2 out share | Income remaining after listed debt payments | $ | income - debt | 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
- Debt-to-Income Calculator and Spreadsheet Formula40.00 %
- Debt-to-Income Ratio Calculator and How It Works30.00 %
- Debt-to-Income Ratio Calculator: Meaning and Formula24.00 %
- Debt-to-Income Ratio Calculator and Scale24.00 %
- Debt-to-Income Ratio: Is Yours Too High?36.00 %
- Debt-to-Income Ratio Calculator and Sheet38.33 %