Bus Mass Transit Financial Model (Excel) | Concession, Farebox & Subsidy, DSCR Debt & Cost per KM

Break-Even, Cash Flow Projections, DCF Model, Debt Schedule, Debt Service Coverage, Excel, Financial Feasibility, Financial Model, Financial Statements, IRR (Internal Rate of Return), LLCR (Loan Life Coverage Ratio), NPV (Net Present Value), Payback Period, PLCR (Project Life Coverage Ratio), Project Finance, Return on Invested Capital (ROIC), Scenario Analysis, Sensitivity Analysis, Stress Test, Uses and Sources of Funds, Valuation

The Bus Mass Transit Financial Model is an institutional, lender-grade Excel model for an urban bus operator or concession, built on the same rigour as a full project-finance underwriting. It takes a fleet, a network and a revenue mechanism and turns them into a defensible investment case: revenue, coverage, returns and cost per vehicle-kilometre, over the concession life.

Bus mass transit is contracted infrastructure, and the central question is who carries revenue risk. I represent both sides of that: a farebox stream the operator earns from passengers, and a subsidy or availability payment the authority pays per vehicle-kilometre, indexed to inflation. That mix, close to a gross-cost concession at one extreme and a net-cost concession at the other, drives how stable the cash flow is and how much debt the operator can carry.

What is inside

A network and service engine: fleet, peak vehicle requirement, revenue and service kilometres, and a ridership ramp.
A revenue build combining farebox, a per vehicle-kilometre subsidy or availability payment, and ancillary income.
A fleet and depot capital schedule with interest during construction and a fleet replacement reserve.
DSCR-sculpted senior debt with a debt service reserve, LLCR and PLCR, and covenant headroom.
Traditional statements and reorganized statements (NOPLAT, invested capital, ROIC, economic profit) reconciled by three bridges that equal zero every period.
Capital allowances and deferred tax, a minimum operating cash balance, and distributions limited to distributable reserves.
Project and equity IRR, equity multiple, payback, minimum and average DSCR, LLCR, PLCR and cost per vehicle-kilometre.
A four-case scenario manager, a tornado, a two-way sensitivity grid, break-even analysis and a sponsor equity cure, feeding a live dashboard.

Every calculation is formula-driven and transparent, with no macros. All inputs are illustrative and benchmarked to public industry ranges and clearly flagged; there is no reference to any specific city, authority, operator or private contract. The model is independently checked and carries zero formula errors. A 31-page development and modelling guide is included, covering network planning, depot and fleet, the concession and revenue mechanism, operations and fleet renewal, mapped to the model.

Who it is for: bus operators, transit authorities, concessionaires, sponsors, and infrastructure lenders sizing debt against a transit concession.

You must log in to submit a review.