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.