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:

$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$)$

$$

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 don$$t know what that means.$$

$$

I will grade this by changing any value in rows $1-5.$ To get full credit, 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.