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