VBA Function - Portfolio Volatility using Co-variance Matrix

[tsimamora]

Hi everyone,

I am a newbie in VBA and in this forum and I am in need of your help. I am trying to write a code for calculating portfolio volatility using co-variance matrix:

AAA.jpg

The Port_Vol function that I created does not work when I included "Square Root (^0.5)". But if I didn't include Square Root, the function works just fine.

I need to include the Square Root in order to calculate the real value of volatility. Can you help me with this?

Thank you very much beforehand!

[p45cal]

try:
Port_Vol = Application.MMult(Application.MMult(Application.Transpose(W), CovMat), W)(1) ^ 0.5

[tsimamora]

Wow, it works! Many thanks p45cal.

May I know why adding (1) could make the function work?

[p45cal]

because the result of Application.MMult is an array (albeit with only one member).

[tsimamora]

Got it. At first I thought the output will already be the sum of all of the array (variances); so I just needed to SQRT them.

Apparently I was wrong. Thank you for your awesome answers!