Create an excel spreadsheet that shows 1. The calculation of bond issue price. S
ID: 2538686 • Letter: C
Question
Create an excel spreadsheet that shows 1. The calculation of bond issue price. Show the PV of interest payments and the PV of the lump sum (maturity value of bond), then show final bond issue price. You will have 3 different bond issues (one with discount, one with premium, and one at par). Each scenario should be in a separate tab of the excel file. Ideally you should use formulas and be able to change the parameters of the problem and the second and third scenarios only require copy and paste and changing input fields. (Market rate of interest is the only changing parameter. 2. The amortization table for each scenario. You can do the columns like our book does. If you prefer to include a discount or premium account, you may do that as well. 3. Use formatting tools to make your excel file look professional and user-friendly. In the end you should have 3 worksheets in one excel file. Here is the data: On January 2, Donald Corp. iIssued 700, $1,000 bonds to finance a new building. They are 10-year, bonds paying interest on July 1 and December 31 each year. The stated rate of interest = 6%, when issued, the market rate of issue was: Scenario 1: 8% Scenario 2: 4% Scenario 3: 6% Upload your file here.Explanation / Answer
Scenario 1. Table Value Based on n= 10 i= 8.00% Cash Flow Amount Present Value Interest - $700,000 X 6% 42,000 281,823 ($42,000 X 6.71008) Principal 700,000 324,235 ($700,000 X 0.46319) Issue Price of Bonds 606,059 Discount on issue of Bond 93,941 Bond Discount Amortization Schedule Date Interest Paid - $700,000 X 6% Interest Expense - Preceeding Bond Carrying Value X 8% Discount Amortization Unamortized Discount Bonds Carrying Amount A B C = B-A D = D - C E = $700,000 - D 0 - - - 93,941 606,059 1 42,000 48,485 6,485 87,456 612,544 2 42,000 49,003 7,003 80,453 619,547 3 42,000 49,564 7,564 72,889 627,111 4 42,000 50,169 8,169 64,720 635,280 5 42,000 50,822 8,822 55,898 644,102 6 42,000 51,528 9,528 46,370 653,630 7 42,000 52,290 10,290 36,079 663,921 8 42,000 53,114 11,114 24,966 675,034 9 42,000 54,003 12,003 12,963 687,037 10 42,000 54,963 12,963 0 700,000 Scenario 2. Table Value Based on n= 10 i= 4.00% Cash Flow Amount Present Value Interest - $700,000 X 6% 42,000 340,658 ($42,000 X 8.11090) Principal 700,000 472,895 ($700,000 X 0.67556) Issue Price of Bonds 813,553 Premium on issue of Bond 113,553 Bond Premium Amortization Schedule Date Interest Paid - $700,000 X 6% Interest Expense - Preceeding Bond Carrying Value X 4% Premium Amortization Unamortized Premium Bonds Carrying Amount A B C = A - B D = D - C E = $700,000 + D 0 - - - 113,553 813,553 1 42,000 32,542 9,458 104,095 804,095 2 42,000 32,164 9,836 94,258 794,258 3 42,000 31,770 10,230 84,029 784,029 4 42,000 31,361 10,639 73,390 773,390 5 42,000 30,936 11,064 62,326 762,326 6 42,000 30,493 11,507 50,819 750,819 7 42,000 30,033 11,967 38,851 738,851 8 42,000 29,554 12,446 26,405 726,405 9 42,000 29,056 12,944 13,462 713,462 10 42,000 28,538 13,462 (0) 700,000 Scenario 3. Table Value Based on n= 10 i= 6.00% Cash Flow Amount Present Value Interest - $700,000 X 6% 42,000 309,124 ($42,000 X 7.36009) Principal 700,000 390,876 ($700,000 X 0.55389) Issue Price of Bonds 700,000 Premium on issue of Bond - Bond Amortization Schedule Date Interest Paid - $700,000 X 6% Interest Expense - Preceeding Bond Carrying Value X 6 Premium Amortization Unamortized Premium Bonds Carrying Amount A B C = A - B D = D - C E = $700,000 + D 0 - - - - 700,000 1 42,000 42,000 - - 700,000 2 42,000 42,000 - - 700,000 3 42,000 42,000 - - 700,000 4 42,000 42,000 - - 700,000 5 42,000 42,000 - - 700,000 6 42,000 42,000 - - 700,000 7 42,000 42,000 - - 700,000 8 42,000 42,000 - - 700,000 9 42,000 42,000 - - 700,000 10 42,000 42,000 - - 700,000
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.