PPP road with climate adaptation

model "PPP Road with Climate Adaptation" {
  currency   USD
  unit       vkm = vehicle * km
  day_count  act/360
  data       "ppp_road_inputs.xlsx"
}

timeline {
  start          2023-01-01
  construction   34m
  operations     38y
  ruler monthly  month                   // traffic, revenue, operating costs
  ruler finance  half  ends May, Nov     // repayments fall in May and November
  ruler annual   year  ends Dec          // tax and statements
}

table traffic {                          // one row per vehicle class
  rows     class
  columns  per_day: vehicle/d "Vehicles per day", trips "Trips per vehicle",
           distance: km "Distance per trip"
}
table climate: x                          // Scenario | Revenue multiplier
table spend_curve: %

table resurfacing: USD                    // Operating year | Resurfacing spend

/// Values on the Operations sheet, so the concession team can edit them
inputs "Operations" sheet "Operations" {
  opex_share:  %     "O&M as percent of revenue"
  actuals_to:  date  "Actuals up to"
  depreciation_life: y "Depreciation life"
}

inputs "Concession" {
  toll              = 0.10 USD/vkm
  climate_case      = option central | dry | wet | extreme
  adaptation        = option yes | no
  base_cost         = 299_451_341 USD
  adaptation_cost   = 71_073_911 USD
  tax_rate          = 13.5 %
  target_irr        = 15 %
  grant             = 0 %                  // of capex, paid up front
}

inputs "Financing" {
  gearing           = 80 %
  target_dscr       = 2.00 x
  dsra_periods      = 1
  base_rate         = 3.6 %/y
  spread            = 4.0 %/y
  concessional_rate = 2.5 %/y
  concessional_share = 0 %
  upfront_fee       = 1.5 %
  commitment_fee    = 1.0 %/y
  commercial_tenor  = 20y
  concessional_tenor = 35y
}

module traffic "Traffic and revenue" on monthly {
  for c in inputs.traffic {
    vkm[c]: vkm = inputs.traffic[c].per_day * inputs.traffic[c].trips
                * inputs.traffic[c].distance * period.days       | during operations
  }
  revenue: USD = sum(vkm[*]) * inputs.toll * inputs.climate[inputs.climate_case]
               | actuals "Toll revenue" until inputs.actuals_to

  opex: USD = revenue * inputs.opex_share
  lifecycle: USD = inputs.resurfacing[operations.year] * year_start
}

module capex "Construction" on monthly {
  cost: USD = inputs.base_cost
            + (if inputs.adaptation == yes then inputs.adaptation_cost else 0 USD)
  spend: USD = spread(cost, over: construction, profile: inputs.spend_curve)
}

module debt "Financing" on finance {
  period tenor = operations.start for inputs.commercial_tenor

  /// Replaces UDFInputs / UDFOutput / UDFFix: the loop is solved in a VBA function
  solve uses: USD = capex.spend + idc + fees + dsra.initial   | by function

  funding uses {
    first  grant   up to inputs.grant * sum(capex.spend)
    then   pro_rata {
      commercial    share inputs.gearing * (1 - inputs.concessional_share)
      concessional  share inputs.gearing * inputs.concessional_share
      equity        rest
    }
  }

  /// Monthly revenue is summed into each half-year; yearly tax lands in the second half
  cfads: USD = traffic.revenue - traffic.opex - (tax.paid | at end)
  commercial = sculpted_loan(draws: funding.commercial,
                             rate: inputs.base_rate + inputs.spread,
                             window: tenor, cfads: cfads, dscr: inputs.target_dscr,
                             fees: { upfront: inputs.upfront_fee, commitment: inputs.commitment_fee })
  concessional = term_loan(draws: funding.concessional, rate: inputs.concessional_rate,
                           window: operations.start for inputs.concessional_tenor,
                           profile: annuity)
  idc:  USD = commercial.idc + concessional.idc
  fees: USD = commercial.fees
  dsra = reserve(target: commercial.debt_service + concessional.debt_service
                         | next inputs.dsra_periods * 6m)
  mra  = reserve(target: traffic.lifecycle | next 3y)
}

module tax "Tax" on annual {
  depreciation: USD = sum(capex.spend) / inputs.depreciation_life | during operations
  taxable: USD = traffic.revenue - traffic.opex - depreciation
               - debt.commercial.interest - debt.concessional.interest   // summed into years
  paid:    USD = max(0, taxable) * inputs.tax_rate
}

module returns "Returns and bid" on finance {
  to_equity: USD = debt.cfads - debt.commercial.debt_service
                 - debt.concessional.debt_service - debt.dsra.net - debt.mra.net
  equity_irr = xirr(to_equity - debt.funding.equity)
}

/// The concession is bid at the toll that gives equity its target
seek inputs.toll so that returns.equity_irr = inputs.target_irr

scenario bau "Business as usual" { inputs.adaptation = no }

/// Adaptation vs business as usual, side by side
dashboard "Comparison" {
  "Bid toll (USD/vkm)"        inputs.toll,             inputs.toll under bau
  "Project cost"              sum(capex.spend),        sum(capex.spend) under bau
  "Min DSCR"                  min(debt.commercial.dscr), min(debt.commercial.dscr) under bau
}