on part B of this Excel. I`m trying to do what if formulas. I`m very confused can an expert help me fix my formulas in
on part B of this Excel. I`m trying to do "what if" formulas. I`m very confused can an expert help me fix my formulas in part B please the has a defined benefit plan that specifies annual retirement benefits equal toSet %X Service YearsXFinal Year's Salary I`m 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. PBO increases each year for service cost and interest.After retirement, benefits are paid and PBO is reduced.the 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 40 years after 40 years of service.You expect to live 20 years after retirement.You expect your salary to be $200,000 your final year of work and the set% is 1%.The settlement rate (discount rate) is estimated to be 4%.
A B C G H J K L M N Service Cost and PBQ Input PBO Data: 5 Set % 1.00% Service Years 40.001 Final Year's Salary S 200,000.00 Years after retirement 20.00 The settlement rate 4.00% 10 11 Output Model that calculates the Service Cost, Interest and Projected Benefit Obligation at 12 the end of each year for one employee. 13 Projected Benefit Obligation at the end of 14 Years from now Service Cost Interest year 15 16 Initial 17 1 S 5,887.89 . S IS 5.887.89 1 18 21 S 6,123.41 IS 235.52 1=IF(IF (D18=0, PV(SC59, SC$6-A18),0,-F18), G17-D18+C18)0, G16*5059, 0) 18 2 6,123.41 : IF(logical_test, [value_if_true], [value_if_false]) 19 31 6,368.34 409.07 3 19, 103.02 : 20 4 6,623.07 764.20 26,492.30 : 21 6,888.00 1,059.69 34,439.99 : 22 6 7,163.52 1,377.60 42,981.11 : 23 7 7,450.06 1,719.24 52,150.41 : 24 8 7,748.06 2,086.02 61,984.49 25 8,057.98 2,479.38 72,521.85 26 10 8,380.30 2,900.87 83,803.03 : 27 11 8,715.51 3,352.12 S 95,870.66Service Cost and PBO 2 3 Input 4 PBO Data: 5 Set % 1.00% 6 Service Years 40.00 7 Final Year's Salary S 200,000.00 8 Years after retirement 20.00 9 The settlement rate 4.00% 10 11 Output Model that calculates the Service Cost, Interest and Projected Benefit Obligation at 12 the end of each year for one employee. 13 Projected Benefit Obligation at the end of 14 Years from now Service Cost Interest year 15 16 Initial 01 17 1 =IF((G17-G16-C17)>0, G17-G16-C17, 0) 5.887.89 1 18 12,246.81 19 3 IF (logical_test, [value_if_true], [value_if_false]) 0, 300.34 409.07 19, 105.02 20 4 6,623.07 S 764.20 26,492.30 21 5 6,888.00 1,059.69 34,439.99 22 6 5 7,163.52 1,377.60 42,981.11 23 7 5 7,450.06 1,719.24 52,150.41 24 7,748.06 2,086.02 61,984.49 25 8,057.98 2,479.38 72,521.85 26 10 8,380.30 2,900.87 83,803.03 27 11 8,715.51 3,352.12 95,870.66