Question

The Sketchy Loner is a bank that issues 6 types of loans. In addition, to diversify its portfolio and minimize its risk, the bank invests in risk-free securities. The 6 loan types and the risk-free securities with their annual rates of return are given below:

Type of Loan or Security	Annual Rate of Return (%)
Home Mortgage (first)	12%
Home Mortgage (second)	13%
Commercial loan	16%
Auto Loan	16%
Home Improvement Loan	19%
Risk Free Security	11.5%

The bank’s objective is to maximize the annual rate of return on investments subject to the following policies, restrictions, and regulations:

1. The bank has $780 million in available funds.

2. Risk-free securities must contain at least 10% of the total funds available for investments.

3. Home improvement loans cannot exceed $90,000,000.

4. The investment in first and second mortgage loans must be at least 60 percent of all the funds invested in the 5 loan types.

5. The investment in the first mortgage loans must be at least three times the funds invested in second mortgage loans.

6. Home improvement loans cannot exceed 15 percent of the funds invested in first mortgage loans.

7. Automotive loans and home improvement loans together may not exceed the commercial loans.

8. Commercial loans cannot exceed 60% of the total funds invested in mortgage loans.

a) Formulate the problem into proper Linear programming format

b) Use Excel’s Solver to determine the optimal solution

c) State the optimal solution in the context of the business problem, that is to say, how exactly should the money be allocated to the possible investments?

Answer 1

Let be the proportion of total funds invested in the loan types (also called weights) , Home Mortgage (first), Home Mortgage (second), Commercial loan, Auto Loan, Home Improvement Loan, Risk Free Security respectively

These are the decision variables.

The annual rate of return of the portfolio is

We want to maximize the annual rate of return on investments and hence the above os the objective function

Now the constraints

The proportions sum to 1

2. Risk-free securities (proportion X6)  must contain at least 10% of the total funds available for investments.

3. Home improvement loans (proportion X5) cannot exceed $90,000,000

4. The investment in first and second mortgage loans (X1,X2) must be at least 60 percent of all the funds invested in the 5 loan types (X1+..+ X5 which is same as 1-X6) .

5. The investment in the first mortgage loans (X1) must be at least three times the funds invested in second mortgage loans (X2)

6. Home improvement loans (X5) cannot exceed 15 percent of the funds invested in first mortgage loans (X1).

7. Automotive loans (X4) and home improvement loans (X5) together may not exceed the commercial loans (X3).

8. Commercial loans (X3) cannot exceed 60% of the total funds invested in mortgage loans.

a) Formulate the problem into proper Linear programming format

ans:

The LP model is

maximize

s.t.

b) Use Excel’s Solver to determine the optimal solution

Prepare the following sheet

get this

setup the solver using data--->solver

get this

c) State the optimal solution in the context of the business problem, that is to say, how exactly should the money be allocated to the possible investments?

$780 million in available funds needs to be allocated in the following manner to get  an optimum/maximum annual rate of return on investments of 13.71%

Type of Loan or Security	Amount invested ($)
Home Mortgage (first)	315,900,000.00
Home Mortgage (second)	105,300,000.00
Commercial loan	233,415,000.00
Auto Loan	-
Home Improvement Loan	47,385,000.00
Risk Free Security	78,000,000.00