Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

Debt Financing and Management for Public Organizations - Final Exam - Fall 2024

We will be recreating a fictional debt issuance entitled “New Bond Series A” that was structured with the following parameters:

· Dated and Delivery Date of October 7, 2021

· First interest payment January 1, 2022

· The bonds maturing after January 1, 2031 are subject to optional redemption on or after January 1, 2031, at a Redemption Price equal to 100% of the principal amount thereof

· Total par amount of $502,000,000 in the following maturities

· Sinking Fund schedules for each term bond are as follows:

Instructions: Submit one single Excel File with your full name in the title that answers the following questions. If I cannot find your answers, I cannot award you credit. In order to receive ANY credit, you must show the full formula for each calculation, with the Excel formulas visible and linked to individual cell inputs – zero points will be awarded to answers in the form of hard coded numbers.

Because the grading is based on your calculations, I have listed hints throughout the question to provide the proper answer. These hints should be used to check your work as you progress through the exam to  avoid compounding the impact of errors from the first few questions.

To stay as organized as possible and save time, you may wish to use the Excel Template posted to the Course Website from Class 12 (File name: “IN CLASS 12 Excel Exercises”) as a base for your exam response spreadsheet. Ideally your response file will have 4 separate tabs in your Excel response file: Questions #1-5 should be located on the first tab; Questions #6-9 should be placed on a separate, second tab; Question #10 should be a separate third tab; and the Bonus Question should be a separate tab fourth tab.

Final Exam Questions:

1. Debt Service: Calculate the semimanual interest and principal payments in each period until the final bond maturity. Show one column in your Excel spreadsheet for semiannual interest payments and one column for principal payments in each period plus one column for total debt service for each period. Hint: Total Debt Service should be $1,103,695,760.83

2. Bond Price: Calculate the price of each bond maturity. Display the prices in a separate column in your Excel exam response spreadsheet as a whole number with three decimal places, i.e. 101.245 Reminder: take into consideration the call features of each bond and its status as a premium, par or discount bond. Also recall that the price function is truncated.

3. Present Value Debt Service: Calculate the Present Value of Debt Service in each semiannual period. Show the present value debt service payments in a separate column in your Excel exam response spreadsheet. Reminder: do NOT use the EXCEL formula for Net Present Value or the answer will be incorrect given the partial periods. Hint: Total Present Value Debt Service with the discount rate set to 5% should be $513,351,099.68.

4. Premium & Discount: Calculate the total bond proceeds produced by each bond maturity, creating a separate column to isolate the premium or discount generated by each bond. Hint: Total Premium should be $7,059,746.95.

5. Cost of Funds: Calculate the blended bond yield (Total Interest Cost, “TIC” in %) that represents the Issuer’s cost of funds using the Excel goal seek function to equate Total Present Value Debt Service to Total Production by adjusting the Discount Rate. Display the TIC as a % rounded to 6 decimal places and make sure the subtotal of your Present Value Debt Service column reflects the Total Production (demonstrating the goal seek was successful). Hint: Total Production is $509,059,746.95

6. Graphing Debt Service: Create a separate tab in your Excel exam response spreadsheet that summarizes the debt service for New Bond Series A in annual periods. Display a column for total annual interest and a column for annual principal payments. Also show a column of subtotaled annual debt service. Graph the Annual Principal and Annual Interest payments for New Bond Series A in each year, from the year of the first interest payment through the final maturity of the New Bond Series A. Create a stacked bar graph of the annual debt service with a blue color representing principal payments and an orange color representing interest payments. Note: I expect the graph to have stacked bars from 2022 through 2051. For your graph to receive full credit, you must have a title, labeled axes and a labeled legend.

7. Interpreting Debt Structuring Strategy: Based on the patterns in the annual debt service graph for New Bond Series A, what amortization style do you believe the Issuer was targeting: level annual debt service, equal principal payments, accelerated or deferred amortization? (Choose the one style that most resembles the New Bond Series A principal amortization)

8. Graphing Portfolio Debt Service Requirements: Create a second stacked bar graph that depicts the annual debt service requirements for New Bond Series A in an orange color and the annual debt service on the Issuer’s other outstanding indebtedness in a blue color, starting in the year 2022 through the final maturity of any bond. Assume the schedule of annual debt service on the Issuer’s other outstanding bonds is as shown in the table below. Hint: if your calculations are correct the subtotal of all Other Outstanding Debt Service Requirements (excluding New Bond Series A) is $3,896,851,568.  Note: for your graph to receive full credit, you must have a title, labeled axes and a labeled legend.

9. Interpreting Debt Portfolio Management Strategy: Based on the patterns in the annual debt service requirements graph, study how the New Bond Series A was structured in the context of other outstanding debt service requirements (prior bonds). What debt portfolio management approach do you believe the Issuer was targeting when they designed the New Bond Series A: uniform, fill or wrapped/bullets? (Choose the one style that most resembles the New Bond Series A amortization)

10. Secondary Market Trading: If someone purchased $1 million of the New Bond Series A 2042 maturity in the secondary market with a settlement date of November 12, 2024, what would be the accrued interest at the time of the trade? For additional clarity, this is a $1M portion of the $29.525M bond maturing on January 1, 2042 that is being traded between two investors in the secondary market. We are looking for the $ amount of accrued interest that would be specified as part of the trade transaction (buyer to compensate the seller for interest accrued through the settlement date). Hint: Look at Example #2 on slide 5 from Class #2 (and the Excel files we used in class). If your calculations are correct, the answer is $18,194.44. 

Bonus Question (+3 points) Average Life: In the Excel file from Class 12 (File name: “IN CLASS 12 Excel Exercises”), in the tab “Average Life”, investigate how bond maturities are converted to “Bond Years” in $ by multiplying the principal payments in $ x the years to maturity (on a 30/360 basis) to arrive at “Bond Years ($)”. Then the Average Life statistic is calculated as Total Bond Years divided by Total Principal. Show your calculation for the Average Life (in years) for the New Bond Series A. Hint: if your calculations are correct, the answer is 23.160 years.