DSCR Calculation in Excel: Free Template, Formula & Step-by-Step Guide

The Debt Service Coverage Ratio — DSCR — is the metric that makes or breaks a project finance deal. Every lender, every credit committee, every financial model review will centre on it. Yet it is widely miscalculated in Excel, misunderstood in definitions, and misapplied in sensitivity testing. This guide covers the exact DSCR formula used in project finance, how to build it correctly in Excel, how it compares to LLCR and PLCR, how debt sculpting works, and where the most expensive calculation errors happen.

What Is DSCR and Why Does It Matter?

DSCR stands for Debt Service Coverage Ratio. It measures how many times a project's operating cash flow covers its debt obligations in a given period:

DSCR = CFADS / Debt Service

Where:
  CFADS        = Cash Flow Available for Debt Service
  Debt Service = Principal Repayment + Interest Payment (in the period)

A DSCR of 1.0x means the project earns exactly enough to cover its debt — no margin. A DSCR of 1.30x means it earns 30% more than it needs. Lenders set minimum DSCR covenants as a buffer against downside scenarios. If DSCR falls below the covenant level, the project is in technical default — even if it has not missed a payment.

DSCR is calculated at every period of the model (typically annual, sometimes semi-annual) and reported as a minimum, average, and at loan maturity.

The DSCR Formula Explained in Full

The most common error in DSCR calculation is using the wrong numerator. Many analysts use EBITDA — this is incorrect in project finance. The correct numerator is CFADS: Cash Flow Available for Debt Service.

Step 1: Calculate CFADS

EBITDA
  − Corporate Income Tax (cash paid in the period)
  − Change in Net Working Capital (ΔNWC)
  − Sustaining / Maintenance CAPEX
  ─────────────────────────
  = CFADS

Step 2: Calculate Debt Service

Senior Debt Principal Repayment (in period)
  + Senior Debt Interest Payment (in period)
  ─────────────────────────
  = Debt Service

Note: subordinated debt service is excluded from the senior DSCR calculation.
Mezzanine DSCR is calculated separately using a wider debt service definition.

Step 3: Divide

DSCR = CFADS / Debt Service

In Excel (example, where CFADS is in D15 and Debt Service is in D16):
  =D15/D16

Add error handling to avoid #DIV/0! in construction years when debt service is zero:
  =IFERROR(D15/D16, "")

Building DSCR in Excel: Step by Step

Here is the exact sequence of rows you need to build a correct DSCR calculation in an Excel project finance model:

1. Revenue Price × volume for each revenue stream. Escalate using a linked inflation assumption row.
2. OPEX Fixed costs, variable costs, and management fees. Apply separate escalation rates to each.
3. EBITDA Revenue minus OPEX. This is your starting point for CFADS — not your ending point.
4. Tax Paid Calculate taxable income (EBITDA minus depreciation minus interest), apply the tax rate, then adjust for timing (deferred tax, loss carry-forward). Use cash tax paid, not the income statement charge.
5. Working Capital Movement Apply a days-outstanding formula to receivables and payables. The ΔNWC is the change from the prior period opening balance.
6. Sustaining CAPEX Major maintenance items that extend asset life. These are cash outflows that reduce CFADS. Exclude expansionary CAPEX (which is typically funded separately).
7. CFADS Sum: EBITDA minus tax paid minus ΔNWC minus sustaining CAPEX.
8. Debt Service From the debt schedule: interest charge plus scheduled principal repayment for the period.
9. DSCR CFADS divided by Debt Service. Format as a number with 2 decimal places followed by "x".

DSCR vs LLCR vs PLCR: What Each Metric Tells You

DSCR is a point-in-time metric — it tells you about a single period. Lenders also require two forward-looking ratios that give a lifetime view of coverage.

LLCR and PLCR use NPV rather than raw CFADS — the discount rate is the cost of debt (or the weighted average cost of debt in multi-tranche structures). PLCR is always higher than LLCR because it captures the project's "tail" cash flows beyond the debt tenor.

DSCR Benchmarks by Sector

Lenders set minimum DSCR covenants based on the risk profile of the asset. Higher-risk, merchant-exposed projects require more headroom.

DSCR Sculpting: Matching Debt Repayment to Cash Flow

In many infrastructure projects, cash flows are not uniform — they ramp up in early years and may decline later. A straight-line repayment schedule would create very low DSCR in early years and very high DSCR later. Lenders and sponsors use debt sculpting to solve this: repayment is sized so that DSCR is constant (or near-constant) throughout the loan life.

The sculpted repayment in any period is:

Repayment(t) = CFADS(t) / Target DSCR − Interest(t)

This creates a circular reference in Excel:
  Interest depends on opening debt balance
  Opening debt balance depends on prior repayment
  Repayment depends on CFADS and interest

Excel's iterative calculation can handle this, but requires:
  File → Options → Formulas → Enable Iterative Calculation
  Maximum iterations: 1000, Maximum change: 0.001

Dedicated tools like Intellifields Horizon solve sculpting without circulars.

Sculpting is used in virtually all large infrastructure transactions today. It maximises the debt quantum while keeping coverage ratios compliant throughout the loan life.

The 7 Most Expensive DSCR Calculation Mistakes in Excel

1. Using EBITDA instead of CFADS. EBITDA ignores taxes paid, working capital movements, and sustaining CAPEX. Using it will overstate DSCR, sometimes significantly.
2. Using income statement tax instead of cash tax. Deferred tax timing differences mean the cash tax paid in any period differs from the income statement charge. Always model cash taxes separately.
3. Forgetting DSRA mechanics. When CFADS is short of debt service, the DSRA covers the gap. Ignoring this makes DSCR appear lower than the lender will see it — and misrepresents the covenant position.
4. Mixing gross and net debt service. Some definitions include DSRA movements in debt service; others exclude them. Always clarify which definition applies to a given loan agreement.
5. Applying the wrong period. If the model is semi-annual, debt service must be semi-annual. If it is annual, ensure interest is accrued on the correct opening or average balance.
6. Using net income as a proxy for CFADS. Net income includes non-cash depreciation and excludes capital items. It will always give a different (and incorrect) DSCR.
7. Not distinguishing senior and total DSCR. Senior DSCR covers only senior debt service. Total DSCR (or junior DSCR) covers subordinated and mezzanine debt as well. These are different metrics with different covenants.

Free DSCR Excel Template Download

Rather than building a DSCR model from scratch in Excel, Intellifields Horizon provides a ready-to-use project finance model with DSCR, LLCR, and PLCR calculated period-by-period from day one. It eliminates circular references, handles DSRA mechanics automatically, and produces lender-ready outputs at the click of a button.

Related Resources

Continue building your project finance knowledge with these related guides:

• The Complete Guide to Project Finance Modelling: Templates, DSCR & IRR
• Infrastructure Financial Model: Structure, Key Metrics & Best Practices
• Renewable Energy Financial Model: Solar, Wind & Battery Storage