For the excel assignment, I would like for you to complete two amortization tabl
ID: 2577236 • 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.
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 correctlyExplanation / Answer
(a) Number of payments 20 (b) Principal amount 10000000 (c) Stated Rate of Interest 4% (d) Market rate of interest 3% (e) Semi-annual payment c2*3 400000 Price of the bond = c × F × (1 (1 + r)-t)/r+F(1 + r)t C=Interest Rate F= Face Value r=Market Interest Rate Price of Bond= Present value of Interest payments+Present value of the bond Price of Bond 400000*(1-(1.03)^-)/.03)+10000000/(1.03)^8 (400000*(1-.55368)/.03)+10000000*.55368 11487733 Face vale 10000000 Premium 1487733 Discount factor is taken upto 5 decimal. As the table is not provided so I have taken till 5 decimal Premium Date Beginning Book value Interest expenses at 3% Cash Flow/Payment @4% Amortization of Bond Reduction in Principle Ending book avlue Debit Interest Expense Credit cash Bond Premium 1-Jan 11487733 11487733 30 June Year 1 11487733 344632 400000 -55368 -55368 11432365 31 Dec year 1 11432365 342971 400000 -57029 -57029 11375336 30 June Year 2 11375336 341260 400000 -58740 -58740 11316596 31 Dec year 2 11316596 339498 400000 -60502 -60502 11256094 30 June Year 3 11256094 337683 400000 -62317 -62317 11193777 31 Dec year 3 11193777 335813 400000 -64187 -64187 11129590 30 June Year 4 11129590 333888 400000 -66112 -66112 11063478 31 Dec year 4 11063478 331904 400000 -68096 -68096 10995382 30 June Year 5 10995382 329861 400000 -70139 -70139 10925244 31 Dec year 5 10925244 327757 400000 -72243 -72243 10853001 30 June Year 6 10853001 325590 400000 -74410 -74410 10778591 31 Dec year 6 10778591 323358 400000 -76642 -76642 10701949 30 June Year 7 10701949 321058 400000 -78942 -78942 10623008 31 Dec year 7 10623008 318690 400000 -81310 -81310 10541698 30 June Year 8 10541698 316251 400000 -83749 -83749 10457949 31 Dec year 8 10457949 313738 400000 -86262 -86262 10371687 30 June Year 9 10371687 311151 400000 -88849 -88849 10282838 31 Dec year 9 10282838 308485 400000 -91515 -91515 10191323 30 June Year 10 10191323 305740 400000 -94260 -94260 10097063 31 Dec year 10 10097063 302912 400000 -97062 -97062 10000000 Discount Price of the bond = c × F × (1 (1 + r)-t)/r+F(1 + r)t C=Interest Rate F= Face Value r=Market Interest Rate Price of Bond= Present value of Interest payments+Present value of the bond Price of Bond 400000*(1-(1.03)^-)/.05)+10000000/(1.05)^20 (400000*(1-.37680)/.05)+10000000*.37689 8754500 Face vale 10000000 Discount 1245500 Discount factor is taken upto 5 decimal. As the table is not provided so I have taken till 5 decimal Discount Date Beginning Book value Interest expenses at 3% Cash Flow/Payment @4% Amortization of Bond Reduction in Principle Ending book avlue Debit Interest Expense Credit cash Bond dicount 1-Jan 8754500 8754500 30 June Year 1 8754500 437725 400000 37725 37725 8792225 31 Dec year 1 8792225 439611 400000 39611 39611 8831836 30 June Year 2 8831836 441592 400000 41592 41592 8873428 31 Dec year 2 8873428 443671 400000 43671 43671 8917099 30 June Year 3 8917099 445855 400000 45855 45855 8962954 31 Dec year 3 8962954 448148 400000 48148 48148 9011102 30 June Year 4 9011102 450555 400000 50555 50555 9061657 31 Dec year 4 9061657 453083 400000 53083 53083 9114740 30 June Year 5 9114740 455737 400000 55737 55737 9170477 31 Dec year 5 9170477 458524 400000 58524 58524 9229001 30 June Year 6 9229001 461450 400000 61450 61450 9290451 31 Dec year 6 9290451 464523 400000 64523 64523 9354974 30 June Year 7 9354974 467749 400000 67749 67749 9422722 31 Dec year 7 9422722 471136 400000 71136 71136 9493858 30 June Year 8 9493858 474693 400000 74693 74693 9568551 31 Dec year 8 9568551 478428 400000 78428 78428 9646979 30 June Year 9 9646979 482349 400000 82349 82349 9729328 31 Dec year 9 9729328 486466 400000 86466 86466 9815794 30 June Year 10 9815794 490790 400000 90790 90790 9906584 31 Dec year 10 9906584 495329 400000 93416 93416 10000000