Suppose that you have recently been hired to help manage a companys pension fund. The funds required to make the payments on the first day
Suppose that you have recently been hired to help manage a companys pension fund. The funds required to make the payments on the first day of each year for the period 2007-2021 are shown in the Problem worksheet. Assume that you are going to finance these payments by buying bonds. Three type of bonds (bond 1, bond 2, and bond 3) are available for purchase on January 1, 2007. The price (cash outflow) and coupons (cash inflows) for one unit of each bond are also provided in the Problem 2 worksheet. Assume that all coupon payments are received on January 1 and arrive in time to meet the cash demands for the date on which they arrive. On January 1, 2007, you purchase bonds and then meet your first required payment of $15,000. During each year the excess cash on hand earns 3% interest. Using a linear programming model, determine the number of each bond that should be purchased in order to minimize the January 1, 2007 investment needed to meet the 2007-2021 cash requirements. You may assume that fractional numbers of bonds can be purchased.
Please show all work and excel formulas, thank you!
AutoSave oFF GS 2-4 Q Search Sheet Homc Insert Draw Page Layout Formulas Data Revicw Vicw Devcloper Share insert Arial Wrap Text General Delete B 1 U, i , d , , Merge & Center, $, 96 , *.0.00 Conditional Format Cell Sort & Filter Formatting as Table Styles Format L17 1 Problem 2 2007 2008 2009 2010 2011 2012 2013 20142015 2016 2017 2018 2019 20202021 4 Required payment at start $15,000 $16,000 $18,000 $20,000 $21,000 $23,000 $25,000 $26,000 $27,000 $29,000 $30,000 $35,000 36,000 $37,000 $37,000 e Cash outflows (in 2007) and inflows (in other years) from one unit of each bond 2007 2008 ($980) $60 (5970) $65 $75 2009 2010 202012 2013 2014 2015 2016 2017 2018 2019 2020 2021 Bond ! 9 Bond 2 0 Bond3 S60 $60 $1,060 $65 S65 $65$65 S65 $65 S65 $65 S65$1,065 $75 S75 $75 S75 $75$75 S75 $7575 75 75 S75 $1.075 2 Annual interest rate 4 Number of bonds (allowing fractional values) to purchase in 2007 1s Bond1 Bond 2 7 Bond 3 9 Money allocated to buy bonds and cover 2007 pa 21 Constraints to meet payments 3 Amount available $0.00 2007 2008 2009 2010 20112012 2013 20142015 2016 2017 2018 2019 20202021 24 Amount required 15,000 $16,000 $18,000 $20,000 $21,000 $23,000 $25,000 000 S27,000 $29,000 $30,000 $35,000 000 S37,000 $37,000 2 Amount to allocate in 2007 Problem 1 Problem 2 Problem 3 Sheet4 Shect5 Shect6 Shect8 Sheet9 Shect 10 Sheet11 Shect 12 Sheet13 Ready I -- - + 90% AutoSave oFF GS 2-4 Q Search Sheet Homc Insert Draw Page Layout Formulas Data Revicw Vicw Devcloper Share insert Arial Wrap Text General Delete B 1 U, i , d , , Merge & Center, $, 96 , *.0.00 Conditional Format Cell Sort & Filter Formatting as Table Styles Format L17 1 Problem 2 2007 2008 2009 2010 2011 2012 2013 20142015 2016 2017 2018 2019 20202021 4 Required payment at start $15,000 $16,000 $18,000 $20,000 $21,000 $23,000 $25,000 $26,000 $27,000 $29,000 $30,000 $35,000 36,000 $37,000 $37,000 e Cash outflows (in 2007) and inflows (in other years) from one unit of each bond 2007 2008 ($980) $60 (5970) $65 $75 2009 2010 202012 2013 2014 2015 2016 2017 2018 2019 2020 2021 Bond ! 9 Bond 2 0 Bond3 S60 $60 $1,060 $65 S65 $65$65 S65 $65 S65 $65 S65$1,065 $75 S75 $75 S75 $75$75 S75 $7575 75 75 S75 $1.075 2 Annual interest rate 4 Number of bonds (allowing fractional values) to purchase in 2007 1s Bond1 Bond 2 7 Bond 3 9 Money allocated to buy bonds and cover 2007 pa 21 Constraints to meet payments 3 Amount available $0.00 2007 2008 2009 2010 20112012 2013 20142015 2016 2017 2018 2019 20202021 24 Amount required 15,000 $16,000 $18,000 $20,000 $21,000 $23,000 $25,000 000 S27,000 $29,000 $30,000 $35,000 000 S37,000 $37,000 2 Amount to allocate in 2007 Problem 1 Problem 2 Problem 3 Sheet4 Shect5 Shect6 Shect8 Sheet9 Shect 10 Sheet11 Shect 12 Sheet13 Ready I -- - + 90%
