Capital Fund Planning

GAFL 6510 · Fels Institute of Government

Capital Fund
Planning

City Transit Authority — FY2025 Capital Program • 5-Year CIP: $1.575 Billion
Skills: CIP Scheduling • Funding Allocation • Bond Amortization • Fiscal Impact Analysis

Context

City Transit Authority Capital Improvement Program

The City Transit Authority (CTA) operates one of the region's largest public transit systems — serving over 1 million daily riders across rail, subway, light rail, and bus networks. In FY2025, the CTA's board approved a $922.8 million annual capital budget, part of an all-time-high $14 billion 12-year Capital Program enabled by the federal Infrastructure Investment and Jobs Act (IIJA).

In this exercise you play the role of a capital budget analyst at the City Transit Authority (CTA). The board has approved a 5-year, $1.575 billion Capital Improvement Plan (FY2025–FY2029). Your job is to build the financial architecture behind that plan: scheduling project expenditures, mapping out funding sources, modeling bond debt service, and assessing the impact on the authority's operating budget.

$1.575B

5-Year Capital Program

Project Categories

$505M

Bond Financing Required

4.50%

Revenue Bond Rate

Four Tasks in This Exercise

Task 1 — CIP Schedule: Build the project-by-year spending matrix and compute annual totals.
Task 2 — Funding Sources: Allocate federal, state, and local financing for each year.
Task 3 — Debt Service: Construct a 20-year bond amortization schedule for the local share.
Task 4 — Operating Impact: Calculate debt service as a percentage of the growing operating budget.

Download the Excel Starter file above. The workbook contains four worksheets — one per task. Pre-filled data is provided in each sheet; your job is to enter the formulas indicated by the column headers.

Task 1

CIP Annual Schedule

Pending

The CIP organizes capital spending by project category (rows) and fiscal year (columns). Your first task is to calculate the totals that summarize this matrix — both across projects for each year and across years for each project.

What is a Capital Improvement Plan?

A Capital Improvement Plan (CIP) is a multi-year schedule that identifies needed capital projects, estimates their costs, and aligns them with projected funding. Transit agencies use CIPs to:

Communicate investment priorities to elected boards and the public
Sequence projects to match funding availability (especially federal grant timelines)
Satisfy federal requirements for State of Good Repair tracking

Formula A — Project 5-Year Total (Column G)

5-Year Total = Sum of all annual expenditures for that project

=SUM(B3:F3) — drag down through row 10

Each row covers one project category across FY2025–FY2029 (columns B–F).

Formula B — Annual Total (Row 11)

Annual Total = Sum of all project expenditures in that fiscal year

=SUM(B3:B10) — drag right through column G

Cell G11 (grand total) should equal both =SUM(G3:G10) and =SUM(B11:F11) — a built-in cross-check.

Step-by-Step

Open the CIP_Schedule sheet in the Excel Starter file. Rows 3–10 list the eight project categories; columns B–F contain given annual expenditures.

In cell G3, enter =SUM(B3:F3) to total the MFL Railcar Replacement project across all five years.

Copy G3 down through G10 to calculate the 5-year total for each remaining project.

In cell B11, enter =SUM(B3:B10) to total all FY2025 capital spending across projects.

Copy B11 right through G11. Cell G11 is your grand total; verify it equals $1,575,000,000.

Capital Spending by Category — FY2025 to FY2029

Hints

Think about the two dimensions of a project-by-year matrix. Project totals sum across columns (years); annual totals sum down rows (projects).

Use SUM with a range spanning all five fiscal-year columns for the project total. For the annual total, sum down the column from the first project row to the last.

Column G formula: =SUM(B3:F3) dragged down. Row 11: =SUM(B3:B10) dragged right. Grand total G11: =SUM(G3:G10).

Task 1 Verification

Paste three values from your Excel sheet:

FY2027 Annual Total (D11)

Trolley Mod 5-Yr Total (G4)

Grand Total (G11)

🔑 Answer Key — Task 1: CIP Schedule

Project	FY2025	FY2026	FY2027	FY2028	FY2029	5-Yr Total
MFL Railcar Replacement	$85,000,000	$95,000,000	$120,000,000	$110,000,000	$70,000,000	$480,000,000
Trolley Modernization	$30,000,000	$45,000,000	$65,000,000	$85,000,000	$95,000,000	$320,000,000
Station Rehabilitation	$38,000,000	$42,000,000	$48,000,000	$45,000,000	$42,000,000	$215,000,000
Track & Infrastructure	$35,000,000	$37,000,000	$38,000,000	$38,000,000	$37,000,000	$185,000,000
Bus Fleet Replacement	$28,000,000	$32,000,000	$30,000,000	$28,000,000	$27,000,000	$145,000,000
Technology & Systems	$15,000,000	$18,000,000	$22,000,000	$20,000,000	$20,000,000	$95,000,000
Facilities & Maintenance	$12,000,000	$14,000,000	$16,000,000	$17,000,000	$16,000,000	$75,000,000
ADA Accessibility	$10,000,000	$12,000,000	$13,000,000	$13,000,000	$12,000,000	$60,000,000
Annual Total	$253,000,000	$295,000,000	$352,000,000	$356,000,000	$319,000,000	$1,575,000,000

Task 2

Funding Source Allocation

Pending

Capital projects are rarely funded from a single source. Transit agencies blend federal formula grants, competitive federal discretionary grants, state assistance, and locally-raised bond proceeds. For each fiscal year, you will calculate how much comes from each source — and determine the local share that must be bond-financed.

Funding Source Rules for This Exercise

Federal Formula Funds (FTA §5307/§5337): 40% of annual capital need. These are formula-based allocations received annually based on ridership and route miles.
PA State Capital Assistance (Act 89): 20% of annual capital need. Pennsylvania's dedicated transit capital funding stream.
Federal Discretionary Grants (IIJA): Lump-sum competitive grants — $80M awarded in FY2025 (MFL railcar contract), $45M in FY2027 (trolley signal system). These are given; do not calculate.
Local Bond Financing: The remainder after all other sources. This is what the authority must borrow.

Formula C — Federal Formula Funds

Federal Formula = Annual Capital Need × 40%

=B4*0.40 — drag right across FY2025–FY2029, then to Total column

Formula D — State Capital Assistance

State Assistance = Annual Capital Need × 20%

=B4*0.20 — drag right across FY2025–FY2029, then to Total column

Formula E — Local Bond Financing

Local Bonds = Annual Need − Federal Formula − State − Federal Discretionary

=B4-B5-B6-B7 — drag right; Total column: =SUM(B8:F8)

This is your residual — the amount the authority must raise through bond issuance.

Formula F — Federal Leverage Ratio

Leverage Ratio = (Federal Formula + Federal Discretionary) ÷ Local Bond Financing

=(B5+B7)/B8 — drag right; this ratio shows federal dollars per local dollar invested

A leverage ratio > 1 means you attract more federal dollars than you spend locally. In FY2025, watch what happens with the large discretionary grant.

Step-by-Step

Open the Funding_Sources sheet. Row 4 has the annual capital needs (from Task 1, already filled). Row 7 has the given federal discretionary amounts.

In B5, enter =B4*0.40 for the Federal Formula amount. Drag right through F5, then enter =SUM(B5:F5) in G5.

In B6, enter =B4*0.20 for State Assistance. Drag right, then sum in G6.

In B8, enter =B4-B5-B6-B7 for Local Bonds. Drag right; in G8 enter =SUM(B8:F8). This should total $505,000,000.

In B9, enter the verification row =SUM(B5:B8). It should equal B4 exactly. If not, recheck your formulas.

In B10, enter the leverage ratio =(B5+B7)/B8. Drag right through FY2029. Note the FY2025 ratio.

Annual Capital Funding by Source — FY2025 to FY2029

Hints

Each funding source is calculated as a percentage of the annual capital need or as the residual after other sources. The total of all sources must always equal the annual capital need.

Federal formula and state are straightforward percentages of the annual total. Discretionary is given. Local bonds are the remainder: total need minus the three other sources.

Row 5 (Federal Formula): =B4*0.40. Row 6 (State): =B4*0.20. Row 8 (Local Bonds): =B4-B5-B6-B7. Leverage ratio: =(B5+B7)/B8. Drag all formulas right through column F.

Task 2 Verification

Paste three values from your Funding_Sources sheet:

FY2025 Local Bonds (B8)

Total Local Bonds (G8)

FY2025 Leverage Ratio (B10)

🔑 Answer Key — Task 2: Funding Sources

Source	FY2025	FY2026	FY2027	FY2028	FY2029	Total
Annual Capital Need	$253,000,000	$295,000,000	$352,000,000	$356,000,000	$319,000,000	$1,575,000,000
Federal Formula (40%)	$101,200,000	$118,000,000	$140,800,000	$142,400,000	$127,600,000	$630,000,000
State Act 89 (20%)	$50,600,000	$59,000,000	$70,400,000	$71,200,000	$63,800,000	$315,000,000
Federal Discretionary (given)	$80,000,000	$0	$45,000,000	$0	$0	$125,000,000
Local Bond Financing	$21,200,000	$118,000,000	$95,800,000	$142,400,000	$127,600,000	$505,000,000
Federal Leverage Ratio	8.55x	1.00x	1.94x	1.00x	1.00x	—

Task 3

Bond Debt Service Schedule

Pending

The authority will issue $505,000,000 in revenue bonds to cover the 5-year local share identified in Task 2. These bonds carry a 4.50% annual interest rate and a 20-year term, with equal annual debt service payments (level annuity). Your task is to build the complete 20-year amortization schedule.

How Bond Amortization Works

Each year the authority makes a fixed annual payment that covers both interest and principal. In early years, most of the payment is interest (because the outstanding balance is high). Over time, as the balance falls, less interest accrues and more of each payment retires principal — even though the total payment stays the same.

Interest portion: Outstanding balance × annual rate
Principal portion: Fixed payment − interest
Ending balance: Beginning balance − principal repaid

Formula G — Annual Debt Service Payment (PMT)

Annual Payment = PMT(rate, term, −bond amount)

=ABS(PMT(C3,D3,−B3)) → place result in cell E3

B3 = bond amount ($505,000,000); C3 = rate (0.045); D3 = term (20). PMT returns a negative value (outflow), so wrap with ABS(). Your answer: $38,822,453 per year.

Formula H — Interest Portion

Interest = Beginning Balance × Interest Rate

=B6*$C$3 — absolute reference to rate cell; drag down all 20 rows

Formula I — Principal Portion

Principal = Annual Payment − Interest

=C6−D6 — (Annual DS minus Interest); drag down all 20 rows

Formula J — Ending Balance

Ending Balance = Beginning Balance − Principal Repaid

=B6−E6 — Year 1 only; subsequent years: beginning balance = prior ending balance

Year 20 ending balance should equal $0 (within a few cents of rounding). Use this as your built-in check.

Step-by-Step

Open the Debt_Service sheet. Bond parameters are in row 3: B3 = bond amount, C3 = rate, D3 = term.

In E3, enter =ABS(PMT(C3,D3,-B3)). This is the fixed annual payment — approximately $38,822,453.

In B6, enter =B3 (the full bond amount as the Year 1 beginning balance). In B7 through B25, enter =F6, =F7, etc. (the prior year's ending balance).

In C6, enter =$E$3 (absolute reference to the annual payment). Drag down through C25.

In D6, enter =B6*$C$3 (interest = balance × rate). Drag down through D25.

In E6, enter =C6-D6 (principal = payment − interest). In F6, enter =B6-E6 (ending balance). Drag both down through row 25.

Verify: F25 (Year 20 ending balance) should be $0 or very close to it. If not, check your beginning-balance formulas.

20-Year Debt Service: Interest vs. Principal Breakdown

Hints

In a level-payment amortization, the payment is fixed but its composition shifts. Early payments are mostly interest; late payments are mostly principal. The balance reaches zero on the final payment.

Use Excel's PMT function to find the level annual payment. Then for each year: Interest = Beginning Balance × rate; Principal = Payment − Interest; Ending Balance = Beginning Balance − Principal.

Annual payment in E3: =ABS(PMT(C3,D3,-B3)). Year 1 beginning = bond amount. Year 2+ beginning = prior ending (=F6 dragged down). Interest: =B6*$C$3. Principal: =C6-D6. Ending: =B6-E6.

Task 3 Verification

Paste three values from your Debt_Service sheet:

Annual Debt Service (E3)

Year 1 Interest (D6)

Year 20 Ending Balance (F25)

Schedule Preview (Years 1–5 and 18–20)

🔑 Answer Key — Task 3: Debt Service Schedule (selected years)

Year	Beginning Balance	Annual Payment	Interest	Principal	Ending Balance
2025 (Yr 1)	$505,000,000	$38,822,453	$22,725,000	$16,097,453	$488,902,547
2026 (Yr 2)	$488,902,547	$38,822,453	$22,000,615	$16,821,838	$472,080,709
2027 (Yr 3)	$472,080,709	$38,822,453	$21,243,632	$17,578,821	$454,501,888
2029 (Yr 5)	$436,132,020	$38,822,453	$19,625,941	$19,196,512	$416,935,508
2034 (Yr 10)	$307,191,129	$38,822,453	$13,823,601	$24,998,852	$282,192,277
2042 (Yr 18)	$106,721,539	$38,822,453	$4,802,469	$34,019,984	$72,701,556
2043 (Yr 19)	$72,701,556	$38,822,453	$3,271,570	$35,550,883	$37,150,673
2044 (Yr 20)	$37,150,673	$38,822,453	$1,671,780	$37,150,673	$0

Total debt service over 20 years: $776,449,060 • Total interest paid: $271,449,060 • Total principal: $505,000,000

Task 4

Operating Budget Impact

Pending

Bond debt service is not a capital expense — it is an operating expense paid from the authority's annual operating budget. Understanding how debt service grows relative to the operating budget is critical for long-range fiscal health. In this task you model the debt service burden over the first five years of the CIP.

Key Parameters for Task 4

FY2025 Operating Budget: $1,740,000,000 (CTA FY2025 approved budget)
Annual Budget Growth Rate: 3.0% per year (driven by labor contracts, fuel, and maintenance inflation)
Annual Debt Service: $38,822,453 (from Task 3 — fixed for the full 20-year term)

Formula K — Operating Budget (Year-Over-Year Growth)

Operating Budget (next year) = Prior Year Budget × (1 + Growth Rate)

=B4*1.03 in C4, then drag right through F4 (FY2026–FY2029)

FY2025 operating budget ($1,740,000,000) is given in cell B4.

Formula L — Debt Service as % of Operating Budget

DS Ratio (%) = Annual Debt Service ÷ Operating Budget × 100

=B5/B4*100 — drag right through F6 (express as a percentage)

As the operating budget grows at 3% per year while debt service stays flat, the ratio will decrease. Bond rating agencies watch this ratio closely.

Step-by-Step

Open the Operating_Impact sheet. FY2025 operating budget ($1,740,000,000) is given in B4. Annual debt service ($38,822,453) is given in B5 through F5.

In C4, enter =B4*1.03. Drag right through F4 to calculate FY2026–FY2029 operating budgets.

In B6, enter =B5/B4*100 to calculate the debt service ratio as a percentage. Drag right through F6.

Observe the trend: the FY2025 ratio should be approximately 2.23%, declining to 1.98% by FY2029 as the operating budget grows.

Operating Budget vs. Debt Service Ratio — FY2025 to FY2029

Hints

The operating budget grows each year while debt service stays constant. A flat numerator and growing denominator means the ratio will decline over time — a favorable trend for financial health.

Apply 3% compound growth year-over-year starting from the FY2025 base. The ratio is simply annual debt service divided by that year's operating budget, expressed as a percentage.

FY2026 budget: =B4*1.03 dragged right. DS ratio: =B5/B4*100 dragged right. FY2025 ≈ 2.23%, declining to ≈ 1.98% by FY2029.

Task 4 Verification

Paste three values from your Operating_Impact sheet:

FY2026 Operating Budget (C4)

FY2025 DS Ratio % (B6)

FY2029 DS Ratio % (F6)

🔑 Answer Key — Task 4: Operating Budget Impact

	FY2025	FY2026	FY2027	FY2028	FY2029
Operating Budget	$1,740,000,000	$1,792,200,000	$1,845,966,000	$1,901,344,980	$1,958,385,329
Annual Debt Service	$38,822,453	$38,822,453	$38,822,453	$38,822,453	$38,822,453
DS as % of Operating	2.23%	2.17%	2.10%	2.04%	1.98%

Discussion

Analysis & Policy Questions

Use your completed spreadsheet and the data from the four tasks to address the following questions. These are designed for written response or in-class discussion.

In FY2025, the federal leverage ratio is 8.55x — meaning the authority attracts $8.55 in federal dollars for every $1 of local bond financing. In FY2026 and later years without discretionary grants, the ratio falls to approximately 1.0x.

Questions to consider:

What accounts for this dramatic variation in leverage across years?
If a large discretionary grant is awarded in Year 1 but the project takes three years to build, how does grant timing affect cash flow and fund balance management?
What risks does an authority face if it builds a capital plan that depends heavily on competitive federal grants that may or may not be awarded?
How should a capital plan treat uncertain discretionary grants — as committed funding or as a contingency?

The authority issued 20-year bonds to finance a mix of assets with very different lifespans:

Railcars: 30–40 year useful life
Track & Infrastructure: 30–50 years
Bus Fleet: 12–15 years
Technology & Systems: 5–10 years

Questions to consider:

For which projects does a 20-year bond term make sense? For which is it too long or too short?
What problems arise when you finance a 10-year asset with a 20-year bond? What about financing a 40-year asset with a 10-year bond?
How might rating agencies or bond covenants constrain the term of financing for short-lived assets?

The debt service-to-operating-budget ratio ranges from 2.23% in FY2025 to 1.98% in FY2029. Transit bond rating agencies typically flag affordability concerns when this ratio exceeds 10–15%.

Questions to consider:

On its face, the CTA's position appears comfortable. What assumptions in your model are most critical to this conclusion?
The CTA already carries significant legacy debt from prior capital programs. How would you incorporate existing debt service obligations into this analysis?
If the operating budget growth rate slowed from 3% to 1% (e.g., due to a recession), how would the DS ratio change by FY2029? Recalculate and compare.
What non-financial factors (political pressure, labor contracts, federal compliance requirements) might constrain the authority's ability to manage its debt burden?

Pennsylvania's Act 89 capital assistance is funded partly by liquid fuels tax revenues, which have been declining as electric vehicles displace gasoline-powered cars.

Scenario: Suppose Act 89 capital assistance is reduced from 20% to 15% of annual capital needs for FY2027–FY2029.

How much additional local bond financing would be required for each affected year?
What would the new total bond amount be? Recalculate the annual debt service using the PMT function.
How much additional interest would the authority pay over the full 20-year life of the expanded bond issue?
At what point does a state funding cut become a structural threat to capital program delivery?

Major capital projects — new railcars on the Market-Frankford Line, trolley modernization — primarily benefit riders on specific corridors. Yet bond debt service is repaid from system-wide revenues: fares paid by all riders, state subsidies, and local government contributions.

Questions to consider:

Is it equitable for riders on unaffected routes to help pay for improvements they will not directly use?
How does the geographic and demographic distribution of the CTA's ridership affect the equity implications of this capital plan?
What alternatives exist — dedicated revenue bonds, TIF districts, special assessment districts — that might better align who pays with who benefits?
How should public transit agencies balance system-wide equity goals with the need to invest heavily in the most-used (and often most-underserved) corridors?