Question

Your company manages investments for your clients, where you build portfolios based on anticipated yield and risk. (Yield and risk are generally based on historical performance of the investment instruments.)

The three instruments you are considering for a client are growth, income, and money market funds, which you determine to have risks of .1, .05, and .01 respectively. Furthermore, you project the yields of these funds to be 20%, 10%, and 6% respectively.

Your client insists that you diversify, with at least 10% in each of the growth and income funds, and at least 20% in the money market fund.

Your client wants to assume no more that 5% risk, where overall risk is calculated based on a weighted average of the risks of each individual instrument, and where the weightings are the percents of the investment that go towards each instrument.

If your client has $1,000,000 to invest, how should you allocate his funds?

How much can your yield estimates for each instrument change (individually) before you have to change this allocation?

	Growth	Income	Money market fund
Yield	0.1	0.05	0.01
Risk	20%	10%	6%

How do you solve this problem using excel solver?

Answer 1

First of all, note that that problem statement and the risk/ yield table are completely reverse. The table should be corrected. The risk entries are for yield and vice versa.

Let G, N, and M be the sum invested in Growth, Income, and Money market funds respectively.

Max Z = total yield = 20% * G + 10% * N + 6% * M
s.t.
G >= 10% (G + N + M) or, 0.9 G - 0.1 N - 0.1 M >= 0

N >= 10% (G + N + M) or, -0.1 G + 0.9 N - 0.1 M >= 0

M >= 20% (G + N + M) or, -0.2 G - 0.2 N + 0.8 M >= 0

(0.1 G + 0.05 N + 0.01 M) / (G+N+M) <= 0.05
or, 0.1 G + 0.05 N + 0.01 M <= 0.05 (G+N+M)
or, (0.1 - 0.05) G + (0.05 - 0.05) N + (0.01 - 0.05) M <= 0

G + N + M = 1000000

G, N, M >= 0

Solver setup

Solution