For the excel assignment, I would like for you to complete two amortization tables.

Follow these instructions (meant to make sure you can use excel).

Create each table on a separate worksheet (not file).

Name the worksheets Discount and Premium

assume the following for the bond:

1. 1. 10 year term
   2. bond payments happen semi-annually
   3. Principal amount of bond is 10,000,000;
   4. Stated rate on the bond is 8%
   5. Market rate of interest is 6% for one table and 10 % for the other (name the worksheets appropriately) Note that the two worksheets will have identical formulas if you use excel to its full power.

The first 5 lines of the worksheet should have the following information with labels in column A and the numerical value (or formula to calculate the numerical value in column B.

Number of payments

Principal amount

Stated Rate of Interest

Market rate of interest

Semi-annual payment (use a formula to calculate this referencing other cells)

The sixth line of the worksheet should have calculation of the present value of the bond. Hint: use the PV function

Lines 8-27 should include the amortization table. The table shall contain 5 columns.

Col 1: Beginning Book Value

Col 2: Interest Expense

Col 3: Cash flow or payment

Col 4: Reduction in Principal

Col 5: Ending book value