Mean–Variance Optimization with a Risk-Free Asset and Target Volatility
Summary
The document describes an Excel setup for finding a portfolio with a specified annual volatility from three risky assets and a risk-free asset. It outlines how to use estimated expected returns and an annual covariance matrix, account for the risk-free asset’s zero variance and covariance, and impose a full-investment constraint. The stated objective is to maximize expected return while matching the volatility target, with optional long-only bounds.
It asks whether to optimize risky assets first to find a tangency portfolio and then scale it with the risk-free asset, or optimize all weights together. It also asks which Solver formulation and method may be more stable. No solution, numerical results, or screenshots are included, so the document does not establish which formulation is best for this case. Its setup highlights that nonlinear volatility equality constraints and weight bounds can make spreadsheet results sensitive to initialization and solver settings.
Key ideas
- A risk-free asset contributes to expected return but has zero variance and zero covariance with risky assets under the stated assumptions.
- The portfolio weights must sum to one when the risk-free asset is included.
- The proposed task fixes volatility and seeks the highest estimated expected return.
- The document raises tangency-portfolio scaling as an alternative to direct optimization.
- It provides no solver results or recommendations, so convergence guidance remains unresolved.
Tags
Full text
# Excel Solver not converging for mean-variance portfolio optimisation
# Excel Solver not converging for mean-variance portfolio optimisation
I am trying to solve a mean–variance portfolio optimisation problem in Excel, but my Solver setup is not converging / gives inconsistent results. I would appreciate guidance on the correct way to structure the optimisation when a risk-free asset is available and the portfolio must hit a target volatility.
Problem setup
I have 3 risky assets and 1 risk-free asset. I have 60 months of simulated monthly returns in Excel. From those monthly returns, I estimate:
- annual expected returns: $\hat{\mu}$
- annual covariance matrix: $\hat{\Sigma}$
The risk-free rate is $r_f$ = 1% (annual).
The task is:
- Compute the efficient portfolio that has 5% annual volatility.
- Report the expected annual return of this portfolio.
- Compute the realized/true return using the same 60-month simulated sample.
The question hints that I should use the risk-free asset to construct the portfolio.
My Excel structure
I set decision variables as weights:
- $w_1$, $w_2$, $w_3$ = risky asset weights
- $w_f$ = risk-free weight
Constraint: $w_1 + w_2 + w_3 + w_f = 1$
Portfolio expected return: $E[R_p] = w^\top \hat{\mu} + w_f r_f$
Portfolio variance: $\sigma_p^2 = w^\top \hat{\Sigma} w$ (Note: I assume the risk-free variance is 0 and covariance with risky assets is 0)
Target volatility constraint: $\sigma_p = 5\%$
What I tried
I tried to use Solver to maximize portfolio expected return subject to:
- sum of weights = 1
- volatility = 5%
- (sometimes) weights $\ge 0$
However, Solver often does not converge, or returns unstable weights depending on the starting point.
Questions
- Is this the correct approach, or should I: first find the tangency portfolio using only risky assets, and then combine with risk-free to match the 5% volatility target?
- If using Solver directly with the risk-free asset included, what is the most stable formulation: maximize return subject to volatility constraint, or minimize volatility subject to return constraint?
- In Excel, what is the correct best practice for: setting up the objective cell specifying constraints avoiding non-convergence (e.g., using GRG Nonlinear vs Evolutionary)
I will attach screenshots showing:
- estimated returns and covariance matrix
- my portfolio formula layout
- Solver constraintsShown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.