Standard Diviation question

  • Thread starter Thread starter W. Wells
  • Start date Start date
W

W. Wells

I am new at excel and would like some information. I am trying to get a
standard deviation for an investment portfolio. I have the std diav. for
each investment but want to get the average for the whole port. I also have
the % and amount of each investment. I understand that I can use Normdist
for this solution but do not understand how to use the box. Would appreciate
any help. Thanks
 
W. Wells -

I suggest that first you learn more about portfolio theory. You can get
online information by using google to search for "portfolio variance"
(without the quotes) or similar search items.

You'll find that the variance (standard deviation squared) of a portfolio
depends not only on the variance of each asset but also on the covariances
and on the proportion of each asset that comprises the portfolio.

After you understand some of the theory, you can also get help from google
searches that include "excel" as part of the search item. You'll likely even
find example worksheets for doing the calculations, which I think usually
involve the MMULT worksheet function.

- Mike
www.MikeMiddleton.com
 
If you are interested in a VBA approach to the covariance matrix, the code
below is reasonably accurate on data that is not too ill conditioned. The
function calls the subroutine and can be used on the worksheet.

Function VCOV(ip) As Variant
Dim S As Variant
Call VarCov(ip, S)
VCOV = S
End Function

Sub VarCov(x As Variant, S As Variant, Optional ByRef Xbar As Variant)
Dim i As Long, j As Long, n As Long, k As Long, c
Dim temp As Variant, p As Double, n1 As Long

'***********************************************
'* Subroutine computes the variance-covariance *
'* matrix for a group of variables X in S. *
'* The means for each variable are returned in *
'* the Xbar vector. *
'***********************************************

If IsObject(x) Then
n = x.rows.Count
p = x.Columns.Count
Else
n = UBound(x, 1)
p = UBound(x, 2)
End If

n1 = n - 1

ReDim c(1 To n, 1 To p), Xbar(1 To p, 1 To 1)
ReDim S(1 To p, 1 To p)

With Application
For j = 1 To p
For i = 1 To n
Xbar(j, 1) = Xbar(j, 1) + x(i, j) / n
Next i
For i = 1 To n
c(i, j) = x(i, j) - Xbar(j, 1)
Next i
Next j

'Avoid error from MMULT function
If p * p < 5000 Then
S = .MMult(.Transpose(c), c)
For i = 1 To p
For j = i + 1 To p
S(i, j) = S(i, j) / n1
S(j, i) = S(i, j)
Next j
S(i, i) = S(i, i) / n1
Next i
Else
'Multiplication of symmetric return matrix
For i = 1 To p
For j = i To p
For k = 1 To n
temp = temp + c(k, i) * c(k, j)
Next k
S(i, j) = temp / n1
S(j, i) = S(i, j)
temp = 0#
Next j
Next i
End If

End With

End Sub
 
Back
Top