Portfolio Optimization - n risky assets

Question

I'm currently implementing a CAPM model in Excel:

• A portfolio of n risky assets when n=6 (in this case)
• A riskless borrowing rate of 8% and riskless lending rate of 3%
• I'm given the expected return and standard deviation for each risky asset.

The first task was to create a minimum variance portfolio. I did this using the following array formula: {=MMULT(MINVERSE(Cov),TRANSPOSE(I))/SUM(MMULT(MINVERSE(Cov),TRANSPOSE(I)))}

Cov is my 6x6 covariance matrix and I is just a 1x6 array of ones. I could have created I as a 6x1 array and avoided the TRANSPOSE() altogether.

I'm currently stuck on the next task which is to incorporate the riskless asset into the mix and determine the optimal weights for each risky asset given a desired rate of return.

My approach was as follows:

• Calculate the excess returns by subtracting the 3% riskless lending rate from the expected return for each risky asset. Store these values in a 1x6 array called XsRtn
• Execute the array formula to determine the 6 weights: {=MMULT(MINVERSE(Cov),TRANSPOSE(XsRtn))/SUM(MMULT(MINVERSE(Cov),TRANSPOSE(XsRtn)))}
• Sum up the 6 weights to find the weight of the tangent portfolio. If this weight > 1 then investor is net borrower so they will borrow at 8% to bring total weight to 1. Otherwise, the investor lends the excess cash at 3% riskless.

This method appeared to work fine until I realised that the individual weights of the risky assets comprising the tangent portfolio does not change when I change the relevant inputs (expected returns on each individual stock). This cannot be correct as the tangent portfolio mix should change as I move along the tangent curve.

PS - I've already given thought to the idea of using LaGrange Multipliers but it wasn't practical. Matrices are much easier to implement. Also, we're not allowed to use Solver.

Any advice? Thanks for reading.

Answer 1

You have to find the market portfolio, which is the portfolio with maximum Sharpe Ratio:

$$S^*(w)=\frac{R_p(w)-R_f}{\sigma_p(w)}$$

So calculate this ratio and maximize it (I think there is also an exact analytic expression for the market portofolio weights).

Then investors will mix this optimal "best" Market portfolio with the riskfree asset based on their desired return, so you get the investor portfolio weights $w$ for desired return $R^*$ by solving:

$$R^*=w\cdot R_m+(1-w)R_f$$

where $R_m$ being the market portfolio's return and $R_f$ the riskfree asset's return.