Thread: Analysis of allocation problem using Excel

An anlysis using 3 TSP funds:
The problem is optimal asset allocation among a set of investments. In the TSP, one can choose to invest in 3 funds: in a common stock index (S&P500) fund "C", or in a Fixed Income Index Investment Fund "F" (Shearson Lehman Hutton Aggregate), or in a special Government Securities Investment Fund "G" (short term US Treasuries specially issued to the TSP Plan).
What fraction of one's money should be invested in each? Assume the fractions are Kc,Kf, and Kg. For example if one allocated his money 40 % to the stock fund C, 34% to the bond fund F and 26% to G, then this set is kc=.40, kf=.34, kg=.26 Note kc+kf+kg = 1 (by design).

I make the (unsubstantiated) assumption that each investment can be characterized as a normally-distributed random variable with a known mean and standard deviation.
(It is more correct to assume log-normal distributions, but the mathis harder). Thus the funds have Means Mc,Mf,Mg and standard deviations Sc,Sf and Sg. My investment is then the random variable R = kc * C + kf * F + kg * G where C,F,G are random variables drawn from their respective distributions. Make the further assumption that C,F,G are independent processes so they are uncorrelated and have zero covariances. Then R is also gaussian, with the following Mean and Standard deviation:

Mr = kc Mc + kf Mf + kg Mg

Sr = ( kc^2 Sc^2 + kf^2 Sf^2 + kg^2 Sg^2) ^0.5

( "^" is exponentiation: ^2=squared ^.5 = square root)
Thus the fund variances, when weighted by their respective squared investment fractions, sum linearly to the variance of R. Define the ratio Mr/Sr = W. W sets the tradeoff between maximizing the return,(mean value Mr) and minimizing the risk (Sr). For example: the choice W=1.645 puts the mean value of R at 1.645 standard deviations above zero. For this choice, 95% of the time one can expect a positive return R : the cumulative distribution from -infinity to 0 is 5% of the area.
So 5% of the time, or 1 year in 20, one could expect a negative return on this investment.

W should be selected to be consistent with one's risk tolerance and investment horizon; an investor with 25 years horizon may view a positive return 80% of the time ( w=0.842 ) as acceptable while someone with a 5 year horizon may demand 99.9% positive return years ( w = 3.091). The Excel function "Normsinv" gives the cumulative function: Normsinv(0.95)=1.645 Given W, and the means Mc, Mf, Mg and risks Sc,Sf,Sg, one then maximizes Mr subject to the constraints Mr = W Sr, kc + kf + kg =1, 0< kc <1 and kf,kg similarly.

I have used the "Solver" feature in Microsoft Excel on the following statistics (actual returns over the period 1976-1993):
C F G
mean 14.170 9.530 9.420
std dev 12.700 8.510 2.380

Solver uses a quasi-Newton search algorithm to optimize
a variable subject to nonlinear constraints. Results for the above set of data (solving for kc,kf and kg) are given below - the "Success" parameter shows the percentage of time a positive return can be expected.

Success Kc Kf Kg Mr Sr W
86.77% 1.000 0.000 0.000 14.2 12.7 1.115748
86.86% 0.000 1.000 0.000 9.530 8.510 1.119859
90.0% 0.816 0.052 0.132 13.3 10.38 1.28131
93.0% 0.670 0.056 0.275 12.61 8.54 1.476581
95.0% 0.577 0.058 0.365 12.17 7.40 1.644595
99.0% 0.358 0.063 0.579 11.12 4.78 2.32636
99.90% 0.229 0.066 0.705 10.51 3.40 3.091176
99.9962% 0.000 0.000 1.000 9.420 2.380 3.957983

Note how full investment in the stock fund, kc =1, yields
a positive return 86.77% of the time; putting some money
into the rock solid (low Sg) but lower-yielding G fund
(Mg=9.42) improves the success period to over 90%. The F
fund gets little weight, since it returns only slightly more than G but at 4 times higher risk. For my money, 93% is anice operating point, with an expected return of 12.61% and a risk of 8.54%.

I believe this approach may have general applicability to
the asset allocation problem.