Assignment
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. 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.