ExamplePensionFundManagement

.xlsx
Pension fund management Costs (year 1) and income (in other years) from bonds Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 Bond 1 $980 $60 $60 $60 $60 $1,060 Bond 2 $970 $65 $65 $65 $65 $65 $65 $65 $65 $65 $65 $1,065 Bond 3 $1,050 $75 $75 $75 $75 $75 $75 $75 $75 $75 $75 $75 $75 $75 $1,075 Interest rate 4.00% Number of bonds (allowing fractional values) to purchase now Bond 1 73.69 Bond 2 77.21 Bond 3 28.84 Cash allocated $197,768 Objective to minimize, also a decision variable cell Constraints to meet payments Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 Cash available $20,376 $21,354 $21,332 $19,228 $16,000 $85,298 $77,171 $66,639 $54,646 $41,133 $25,000 $84,390 $58,728 $31,000 $31,000 >= >= >= >= >= >= >= >= >= >= >= >= >= >= >= Cash required $11,000 $12,000 $14,000 $15,000 $16,000 $18,000 $20,000 $21,000 $22,000 $24,000 $25,000 $30,000 $31,000 $31,000 $31,000 Range names used: Bonds_purchased =Model!$B$12:$B$14 Cash_allocated =Model!$B$16 Cash_available =Model!$B$20:$P$20 Cash_required =Model!$B$22:$P$22 A B C D E F G H I J K L M N O P 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
Oneway analysis for Solver model in Model worksheet Interest rate (cell $B$9) values along side, output cell(s) along top Bonds_purchased_1 Bonds_purchased_2 Bonds_purchased_3 Cash_allocated 4 2.00% 77.12 78.71 28.84 $202,010 2.40% 76.41 78.40 28.84 $201,145 2.80% 75.72 78.10 28.84 $200,288 3.20% 75.03 77.80 28.84 $199,439 3.60% 74.36 77.50 28.84 $198,600 4.00% 73.69 77.21 28.84 $197,768 4.40% 73.04 76.92 28.84 $196,946 4.80% 72.40 76.63 28.84 $196,131 5.20% 71.77 76.34 28.84 $195,325 5.60% 71.15 76.06 28.84 $194,527 6.00% 70.54 75.78 28.84 $193,737 The most interesting thing above is that as the interest rate increases, the amount of money set aside decreases. This is shown in the chart as well. The most interesting thing above is that as the interest rate increases, the amount of money set aside decreases. This is shown in the chart as well.
Sensitivity of Cash_allocated to Interest rate Data for chart Cash_allocated 202010.3 201144.7 200287.8 199439.5 198599.7 197768.4 196945.5 196131 195324.7 194526.6 193736.7 2.00% 2.40% 2.80% 3.20% 3.60% 4.00% 4.40% 4.80% 5.20% 5.60% 6.00% 188000 190000 192000 194000 196000 198000 200000 202000 204000 Sensitivity of Cash_allocated to Interest rate Interest rate ($B$9) When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.
Page1of 6
Uploaded by EarlLoris3587 on coursehero.com