Disclaimer: Education only. Templates use illustrative numbers, not data from any real project or InvIT.

In simple words (if you are in class 10)

A financial model is a big calculator in Excel that answers: "If X cars use the road and costs are Y, how much money is left each year, and can we repay the loan?" You type assumptions on one sheet, and every other sheet calculates automatically. Change one number (say traffic grows 3% instead of 5%) and you instantly see the effect.

The professional view

Sheet structure (the "flow")

Inputs → Timing → Traffic & Revenue → Opex & MM → Capex/Asset → Debt → Reserves → Tax → Cash Flow Waterfall → Financial Statements → Ratios & Returns → NDCF → Sensitivities
Sheet Contents Tips
Inputs Concession dates, traffic by class, toll rates, WPI, fixed escalator, O&M, MM cycle and cost, debt terms, tax, discount rates One input per cell, blue font, units in a column, source notes
Timing Period flags (construction / operations / MM year / debt period), half-yearly or annual columns, day counts Flags drive everything; avoid IF chains
Traffic & Revenue ADT × growth by class; toll rate revised annually from 1 April; leakage and exemptions Model the fixed 3% (non-compounding) and the WPI part (40% pass-through) separately, then round to ₹5
Opex & MM Routine O&M per lane-km, insurance, manager fees; MM every N years indexed to inflation Model MM as a flag × cost, not hard-coded years
Asset Service concession intangible or HAM financial asset, amortisation Keep accounting separate from cash
Debt Drawdowns, interest, sculpted repayments, fees Use a copy-paste macro or an algebraic solve to break circularity
Reserves DSRA target (e.g. next 6 months of debt service), MMRA build-up and release Reserve releases are a classic cash double-count
Tax Tax depreciation, loss carry-forward, regime choice Separate tax and book depreciation
Cash Flow Waterfall CFADS → senior debt → reserves → distributions Mirror the escrow waterfall order of the concession
Ratios & Returns DSCR, LLCR, PLCR, project IRR, equity IRR, payback Show minimum and average; flag breaches
NDCF SPV NDCF and InvIT NDCF, 90% test Exclude restricted cash
Sensitivities Traffic, WPI, MM cost, interest rate, delay Data tables or scenario switch

HAM-specific mechanics

  • Bid project cost indexed by a Price Index Multiple (70:30 WPI/CPI) until COD
  • 40% construction support in five instalments tied to progress milestones
  • 60% repaid as semi-annual annuities over 15 years, plus interest on the reducing balance at bank rate + 3% (or MCLR-linked in revised agreements), plus O&M payments (CARE HAM methodology)

Use the HAM annuity schedule template.

Integrity checks every model should have

  1. Balance sheet balances every period
  2. Cash in waterfall = change in cash on balance sheet
  3. Debt fully repaid by final maturity; no negative balances
  4. Sum of revenue across classes = total revenue
  5. Reserve balances never negative; releases only when allowed
  6. No distributions in cash-trap periods
  7. Error flags rolled up to one master check on the cover sheet

Modelling standards worth following

The FAST Standard and similar conventions (consistent columns, one formula per row, no hard-codes in calculations, flows separated from balances).

Common mistakes in toll models

  • Applying full WPI instead of the 40% pass-through
  • Forgetting 1 April revision timing in a calendar-year model
  • MM cost not indexed to inflation
  • Using a perpetual terminal value for a finite concession
  • Tax losses not carried forward or lost on change of ownership
  • DSCR calculated on post-reserve cash (double-counting)

Templates on this site

Previous: Part 11 · Next: Part 13: InvIT and toll road FAQ

Education only. This page is not investment, tax, legal or accounting advice, and it does not recommend buying, selling or holding any security. Laws and rules change: check the linked primary source and consult a qualified professional before acting.

Last reviewed 25 October 2026. Spotted an error? Tell us and we will correct it.