E-mail:

# How To Calculate Variance Covariance Matrix?

Hi board,

I want to calculate the yearly standard deviation of a portfolio with a few hundred stocks. I plan to first calculate the variance covariance matrix for the portfolio's stocks and then use matrix algebra (like here http://www.fenews.com/fen39/back_to_...n-formulas.htm) to arrive at the porfolio's standard deviation.

I have a large array with stock returns that looks like this:

StockID;Year;Month;Return;Portfolio
123456;1990;1;0.05;1
234567;1990;1;0.06;1
345678;1990;1;0.02;2
...

If there would be 100 different stock IDs, I would like to calculate a 100x100 matrix that contains the covariances of the returns of the stocks of that portfolio for a specific year.

I did some experiments with array formulas like {=cov(if(stockID=...);if(stockID=...))} but that didn't work. I couldn't find any plugins that would help. Any ideas?

## Similar Excel Tutorials

Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Calculate Someone's Age in Excel
How to automatically calculate someone's age using Excel.  This method is simple and will update every year so tha ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...
FV Function - Get the Future Value in Excel
The Future Value function (FV) in Excel will return the future value of an investment based on a particular intere ...

Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the
WEEKNUM UDF for Excel Versions That Don't Contain This Function - UDF
- This is a WEEKNUM UDF for Excel. This allows you to determine the number of the week in the year on which a particular
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return

## Similar Topics

Hi guys,

I need to calculate standard deviation for a portfolio with 31 stock. I have a column with the stock names (column B), their mean return (column F), standard deviation (column G) and 31*31 correlation matrix.

Is there a convenient way to calculate this stuff?

Hi, i'm trying to plot the Markowitz efficient frontier in Excel. I have returns and other data for 20 stocks.

I use solver to find the optimal weights of the 20 stocks for the the lowest variance portfolio.

I need to then find the weights of the stocks from this point to the highest possible return portfolio.

Finding the portfolio with the smallest variance and with the highest return is fine with solver, i just use the min and max functions.

How do i find the points in between?

For example, the return on my minimum variance portfolio is 1, the return on my max return portfolio is 100. Right now, i would need to run optimizer with minimum variance for the returns of 2,3,4....all the way to 99 to find those points.

Thanks

I am trying to calculate a covariance matrix in VBA. I have managed to get my data into an array "datarng" which has 10 columns and 100 rows. I want to calculate the covariance matrix of my dataset so that I will have a new array 10x10 by calculating the covariance between each column in the original array. can anyone help?

I am trying to calculate the covariance of a stocks return with the entire portfolio using the COVAR() command... is there a way to exclude text from an arraywithout altering the data ? It would be particlularly useful when using the MMULT command as well.

cheers for any ideas

Hi there,

This is my first post (hopefully of many).

I need assistance in creating 100 random portfolios from a list of 10 assets. I have the return history of the 10 assets and I'm able to calculate the mean and standard deviation of each asset as well as the covariance/correlation between each of the 10 assets. I want to choose to portfolio with the least variance as well the portfolio with the lowest and highest return.

Would it also be possible to introduce a limit that constrains the maximum allocation to each asset at 25%?

Many thanks for any assistance on this matter.

Regards,
Grant Hogan

I'm trying to create individual charts of the performance of over 100 separate stocks going back to 1990. It's a line chart starting at 0% at the left axis (1990) and the line moves up or down based on the stock's performance until September 2013. The math for the individual points on the line chart is pretty simple: (closing price at any point in time)/(closing price in January 1990)-1.

I'm using =INDIRECT in my primary worksheet to do the math and create the individual graphs, which pulls raw closing stock price data from another spreadsheet. The problem is that some stocks weren't trading back in 1990 and in those cases, the math is dividing the current stock price by zero. I want to avoid manually finding the first trade date for each stock (which is the denominator in every cell for a given stock) by automating the process.

So my question: Is there a way, using the =INDIRECT function to have the formula automatically calculate performance back to the first non-zero point in time (i.e. when the stock first started trading)? The formula would need to be sophisticated enough to know which cell was the first month of non-zero data.

I am trying to learn VBA and started reading Wallenbach's book and I thought the best way to get up to speed fast was to work on some real problems.

So this is what I am trying to do.

I have a list of about 1000 stock price returns in the following format

jan 1 2008 .033
jan 2 2008 .01
jan 3 2008 -.002
jan 4 2008 -.0033
jan 5 2008 .04

What I want to do is calculate in VBA a moving 3 day standard deviation of returns. So I want the first calculation to include jan 1 - jan 3 and place the answer in the cell to the right of jan 3 return. Then move down 1 row and calculate the standard deviation using jan 2 - jan4 and place the answer in the cell to the right of jan 4 return...and so on until the last cell.

Thanks

I trying to calculate the covariance and variance of two sets of monthly returns.

I think excel will give me the montly covar and var and I need to annualize these numbers. Is there a way to do this in excel?

Hi,

I am trying to compute the monthly standard deviation from the daily returns across large data of a stock. Can anyone help me with the vba code to calculate automatically. I want the vba macro not the functions for the project.

Thanks

Hi everyone,
I need to perform a number of matrix calculations in VBA and intend to use Excel Worksheetfunctions for that. Unfortunately, matrix subtract is only know to .net and excel only knows the following matrix formulas:
.MMULT()
.MINVERSE()
.TRANSPOSE()
.DETERM()
I would like to subtract one matrix from another. My not very elegant workaround is the following:

ReDim subtract(1 To n, 1 To n)
For j = 1 To n
For i = 1 To n
subtract(j, i) = matrix1(j, i) - matrix2(j, i)
Next i
Next j

Both matrices are identical in size (req.) and symmetrical (if it matters). Is there a way of entering a non-descript array formula to calculate the difference of the two matrices?

Hello,

I have a dynamic range that I need to be able to calculate the standard deviation for. The dynamic range sometimes has blank cells and #N/A cells which I do not want included in the calculation. However, there are sometimes cells with the number 0 in it (instead of blank) which I do want to include. I have come up with an array formula, but the standard deviation it calculates is not correct. The formula is:

=STDEV(IF(ISNUMBER(A3:A20000)*ISNUMBER(A3:A20000),1,0))

*This is an array formula calculated by pressing CTRL+SHIFT+ ENTER

The number it calculates is 0.43, however, this is incorrect. If you just calculate the standard deviation of the actual data points in excel (cells A138:A5055 in this case) : STDEV(A138:A5055), it returns 0.17. This is the correct standard deviation. Could someone please help me with this?

Kind Regards,

Maani

http://www.4shared.com/file/12409801...c74/Book1.html

Hey guys,

I am trying to simulate some stock return data with the random number generator.. I have 7 stocks that I want to simulate with a multivariate normal distribution where the inputs are the mean vector and the covariance matrix..

The random number generator that comes with Excel does not have a feature for multivariate normal distributions and I am trying to avoid having to buy a new program or add-in... Is there any way to do this? maybe using vba code?

I want to automatically import wesbite data in Excel cells, to perform
calculations. For example, I could have a portfolio in Excel that does
complex calculations. The portfolio needs to update stock prices every day
(and other numerical information). Don't want to manually cut, format and
paste every day for dozens of stocks and other values. Want to link up the
stock price in Yahoo (or from Fidelity Investments) to the cell
automatically, such that every day I can open Excel and ask it to refresh all
the prices at once.

Is this possible? Thanks for your help.

Tony from Houston

I was inspired by a recent E-Trade commercial to build a financial portfolio
pie chart using Excel. The chart I have in mind would show the percent
breakdown of asset allocation (e.g., cash, large cap stocks, small cap stock,
etc). This is the data table format I had in mind:
Financial Institute (column 1), Allocation (column 2), and Amount (column 3)
e.g.,
BofA Cash \$1600
Fidelity Large Cap \$1000
Fidelity Cash \$101

As I update my allocation, say, move some cash to small cap stocks, the
chart would automatically display my new portfolio breakdown.

The problem I'm running into is this: when I try to create such a pie chart,
each row (series) is created as its own slice (e.g., BofA cash, Fidelity
Large Cap, Fidelity cash) rather than grouping a single "cash" category. Is
it possible for Excel to dynamically look for cells with the same content
(say, "Cash") and add the corresponding amount to form a single slice?

hi all,

i have a range of numbers in C5:AL5
Not all of the cells are filled in, and some that are filled in are blank.

In AM5 I calculate the average of the data
and in AN5 the standard deviation where the value in the cells in the range are >0

Is it possible to calculate a standard deviation in another cell which excludes those values in the range which are 3 standard deviations away from the value in cell AN5.
Therefore, the standard deviation with the outliers.

If there is another way of doing this, i would be grateful for your help.
Luce

Heres the problem. I have a list of nominal rates (percentages) and need to use them to make colums of "real rates".
What formula do I use and how do I enter it?

Nominal Returns.................................................Real Returns

Large Stocks...Small Stocks....Inflation........Large Stocks...Small Stocks
11.62%................0.30%...............-1.50................?...........................?
37.49%................22.03%.............-2.03................?...........................?

I know the answer to the first "Large Stock Real Return" should be 10.12%. The equation I used in my calculater is...
(1+.1162/1-.0150) -1 =.1012
(1+nominal / 1+interest rate) -1

Hi guys I am having serious problems with a very large database I need to use. I am trying to calculate standard deviation but my results are being thrown by 0s in the data, is there a formula that ignores the 0 and still calculates the standard deviation, I also need to do this for mean formula aswell, any help would be greatly appreciated.

Hi everyone,

I'm just a newbee to VBA/Excel. For a project, I have to write a function to calculate the eigenvectors and eigenvalues of a matrix using the "Power Method". I've tried searching the web for some help but there's not much out there. Essentially, I want to input a matrix from the excel spreadsheet along with a tolerance level (i.e. two inputs).

The function then calculates the covariance matrix, call this M (m x m), of the input data. You then make an initial guess of the eigenvector, let's say this is a vector of 1's (m x 1) (call this x), you multiply the two together to get

y=Mx

You then calculate beta which is the element of y with the largest modulus. And, recalculate x as

x=(1/beta)y

and then calculate new y, y=Mx

This is done iteratively until the difference between the old x and new x is less than the tolerance level.

The normalised x, i.e. (1/sqrt(sum(x^2)))*x , call this v, is the first prinicipal component and the last value of beta is the associated eigenvalue.

A new M is calculated as Mnew = M-beta*v*transpose(v)

And, the whole procedure is repeat for Mnew to get the second prinicipal component and associated eigenvalue. The is done m times.

I want to output all the eigenvectors (prinicipal components) and eigenvalues to some location in the spreadsheet.

I would be extremely grateful if someone could assist me with writing this function or providing some pointers.

Regards,
John

In 3 rows (B1:B3) and in 5151 columns (till column GPD) I got alle the combinations which sum up to 1. (Thanx for the help in an other thread).

The first return I calculated in the following way.
=MMULT(TRANSPOSE(B1:B3);H12:H14)-1

The Standard Deviation is calculated in this way.
=SQRT(MMULT(MMULT(TRANSPOSE(B1:B3);C24:E26);B1:B3))

See attached excel sheet.

It would be very helpful if a VBA script can be written which automatically calculates the 5151 returns and the 5151 standard deviation.

I work in finance and often use formulas to calculate variances between two figures. Many of my spreadsheets are quite large at this point and there are occasional zeroes in the data. This creates #DIV/0! errors in my variance formulas.

For example, let's say I have the following formula to calculate year-over-year variance for two numbers:

EXAMPLE 1 =B1/A1-1

This works fine if the value in A1 is not zero. So when I see a #DIV/0! error I go into the formula and change it to:

EXAMPLE 2 =IF(ISERROR(B1/A1-1),"n/a",B1/A1-1)

This will show n/a if the formula is equating to an error.

Since I have so many workbooks with variance calculations and the IF-ISERROR formula is not easy to type out quickly, is there a way to use VBA to modify the formula in the ActiveCell to bring in the IF-ISERROR syntax? Maybe something like this:

Take original formula from Example 1 above, add "IF(ISERROR(" before it, then add the n/a part, and finally put the origianl formula at the end and enclose in a closing parenthesis.

Thanks in advance for any tips!

I have to write a VBA function that will calculate the inverse of an arbitrary 2X2 or 3X3 matrix, involving the adjoint of the matrix. The function should flag any unsuitable matrices that are not 2X2 or 3X3 and should stop. I can't use the MINVERSE function in Excel.

How to plot efficient frontier from sets of data containing expected return,
average return, variance, standard deviation, covariance and correlation ?

I have return streams going down colums B through I
Dates are down column A
I want colum J to give me the weighted return for the streams of returns.
But it must re-allocate to funds that have performance and not count funds
that do not. So the Dec-01 portfolio return should be 50% fund 2, and 50%
Fund 3, and 0% fund 1
the allocations are in row 45 and should be flexible to be adjusted as
needed.
THANKS!

Fund1 Fund2 Fund 3 Portfolio
Dec-01 -1.88% 0.36% -0.51%
Jan-02 1.51% 1.85% 1.12%
Feb-02 0.06% 0.16% 0.07%
Mar-02 -0.66% 0.63% -0.01%
Apr-02 1.00% 1.20% 0.73%
May-02 0.99% 1.40% 0.80%
Jun-02 1.27% 1.20% 0.82%
Jul-02 1.22% 0.06% 0.43%
Aug-02 1.17% 1.02% 0.73%
Sep-02 3.27% 1.70% 1.66%
Oct-02 4.51% 1.03% 1.85%
Nov-02 2.28% 1.50% 1.26%
Dec-02 1.38% 0.79% 0.72%
Jan-03 2.80% 1.58% 1.46%
Feb-03 1.31% 1.00% 0.77%
Mar-03 1.05% 0.18% 0.41%
Apr-03 3.20% 0.71% 1.30%
May-03 3.13% 0.58% 1.24%
Jun-03 2.13% -1.32% -2.54% -0.58%
Jul-03 3.33% -0.79% -1.94% 0.20%
Aug-03 1.79% -0.40% -0.73% 0.22%
Sep-03 2.98% 2.16% 3.12% 2.75%
Oct-03 1.35% 3.83% 1.38% 2.19%
Nov-03 2.27% 1.92% 1.12% 1.77%
Dec-03 1.05% 1.36% 1.04% 1.15%
Jan-04 1.32% 2.21% 0.88% 1.47%
Feb-04 0.20% 0.07% -0.18% 0.03%
Mar-04 1.19% 0.61% 0.77% 0.86%
Apr-04 1.15% 0.55% -0.10% 0.53%
May-04 -0.85% -0.97% -1.82% -1.21%
Jun-04 -0.76% 0.03% -0.74% -0.49%
Jul-04 0.92% 0.74% 1.25% 0.97%
Aug-04 0.65% 0.59% 1.32% 0.85%
Sep-04 2.87% 0.41% 0.20% 1.16%
Oct-04 1.00% 2.11% -0.42% 0.90%
Nov-04 1.65% 2.15% 0.85% 1.55%
Dec-04 1.79% 1.40% 0.91% 1.37%

Allocations 33.3% 33.3% 33.3% 100.00%

Hi all,
I am having hardtime to calculate the weighted standard deviation. I have different values in column A and its weight in Colum B. How can i calculate WSD of the values considering the weightage of each value. That would be great if anyone could suggest the solution. Thanks in advance.

Rohb

Value Weight 0.15 0.22 0.65 0.54 2.5 0.14 6 0.04 12 0.03 23.5 0.02 77.5 0.00 124 0.00 Sum
weight

1.00

I have searched all over and can not find a thread that addresses my issue. If I missed it - please help and direct me to it. My issue is that someone helped me on this forum with the following formula which works great. However, I need to copy it down at least 200 rows. Problem is when I've copied it down - row 1 stays as row 1. I need that to increment +1 because right now I am going in and manually changing the formula I copied down. There has got to be a better way.

[I even learned about macros and tried Range("P1:P200").Formula = "=INDEX(Matrix!\$B\$8:\$IV\$65500,1,MATCH(\$B\$1,Matrix!\$1:\$1,0))" but had the same problem. It copied 1 all the way down.]

Currently when copied - formula looks like this:
=INDEX(Matrix!\$B\$8:\$IV\$65500,1,MATCH(\$B\$1,Matrix!\$1:\$1,0))
=INDEX(Matrix!\$B\$8:\$IV\$65500,1,MATCH(\$B\$1,Matrix!\$1:\$1,0))
=INDEX(Matrix!\$B\$8:\$IV\$65500,1,MATCH(\$B\$1,Matrix!\$1:\$1,0))

Desired Result when copied - to look like this:
=INDEX(Matrix!\$B\$8:\$IV\$65500,1,MATCH(\$B\$1,Matrix!\$1:\$1,0))
=INDEX(Matrix!\$B\$8:\$IV\$65500,2,MATCH(\$B\$1,Matrix!\$1:\$1,0))
=INDEX(Matrix!\$B\$8:\$IV\$65500,3,MATCH(\$B\$1,Matrix!\$1:\$1,0))

Any help would be immensely appreciated.
Cindy