M5.2An investor wishes to invest all of her $8million in a diversified portfolio through a commercial lender. The types of investments, the expected annual interest rate for the investment, and the maximum allowed percentage of the total portfolio that the investment can represent are shown in the table below:

INNVESTMENT	EXPECTED INTEREST	MAXIMUM ALLOWED (% of total portfolio)
Low-income mortgage loans	4.80%	30%
Conventional mortgage loans	4.20%	25%
Government sponsored mortgage loans	8.20%	15%
Bond investments	4.45%	40%
Stock investments	7.50%	15%
Futures trading	8.50%	10%

She wants at least 30% of her total investment in non-mortgage instruments. Furthermore, she wants no more than 20% of her total investment to be in high-yield and high-risk instruments (i.e. expected interest rate of investment is 8% or greater). Formulate and solve this problem in Excel to determine how her money should be diversified in a manner which will meet the requirements and maximize the amount of interest income. (Hint: Make sure that the LHS and RHS of constraints are the same units)

SOLUTION: The optimal solution, the value of the interest income, should be $463,400($10 for rounding). The minimum invested in any one investment is $0. The maximum invested in any one investment is $2,800,000.

Questions:

a)Based on your solution, how much should be invested in government sponsored mortgage loans?

b)Based on your solution, how much should be invested in stock investments?

c)If the return on low-income mortgage loans was reduced to 4%, how much should be invested in these low-income mortgage loans based on your new solution?

Use the below LP Template.xlsx provided to you in the Module Documents & Files. Solve the problem using Excel Solver, place the answers to the questions in the appropriate text boxes. I will delete your decision variables in your Excel submission and re-solve your problem. There is no credit for the problem if your Excel submission doesnt solve. If necessary, remove problem constraints so that your Excel file solves. It is better to get partial credit than no credit. You do not need to submit the formulation for this problem.

INVESTMENT EXPECTED INTEREST MAXIMUM ALLOWED (% of total portfolio) 30% 25% 4.80% 4.20% 8.20% 15% Low-income mortgage loans Conventional mortgage loans Government sponsored mortgage loans Bond investments Stock investments Futures trading 4.4596 7.50% 8.50% 40% 15% 10% LP Template - Excel mohamed abdule X File Pagelart Formule Dieta Beiru Viru Hels Tell me what you want to do A Share Home Incent x Out A --- Wrap lest General - Normal Bad Goud ECO Neutral Like C 17 L. Normel 2 Cond bone Tomates Calculation Fumetting Table BIU A = = = Morge Carter & - $. % *** Check Cell Explanatory.. Input Insert Deere Format urnbu AYO Sort find Clear File - Sci- Eden Format Painter Chipboard Fani Number Style Cu Al Ju A B D E F G H j K L M N O P Q R 1 DV#1 DV#2 LHS Sign RHS 2 3 4 Decision Variables 5 Objective Function Coefficient 6 Constraints: 7 Constraint #1 8 Constraint #2 9 Constraint #3 10 Constraint #4 11 12 FORMULATION 13 14 Decision Variables: 15 ANSWERS TO QUESTIONS (do not just put numbers - answers should be complete sentences) a) Objective Function: b) 16 17 18 19 20 21 22 23 24 25 Constraints: c) 26 General LP Sheet + Ready Display Settings 1 P 7509 10:39 PM 7/8/2001 O T