Question

You are selling $20,000,000 of $1,000, 10 year bonds with a stated rate of 4.7% interest. The bonds pay interest semi-annually starting at the end of the first period. The bond principal will be paid back at the end of the term. You will be using Excel and Excel Functions to make a model to find the "pricing" of the bonds at the date of sale (7/30/2020) when the market rate of interest was 5% and an amortization table assuming for all 10 years of the bond term. Many of your answers will be calculated using Excel Functions and/or cell references. Be sure to follow the instructions carefully and do not type in amounts when the instructions require otherwise.

Entering Variable Labels:

           

           Starting in cell A3 and ending in cell A8 enter the following variable labels

Bond Face Amount

Stated Rate of Interest (Annual)

Periodic Cash Interest Amount

Market Rate of Interest (Annual)

Term (Years)

Number of Periods

Entering/computing Variable amounts:

Starting in Cell B3 and ending in Cell B8 enter the appropriate variable amount. The Periodic Cash Interest Amount and Number of Periods should be computed using excel formulas and cell references. Remember to compute the periodic interest rate within the excel formula/function,

Entering Present Value (PV) Labels:

Starting in cell A10 and ending in cell A12 enter the following present value labels

PV of Interest Payments

PV of Principle Repayment

Pricing of Bonds**

** a.k.a "the proceeds from selling the bonds"

Compute the Present Value amounts and the total Pricing of the Bonds on 7/31/20:

Using the PV function, determine the present value of all bond cash interest payments in cell B10

Using the PV function, determine the present value of the bond principle repayment in cell B11

Using an Excel formula, determine the total pricing of the bonds in cell B12

Prepair an Amortization Table for the Bond Offering

Entering Column Headings for the Amortization Table:

Starting in cell A18 and ending in cell E18 enter the following column headings/titles. You may use Format Cells/Alignment/Wrap Text to keep your column widths reasonable. Alternatively, you may use rows 17 and 18, same cells A-E, to enter your column headings using multiple cells.

Date

Cash Interest Amount

Effective Interest Amount

Increase/(Decrease) in Outstanding Balance

Outstanding Debt Balance

Completing the Amortization Table

Starting in cell A19 thru A39 input the dates of each period...start with 7/31/2020

Enter the amount of the Outstanding Debt Balance as of 7/31/20 in cell E19

Starting in Cell B20 and ending in B39, reference the Cash Interest payment made every period. The cell reference in B20 should be an "absolute reference" (see discussion below) that can then be copied into cells B21 thru B39.

The $ is used in a cell reference to make an absolute reference. An absolute reference is one that will not change when the formula is copied.

There are many variations of absolute references:

·        $A$1 will always point to column A and row 1

Starting in Cell C20 and ending in C39, compute, using an Excel formula, the Effective Interest amount for every period. Remember to compute the periodic interest rate within the excel formula/function.   The cell reference in C20 (for the market rate, only) should be an "absolute reference" (see discussion above) that can then be copied into cells C21 thru C39.

Starting in Cell D20 and ending in D39, compute, using an Excel formula, the outstanding debt balance amount for every period.

Starting in Cell E20 and ending in E39, compute, using an Excel formula, the outstanding debt balance increase/(decrease) amount for ending date of every period.

Answer 1

Formulas used-