Debt Sculpting DSCR LLCR Covenant Calculator

Purpose and model boundary

This model creates a ten-year project debt schedule under target-DSCR sculpting, straight-line principal, or level debt service. It calculates annual interest, principal, debt service, ending debt, DSCR, loan life coverage ratio (LLCR), covenant breaches, and any balloon remaining at the selected maturity.

It is a debt-schedule and coverage diagnostic, not a credit decision, debt commitment, covenant interpretation, rating, refinancing assumption, or legal opinion. It does not size opening debt from LLCR or prove that entered CFADS is achievable.

Inputs and units

Debt, CFADS, interest, principal, and debt service use one user-consistent currency scale, shown as currency millions. The compact grid contains exactly ten positional rows: year 1 through year 10 and nonnegative CFADS for each year.

The amortization selector chooses target DSCR, straight-line principal, or level debt service. The interest selector uses beginning debt or a disclosed noncircular average after provisional principal. Maturity is 5 through 10 years. Opening debt must be positive. The model also accepts annual interest, target DSCR, minimum covenant DSCR, and the discount rate used only for LLCR.

Governing relationships

Only years through the selected maturity are active. Let B_t be beginning debt, C_t CFADS, r the annual interest rate, n maturity, and d the LLCR discount rate.

For straight-line principal, provisional and final principal are capped at beginning debt:

Principal_t = min(B_t, Opening debt / n)

For target sculpting, planned service is:

Planned debt service_t = C_t / Target DSCR

For level debt service, the fixed annual amount is:

Level service = Opening debt / sum(1 / (1 + r)^k), for k = 1...n

The zero-rate case is supported because the denominator becomes the number of active years.

Beginning-debt interest is B_t × r. The average-debt route first estimates provisional principal using beginning-debt interest, then calculates:

Interest_t = ((B_t + max(0, B_t - Provisional principal_t)) / 2) × r

This is intentionally noncircular. For target and level-service routes, final principal is:

Principal_t = min(B_t, max(0, Planned debt service_t - Interest_t))

Debt service is interest plus principal, and ending debt is max(0, B_t - Principal_t). Coverage is:

DSCR_t = CFADS_t / Debt service_t, when debt service is positive

LLCR = sum(CFADS_t / (1 + d)^t for active t) / Opening debt

An active year with positive debt service breaches when its DSCR is below the entered minimum covenant DSCR.

Calculation sequence

1. Validate the amortization, interest, and maturity routes, scalar bounds, and ten positional CFADS rows.
2. Mark years through maturity as active and carry beginning debt forward.
3. Determine straight-line principal or the target/level service amount.
4. Calculate interest using the selected beginning or noncircular average-debt basis.
5. Calculate final principal, debt service, ending debt, DSCR, and the covenant-breach flag for each active year.
6. Discount active-year CFADS and calculate LLCR.
7. Aggregate service, interest, principal, minimum/average DSCR, maximum service, breach count, and the maturity balloon.
8. Populate the debt-service and ending-balance chart, apply the reconciliation gate, and evaluate status.

Outputs and interpretation

Headline results include total debt service, total principal, ending balloon, minimum DSCR, LLCR, and covenant-breach count. Supporting values include total interest, average positive DSCR, and maximum annual debt service. The chart combines annual debt-service bars with the ending-debt path so amortization and payment burden can be read together.

Minimum and average DSCR use positive active-year DSCR observations. LLCR discounts CFADS only through the selected debt maturity. A balloon is reported rather than refinanced or forced into the last payment.

Validation and status logic

The workbook evaluates status in this order:

The covenant warning takes precedence over the balloon warning. The derived gate also requires Opening debt - Total principal - Balloon to close within 0.000000001 × max(1, Opening debt).

Assumptions and limitations

• CFADS is entered before debt service and is nonnegative. Taxes, reserves, maintenance cash flows, and working capital must already be reflected if relevant.
• The average-debt interest basis is a one-pass, noncircular approximation.
• One annual interest rate applies to one debt balance. Floating-rate curves, multiple tranches, fees, reserve accounts, refinancing, cash sweeps, and sculpting constraints beyond the selected method are excluded.
• LLCR uses end-of-period annual CFADS through maturity and does not size debt.
• Covenant results are arithmetic comparisons to a user-entered threshold, not legal conclusions.
• All shipped debt, rate, covenant, maturity, and CFADS values are synthetic illustrations.

Restrictions and non-computing states

The grid must contain exactly ten complete rows, and each year must equal its row position from 1 through 10. CFADS is nonnegative and within the published limits. Opening debt is from 0.001 through the published maximum. Interest and LLCR discount rates are from 0 through 1, target DSCR is from 0.1 through 10, and minimum covenant DSCR is from 0 through 10.

Inactive post-maturity CFADS remains in the fixed grid but does not enter service, DSCR, or LLCR. Zero interest is valid. Zero active CFADS can leave a balloon or produce covenant breaches rather than an input error. Errors in the entered values and in the shape of the grid are rejected before calculation; positional-year failures return workbook NOT VALID.

Errors and warnings

A rejected entry means an option, number, or fixed-grid entry did not satisfy the input rules. Workbook NOT VALID means the selected model domain or derived arithmetic failed. Workbook CHECK retains the schedule and identifies covenant breach first, then balloon risk. A connection or calculation-service failure is not a debt or covenant finding. OK confirms modeled arithmetic and the entered threshold comparison only.

Debt repayment, coverage, covenant, and balloon-risk context follows the OCC Comptroller's Handbook: Commercial Real Estate Lending. Infrastructure debt and sensitivity context follows the FHWA P3 Toolkit primers. No lender term, rating, project case, or proprietary model is embedded.