I am attempting a portfolio optimization model and ended up generating negative portfolio variance using 2WaWbσaσbcorrel(a,b) or 2WaWb*Cov(a,b)

From reading the linked article where other users had an issue, I’m seeing that it is because the covariance matrix is not semi definite positive:

Negative variance?

The solutions offered are for code, but I I need to use excel. Is there a way to generate a true covariance matrix within excel?

I’m also trying to wrap my head around what exactly semi-definite positive means and why what I’ve done won’t work. I understand that the portfolio variance cannot be negative. Within the linked post, another user states, “there exists no data set (with complete observations) from which you could have estimated such a covariance matrix”, but I don’t see why as covariance can be negative.

  • 1
    $\begingroup$ How did you come up with your covariance matrix? The most common cause of problems is when different parts of the matrix are estimated over different time periods (ex: the $\rho_{AB}$ from 2020-2022 and $\rho_{AC}$ from 2021-2022), because you don't have data for Stock C in 2020). $\endgroup$
    – nbbo2
    Aug 22, 2023 at 15:39
  • $\begingroup$ I wanted to do 120 months but some didn't go far that back. I removed the ones with differing time periods and it still didn't fix the problem unfortunately $\endgroup$ Aug 22, 2023 at 15:59
  • $\begingroup$ I notice that in your setup you are considering only the covariances. Have you added the asset variances as well? Rewriting the formula, you should have: (assuming your portfolio has stocks $S_1, \dots, S_n$ with weights $w_1, \dots, w_n$ $$\sum_{i=1}^n \sum_{j=1}^n w_i w_i \text{Cov}(S_i, S_j)$$ where I'll note that $\text{Cov}(S_i, S_i) = \text{Var}(S_i)$ $\endgroup$
    – Rylan
    Aug 22, 2023 at 16:32
  • $\begingroup$ Sorry, I can't edit that comment any more, the formula I wrote should be $$\sum_{i=1}^n \sum_{j=1}^n w_i w_j \text{Cov}(S_i, S_j)$$ $\endgroup$
    – Rylan
    Aug 22, 2023 at 16:45
  • $\begingroup$ @Rylan I'm not sure that I follow, as in cov(a,b) & cov(b,a)? $\endgroup$ Aug 22, 2023 at 17:06


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.