PPP Availability Payment Model: Deductions, Headroom, DSCR – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Builds the deduction regime the way the contract is written: unavailability days and performance points, both indexed, both against the contractual cap, with warning-notice and cap-reached flags.
2. Computes the deduction headroom - the deduction at which the cover ratio reaches its covenant - in dollars, as a share of the unitary charge and in additional unavailability days.
3. Funds the lifecycle programme and the handback obligation through reserves, which is why the cover ratio is flat at 1.25x instead of collapsing in every fifth year.

FINANCIAL MODELING EXCEL DESCRIPTION

PPP Availability Payment Model: Deductions, Headroom, DSCR is an Excel template (XLSX) with a supplemental PDF document available for immediate download upon purchase.

Every toll road and PPP model on the market answers the demand question: how much traffic, at what toll, growing at what rate. On an availability-based concession that question does not exist. The public authority pays a contractual unitary charge for making the asset available to a standard, and the only way the revenue line moves is if the concession fails to deliver. Modelling that as a percentage haircut on revenue tells you nothing you can negotiate with.

THE DEDUCTION REGIME, BUILT THE WAY THE CONTRACT IS WRITTEN

Unavailability is charged per unavailability day at a contractual daily rate. Performance failures that leave the asset available are charged in performance points at a point value. Both are indexed, both accumulate, and both are subject to a contractual cap on what can be deducted in a payment period. Above the cap the authority cannot deduct more – it issues warning notices instead, and persistent breach becomes a termination event. This model builds each of those lines, flags the year the cap is reached and flags the year the warning threshold is crossed.

THE NUMBER NOBODY ELSE COMPUTES

The question a sponsor, a lender and an FM contractor all argue about is how much failure the financing can absorb. It has an exact answer: the deduction at which CFADS falls to the covenant multiple of debt service. This model reports it three ways – in dollars, as a share of the unitary charge, and as additional unavailability days on top of those already assumed. In the worked example the tightest year absorbs 5.1 million, which is 8.6 per cent of the charge or about 106 extra unavailability days. Against a contractual cap of ten per cent, that means the concession survives almost exactly the worst the contract permits – and the sensitivity table shows the breach arriving only when the cap itself binds. Deductions alone cannot quite break this deal. The lifecycle programme can.

LIFECYCLE IS LUMPY, AND THAT IS A FINANCING PROBLEM

A twenty-five year concession replaces roofs, plant and finishes on a cycle. Paid out of the year it falls in, a 24 million intervention against a 40 million cash flow takes the cover ratio below one in that year and leaves it high in the four years either side. No lender accepts that profile, and no sculpted schedule can fix it, because sculpting follows the cash flow rather than smoothing it. The market's answer, and this model's, is a reserve: the concession contributes over the cycle preceding each intervention, the reserve pays the contractor, and the contribution – not the spend – sits inside CFADS. The result is a cover ratio that is flat at 1.25 times in every one of the twenty-three years of the tenor. The handback obligation is funded the same way over the final years, and two checks require both reserves to pay in full and to unwind to zero.

THE TENOR AND THE QUANTUM ARE BOTH OUTPUTS

Revenue stops on the day the concession expires, so a loan maturing on that day has no refinancing option and no asset to look at. Lenders insist on a tail, which makes the tenor a result of the term and the tail rather than an assumption: the sponsor here asks for twenty-five years and gets twenty-three. The quantum is the lesser of the DSCR capacity – computed in closed form, one division, with the year-by-year workings on the page – and the gearing cap on capitalised project cost, with the binding constraint named on the Dashboard.

NO CIRCULAR REFERENCES, AND NO MACROS

Thirty monthly construction columns capitalise the arrangement fee, the commitment fee and the interest during construction month by month. Because interest is charged on the balance at the start of each month, the ledger resolves left to right – no cell depends on itself and iterative calculation stays switched off. The sculpting recursion is removed the same way, algebraically rather than by iteration. The file is a plain xlsx: no macros, no add-ins, no external links, no locked cells and no passwords, which matters when corporate IT will not open a macro-enabled workbook.

TWENTY-THREE INTEGRITY CHECKS

Every one must read PASS before you quote a number. Eleven of them exist only because this model builds the payment mechanism and the reserves rather than assuming them: the charge ties to its indexed and fixed elements, deductions never exceed the cap, the net charge reconciles, the lifecycle programme is spent in full in real terms, both reserves pay in full and unwind to zero, the debt matures before the tail, and the deduction headroom is positive in every year of the tenor.

Eleven tabs: Read Me, Dashboard, Assumptions, Construction & Funding, Unitary Charge & Deductions, Operating Cost & Lifecycle, Debt Sizing, Debt Schedule, Cash Flow, Returns, Checks. Live formulas throughout. Two exact sensitivity tables – deductions to the contractual cap and lifecycle to twice the programme – each with the minimum cover ratio and a verdict against the covenant. A twelve-page PDF guide covers the method, the three inputs people get wrong, and the limitations stated plainly.

Source: Best Practices in Financial Modeling, Government Excel: PPP Availability Payment Model: Deductions, Headroom, DSCR Excel (XLSX) Spreadsheet, Balancewright