Question

A trust officer at the Blacksburg National Bank needs to determine how to invest $150,000 in the following collection of bonds to maximize the annual return.

Bond	Annual Return	Maturity	Risk	Tax Free
A	9.5%	Long	High	Yes
B	8.0%	Short	Low	Yes
C	9.0%	Long	Low	No
D	9.0%	Long	High	Yes
E	9.0%	Short	High	No

The officer wants to invest at least 40% of the money in short-term issues and no more than 20% in high-risk issues. At least 25% of the funds should go in tax-free investments, and at least 45% of the total annual return should be tax free.

1. Formulate the LP model for this problem.
2. Create the spreadsheet model and use Solver to solve the problem.

Answer 1

Let the amount invested in Bond A be Xa, Bond B be Xb, Bond C be Xc, Bond D be Xd, Bond E be Xe

Based on the Annual returns rate given, total annual returns = 9.5%*Xa + 8%*Xb + 9%*Xc + 9%*Xd + 9%*Xe

We have to maximize this return, hence, we get Objective function as

Maximize Total returns R = 9.5%*Xa + 8%*Xb + 9%*Xc + 9%*Xd + 9%*Xe

Various constraints given can be written as follows:

Xa + Xb + Xc + Xd + Xe <= 150,000...........................Constraint for amount available for investment

Xb + Xc >= 40%*(Xa + Xb + Xc + Xd + Xe)................Constraint for minimum investment in short-term issues

Xa + Xd + Xe <= 20%*(Xa + Xb + Xc + Xd + Xe)........Constraint for maximum amount in high-risk issues

Xa + Xb + Xd >= 25%*(Xa + Xb + Xc + Xd + Xe).........Constraint for minimum investment in tax-free issues

9.5%*Xa + 8%*Xb + 9%*Xd >= 45%*(9.5%*Xa + 8%*Xb + 9%*Xc + 9%*Xd + 9%*Xe) .....................................................................................Constraint for minimum return to be tax-free

Xa, Xb, Xc, Xd, Xe >= 0...............................................Non-negativity constraint as investment cannot be negative

We solve the above problem in excel solver as shown below:

Xa Variables 30000 Xb38964.50 XC81035.50 Xd 0 Xe 0 Objective P $13,260 Constraints 1 Condition <= 3 150000 120000 30000 68964.50 5967.16 150000 60000 30000 37500 5967.16 >= >=

Above solution in form of formulas along with Excel Solver Extract is shown below for better understanding and reference:

B D Solver Parameters -Nm to Set Objective: $B$9 To: Max Min value of: D ДА 1 Variables | 2 | Xa | 30000 3 Xb 38964.4970414201 81035.5029585799 5 Xd 0 | 6 | Xe |0. 7 Objective 9 p =9.5%*B2+8%*B3+9%*B4+9%*B5+9%*B6 10 11 Constraints 12 1 =B2+B3+B4+B5+B6 13 2 =B3+B4 14 3 =B2+B5+B6 15 4 =B2+B3+B5 16 5 =9.5%*B2+8%*B3+9%*B5 By Changing Variable Cells: $B$2:$B$6 Condition Add Subject to the Constraints: $B$12 <= $D$12 $B$13 >= $D$13 $B$14 <= $D$14 $B$15:$B$16 >= $D$15:$D$16 Change | VAVAA 150000 =40%*B12 =20%*B12 =25%*B12 =45%*B9 Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help I solve close Close

Investment in Bond A = $30,000

Investment in Bond B = $38,964.5

Investment in Bond C = $81,035.5

Investment in Bond D = 0

Investment in Bond E = 0

Total Return = $13,260

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

Kindly upvote the answer as it will be encouraging for me to keep answering further questions!!!