0
$\begingroup$

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.

Improve this question
$\endgroup$
5
  • 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 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$
    – user14894283
    Aug 22 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 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 at 16:45
  • $\begingroup$ @Rylan I'm not sure that I follow, as in cov(a,b) & cov(b,a)? $\endgroup$
    – user14894283
    Aug 22 at 17:06

0

Reset to default

Your Answer

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