I want to know how to use excel to do total variance calculation for a portfolio of assets
Four answers:
RMC
2006-07-23 08:00:23 UTC
chkmstra is correct. you need to consider the weights.
matte
2016-11-12 06:28:51 UTC
Variance Excel
chkmstra
2006-07-23 06:53:39 UTC
The previous answer assumes that you have equal weights (amount invested in each stock in your portfolio.) If you have different weights invested in your portfolio, you'll have to create a new column. In this new column have this: weight of the stock (percentage of money on that stock) times the variance of that stock. Then just add up that column.. That'll give you the portfolio variance.
Var = Sum (weights * Var of Each Stock)
qwertykph
2006-07-23 06:13:26 UTC
You want the VAR function.
Example: Column A contains the following:
5.00%
10.00%
15.00%
20.00%
25.00%
30.00%
35.00%
40.00%
45.00%
50.00%
55.00%
60.00%
65.00%
70.00%
75.00%
80.00%
85.00%
90.00%
95.00%
100.00%
The formula is : =VAR (A1: A20)
The result is 0.0875
ⓘ
This content was originally posted on Y! Answers, a Q&A website that shut down in 2021.