Debt sculpting in Excel: DSCR-based repayment explained

Why sculpt instead of an annuity?

A mortgage-style annuity has the same payment every year. A project's cash flow available for debt service (CFADS) does not: it changes with production, tariffs and costs. With an annuity, the DSCR is lowest in the weakest year, and lenders size the whole loan on that year. Sculpting lets the debt service follow the CFADS instead, so every year has exactly the target cover and no year is the bottleneck.

The debt service coverage ratio is CFADS ÷ (interest + principal). Lenders set a target, often somewhere around 1.2x to 1.4x for projects with contracted revenue and more where revenue is exposed to market prices. Turned around, the target gives the most debt service each year can carry: CFADS ÷ DSCR.

Step 1: size the debt from the DSCR

If every year pays CFADS ÷ DSCR, the largest loan those payments can repay is their present value at the loan's interest rate:

Debt = NPV(rate, CFADS₁…CFADSₙ) ÷ DSCR

With CFADS from $5,200,000 in year 1 to $5,640,000 in year 10, a target DSCR of 1.35 and 6.5% interest, =NPV(B15,B2:B11)/B14 gives $28,801,474. NPV discounts every payment by full years, which matches debt service paid at the end of each year. In a real model the lenders also cap the debt at a maximum gearing, a share of the project cost, and the senior debt is the lower of the two.

Step 2: build the sculpted schedule

Each year has four formulas:

• debt service: =B2/$B$14, year 1: $3,851,852
• interest on the opening balance: =$B$16*$B$15 in year 1 ($1,872,096), then =F2*$B$15 on the previous closing balance
• principal: =C2-D2, year 1: $1,979,756
• closing balance: opening balance − principal

Because the debt was sized as the present value of exactly these payments, the closing balance reaches $0 in year 10. That is the built-in check of a sculpted schedule: if the last balance is not zero, a reference is wrong. Interest falls each year as the balance shrinks, so principal grows faster than the debt service. Total interest over the tenor is $11,450,378.

Worked example: a 10-year sculpted loan at a 1.35x DSCR

CFADS, the target DSCR and the interest rate are yellow inputs. Debt service, interest, principal, the balance, the sized debt and the annuity comparison are formulas. Interest is charged on the previous year's closing balance.

Worked example: a 10-year sculpted loan at a 1.35x DSCR

	A	B	C	D	E	F
1	Year	CFADS ($)	Debt service ($)	Interest ($)	Principal ($)	Closing balance ($)
2	1	5,200,000	3,851,852	1,872,096	1,979,756	26,821,718
3	2	5,260,000	3,896,296	1,743,412	2,152,885	24,668,833
4	3	5,310,000	3,933,333	1,603,474	2,329,859	22,338,974
5	4	5,370,000	3,977,778	1,452,033	2,525,744	19,813,230
6	5	5,420,000	4,014,815	1,287,860	2,726,955	17,086,275
7	6	5,470,000	4,051,852	1,110,608	2,941,244	14,145,031
8	7	5,510,000	4,081,481	919,427	3,162,054	10,982,976
9	8	5,560,000	4,118,519	713,893	3,404,625	7,578,351
10	9	5,600,000	4,148,148	492,593	3,655,555	3,922,796
11	10	5,640,000	4,177,778	254,982	3,922,796	0
12	Total	54,340,000	40,251,852	11,450,378	28,801,474
13
14	Target DSCR	1.35x
15	Interest rate	6.50%
16	Debt sized = NPV(CFADS) ÷ DSCR	28,801,474
17	DSCR in year 1 / year 10	1.35x	1.35x
18	Annuity payment, same debt	4,006,420
19	Minimum DSCR with the annuity	1.30x
20	Max annuity debt at 1.35x minimum	27,690,309

Fictional project with illustrative terms, not a lender's offer. Educational, not investment or financing advice.

Step 3: compare with an annuity

Repay the same $28.8 million as an annuity and the payment is =PMT(6.5%,10,-debt) = $4,006,420 every year. In year 1, CFADS of $5,200,000 covers that only 1.30x, below the 1.35x target. To keep 1.35x in the weakest year, an annuity loan could only be =PV(6.5%,10,-5,200,000/1.35) = $27,690,309.

Sculpting therefore raises the debt capacity by about $1.1 million here, which means less sponsor equity and a higher equity IRR. The effect is bigger when CFADS rises or falls more steeply over the tenor, and zero when CFADS is flat, because then the sculpted payment is an annuity anyway.

What real project finance models add

The core is the three formulas above, but lender models add layers. Debt is usually sized on a conservative P90 production case, with a second DSCR test on the P50 case. A tail of a few years after the final repayment is left as a buffer. A debt service reserve account (DSRA), often six months of debt service, sits between debt service and distributions in the cash flow waterfall. And the LLCR, the present value of CFADS over the loan life ÷ the debt, is tested alongside the yearly DSCR.

With a fixed debt amount and a minimum DSCR, or several tranches, the simple formula no longer closes on its own, and modellers solve for the DSCR with Goal Seek or a small macro. Sculpting is common in energy and infrastructure finance, for example the projects whose costs the LCOE summarises.

Common mistakes

• Multiplying CFADS by the DSCR. Debt service is CFADS ÷ DSCR, always smaller than CFADS.
• Charging interest on the closing balance. Interest accrues on the balance at the start of the year, which is last year's closing balance.
• Discounting at the wrong rate. The debt capacity is the present value at the loan's all-in interest rate, not the project's discount rate or the equity return.
• Ignoring the tail. Lenders rarely let the debt run to the last year of the project. Size it only over the tenor.
• Sizing on P50 only. Lenders usually size on a downside production case (P90) and check the cover in the base case.
• Not checking that the balance ends at zero. A sculpted schedule that does not repay exactly has a broken link.