Question

Please solve using linear programming

In anticipation of the immense college expenses, Joe and Jill started an annual investment program on their child's eighth birthday that will last until the eighteenth birthday. They plan to invest the following amounts at the beginning of each year: Year 1 2 3 4 5 6 3500 7 3500 8 4000 9 4000 10 5000 Amount ($) 2000 2000 2500 2500 3000 To avoid unpleasant surprises, they want to invest the money safely in the following options: Insured savings with 7.5% annual yield, 6-year government bonds that yield 7.9% and have a current market price equal to 98% of face value, and 9-year municipal bonds yielding 8.5% and having a current market price of 1.02 of face value. How should the money be invested?

Answer 1

Please do Upvote if you are served. Feel free to reach out in the comments

Cheers!!!

Answer:

We use excel to create a model for all the given information in bonds & savings

A AD AE AF B 51 C 52 D 53 E 54 F 55 G 56 H 57 I 58 J K L M N O P Q R S 59 60 61 62 63 64 65 M1 M2 01 T 02 U 03 V 04 W 05 X 06 Y 07 Z 08 AA AB AC 09 010 011 0.00 1.02 0.00 & & 1,075 & 3 Return: 4 beginning of year 5 Year1 6 Year 2 -1.075 7 Year 3 8 Year 4 9 Years 10 Year 6 11 Year 7 12 Year 8 13 Year 9 14 Year 10 15 Year 11 1 1.075 & 1 10751 -1075 1 & :58 1 10751 -1.0751 1.0751 -1.075 0.00 - 35 0.00 = 40 0.00 = 40 0.00 = 50 10.000 -2.08 -1 58 -2.08 17 Result:

where Si, Gi, Mi & Ci indicates the amount invested in savings, government bonds, Municipal bonds & Cash from start of the year

We use matrix multiplication for cell AD3,

AD3 =SUMPRODUCT(B3:AC3,$B$17:$AC$17)

Finally we solve the equation using solver in excel using the parameters

S Solver Parameters Set Objective: SAD 3 To: Max Min Value Of: 0 By Changing Variable Cells: $B$17:SACS17 Subject to the Constraints: SAD$5:SAD$15 = SAF55:SAF$15 Add Change Delete Reset All Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems. Solve Close

And arrive at the optimal solution.

As we can see the cash at the end of 10th year comes around $469.5 ~ $470

A Q C 52 B 51 fx D 53 =SUMPRODUCT(B3:AC3,$B$17:$A$17) E F G H J K L M N O P Q R S 54 55 56 57 58 59 610 61 62 63 64 65 M1 M2 01 AD AE AF T 02 U 03 V 04 W 05 X 06 Y 07 Z 08 AA AB AC 09 010 011 469.55 0.98 1.02 0.98 1.02 0.98 0.98 3 Return: 4 beginning of year 5 Year 1 1 6 Year 2 -1.075 1 7 Year 3 -1.075 8 Year 4 9 Year 5 10 Year 6 11 Year 7 12 Year 8 13 Year 9 14 Year 10 15 Year 11 0.98 1075 20.00 = 20 20.00 = 20 25.00 = 25 25.00 = 25 30.00 = 35.00 = 35.00 = 40.00 = 40.00 = 50.00 = 0.00 = -158 -2.08 - 158 -2.08 16 17 Result: 20 0 25 51.88 0 35 72.63 118.1 166.9 229.4 0 0 0 0 87.5 0 40.7 0 0 0 0 0 0 0 0 0 0 470