Question
Bond Payoff Analysis Spreadsheet and Recommendation Report The auditor is in the middle of his FY2015 audit and has suggested looking into paying off the
Bond Payoff Analysis Spreadsheet and Recommendation Report
The auditor is in the middle of his FY2015 audit and has suggested looking into paying off the bond early to save some interest. The Fire District has $1,500,000 set aside for purchase of future fixed assets. Another $500,000 is available for paying bills and payroll through Dec 1, 2015. Revenue coming in from ambulance service and monthly property tax is approximately $50,000 a month. Another $300,000 is also in a checking account at a large regional bank. The main property tax payment for the year comes from the county around January 10th, and is around $1,200,000 which is used mostly for meeting payroll and bills for that calendar year.
The District keeps its cash according to the Utah Money Management Council Rules from the Utah Treasurers Office. It keeps the cash in the PTIF account. Currently the PTIF account is earning an annual interest rate of 0.5971% (.005971) annually (365 day rate).
The bond payment schedule is as follows:
North View Fire District | ||
Bank of Utah Bond Payment and Balance Schedule | ||
FY 6/30/2015 | ||
Interest Rate | 4.90% | |
Date | Principle Pmt | Principle Bal |
12/1/2014 | 813000 | |
12/1/2015 | 90000 | 723000 |
|
|
|
12/1/2016 | 94000 | 629000 |
|
|
|
12/1/2017 | 99000 | 530000 |
|
|
|
12/1/2018 | 103000 | 427000 |
|
|
|
12/1/2019 | 108000 | 319000 |
|
|
|
12/1/2020 | 115000 | 204000 |
|
|
|
12/1/2021 | 204000 | 0 |
|
| |
Totals | 813000 |
Assignment:
Assume the date is Sep 1, 2015 and you have been asked to analyze and prepare a report to the Fire District Board in their November board meeting whether you recommend that the Fire District pay off the bond or not.
Complete the amortization schedule for each principle/interest payment. Interest & principle are due Dec 1 of each year.
Prepare journal entries for each payment transaction and the year end adjustment to accrue interest each FY end at June 30. The interest accrual as of 6/30/2015 has been made and there was a reversing entry recorded related to that accrual on 7/1/2015. (For your journal entries, you can choose whether to use reversing entries on July 1 or not).
You are to complete your analysis of whether to pay off the bond early at Dec 1, 2015 and how much interest would be saved if you paid off the bond at that point in time. Remember if you pay it off early, you have an opportunity cost of the interest that cash would have earned in the PTIF account. Use Present value, future value, or net present value principles and excel formulas. You are to also use at least two "what if" scenarios assuming two different Projected PTIF interest rates.
Write a recommendation to the fire board whether to pay off at Dec 1, 2015, and show your financial analysis to support your recommendation. Include in your report risks of using the cash for payoff. Hand in your report in a pdf file that includes the financial analysis. Also hand in your final Excel Bond payoff analysis spreadsheet. If you decide another timing of payoff other than Dec 1, 2015 payoff, be sure to support your recommendation with appropriate financial analysis for your projected payoff.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started