Budget problems

profiletyger06
BudgetTools2eModule20LongTermSelfChecking2014.xlsx
Home>Business & Finance homework help>Accounting homework help>Budget problems

Questions

Type your name here, Last Name First
20.2. A major urban center is planning to issue a $100 million, 20-year, semiannual-interest-paying municipal bond for the construction of a stadium.
·         The interest rate is 5.875%, based on the economic and financial conditions of the city and city government.
·         The design and issuance costs are estimated to be $10 million and 1%, respectively.
·         What is the total interest paid if the city decides to adopt a level debt service structure?
·         How much will the city still owe on this bond at the end of each year?
Project Cost Components and Other Key Assumptions
Acquistion or construction cost 100,000,000
Design cost 10,000,000
Subtotal 110,000,000
Costs of issuance @ 1% 1,100,000
Bond size 111,100,000
Interest rate 5.875%
Term of bonds 20
x
Type your name here, Last Name First
Please enter your name on the first page

Exercise 20.2

To show the cell checking, put a check here: x Percent Correct 0%
Please enter your name on the first page Grade in Points (All Sheets) 0.00
Level Debt
Principal amount 111,100,000
Interest rate 5.875%
Term in years 20
Structure Level debt service
Year Principal outstanding Interest Principal repaid Total semi-annual service Total annual debt service Check Grade
0.5 111,100,000 0 0
1 0 0 0 0
1.5 0 0 0
2 0 0 0 0
2.5 0 0 0
3 0 0 0 0
3.5 0 0 0
4 0 0 0 0
4.5 0 0 0
5 0 0 0 0
5.5 0 0 0
6 0 0 0 0
6.5 0 0 0
7 0 0 0 0
7.5 0 0 0
8 0 0 0 0
8.5 0 0 0
9 0 0 0 0
9.5 0 0 0
10 0 0 0 0
10.5 0 0 0
11 0 0 0 0
11.5 0 0 0
12 0 0 0 0
12.5 0 0 0
13 0 0 0 0
13.5 0 0 0
14 0 0 0 0
14.5 0 0 0
15 0 0 0 0
15.5 0 0 0
16 0 0 0 0
16.5 0 0 0
17 0 0 0 0
17.5 0 0 0
18 0 0 0 0
18.5 0 0 0
19 0 0 0 0
19.5 0 0 0
20 0 0 0 0
Total 0 0 0 0
0
143

Answer

Answer
Level Debt
Principal amount 111,100,000
Interest rate 5.875%
Term in years 20
Structure Level debt service
Year Principal outstanding Interest Principal repaid Total semi-annual service Total annual debt service
0.5 111,100,000 3,263,563 3,263,563
1 3,263,563 3,061,020 6,324,582 9,588,145
1.5 108,038,980 3,173,645 3,173,645
2 3,173,645 3,240,854 6,414,500 9,588,145
2.5 104,798,126 3,078,445 3,078,445
3 3,078,445 3,431,255 6,509,700 9,588,145
3.5 101,366,871 2,977,652 2,977,652
4 2,977,652 3,632,841 6,610,493 9,588,145
4.5 97,734,030 2,870,937 2,870,937
5 2,870,937 3,846,270 6,717,207 9,588,145
5.5 93,887,760 2,757,953 2,757,953
6 2,757,953 4,072,239 6,830,192 9,588,145
6.5 89,815,521 2,638,331 2,638,331
7 2,638,331 4,311,483 6,949,814 9,588,145
7.5 85,504,039 2,511,681 2,511,681
8 2,511,681 4,564,782 7,076,463 9,588,145
8.5 80,939,256 2,377,591 2,377,591
9 2,377,591 4,832,963 7,210,554 9,588,145
9.5 76,106,293 2,235,622 2,235,622
10 2,235,622 5,116,900 7,352,522 9,588,145
10.5 70,989,393 2,085,313 2,085,313
11 2,085,313 5,417,518 7,502,831 9,588,145
11.5 65,571,876 1,926,174 1,926,174
12 1,926,174 5,735,797 7,661,971 9,588,145
12.5 59,836,079 1,757,685 1,757,685
13 1,757,685 6,072,775 7,830,460 9,588,145
13.5 53,763,304 1,579,297 1,579,297
14 1,579,297 6,429,550 8,008,848 9,588,145
14.5 47,333,753 1,390,429 1,390,429
15 1,390,429 6,807,287 8,197,716 9,588,145
15.5 40,526,467 1,190,465 1,190,465
16 1,190,465 7,207,215 8,397,680 9,588,145
16.5 33,319,252 978,753 978,753
17 978,753 7,630,639 8,609,392 9,588,145
17.5 25,688,613 754,603 754,603
18 754,603 8,078,939 8,833,542 9,588,145
18.5 17,609,675 517,284 517,284
19 517,284 8,553,576 9,070,860 9,588,145
19.5 9,056,099 266,023 266,023
20 266,023 9,056,099 9,322,122 9,588,145
Total 80,662,892 111,100,000 191,762,892 191,762,892
Note:
The answer to questions 3 displays semiannual interest payments for a level debt structure.
Every six months, an interest-only payment is made. At the end of each year, a payment of interest and principal is due.
The calculations still key off the PMT calculation, which is used to determine total annual debt service.
Interest expense for each period is calculated by multiplying the face value of the bond by the annual interest rate divided by two.
This approach is typical in TVM problems, where it is important to match the interest rate with the corresponding compounding or payment period.
The annual principal payment is calculated by subtracting the two interest payments from the total annual debt service calculated by PMT.
In a typical bond cash flow, the periods would be identified with actual dates, even if those dates might be subject to change in the future.

Grade Worksheet

2.5 Total Points Available Item Grade Weight x A mark here makes the numbers in the grade calculation visible.
0.25 Base Points (for trying this exercise)
0 Manual Points
2.25 Points to be graded
0% Percent all sheets
0 Graded Points
If these cells are blank, there are no cells requiring manual grading
0.25 Initial Grade 0
0.25 Minimum Earned Points
0.5 Threshold
0 Grade Grade Table
A 100%
B 90%
C 80%
D 70%
F 0%