80.2K

Verified Solution

Link Copied!

please calculate the interest and PBO based of the service cost calculated and show formulas.

Ulaas Paste Font Alignment Number Cell Styles =IF($F$4-B16>=0,$F$12*(1+$F$8)^_($F$4-B16),0) fx 100.000.00 Free Accounting Firm Defined Ben Plan C utions Input Year's Unis Retirement Service Life Expectancy Final Year of Work Salary % Discount Rate Benefit Parlod (Annual) Amusl Retirement Benefit Payment Amuel Retirement Earned Yearly Service Cast 25000100 1.000.00 SUB110190 Outout Year Bene EBENSORENNBBURONOW Service Cost Interest 3,164 23 MVALUE! 3.290 80T 3,422.44 3,559.33 3,701.71 3,849.78 4,003.77 4,163.92 4,330:47 4,503.69 4683.84 4,871.19 5,088.04 5.268.68 5.479.43 5,698.61 5.925.55 6.163.61 6.410.16 6.888.57 6,933 23 7210.56 7,498.98 7,798.94 8.110.90 Bond Amortization Schedule Input Bond Data: Maturity Value Bond Issue Date Stated Interest Rate Market Interest Rate 2 Years 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 $103,956.36 15 Bond Carrying Value Amortization DR (CR) 19 16 17 Bond Amortization Schedule 18 Interest Payment Cash Payment Interest Expense CR DR 20 21 Initial $ 5,000.00 $ 4,678.04 $ $ 5,000.00 $ 4,663.55 $ $ 5,000.00 $ 4,648.41 $ $ 5,000.00 $ 4,632.59 $ 5,000.00 $ 4,616.05 $ $ 5,000.00 $ 4,598.77 $ $ 5,000.00 $ 4,580.72 $ $ 5,000.00 $ 4,561.85 $ $ 5,000.00 $ 4,542.14 $ 5,000.00 $ 4,521.53 $ OWN- 321.96 336.45 351.59 367.41 383.95 401.23 419.28 438.15 457.86 478.47 $103,956.36 $103,634.40 $103,297.94 $102,946.35 $102,578.94 $102,194.99 $101,793.76 $101,374.48 $100,936.33 $100,478 47 $100,000.00 $0.00 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 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%

Answer & Explanation Solved by verified expert

See Answer