In: Statistics and Probability
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?
Sol:
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)
The LP model is
maximize
s.t.
b)
Prepare the following sheet
get this
setup the solver using data--->solver
get this
c).
$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 |
If you Satisfy with Answer, Please give me "Thumb
Up". It is very useful for me.
Thank you for your support.