- Decreasing Debt 1 800 000 00 1 600 000 00 1 400 000 00 1 200 000 00 1 000 000 00 800 000 00 600 000 00 400 000 0 1 (258.35 KiB) Viewed 33 times
1. As the budget analyst, how much should this local
government
budget for debt service in the first year? How much in the
10th
year? In the 20th year?
2. Assume that growth in the tax base increases to an average
of
2.5% annually. How much should the local government budget
for
debt service in the first year? In the 10th year? In the 20th
year?
How much more debt can be issued in year 10? In year 20? For
all
20 years?
3. Returning the annual tax base growth to 2%, assume that
the
coupon interest rates increase by 1% (.01). How much does the
interest cost increase? The average interest rate? The true
interest cost?
4. The Council has asked how much in total interest would be
due
if the par amount increased to $22 million. How does the ratio
of
I&S to the total tax rate change if this new amount of debt is
sold?
Is it within the recommended cap? How much in total new debt
is
now possible with this new par amount? How do you explain to
the council the meaning of the net present value of this
amount?
Decreasing Debt $1,800,000.00 $1,600,000.00 $1,400,000.00 $1,200,000.00 $1,000,000.00 $800,000.00 $600,000.00 $400,000.00 $200,000.00 $0.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 Principal Interest New Debt Service Decreasing Debt $2,500,000.00 $2,000,000.00 $1,500,000.00 $1,000,000.00 $500,000.00 $0.00 3 18 19 20 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Principal Interest New Debt Service
AutoSave OFF @ Excel #5 Debt Service Using Constant Principal -- EXCEL WORKSHEET (3) Home Insert Draw Page Layout Formulas Data Review View Developer Tell me Share 0 Comments X 9 Calibri (Body) ~Å V Insert v = = ab Wrap Text Σν Currency V Do 國留通 WE 48- 0 Ow @ DX Delete Paste B I U A 三三三 = = 9 Merge & Center v $ % ) .00 00 0 V Sensitivity Conditional Format Cell Formatting as Table Styles Sort & Filter Analyze Data Format Find & Select B6 A x ✓ fx A B с D м N O P a R 5 T U v w X Y Z AA АА. AB AC AD E H 1 DECREASING DEBT MODEL THAT ALLOWS FOR FUTURE CAPACITY 1 1 YEAR P PRINCIPAL 1 1,000,000.00 2 1,000,000.00 3 1,000,000.00 4 1,000,000.00 Sl 1,000,000.00 6 1,000,000.00 7 1,000,000.00 8 1,000,000.00 9 1,000,000.00 10 1,000,000.00 11 1,000,000.00 12 1,000,000.00 13 1,000,000.00 14 1,000,000.00 15 1,000,000.00 16 1,000,000.00 17| 1,000,000.00 18 1,000,000.00 19 1,000,000.00 20 1,000,000.00 20,000,000.00 COUPON 26337% % 2.6337% 2.6337% 2.6337% 2.6337 2.6337% 2.6337% 2.6337% 2.6337% 26337% 2.6337% 2.6337% 2.6337 2.6337% 2.6337% 2.6337% 26337% 2.6337% 2.6337 % 2.6337% PH INTEREST DEBT SERVICE 526,733 33 1,526,733.33 500,396.67 1.500,396.67 474,000.00 1,474,000.00 447,723 33 1.447.722 33 421, 386.67 1.421,386,67 395,050.00 1195.050.00 368,713 33 1,368,713 33 342.376.67 1342,376.67 316,040.00 1,316,040.00 289,703 33 1,289,703 33 263,366.67 1,263,366.67 237,030.00 1,237,030.00 210,693 33 1.210.693 33 184, 356,67 1,184,356.67 158.020.00 1,158,020.00 131,68333 1,131,683.33 105,346.67 1,105,346.67 79,010.00 1,079,010.00 52,673.33 1,052,672.33 26.336.67 1,026,336 67 5,530,700.00 25.530,700.00 TRUE INTEREST 3 XTAX BASE KEY ASSUMPTIONS & METRICS PH COST TAX BASE I&S RATE AVAILABLE 4 YEAR PRINCIPAL COUPON INTEREST DEBT SERVICE $20.000.000,00 GROWTH NEEDED OG FROMY STREAM 5 AOND PAA AMOUNT $20,000,000 Impur 1 $1,000,000.00 0.18% $418.600.00 $1,418,600.00 1,418,600.00 $600,000,000 $0.2364 $0.0000 Op M. Z $1,000,000.00 0.32% $416,800.00 $1,416,800.00 1,416,800.00 $612,000,000 $0.2315 $0.0049 $30, 172 7 TEAM IN YEARS : 20 GIVEN 3 $1,000,000.00 0.56% $413,500.00 $1,413,600.00 -1,413,600.00 $624,240,000 $0 2265 $0.0100 0 $62,311 8 8 4 $1,000,000.00 0.84% $408.000.00 $1.400.000 1,408,000.00 $636,724 800 $0.2211 $0.0153 $97, 430 9 ANNUAL MANCIPAL: $1,000,000 CALC 5 $1,000,000.00 11496 $399,600.00 $1,399,600.00 1,399,500.00 $649,459,295 $0.2155 $0.0209 $135,938 10 6 $1,000,000.00 $ 1.42% $388,200.00 $1,388,200.00 $ 1,388,200.00 $662,448,482 $0 2096 $0.0269 $178,049 11 CUWENT TAXAASE Sco,000,000 INPUT 7 $1,000,000.00 1.65% $374,000.00 $1,374,000.00 1,374,000.00 $675,697,452 $0.2033 $0.0331 $223,574 12 GADWTW MTE 2. Mur 8 $1,000,000.00 193% $357,500.00 $1,357 500.00 1357 500 00 09 211 401 $0.1970 $0.0395 $272,025 $ 13 9 $1,000,000.00 2.14% $338,200.00 $1,338,200.00 1,338,200.00 $702,995,629 $0 1901 $0.0461 $323,916 14 OEMTAXMTE AA100 Sa. 1000 GIVEN 10 $1.000.000 2 28% $316,800.00 $1.316.800 1,316,800.00 $717,055,543 $0.1836 $0.0528 $ $378,558 15 11 $1,000,000.00 2.42% $294.000.00 $1.294,000.00 -1,294,000.00 $731, 396,652 $0.1769 $0.0595 $435,265 16 MANMUMIEMTE $0.236 CALC 25 12 $1,000,000.00 2 56% $269,800.00 $1,269,800.00 1.269.800.00 5746.024 S $0.1702 $0.056. $494,051 17 13 $1,000,000.00 2.70% $244 200.00 $1,244,200.00 1,244,200.00 $760,945,077 $0.1635 $0.0729 $554 925 18 TOTAL TAXMTE SACRI CALC 14 $1,000,000.00 282% $217,200.00 $1,217,200.00 1,217,200.00 $776, 163,97€ $0.1568 $0.0796 $617 910 19 15 $1,000,000.00 2.94% $189,000.00 $1,189,000.00 1,189,000.00 $791,687,258 $0.1502 $0.0862 $682 813 20 SIMTE MAXMTE 17.15 CALC 16 $1,000,000.00 $ 3.05% $159,600.00 $1,159,600.0 1,159,800.00 $807 521,003 $0.1436 $0.0928 $749,649 21 17 $1,000,000.00 3.14% $129,100.00 $1,129,100.00 1,129,100.00 $823,671,423 $0.1371 $0.0994 $818,334 22 AVIMGENE 10.30 CALC 18 $1.000.000.00 3.20% $97,700.00 $1,097,700.00 -1,097,700.00 $840,144,852 $0.1307 $0.1058 $888.682 23 19 $1,000,000.00 3.25% $65,700.00 $1,065,700.00 1,065,700.00 $856, 947,749 $0.1244 $0.1121 $980,410 24 AVEAGENTIATASTMTE 2.6M CALC 20 $1.000.000.00 3.31% $33,100.00 $ $1,033,100.00 1,033, 100.00 $874,085,704 $0.1182 $0.1182 $1,033, 532 $ 25 TOTAL $20,000,000.00 $5,530,700.00 $25,530,700.00 2.591856% $8,937 549 26 27 BOND YEARS $210,000,000.00 WMCIMAL AWAAS OUTSTANOWS/MODUCT) MV or BFTATAM $6,315.000 28 29 AVG LIFE 10.50 AWD-WAIDWORD AT AWNICIPAL 30 31 AVG INT RATE 2.63367% TOTALITARSTWORD STAND-TEARS 32 33 TRUE INT COST 2.591856% INTAMALMTE OVAT OBAT SERVICE 34 35 36 Decreasing Debt 37 $2,500,000.00 38 $2,000,000.00 39 40 $1.500.000,00 41 42 $1,000,000.00 43 44 $500,000.00 45 46 $0.00 47 1 2 3 4 5 6 7 9 11 12 13 14 15 16 17 19 20 10 LO 48 Principal interest New Debt Service 49 SO 51 52 Yield Curve 53 1.50% 54 1.00% 55 2.50 56 57 2.00% 58 1.50 59 LON 60 61 0.0% 0.00% 62 63 9 10 11 12 13 14 15 16 17 18 19 20 64 Yale Curve 5 Decreasing Debt L $1,600,000.00 $1,600,000.00 $1,400,000.00 $ $1,200,000.00 $1,000,000.00 SIDD, DDD.DO SEDO, 000.00 $400,000.00 sate, 0.00 SD.DE 1234567ID 12 1 3 17 19 20 14 15 16 Principalment COUPON 1.00% 2.50% 1.50 1.00% 0.00% + Decreasing 17 3 Ready @ a + 68%