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:

(a) 10 year term

(b) bond payments happen semi-annually

(c) Principal amount of bond is 10,000,000;

(d) Stated rate on the bond is 8%

(e) 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.

(a) Number of payments

(b) Principal amount

(c) Stated Rate of Interest

(d) Market rate of interest

(e) 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

There should be nothing in the worksheet except formulas which will reference back to one of the calculations or inputs in rows 1-6. For example, your calculation of interest expense in the table will be a formula that references back to the cell address where you entered principal amount and the relevant interest rate. Using an absolute reference will make this easier for some of the work. Look that up in the help function if you dont know what that means.

The entire worksheet should update accurately using the new values input in rows 1-5. Thus, the worksheet should work for any value in rows 1-5.

Your last row, last column should equal to zero if you have done the table correctly.

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 assume the following for the bond: a. b. c. d. e. 10 year term bond payments happen semi-annually Principal amount of bond is 10,000,000; Stated rate on the bond is 8% 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) Hint: The sixth line of the worksheet should have calculation of the present value of the bond. 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 There should be nothing in the worksheet except formulas which will reference back to one of the calculations or inputs in rows 1-6. For example, your calculation of interest expense in the table will be a formula that references back to the cell address where you entered principal amount and the relevant interest rate. Using an "absolute reference" will make this easier for some of the work. Look that up in the help function if you don't know what that means. The entire worksheet should update accurately using the new values input in rows 1-5. Thus, the worksheet should work for any value in rows 1-5 Your last row, last column should equal to zero if you have done the table correctly