View the step-by-step solution to:

# Need help to solve using Excel's Solver using Simplex LP. In anticipation of the immense college expenses of their child, a couple has started an

Need help to solve using Excel's Solver using Simplex LP.

In anticipation of the immense college expenses of their child, a couple has started an annual investment program on the child's twelfth birthday that will last until the twenty second birthday. Judging from their expected financial position over the next 10 years, the couple estimate that they will be able to invest the following amounts at the beginning of each year:

To avoid unpleasant surprises, the couple opts to invest the money very safely. The following options are open to them:

1. Insured savings with 4.5% annual yield.
2. Nine-year government bonds that yield 6.9% annually

a) Find an optimal investment plan to maximize the cash balance at the end of Year 10 (beginning of Year 11).

b) The child is expected to pay the college tuition of \$20K annually at the beginning of year 7 to year 10. The parents have the option of getting a one-time college loan at the beginning of year 7 with an annual interest of 5%.  Set up an Excel linear program model to help the couple determine the optimal investment and college loan amount to maximize the balance at the end of Year 10 (beginning of Year 11). HINT Copy Sheet 1 and sheet #2 and modify the setup. We assume that when you borrow a college loan, you will pay interest annually. Your loan principle should be reflected in the balance at the beginning of Year 11. The maximum balance should be between -49K and -50K)

c) Find the minimum amount of college loan to take and the investment plan for the next 10 years in order to meet the tuition payments from year 7 to year 10. sheet #3. (Hint: Copy your Excel setup on sheet #2 to the sheet #3 and modify the setup. The minimum loan amount is between \$47K and \$48K).

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents