Open the Project #1 excel spreadsheet. Note how the Bond Amortization sheet is set-up. Your task is to develop something similar for the PBO of a Defined Benefit Plan. There should be an input area and an output area. Your output area should be a schedule that will indicate the Service Cost, the Interest, the Benefits Paid and the Ending Balance in the PBO each year. Your company has a defined benefit plan that specifies annual retirement benefits equal to: Set % X Service Years X Final Year's Salary You have been asked to develop a model that will calculate the Service Cost, Interest and Projected Benefit Obligation at the end of each year for one employee. Your PBO increases for service cost and interest. After retirement, benefits are paid and your PBO is reduced. (Interest is still incurred during retirement since a long-term liability exists). Your model should be flexible, but you can have it set for a maximum number of years. To test your each year model, you should make up assumptions. For example, you expect to retire in 50 years after 50 years of service. You expect to live 20 years after retirement. You expect your salary to be $240,000 your estimated to be 6%. of work and the set % is 2 %. The settlement rate (discount rate) is year final B C D E Bond Amortization Schedule Input Bond Data: Maturity Value Bond Issue Date Stated Interest Rate Market Interest Rate eYears Outstanding 10 Times Interest Paid During the Year 11 12 Output 13 Bond Report: 14 Cash Proceeds From Bond Issue: 100,000 00 $ 1/12019 5.00% 4.50% 10 1 $103,956.36 15 16 17 Bond Amortization Schedule Interest Payment Bond Carrying Value Cash Payment Interest Expense 18 Amortization DR (CR) DR 19 CR 20 $103,956.36 $103,634.40 $103,297.94 $102,946.35 $102,578.94 21 Initial 4,678.04 $ 4,663.55 $ $ 321.96 $ 5,000 00 $ 22 336.45 23 2 $ 5,000.00 $ 351.59 24 3 $ 5,000.00 $ 4,648.41 4,632.59 $ 4,616.05 $ 367.41 $ 25 4 5,000.00 $ $102,194.99 $101,793.76 $101,374.48 $100,936.33 $100,478 47 $100,000 00 5,000.00 $ 383.95 26 5 $ 6 $ 5,000.00 $ 4,598.77 4,580.72 $ 401.23 27 $ 419.28 28 7 5,000.00 4,561.85 $ 5,000.00 $ $ 438.15 29 8 9 $ 5,000.00 $ $ 457 86 30 4,542.14 5,000.00 $ 4,521.53 478.47 10 $ 31 $0.00 $ $ 32 11 $ Bond Amortization PBO Model Open the Project #1 excel spreadsheet. Note how the Bond Amortization sheet is set-up. Your task is to develop something similar for the PBO of a Defined Benefit Plan. There should be an input area and an output area. Your output area should be a schedule that will indicate the Service Cost, the Interest, the Benefits Paid and the Ending Balance in the PBO each year. Your company has a defined benefit plan that specifies annual retirement benefits equal to: Set % X Service Years X Final Year's Salary You have been asked to develop a model that will calculate the Service Cost, Interest and Projected Benefit Obligation at the end of each year for one employee. Your PBO increases for service cost and interest. After retirement, benefits are paid and your PBO is reduced. (Interest is still incurred during retirement since a long-term liability exists). Your model should be flexible, but you can have it set for a maximum number of years. To test your each year model, you should make up assumptions. For example, you expect to retire in 50 years after 50 years of service. You expect to live 20 years after retirement. You expect your salary to be $240,000 your estimated to be 6%. of work and the set % is 2 %. The settlement rate (discount rate) is year final B C D E Bond Amortization Schedule Input Bond Data: Maturity Value Bond Issue Date Stated Interest Rate Market Interest Rate eYears Outstanding 10 Times Interest Paid During the Year 11 12 Output 13 Bond Report: 14 Cash Proceeds From Bond Issue: 100,000 00 $ 1/12019 5.00% 4.50% 10 1 $103,956.36 15 16 17 Bond Amortization Schedule Interest Payment Bond Carrying Value Cash Payment Interest Expense 18 Amortization DR (CR) DR 19 CR 20 $103,956.36 $103,634.40 $103,297.94 $102,946.35 $102,578.94 21 Initial 4,678.04 $ 4,663.55 $ $ 321.96 $ 5,000 00 $ 22 336.45 23 2 $ 5,000.00 $ 351.59 24 3 $ 5,000.00 $ 4,648.41 4,632.59 $ 4,616.05 $ 367.41 $ 25 4 5,000.00 $ $102,194.99 $101,793.76 $101,374.48 $100,936.33 $100,478 47 $100,000 00 5,000.00 $ 383.95 26 5 $ 6 $ 5,000.00 $ 4,598.77 4,580.72 $ 401.23 27 $ 419.28 28 7 5,000.00 4,561.85 $ 5,000.00 $ $ 438.15 29 8 9 $ 5,000.00 $ $ 457 86 30 4,542.14 5,000.00 $ 4,521.53 478.47 10 $ 31 $0.00 $ $ 32 11 $ Bond Amortization PBO Model