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