On January 1, 2019, The company you work for sold 6% bonds having a maturity value of $1,000,000 and a 3% yield (market rate). The bonds are dated January 1, 2019, and mature January 1, 2024, with interest payable June 30 and December 31 of each year. Your company allocates interest and unamortized discount or premium on the effective-interest basis.

You are trying to explain the cash flow, interest and liability impacts of the bond issue to your CEO on what this bond issue means to the business and financial statements.

As an intern in the Accounting department, it is your responsibility to explain this bond issue to the CEO. Present an amortization schedule that details out the cash flows, interest expense and carrying amount of the bond issue throughout its life.

Prepare an interest amortization schedule in Excel (in good format as you are going to share this with the CEO) for the bond issue detailed above. The amortization schedule should cover the full 5-year bond issue and show the cash impact, interest expense and carrying value of the bond for each period.

For your CEO, you are required to

1. Work out an excel worksheet showing the value of the bond at the date of issuance using the PV function in Excel (make sure to use formulas).
2. Amortization table, in good deliverable form for the full life of the bond. Use formulas to calculate each of the values in the amortization table (do not calculate using present value factors and hard-code into Excel)
3. Journal entries your company would record at 1) the date of issuance 2) the first interest payment date and 3) the maturity date.
4. Calculation of the total cost (expense) of issuing the bond and the total cash impact to your company over the life of the bond (ex: net ALL of the cash flows over the life of the bond).