Hands-On Tutorial

Budgeting for Debt Service Using Constant Principal

Based on the exercise by Lewis F. McLain, Jr.
Local Government Budgeting — Public Administration
Getting Started

Understanding Municipal Debt Service

In this exercise you will build a complete debt service schedule for a $20 million municipal serial bond issue with level (constant) principal payments. You will compute interest costs using the SUMPRODUCT concept, calculate key bond metrics, and analyze the local government's debt capacity.

Key Concepts

Debt service is often a significant part of local government budgets — as much as one-third of the property tax rate. This exercise covers:

  • Serial bonds — bonds with staggered maturities ($1M per year for 20 years)
  • Yield curve — longer maturities carry higher coupon rates due to greater risk
  • SUMPRODUCT interest — each year's interest = sum of (outstanding principal for each maturity × its coupon rate)
  • Bond-years — a factor accounting for the serial maturity structure
  • Net Interest Cost (NIC) vs True Interest Cost (TIC) — two ways to measure borrowing cost
  • I&S tax rate — the property tax rate needed to fund annual debt service

Setup Instructions

1.
Click Download Starter Spreadsheet above to get the .xlsx file with bond assumptions and the yield curve pre-filled.
2.
Open the file in Excel or Google Sheets. You'll see key assumptions in columns A–C and empty columns D–M for your work.
3.
Complete each task below, then use the Quick Check or Full Check panels to verify your answers.
Task 1

Compute Interest & Debt Service

Pending

The interest column (G) is the most complex computation in this exercise. Each year, multiple bond series are outstanding, each with its own coupon rate. You need to calculate how much total interest is owed in each year.

How SUMPRODUCT Interest Works

In Year 1, all 20 maturities are outstanding. The interest is the sum of ($1M × each maturity's coupon rate). In Year 2, only 19 maturities remain (the Year 1 bonds have matured), so you sum ($1M × remaining 19 coupon rates). And so on.

In Excel, this is expressed using SUMPRODUCT on the coupon rates for the remaining maturities.

Interest Formula (Year n)
Interest(n) = Principal × Σ(Coupon rates for years n through 20)
=SUMPRODUCT($E$5:$E$24,$F5:$F24) ← anchored top, sliding bottom
The $E$5:$E$24 range is the principal column (all $1M). The F column range starts at the current row and extends to F24 (last maturity). As you move down, fewer maturities remain.

Worked Example — Year 1 Interest

$1,000,000 × ( 0.18%+ 0.32%+ 0.56%+ … + 3.31%) = $418,600

Sum of all 20 coupon rates × $1M principal per maturity = $418,600 in Year 1

Debt Service Formula
Debt Service = Principal + Interest
=E5+G5

Steps

1.
In cell G5, enter =SUMPRODUCT($E$5:$E$24,$F5:$F24). This sums the product of each principal × its coupon rate for all 20 maturities.
2.
In cell G6, enter =SUMPRODUCT($E$6:$E$24,$F6:$F24). Notice the start row shifts down — Year 1 bonds have matured.
3.
Copy the formula down through G24. Each row automatically excludes matured bonds.
4.
In cell H5, enter =E5+G5 for debt service. Copy down through H24.
5.
In cells E25, G25, and H25, enter =SUM() formulas for the totals row.
Think of it this way: in Year 5, bonds from Years 1–4 have already matured. So you only pay interest on the remaining 16 maturities (Years 5–20). The SUMPRODUCT formula handles this automatically by shrinking the range.
The key is the absolute reference on the principal column $E$5:$E$24 and the relative reference on the coupon column. When you copy the formula from G5 to G6, the coupon range shifts from $F5:$F24 to $F6:$F24, excluding Year 1.
For Year 1 (G5): =SUMPRODUCT($E$5:$E$24,$F5:$F24) = $418,600
For Year 2 (G6): =SUMPRODUCT($E$6:$E$24,$F6:$F24) = $416,800
For Year 20 (G24): =SUMPRODUCT($E$24:$E$24,$F24:$F24) = $33,100
Total Interest (G25): =SUM(G5:G24) = $5,530,700
Verify Your Work — Task 1

Enter the values from your spreadsheet:

G5 (Yr 1 Int):
G10 (Yr 6 Int):
G25 (Total Int):
H5 (Yr 1 DS):
H25 (Total DS):
Task 2

Calculate Bond Metrics (NIC)

Pending

Now compute the summary metrics that allow budget analysts to compare bond issues: bond-years, average life, average interest rate (Net Interest Cost), and True Interest Cost.

Bond-Years Explained

Bond-years account for how long each dollar of principal is outstanding. For $1M maturing in Year 1, that's $1M × 1 = 1,000,000 bond-years. For $1M maturing in Year 20, that's $1M × 20 = 20,000,000 bond-years.

Total bond-years = SUMPRODUCT(Principal per year × Years outstanding). With $1M per year for 20 years: 1M×1 + 1M×2 + … + 1M×20 = $210,000,000.

Bond-Years
Bond-Years = SUMPRODUCT(Principal × Year Numbers)
=SUMPRODUCT(E5:E24,D5:D24)
Average Life
Average Life = Bond-Years ÷ Par Amount
=E27/B5
For level principal schedules, average life always equals (term ÷ 2) + 0.5
Average Interest Rate (NIC)
NIC = Total Interest ÷ Bond-Years
=G25/E27
True Interest Cost (TIC)
TIC = IRR of the debt service stream
=IRR(I4:I24)
The IRR column has the par amount as a positive cash inflow in I4, then negative debt service payments in I5:I24.
Bond-years is the weighted measure of how long money is borrowed. Think of SUMPRODUCT as a dot product: each principal amount multiplied by the number of years it's outstanding.
For the IRR/TIC calculation, you need a column with cash flows. Cell I4 should contain the positive par amount ($20,000,000), and cells I5:I24 should contain the negative debt service payments (-H5 through -H24). The IRR function finds the discount rate that makes NPV = 0.
Bond-Years (E27): =SUMPRODUCT(E5:E24,D5:D24) = 210,000,000
Average Life (E29): =E27/B5 = 10.5
NIC (E31): =G25/E27 = 2.6337%
TIC (E33): =IRR(I4:I24) = 2.5919%
Verify Your Work — Task 2

Enter your computed metrics:

Bond-Years:
Avg Life:
NIC (%):
TIC (%):
Task 3

I&S Tax Rate & Debt Capacity

Pending

Now analyze whether the local government can afford this debt by computing the required Interest & Sinking Fund (I&S) property tax rate and determining how much additional debt capacity exists.

Tax Rate Analysis

The tax base starts at $600 million and grows 2% annually. Each year, the I&S tax rate is computed as:

  • Tax Base = initial base × (1 + growth rate)year-1
  • I&S Rate = Debt Service ÷ Tax Base (expressed per $100)
  • The maximum I&S rate occurs in Year 1 (highest debt service, lowest tax base)
  • Debt capacity = (Max I&S Rate − Current I&S Rate) × Tax Base in each year
Tax Base Growth (Column J)
Tax Base(n) = Initial Base × (1 + Growth Rate)n-1
J5: =B11    J6: =J5*(1+$B$12)
I&S Rate Needed (Column K)
I&S Rate = Debt Service ÷ Tax Base
=H5/J5
This gives the rate per $100 of assessed value. Year 1's rate ($0.2364) is the maximum.
Change from Year 1 (Column L)
Change = Max I&S Rate − Current I&S Rate
=K$5-K6
Available Debt Capacity Stream (Column M)
Capacity = Change × Tax Base
=L6*J6
Year 1 has no capacity (L5=0). Years 2–20 show the annual capacity available for additional debt.
NPV of Debt Capacity (M27)
NPV = Net Present Value of the capacity stream at the TIC rate
=NPV(E33,M6:M24)
Discounts future capacity to present value using the True Interest Cost rate.
The I&S rate declines over time for two reasons: (1) interest payments decrease as bonds mature, and (2) the tax base grows. The gap between Year 1's rate and each subsequent year's rate represents the capacity to take on more debt without raising taxes.
For the NPV calculation, use the TIC rate as the discount rate. This converts the future stream of available capacity into today's dollars. The NPV tells the council how much additional principal could be borrowed today and serviced within the existing tax rate.
Tax Base Year 1 (J5): =B11 = $600,000,000
Tax Base Year 2 (J6): =J5*(1+$B$12) = $612,000,000
I&S Rate Year 1 (K5): =H5/J5 = 0.23643
Change Year 2 (L6): =K$5-K6 = 0.00493
Capacity Year 2 (M6): =L6*J6 = $30,172
NPV (M27): =NPV(E33,M6:M24) = $6,315,000
Verify Your Work — Task 3

Enter values from your spreadsheet:

K5 (Yr 1 I&S Rate):
K10 (Yr 6 I&S Rate):
M6 (Yr 2 Capacity):
M27 (NPV Capacity):
Task 4

Visualize the Debt Schedule

Pending

Create visualizations of the debt service schedule and yield curve to understand the structure of the bond issue. Review the charts below and recreate similar visualizations in your spreadsheet.

Yield Curve

Charting Instructions

1.
Create a stacked bar chart with Year (column D) on the x-axis, showing Principal (green) and Interest (blue) stacked to form total Debt Service.
2.
Add a line chart of the yield curve: Year on x-axis, Coupon Rate (column F) on y-axis.
3.
Optionally add the I&S tax rate (column K) or debt capacity (column M) as secondary series.
Verify Your Work — Task 4

Confirm you've created these charts in your spreadsheet:

Discussion Questions

Applying Your Analysis

1 As the budget analyst, how much should this local government budget for debt service in the first year? In the 10th year? In the 20th year? ▾
Key Points
  • Year 1 debt service: $1,418,600 (highest — all 20 maturities paying interest)
  • Year 10 debt service: $1,316,800
  • Year 20 debt service: $1,033,100 (lowest — only one maturity remaining)

The decreasing schedule means budget pressure eases over time, freeing capacity for other priorities or additional debt.

2 If tax base growth increases to 2.5% annually, how does this affect debt service, the I&S rate, and debt capacity? ▾
Interactive Calculator

Use this calculator to explore different growth rate scenarios:

Adjust the growth rate and click Calculate to see results.
Analysis

Higher tax base growth doesn't change debt service amounts (those are fixed by the bond terms), but it reduces the I&S tax rate needed each year because the same payment is spread over a larger tax base. This creates more capacity for additional borrowing.

3 If coupon rates increase by 1% (0.01), how much does the interest cost increase? How do the NIC and TIC change? ▾
Interactive Calculator
Adjust the rate increase and click Calculate to see results.
Key Insight

A 1% increase in all coupon rates increases total interest significantly because it applies to $210 million in bond-years. The NIC rises by exactly the increase amount (since NIC = Total Interest / Bond-Years). The TIC also rises but by a slightly different amount because it accounts for the time value of money.

4 The Council wants to know the impact of increasing the par amount to $22 million. How does the I&S-to-total-tax-rate ratio change? Is it within the recommended 40% cap? ▾
Interactive Calculator
Adjust the par amount and click Calculate to see results.
Analysis Framework
  • A 70/30 O&M-to-I&S ratio is the rule of thumb for moderately growing governments
  • For rapidly growing jurisdictions, up to 60/40 may be justified
  • The I&S portion of the total tax rate should rarely exceed 40%
Reference

Answer Key

Complete debt service schedule with all computed values.

Based on the exercise by Lewis F. McLain, Jr. — Local Government Budgeting