DSCR Formula in Excel: Calculator and Template

To calculate a long-term rental debt service coverage ratio (DSCR), divide the lender-accepted eligible monthly rent by the required monthly property payment. For an eligible fully amortizing structure, that payment generally includes principal, interest, taxes, insurance, and association dues (PITIA). For an eligible interest-only structure, it generally includes interest, taxes, insurance, and association dues (ITIA). The lender's current guidelines and written scenario control the exact inputs.

DSCR Formula at a Glance

Debt service coverage ratio is a coverage measure. It does not calculate property profit, cash-on-cash return, reserves, closing cash, or future appreciation.

Use the On-Page DSCR Calculator

Enter the rent and payment assumptions from a hypothetical long-term rental scenario. Use monthly amounts in every field. The calculator displays the formula with the entered values so the arithmetic is visible.

Download the theLender DSCR Excel Calculator

The branded workbook includes 25 property rows, automatic PITIA or ITIA totals, row-level DSCR formulas, an educational aggregate, input validation, conditional formatting, and an instructions sheet.

Download the theLender DSCR Excel calculator

Open the workbook in Microsoft Excel or another compatible spreadsheet application. Review the formulas after import because third-party spreadsheet software can interpret formatting and validation differently.

How to Calculate DSCR in Excel

1. Enter eligible monthly rent: Put the accepted or estimated monthly rent in column B.
2. Enter the loan payment: Put monthly principal and interest, or eligible interest-only payment, in column C.
3. Add property payment items: Enter monthly taxes, insurance, and association dues in columns D through F.
4. Calculate the payment total: Column G uses =SUM(C7:F7).
5. Calculate DSCR: Column H uses =IFERROR(B7/G7,"").
6. Copy the row: Repeat the process for each property using the same monthly time period.

Microsoft documents the IFERROR function used to keep an empty or incomplete row from displaying a division error.

Worked DSCR Example

• Eligible monthly rent: $4,000
• Monthly principal and interest: $2,700
• Monthly property taxes: $300
• Monthly insurance: $150
• Monthly association dues: $50
• Monthly PITIA: $3,200
• Calculation: $4,000 ÷ $3,200 = 1.25 DSCR

This is an educational estimate. A 1.25 result means the entered rent equals 125% of the entered payment. It does not establish approval, pricing, profitability, or the rent and payment figures underwriting will accept.

Choose the Correct Rent Input

The accepted rent may come from a lease, an appraiser-supported market-rent opinion, or another source permitted by the current program. For a conventional one-unit investment property, Fannie Mae's official Single-Family Comparable Rent Schedule, Form 1007 is designed to support an appraiser's opinion of market rent. A lender may use different evidence and calculations for a DSCR program, short-term rental, multi-unit property, or unusual transaction.

Use the rent figure identified for the actual written scenario. Do not substitute gross booking revenue, projected rent, or an online estimate unless the program permits that source and treatment.

Choose the Correct Payment Input

For a fully amortizing LTR scenario under the supplied product guidance, include monthly principal, interest, taxes, insurance, and association dues. For an eligible interest-only scenario, use interest with taxes, insurance, and association dues. Confirm flood insurance, special assessments, subordinate financing, and other required components with the lender.

The DSCR amortization schedule and calculator helps model principal, interest, PITIA, interest-only transitions, and future balances.

Keep Monthly and Annual Figures Separate

A ratio is valid only when the numerator and denominator cover the same period. Monthly rent divided by monthly payment gives the same ratio as annual rent divided by annual payment when each annual amount is exactly twelve times the monthly amount. Mixing monthly rent with annual debt service produces a meaningless result.

Qualification DSCR Is Not Property Profit

A qualification ratio may use a defined rent and payment calculation. Investor cash flow usually includes additional items such as vacancy, maintenance, management, utilities, capital expenditures, leasing costs, reserves, income taxes, and transaction costs. Run a separate profitability analysis before buying or refinancing.

How to Compare Multiple Properties

Enter one property per row and preserve the same definitions across the worksheet. The workbook totals eligible rent and payment inputs, then divides aggregate rent by aggregate payment. That aggregate is an educational portfolio view. It does not establish cross-collateralization, portfolio eligibility, release terms, pricing, or approval.

Common Excel and DSCR Mistakes

• Using NOI for an LTR formula that requires eligible gross rent: Match the current program definition.
• Leaving out taxes, insurance, or dues: Use every payment component required for the scenario.
• Mixing monthly and annual figures: Keep both sides on one time basis.
• Counting unaccepted rent: Use the evidence and treatment underwriting permits.
• Dividing by an incomplete payment: Confirm principal-and-interest or interest-only treatment.
• Treating DSCR as profit: Analyze operating and capital costs separately.
• Hard-coding calculated cells: Preserve formulas in the total and ratio columns.
• Assuming one threshold applies: Eligibility varies by product, property, transaction, leverage, credit, and current matrix.

DSCR Excel Questions

What Excel formula calculates DSCR?

If eligible monthly rent is in B7 and total monthly PITIA or ITIA is in G7, use =IFERROR(B7/G7,"").

Should taxes and insurance appear in the formula?

They generally appear in the payment denominator for the eligible LTR formulas described here. The written scenario and current program definition control.

Can I use projected rent?

Use projected or market rent only when the program permits that source and underwriting accepts the figure.

Does a higher DSCR guarantee better pricing?

No. Pricing and eligibility can also depend on credit, leverage, loan amount, property, transaction, reserves, documentation, and current guidelines.

Does the workbook replace a lender calculation?

No. It is an educational analysis tool. The lender's calculation and final documents control.

DSCR Review Checklist

• Rent source: Identify the lease, market-rent opinion, or permitted evidence.
• Payment type: Confirm amortizing or interest-only treatment.
• Payment components: Include every required item.
• Time period: Use monthly with monthly or annual with annual.
• Formula cells: Check references before copying rows.
• Current terms: Confirm the matrix and written scenario before relying on the result.

Bottom Line

Calculate an eligible long-term rental DSCR by dividing accepted monthly rent by the required monthly PITIA or ITIA. Use the on-page calculator for a quick transparent estimate and the downloadable workbook for multiple properties. Verify every input against current underwriting and analyze property profitability separately.