Michelle is a business student who plans to attend medical school. The average state university medical school education expense can cost around $35,000 per year and is escalating rapidly. Michelle created a spreadsheet model to calculate the total expenses for each year of medical school, including both education and living expenses. Her estimates are Year 1:

$57,067, Year 2: $56,572, Year 3: $67,846, and Year 4: $55,662. She is considering three loan options:

the Stafford loan, a 6.8% loan with a cap of $47,167 that does not accrue interest during medical school;

the Graduate Plus loan, a 7.9% loan with no cap that does accrue interest during medical school; and a private bank loan, a 5.9% loan with a cap of $30,000, also with accruing interest during medical school.

Assume that each loan will be paid over 25 years after graduation. Michelle currently has $39,500 saved from investments, family gifts, and work, and will receive an additional $4,500 in gifts from her grandparents in years 2 through 4. Develop and solve an optimization model to determine how much money to fund from each type of loan to minimize the amount of interest that will have to be paid on the loans. (Hint: use the Excel function CUMIPMT to find the total interest that will be paid over the life of a loan. For example, if a 30-year loan for $100,000 has an interest rate of 9%, then the formula = -

CUMIPMT(9%, 30, 100,000, 1, 30, 0) will yield

$192,009 cumulative interest paid between years 1 and 30. (Note that this function yields a negative value so include the minus sign.)