Question
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
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 Years SalaryYou 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 each year 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 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 final year of work and the set % is 2%. The settlement rate (discount rate) is estimated to be 6%. ( The inital it up to 100 also Bond carry value ended with zero)
| Bond Amortization Schedule | ||||
| Input | ||||
| Bond Data: | ||||
| Maturity Value | $ 100,000.00 | |||
| Bond Issue Date | 1/1/2017 | |||
| Stated Interest Rate | 5.00% | |||
| Market Interest Rate | 4.50% | |||
| Years Outstanding | 10 | |||
| Times Interest Paid During the Year | 1 | |||
| Output | ||||
| Bond Report: | ||||
| Cash Proceeds From Bond Issue: | $103,956.36 | |||
| Bond Amortization Schedule | ||||
| Interest Payment | Cash Payment | Interest Expense | Amortization | Bond Carrying Value |
| CR | DR | DR (CR) | ||
| Initial | $103,956.36 | |||
| 1 | $ 5,000.00 | $ 4,678.04 | $ 321.96 | $103,634.40 |
| 2 | $ 5,000.00 | $ 4,663.55 | $ 336.45 | $103,297.94 |
| 3 | $ 5,000.00 | $ 4,648.41 | $ 351.59 | $102,946.35 |
| 4 | $ 5,000.00 | $ 4,632.59 | $ 367.41 | $102,578.94 |
| 5 | $ 5,000.00 | $ 4,616.05 | $ 383.95 | $102,194.99 |
| 6 | $ 5,000.00 | $ 4,598.77 | $ 401.23 | $101,793.76 |
| 7 | $ 5,000.00 | $ 4,580.72 | $ 419.28 | $101,374.48 |
| 8 | $ 5,000.00 | $ 4,561.85 | $ 438.15 | $100,936.33 |
| 9 | $ 5,000.00 | $ 4,542.14 | $ 457.86 | $100,478.47 |
| 10 | $ 5,000.00 | $ 4,521.53 | $ 478.47 | $100,000.00 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Advanced Accounting
Authors: Joe Hoyle, Thomas Schaefer, Timothy Doupnik
10th edition
0-07-794127-6, 978-0-07-79412, 978-0077431808
