A debt-to-income (DTI) Excel spreadsheet calculator estimates your DTI by dividing your monthly debt payments by your gross monthly income and multiplying by 100. Enter those two amounts to see your percentage; the result is a budgeting estimate, not a lending decision.
Formula
The basic DTI formula is: DTI (%) = monthly debt payments ÷ gross monthly income × 100.
- Monthly debt payments (debt) is the sum of the recurring monthly debt obligations included in the calculation, measured in dollars per month.
- Gross monthly income (income) is income before taxes and other payroll deductions, measured in dollars per month.
- Debt-to-income ratio (DTI) is the resulting percentage.
The spreadsheet block uses the same formula. It accepts nonnegative debt payments and positive income; income cannot be zero because division by zero is undefined. It does not decide which items belong in a particular institution’s calculation.
Worked Example
Using the calculator’s default values:
- Monthly debt payments: $1,200.
- Gross monthly income: $5,000.
- Divide debt by income: $1,200 ÷ $5,000 = 0.24.
- Convert the decimal to a percentage: 0.24 × 100 = 24.00%.
The result is 24.00% when rounded to two decimal places. In plain language, the entered monthly debt payments equal 24% of the entered gross monthly income.
Assumptions
- Both entries represent monthly amounts in the same currency.
- Income is gross income, before taxes and payroll deductions.
- Debt payments are the monthly obligations you choose to include; the calculator does not classify them.
- The formula treats the entered values as fixed for this calculation. It does not account for payment timing, compounding, fees, taxes, insurance, or escrow.
What a DTI Spreadsheet Measures
DTI is a ratio rather than a dollar amount. A result of 24% means the monthly debt total is 24 cents for each dollar of gross monthly income. It does not mean that 24% of take-home pay is available for every other expense: taxes, housing costs not counted as debt, utilities, food, savings, and other spending may still affect a household’s cash flow.
People often use a spreadsheet to organize debts, test how the ratio changes when an input changes, or keep a record over time. A basic sheet needs only debt payments and gross income. A more detailed sheet can list individual obligations, sum their monthly payments, and feed that total into the formula. Ensure the sum includes each payment only once.
How to Build It in Excel
For a simple worksheet, enter monthly debt payments in cell B2 and gross monthly income in B3. In B4, use =B2/B3, then format B4 as Percentage. Excel will display 24% for the example values of 1200 and 5000. Do not multiply by 100 when using percentage formatting; percentage formatting performs that display conversion. Alternatively, use =B2/B3*100 and format the result as a regular number with a percent sign in the label.
For a list-based worksheet, put each monthly payment in a separate row and total them with a sum formula such as =SUM(B2:B8). Divide that total by the gross monthly income cell. Use clear labels and note whether each figure is monthly, so an annual income or a balance owed is not mistakenly used as a monthly payment.
Choosing Inputs and Interpreting the Result
Use monthly payment amounts, not total balances. For example, if a debt balance is $6,000 and its required monthly payment is $200, the basic formula uses $200—not $6,000. Convert income to a monthly gross amount as well. For a stable annual salary, dividing the annual gross amount by 12 gives a monthly equivalent; income that varies may require a representative period, but the chosen method should be stated and applied consistently.
Which payments count can vary by the purpose of the calculation and by the organization using it. A personal budget may track recurring debt payments and housing costs separately. A lender may define its own included obligations and income treatment. Do not assume that this simple spreadsheet reproduces an official underwriting calculation; obtain the applicable definitions from the organization concerned.
A lower numerical ratio means the entered debt payments are a smaller share of entered gross income. It does not, by itself, show whether a budget has enough room for everyday expenses or whether an application will be approved. DTI is only one measure, and the calculator does not model an institution’s rules or other information it may consider.
Common Spreadsheet Errors and Limitations
- Mixing time periods: Comparing monthly payments with annual income produces a misleading result. Convert both to monthly amounts.
- Using net income: The formula here uses gross income. A separate take-home-pay budget may be useful, but it is a different calculation.
- Confusing balance with payment: Enter the monthly amount, not the remaining principal or total owed.
- Double-counting: If a total cell already sums listed payments, do not add that total again as another debt line.
- Rounding too early: Keep the original amounts in the cells and round only the displayed percentage. Small rounded inputs can shift the final result.
- Dividing by zero: Income must be greater than zero. A blank or zero income entry cannot produce a meaningful DTI.
The basic calculation is intentionally limited: it uses two inputs and does not project future income, changing balances, payment schedules, household expenses, or lender-specific definitions. For a more detailed personal budget, list income and expenses separately rather than treating DTI as a complete affordability measure.
Frequently Asked Questions
What is the Excel formula for debt-to-income ratio?
Divide monthly debt payments by gross monthly income. In Excel, if those values are in B2 and B3, use =B2/B3 and format the result as a percentage.
Should I enter gross or net income?
This calculator uses gross income, meaning income before taxes and other deductions. Net income can be used for a separate cash-flow budget, but it changes the measure.
Do I enter debt balances or monthly payments?
Enter monthly payments. A balance is the amount owed, not the periodic payment used in this ratio.
What does a 24% DTI mean?
It means the monthly debt payments entered are 24% of the gross monthly income entered. It does not include every household expense or determine an approval outcome.
Does this spreadsheet calculate the DTI a lender will use?
Not necessarily. Definitions and included items can differ. Ask the relevant organization which debts and income figures it uses.
The result is an estimate; a lender’s disclosure gives the exact figures.
Related
Formula used
in debt | Monthly debt payments | $ | 1200 | 0 | 100000000 in income | Gross monthly income | $ | 5000 | 0.01 | 100000000 out dti | Debt-to-income ratio | % | debt / income * 100 | 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: Is Yours Too High?36.00 %
- Debt-to-Income Ratio Calculator and Scale24.00 %
- Debt-to-Income Ratio Calculator and Sheet38.33 %