For the excel assignment, I would like for you to complete two amortization tabl
ID: 2789580 • Letter: F
Question
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 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.
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 correctlyExplanation / Answer
Please provide feedback.... Thanks in Advance.. :-)
Particulars Calculations Amount No. of payments (10 x 2) 20 Principal Amount 10000000 Stated Rate of Interest (8/2) 4% Market Rate of Interest (6/2) 3% Semi annual payments =D5*D4 400000 Present Value of Coupons on Bond -PV(C6,C3,C7,C4) $ 1,14,87,747.49 Beginning Book Value Interest Expense Payment Reduction in Principle Ending book value 11487747.49 344632.4246 400000 55367.57542 11432379.91 11432379.91 342971.3973 400000 57028.60268 11375351.31 11375351.31 341260.5392 400000 58739.46076 11316611.85 11316611.85 339498.3554 400000 60501.64458 11256110.2 11256110.2 337683.3061 400000 62316.69392 11193793.51 11193793.51 335813.8053 400000 64186.19474 11129607.31 11129607.31 333888.2194 400000 66111.78058 11063495.53 11063495.53 331904.866 400000 68095.134 10995400.4 10995400.4 329862.012 400000 70137.98802 10925262.41 10925262.41 327757.8723 400000 72242.12766 10853020.28 10853020.28 325590.6085 400000 74409.39149 10778610.89 10778610.89 323358.3268 400000 76641.67323 10701969.22 10701969.22 321059.0766 400000 78940.92343 10623028.3 10623028.3 318690.8489 400000 81309.15113 10541719.14 10541719.14 316251.5743 400000 83748.42567 10457970.72 10457970.72 313739.1216 400000 86260.87844 10371709.84 10371709.84 311151.2952 400000 88848.70479 10282861.14 10282861.14 308485.8341 400000 91514.16594 10191346.97 10191346.97 305740.4091 400000 94259.59091 10097087.38 10097087.38 302912.6214 400000 97087.37864 10000000 Formula =A29*3% =C26 =C29-B29 =A29-D29