A free mortgage DTI calculator in Excel, on a complete underwriting file
Qualifying income with a declining self-employed line, principal, interest, mortgage insurance, tax and insurance, the housing and debt-to-income ratios, funds to close and reserves, an appraisal below the price, contrast files and a repurchase: nine sheets, no macros.
The file underwrites the Calloway loan, a fictional 90% purchase loan on a $485,000 home at 7% over 30 years. Self-employed income fell 11.5% from 2024 to 2025, so the underwriter uses the lower, most recent year: total qualifying income is $12,258 a month, not the $12,633 the loan officer used. The appraisal comes in at $478,000; the price is amended and the loan becomes $430,200, with PITI of $3,762. The housing ratio is 30.7% and the debt-to-income ratio 38.0%, rising to 39.4% when new debt appears before closing.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Mortgage_Underwriting_DTI_Model.xlsx (24 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Mortgage Underwriter. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Nine sheets. Blue cells on Inputs are the only inputs.
Read Me and Inputs
Price, loan-to-value, rate, term, mortgage insurance, tax and insurance, the appraisal, income lines, debts, assets and the contrast files.
Income
Base salary, a two-year bonus average and self-employed income, with the declining-income rule.
Payment and DTI
Principal and interest, mortgage insurance, tax and insurance, PITI, other debts, the housing ratio and DTI at application, after the price change and after new debt.
Assets
Down payment, closing costs, cash due at closing, verified funds and the reserves test.
Collateral
The appraisal below contract price and the loan-to-value on the lower of the two.
Contrast Files, QC and Repurchase and Checks
FHA and VA versions of a loan, the defect rate and a repurchase loss, and the checks.
What the worked case returns
Measure
Value
Qualifying income a month
$12,258
Loan amount after the price change
$430,200
PITI
$3,762
Housing ratio
30.7%
DTI, after the price change
38.0%
DTI, after new debt
39.4%
Cash due at closing
$55,700
Funds left after closing
$10,300 (reserves met)
FHA contrast file: DTI
45.1%
The loan officer averaged two years of self-employed income; the underwriter used the lower year because income is declining. The $375 a month difference is the kind of change that moves a borderline file across a DTI limit.
How to use it on your own file
Enter price, loan-to-value, rate and term, tax and insurance on Inputs.
Enter each income line; for declining self-employed income, the file uses the lower year.
Enter monthly debts and read the housing ratio and DTI.
Check funds to close and reserves, then the appraisal.
Questions people ask about it
Is this template really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference.
How is the debt-to-income ratio calculated?
The full monthly housing payment (principal, interest, mortgage insurance, property tax and homeowners insurance) plus every other monthly debt payment, divided by gross monthly qualifying income.
Can I use it for a real loan?
You can reuse the structure. The borrower and every figure are fictional, program limits change, and nothing here is lending advice.
The rest of the files
This template is the companion workbook for The Mortgage Underwriter; the companion page also carries the other files that go with it. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from The Mortgage Underwriter. The book is on Apple Books and Google Play.
If this book helped, or didn’t, a rating where you bought it is worth more than it looks: it is what the next reader goes on. The template stays free either way.