Question

The prospectus of an investment fund states that all money is to be invested in bonds that are rated A, AA, and AAA. No more than 30% of the total investment is in A and AA bonds collectively, and at least 50% is in AA and AAA bonds collectively. The A, AA, and AAA bonds have annual yields of 8%, 7%, and 6% respectively. Determine (a) the percentages of the total investment that should be committed to each type of bond, so that the fund maximizes its annual yield, and (b) the annual yield. Provide your answers as percentages, rounded to 1 decimal point (e.g. 10.0%). Use the Excel linear programming template which has been provided, or create your own LP model in Excel. Note that in order to receive full marks, your solution must be provided in Excel, and must be solved using Excel’s Solver Add-in.

Answer 1

Annual Yield of Portfolio

Where,
n = no. of bonds in the portfolio
= weight of ith bond in portfolio
= yield of ith bond of portfolio

Now, Create an Excel Sheet as described in the question.

Remember, there are following constraints on the portfolio -

1. Total weight of all 3 bonds together in portfolio = 100%
2. Weight of A and AA combined <= 30%
3. Weight of AA and AAA combined >= 50%

Let me start with random weights assigned to A, AA and AAA to 5%, 10% and 15% respectively. My Excel sheet will look like below -

Values in excel, at this moment look like below -

Now, let's open "Excel Solver" from "Data" tab in excel, and make the following settings -

- In "Set Objective", select the cell where you Portfolio Yield is getting calculated
- In "To", select "Max", as we want to maximize the annual yield
- Now, click the "Add" button and add all 3 constraints one-by-one
- Choose any solving method of your choice
- Also, tick the tick-box "Make Unconstrained variables non-negative" as we dont want the portfolio weights to be negative.

Note: If somehow, shorting in a security is allowed, you can un-tick this tick box to allowe negative weights as well.

Finally click "Solve", to get the following results -


Part A:
Bond A - 30%
Bond B - 0%
Bond C - 70%

Part B:
Annual Yield of Portfolio = 6.60%