This free DSCR spreadsheet template turns a rental property's income, expenses and proposed loan into a cash-flow analysis and a borrower-prepared deal memo. Download the macro-free Excel workbook, replace the example inputs, then print the Memo tab. You can also import the file into Google Sheets.
Download the free DSCR underwriting spreadsheet (.xlsx)
No email signup is required. The workbook includes a fictional $350,000 purchase with 20% down, a $280,000 loan and $3,200 monthly rent. Its 7.50% rate is an editable assumption, not current lender pricing. Use a dated written quote for your own property; our DSCR rate guide explains how to compare terms.
By Maniteja · Sources and formulas checked October 16, 2026 · Editorial method
Inside the DSCR underwriting model: what's included in the template
The workbook has three linked tabs: Cash Flow, Debt Stress and Memo. Amber cells contain editable inputs. Formula cells calculate the results, so you can trace a number back to the assumption behind it. Save a separate copy for each property before changing the example.
| Tab | What you enter | What it produces |
|---|---|---|
| Cash Flow | Price, down payment, rent, vacancy and itemized annual costs | EGI, NOI, loan principal and acquisition cash |
| Debt Stress | Note rate, amortization, payment type and planning target | Payments, two coverage measures and a rent/rate stress matrix |
| Memo | Borrower details, supporting evidence, risks and unanswered questions | A linked executive summary you can print or save as PDF |
There are no macros, lender feeds or external-workbook links. The spreadsheet uses ordinary formulas, including PMT and PV. The workbook's base calculations and representative input changes were checked in our calculation engine; native Excel and Google Sheets rendering can differ, so check the example totals after opening or importing it.
The tool models a single rental property's annual cash flow. For a portfolio, mixed-use building or commercial loan with a balloon, use a model that matches the actual debt schedule and lender adjustments. This file doesn't calculate taxes on income, sale proceeds or a complete investment return.
How real estate lenders review an investment submission memo
A useful submission memo lets the reviewer reconcile the requested financing with the property evidence. Start with the purchase price, proposed principal, income source and repayment structure. Then identify what is documented, what is estimated and what remains unresolved. An attractive DSCR is difficult to assess without those distinctions.
For the example purchase, the request begins with $350,000 price, $70,000 down and $280,000 financing. The rent line should identify whether $3,200 comes from an executed lease, appraisal support or an asking-rent estimate. Don't describe an asking rent as collected income.
Keep the supporting file beside the memo. Visio's published process describes credit review, appraisal, identity, purchase-contract and reserve documentation. That is one lender's process; your lender can request different evidence. Our DSCR document checklist organizes the items to confirm before paying application costs.
Conventional financing can also use eligible rental income. Under Fannie Mae's subject-property guidance, the purchase calculation applies 75% to supported monthly gross rent and then subtracts PITIA. The treatment depends on the file, including management experience; refinancing can follow a different documentation path. That qualifying adjustment isn't an actual expense budget.
Investor DSCR programs may qualify income without W-2s or personal tax returns, depending on the program, while still reviewing credit, property and liquidity. “Non-QM” alone doesn't identify the transaction's legal classification or documentation requirements. The DSCR loan guide explains why property coverage and final approval are separate questions.
Tab 1: property cash flow and operational assumptions
Enter potential rent before vacancy, then subtract vacancy once. The template calculates effective gross income, or EGI, followed by NOI. Taxes and insurance belong in the operating budget here. Principal and interest enter debt service on the next tab, rather than being deducted again as operating costs.
The fictional annual budget is explicit:
| Calculation | Example amount |
|---|---|
| Gross rent: $3,200 × 12 | $38,400 |
| Vacancy: 5% of gross rent | −$1,920 |
| Effective gross income | $36,480 |
| Taxes, insurance, repairs, management and other operating costs | −$11,400 |
| Net operating income | $25,080 |
The expense inputs break that $11,400 into $4,800 taxes, $1,800 insurance, $1,500 repairs, $1,800 management and $1,500 other costs. Association dues default to zero. These are illustrations, not estimates for your market. Replace them with tax records, insurance terms and a supportable operating budget.
A separate $1,200 annual capital reserve appears below NOI in the cash calculation. That distinction matters. A planned roof replacement can consume cash even when the operating ratio looks acceptable. Keep recurring repairs and capital replacements consistent so one cost doesn't appear twice.
The acquisition-cash line adds the $70,000 down payment and an illustrative $5,000 closing-cost budget. It doesn't establish the lender's required reserves, renovation cash or every charge at settlement. Record those separately before deciding the cash requirement is manageable.
If your starting income figure already reflects vacant months, don't reduce it by the same vacancy again. Either reconstruct potential gross rent and show the allowance, or use the collected-income basis consistently. Our step-by-step DSCR formula guide walks through matching annual periods.
Tab 2: debt service coverage ratio and stress-testing matrix
The Debt Stress tab calculates the scheduled payment and divides annual NOI by annual debt service. At 7.50% over 30 years, the $280,000 fully amortizing loan has about $1,957.80 monthly principal and interest. Annual debt service is $23,493.61 before displayed rounding. Divide $25,080 by that amount and NOI-based DSCR is approximately 1.07.
Cash after operating costs and debt payments is about $1,586.39 annually. After the separate $1,200 capital reserve, only $386.39 remains. That is roughly $32 monthly. A positive coverage ratio can coexist with a narrow cash margin.
The amortizing-payment formula uses the monthly rate and monthly payment count:
=-PMT(annual_rate/12, amortization_years*12, loan_principal)
Microsoft's PMT documentation explains the constant-payment calculation. The template handles zero interest separately. For interest-only selection, it uses principal multiplied by annual rate, divided by 12; it doesn't forecast a later reset or principal repayment.
Why does the same property show two DSCR figures?
The workbook also displays gross monthly rent divided by PITIA. Add $400 monthly taxes and $150 insurance to $1,957.80 principal and interest, with no association dues, and PITIA is $2,507.80. Gross rent of $3,200 divided by that amount is about 1.28. This illustrative rent-based ratio uses different inputs from NOI coverage.
Ask the lender which rent amount and payment definition it accepts. Neither number should be relabeled as the lender's approved ratio without that confirmation. See the DSCR benchmark guide for a closer comparison.
1.00× means modeled debt break-even. Targets such as 1.25× and 1.35× are planning choices or program-specific requirements, not statutory lending thresholds.
The matrix varies gross rent from −10% to +10% and note rates from 6.50% to 8.50%. It keeps the vacancy percentage and dollar operating expenses unchanged. At rent 10% below the example and a 7.50% note rate, NOI falls to $21,432 and coverage is about 0.91. Expense increases require their own test; the grid doesn't assume them automatically.
The maximum-loan output first divides NOI by your target DSCR, then solves for principal at the entered debt terms. At a 1.25 target, the example permits $20,064 annual debt service and about $239,125 modeled principal. LTV, appraisal, credit and program caps can impose a lower limit.
Tab 3: formal lender submission executive summary
The Memo tab links the purchase, financing and operating results without asking you to type them again. Complete its amber narrative fields before sharing it. The example property name and address are fictional; replace both, and date the income evidence rather than leaving a generic “market rent” note.
Use the risk field for something concrete. In this example, the $386 annual cash margin after capital reserve is a useful issue to flag. Explain the response you can document: a different purchase price, lower debt, stronger verified rent or more available cash. Don't claim a mitigation is complete if it is only planned.
Record the borrowing entity, liquidity evidence, property condition, exit plan and open lender questions. Then print the Memo worksheet or choose your spreadsheet application's PDF option. Review the page layout before sending. The workbook doesn't submit the file to a lender, perform an appraisal or certify the assumptions.
How to sync the spreadsheet with our online calculator
The web-to-sheet preview below recalculates the same NOI-based example in your browser. Change the inputs, inspect the result and download a CSV snapshot. It doesn't alter an open Excel file or Google Sheet. Use its cell map to transfer values into your workbook, then check that both tools use the same expense treatment and payment structure.
Web-to-sheet live calculation preview
Local calculation preview. Downloaded CSV values are a snapshot; no Excel or Google account is connected.
Example: NOI $25,080.00; monthly P&I $1,957.80; NOI DSCR 1.07.
| Measure | Calculated value |
|---|---|
| Loan principal | $280,000.00 |
| Annual effective gross income | $36,480.00 |
| Annual NOI | $25,080.00 |
| Monthly principal and interest | $1,957.80 |
| Annual debt service | $23,493.61 |
| NOI-based DSCR | 1.07 |
| Annual cash after capital reserve | $386.39 |
All expenses must use the same annual period. Capital reserve is separate from NOI. No rate feed, lender submission or online Deal Vault. Local storage may be unavailable or cleared.
For the main DSCR calculator, enter $38,400 annual gross rent and $13,320 total annual operating deductions, including the $1,920 vacancy allowance. Leave the itemized expense fields blank when using that total. Use the $280,000 loan, 7.50% rate and 30-year amortization to reproduce approximately 1.07 NOI coverage. Do not deduct vacancy again.
Enable Compare DSCR methods to see the rent-based estimate alongside the operating budget. Enter $4,800 annual property taxes, $1,800 annual insurance and $0 HOA in the comparison assumptions; these amounts are already included in the operating budget and must not be added to it again. With $3,200 monthly qualifying rent, the rent-to-PITIA estimate is approximately 1.28. Confirm the rent and method your lender accepts before interpreting that result as a qualification calculation.
To use Google Sheets, download the XLSX, upload it to Drive and open it with Google Sheets. Follow Google's Excel-to-Sheets instructions if you want a converted Sheets file. Confirm the example NOI, payment and ratios after import. This release provides an Excel download with import instructions, rather than a hosted Google Sheets copy link.
The preview's save button stores one scenario on this browser and device. It isn't an online Deal Vault and doesn't synchronize across devices. Use an exported file as your durable copy; clearing browser storage can remove a saved scenario. Avoid storing account numbers, identity documents or other sensitive material in the scenario name.
Frequently asked questions
Is this DSCR spreadsheet template really free?
Yes. The Excel download and web preview are free to use without an email signup. You supply and verify the property inputs. There is no lender approval or live pricing included.
Can I use it as a DSCR underwriting model in Excel?
Yes, for the annual cash-flow and debt calculations described here. It uses macro-free formulas. Add the actual lender's adjustments and an appropriate debt schedule when the transaction goes beyond the template's assumptions.
Does the debt service coverage ratio Excel sheet guarantee financing?
No. The spreadsheet calculates ratios from your inputs. The lender separately determines qualifying income, payment treatment, property eligibility and borrower requirements.
How do I save a lender memo as PDF?
Complete the Memo tab, select that worksheet for printing and use your spreadsheet application's PDF option. Check the preview and supporting figures before sharing. The web preview also offers a printable scenario summary.
Start with the download, keep the evidence beside the numbers, and save a separate file for each deal. Return when the rent, rate or expense assumptions change. A dated, reproducible analysis is more useful than a ratio copied from an old quote.
Leave a Reply