verification / datasets / 02-capital-outturn-debt / VARIABLES.md

Variables — Dataset 02: Capital outturn + debt (MHCLG)

Per-council variables this dataset yields for describing/scoring a council's financial efficiency, with emphasis on debt burden and debt-servicing cost. All column names below were verified against the actual files in data/. Join keys across files and years: ONS_Code / LA_LGF_Code (CSV) ↔ LGF code / ONS code (ODS live tables). One CSV row per authority per year (PeriodCode = e.g. 201903 for 2018-19 … 202503 for 2024-25); column codes decode via Metadata_COR_time_series.ods sheet Data_dictionary.

Units: the COR time-series CSV and both live tables report in £ thousand (stated on each live-table worksheet; CSV follows the COR return convention — the CPR4 sheet sourced from the same returns states "£ thousand"). Filter to council classes (LA_Class in UA/SC/SD/L/MD) — the files also carry GLA/other bodies.

1. Extracted

Variable File Column(s) (verified) Unit Interpretation
cfr_end Capital_time_series_data_wide_24_03_26.csv PRU1_prucfrend_amt £000 Capital Financing Requirement at 31 March — the council's underlying need to borrow for capital purposes; the canonical measure of accumulated capital debt.
gross_borrowing_end same CSV PRU2T1_prubrwgrs_end £000 Gross borrowing outstanding at year end (actual external loans stock).
net_debt_end same CSV PRU2T1_prubrwnet_end £000 Net debt at year end (gross borrowing + credit arrangements less investments).
investments_end same CSV PRU2T1_pruinv_end £000 Investments held at year end — the offset side of net debt.
mrp same CSV PRU1_prurpyrevmrp_amt £000/yr Minimum Revenue Provision — the statutory annual revenue charge to repay capital debt; the core debt-servicing (principal) cost.
debt_repayment_total same CSV PRU1_prurpy_amt £000/yr Total contribution from revenue/HRA/MRR/receipts to repay credit liabilities — full principal-repayment effort.
capex_total same CSV EandR1_alltot_expgrandtot £000/yr Total capital expenditure and other capital transactions, all services.
capex_fixed_assets same CSV EandR1_alltot_exptotfa £000/yr Total expenditure on fixed assets (land/buildings, construction, vehicles/plant, intangibles).
capital_receipts_in_year same CSV REC_recrcv_amt £000/yr Capital receipts received in year — proceeds from asset sales.
receipts_used_for_financing same CSV FIN1_finrec_amt £000/yr Capital receipts applied to finance capital spend (reliance on selling assets to fund the programme).
capital_financing_total same CSV FIN1_fingrandtot_amt £000/yr Total resources used to finance capital expenditure (grants + receipts + revenue/borrowing balance).
pwlb_loans_longterm Borrowing_and_investment_live_table_Q4_2025_to_2026.ods, sheet LA_Borrowing_25-26_Q4 Loans Longer-term - PWLB £000 Long-term Public Works Loan Board debt outstanding at quarter end — the dominant borrowing channel.
borrowing_components_q same ODS, same sheet all Loans short term - *, Securities - *, Loans Longer-term - *, Short/Longer Term Loans Local Authorities columns (sum for total; no total column exists on the sheet) £000 Quarterly stock of external borrowing by lender category — timelier than the annual CSV; sum gives total outstanding borrowing.
capex_fixed_assets_q Capital_payments_and_receipts_Q4_2025_to_2026_England.ods, sheet 2025-26_Q1-Q4_CPR4 Expenditure: Total expenditure on fixed assets £000 In-year (provisional) capital spend on fixed assets per authority — the timely counterpart to capex_fixed_assets.

2. Constructed

Normalizers: no verified per-resident population series exists in datasets 01–12, so ratios are normalized per pound of core (net current service) spend using RS_netcurrtot_net_exp from 01-revenue-outturn/data/Revenue_Outturn_time_series_data_v3.2.csv (verified; join on ONS_code/year). Within-dataset normalizers are also used.

Variable Formula Inputs Unit Interpretation
debt_to_core_spend cfr_end / core_spend PRU1_prucfrend_amt (this dataset); RS_netcurrtot_net_exp (01-revenue-outturn) ratio (£/£) Debt burden per pound of core service spend — the Woking-style leverage flag; values several times core spend signal commercial-investment exposure.
gross_borrowing_to_core_spend gross_borrowing_end / core_spend PRU2T1_prubrwgrs_end; RS_netcurrtot_net_exp (01-revenue-outturn) ratio Externalized-debt variant of the leverage measure (actual loans rather than borrowing need).
debt_servicing_share debt_repayment_total / core_spend PRU1_prurpy_amt; RS_netcurrtot_net_exp (01-revenue-outturn) ratio Share of each pound of core spend consumed by repaying capital debt — money not reaching services.
mrp_adequacy mrp / cfr_end PRU1_prurpyrevmrp_amt, PRU1_prucfrend_amt ratio (yr⁻¹) Effective principal-repayment rate on the debt stock; abnormally low values indicate deferred repayment (under-provisioning), a known distress precursor.
internal_borrowing_gap (cfr_end − gross_borrowing_end) / cfr_end PRU1_prucfrend_amt, PRU2T1_prubrwgrs_end ratio Share of borrowing need met internally from cash balances; a large gap that must eventually be externalized at current rates is a refinancing risk.
pwlb_dependence pwlb_loans_longterm / Σ borrowing_components_q LA_Borrowing_25-26_Q4 columns (all lender categories) ratio Concentration of debt in PWLB vs. market/inter-authority lenders; high inter-LA short-term reliance signals liquidity stress.
debt_growth_rate (cfr_end[t] − cfr_end[t−1]) / cfr_end[t−1] PRU1_prucfrend_amt across PeriodCode years (2018-19 to 2024-25) %/yr Pace of debt accumulation; sustained double-digit growth marks aggressive capital/commercial strategies.
asset_sale_reliance receipts_used_for_financing / capital_financing_total FIN1_finrec_amt, FIN1_fingrandtot_amt ratio Dependence on selling assets to fund the capital programme (incl. flexible-use-of-receipts pressure).
receipts_coverage_of_capex capital_receipts_in_year / capex_total REC_recrcv_amt, EandR1_alltot_expgrandtot ratio Whether disposals are outpacing investment — persistent high values suggest the balance sheet is being run down.
capex_to_core_spend capex_total / core_spend EandR1_alltot_expgrandtot; RS_netcurrtot_net_exp (01-revenue-outturn) ratio Scale of the capital programme relative to the council's operating size.

Caveats: HRA-inclusive figures (*_amthra variants exist for most PRU1/FIN1 columns) mix housing landlord debt with general-fund debt — use the non-HRA _amt columns for like-for-like comparisons, or compare stock-holding councils separately. Reorganisations (new unitaries) break year-on-year formulas; the borrowing live table covers the UK — filter to England when joining with the CSV.

3. Variable file index

One file per variable in variables/:

Extracted

Constructed