Value-At-Risk  
 
The aim of this program is to measure portfolio risk using Value-At-Risk (VaR) approach
If you need more theory on that issue you can look at wikipedia:
http://en.wikipedia.org/wiki/Value_at_risk
To be brief, this approach allows you to find the maximum loss on your portfolio of stocks with a given probability
First, we create a portfolio of 10 stocks 
In excel file you can change the parameters of this portfolio: number of shares of each type and confidence interval (probability)
If you want to open short position (sell short), you should put "-" before the number of shares
This file calculates maximum loss for portfolio for 1 week and for 10 weeks periods with a given probability
                                         
1. Portfolio creation STEP 1  
We choose 10 stocks and quantity of each stock 
In this example 10 stocks of large american corporations were chosen
The number of shares is chosen absolutely randomly
Stock Ticker Quantity Price Volume Share
      $ $ %
Apple AAPL 100 314.795 31 480 11.6%
Citi C 500 4.168 2 084 0.8%
General Electric GE 2000 15.94 31 880 11.8%
ExxonMobil XOM 1000 69.88 69 880 25.8%
Alcoa AA 2000 13.31 26 620 9.8%
AT&T T 1230 28.14 34 612 12.8%
American Express AXP 250 43 10 750 4.0%
Caterpillar CAT 127 84.69 10 756 4.0%
Coca-Cola KO 100 64.61 6 461 2.4%
Home Depot HD 1500 31.16 46 740 17.2%
Total       271 262 100.0%
Next, I found price performance for each stock for the last 24 periods (although it is better to use larger arrays) and calculated prices change for each period (the column next to the price)
I took weekly data (the prices at the end of the week)
Thus price changes look more interesting. You can choose any interval: minute, hour, day, quarter etc
                                         
Date AAPL Change C Change GE Change XOM Change AA Change T Change AXP Change CAT Change KO Change HD Change
18.06.2010 274.074   4.01 15.95   63.1 11.11   25.43 42.03   65.85 52.31   31.94  
25.06.2010 266.7 -2.7% 3.94 -1.7% 14.91 -6.5% 59.1 -6.3% 11.23 1.1% 24.79 -2.5% 42.67 1.5% 64.71 -1.7% 50.26 -3.9% 30.2 -5.4%
02.07.2010 246.94 -7.4% 3.79 -3.8% 13.88 -6.9% 56.57 -4.3% 10 -11.0% 24.29 -2.0% 39.42 -7.6% 59.18 -8.5% 50.05 -0.4% 27.76 -8.1%
09.07.2010 259.62 5.1% 4.04 6.6% 14.95 7.7% 58.78 3.9% 10.94 9.4% 24.83 2.2% 42.58 8.0% 64.72 9.4% 52.4 4.7% 28.26 1.8%
16.07.2010 249.9 -3.7% 3.9 -3.5% 14.55 -2.7% 57.96 -1.4% 10.41 -4.8% 24.69 -0.6% 41.38 -2.8% 63.94 -1.2% 52.37 -0.1% 27.11 -4.1%
23.07.2010 259.94 4.0% 4.02 3.1% 15.71 8.0% 59.72 3.0% 11.05 6.1% 25.54 3.4% 44.79 8.2% 69.31 8.4% 54.75 4.5% 28.25 4.2%
30.07.2010 257.25 -1.0% 4.1 2.0% 16.12 2.6% 59.68 -0.1% 11.17 1.1% 25.94 1.6% 44.64 -0.3% 69.75 0.6% 55.11 0.7% 28.51 0.9%
06.08.2010 260.091 1.1% 4.06 -1.0% 16.45 2.0% 61.97 3.8% 11.59 3.8% 26.54 2.3% 43.5 -2.6% 71.56 2.6% 56.75 3.0% 28.68 0.6%
13.08.2010 249.1 -4.2% 3.88 -4.4% 15.38 -6.5% 59.91 -3.3% 10.64 -8.2% 26.72 0.7% 41.73 -4.1% 68.01 -5.0% 55.73 -1.8% 27.31 -4.8%
20.08.2010 249.64 0.2% 3.75 -3.4% 15.03 -2.3% 58.89 -1.7% 10.57 -0.7% 26.45 -1.0% 40.76 -2.3% 68.86 1.2% 55.3 -0.8% 28.17 3.1%
27.08.2010 241.62 -3.2% 3.76 0.3% 14.71 -2.1% 59.8 1.5% 10.32 -2.4% 26.94 1.9% 40.91 0.4% 65.9 -4.3% 56.16 1.6% 28.74 2.0%
03.09.2010 258.77 7.1% 3.91 4.0% 15.393 4.6% 61.32 2.5% 10.88 5.4% 27.44 1.9% 41.8 2.2% 70.08 6.3% 57.56 2.5% 29.85 3.9%
10.09.2010 263.41 1.8% 3.91 0.0% 15.98 3.8% 61.2 -0.2% 11.17 2.7% 27.83 1.4% 40.19 -3.9% 71.26 1.7% 58.52 1.7% 29.68 -0.6%
17.09.2010 275.37 4.5% 3.95 1.0% 16.29 1.9% 60.78 -0.7% 11.172 0.0% 28.17 1.2% 41.37 2.9% 73.18 2.7% 57.56 -1.6% 29.89 0.7%
24.09.2010 292.32 6.2% 3.904 -1.2% 16.66 2.3% 61.75 1.6% 12.2 9.2% 28.58 1.5% 43.13 4.3% 79.73 9.0% 58.62 1.8% 31.64 5.9%
01.10.2010 282.52 -3.4% 4.09 4.8% 16.36 -1.8% 62.54 1.3% 12.23 0.2% 28.81 0.8% 41.78 -3.1% 78.22 -1.9% 59.12 0.9% 31.82 0.6%
08.10.2010 294.07 4.1% 4.19 2.4% 17.12 4.6% 64.38 2.9% 12.89 5.4% 28.22 -2.0% 37.99 -9.1% 80.37 2.7% 59.41 0.5% 31.89 0.2%
15.10.2010 314.74 7.0% 3.95 -5.7% 16.3 -4.8% 65.19 1.3% 13.13 1.9% 28.33 0.4% 39.09 2.9% 79.75 -0.8% 59.94 0.9% 30.7 -3.7%
22.10.2010 307.47 -2.3% 4.11 4.1% 16.055 -1.5% 66.34 1.8% 12.72 -3.1% 28.29 -0.1% 39.03 -0.2% 78.33 -1.8% 61.61 2.8% 31.48 2.5%
29.10.2010 300.98 -2.1% 4.17 1.5% 16.02 -0.2% 66.49 0.2% 13.14 3.3% 28.52 0.8% 41.46 6.2% 78.6 0.3% 61.32 -0.5% 30.9 -1.8%
05.11.2010 317.13 5.4% 4.49 7.7% 16.73 4.4% 70 5.3% 14 6.5% 29.27 2.6% 44.07 6.3% 83.54 6.3% 62.58 2.1% 31.92 3.3%
12.11.2010 308.03 -2.9% 4.29 -4.5% 16.25 -2.9% 70.99 1.4% 13.49 -3.6% 28.46 -2.8% 42.7 -3.1% 81.04 -3.0% 62.92 0.5% 31.44 -1.5%
19.11.2010 306.73 -0.4% 4.268 -0.5% 16.22 -0.2% 70.54 -0.6% 13.38 -0.8% 28.32 -0.5% 42.75 0.1% 83.97 3.6% 64.32 2.2% 31.22 -0.7%
26.11.2010 314.795 2.6% 4.168 -2.3% 15.94 -1.7% 69.88 -0.9% 13.31 -0.5% 28.14 -0.6% 43 0.6% 84.69 0.9% 64.61 0.5% 31.16 -0.2%
We need this table for further calculations
                                         
2. Choose confidence interval STEP 2  
In the blue cell you can enter a confidence interval: from 0.90 to 0.99
I chosed 0.95
This figure is the probability that the potential losses for portfolio will not be higher than VaR, which we will determine in the end
from 90% to 99.9%
Enter confidence interval   0.95
                                         
3. Calculation of K-coefficient STEP 3  
K-coefficient is the inverse value of standart normal distribution
We use NORMSINV function to calculate this
K-coefficient =  1.644854
                                         
4. The calculation of average weekly change and standart deviation STEP 4  
1) calculate average weekly change for each stock
2) Calculate standart deviation for each stocks using function STDEV
  AAPL C GE XOM AA T AXP CAT KO HD
Average weekly change 0.687% 0.233% 0.086% 0.481% 0.914% 0.455% 0.200% 1.199% 0.941% -0.051%
Standart deviation 4.165% 3.693% 4.313% 2.750% 5.098% 1.725% 4.590% 4.583% 1.999% 3.426%
5. Calculation of 1 week volatility multipliers STEP 5  
Just following the steps:
1) Make links to the number of shares in the row 1
2) Make links to stock prices in the row 2
3) Calculate the value of position: price x quantity
4) Make links to weekly volatility
5) Calculate volatility multiplier: weekly volatility x value of position
6) Make links to average weekly change
7) Calculate average weekly change in $: avergae weekly change x value of position
Stock AAPL C GE XOM AA T AXP CAT KO HD
Number of shares 100 500 2000 1000 2000 1230 250 127 100 1500 1)
Share price, $ 314.8 4.2 15.9 69.9 13.3 28.1 43.0 84.7 64.6 31.2 2)
Value of position 31479.5 2084.0 31880.0 69880.0 26620.0 34612.2 10750.0 10755.6 6461.0 46740.0   3)
Weekly volatility 4.17% 3.69% 4.31% 2.75% 5.10% 1.72% 4.59% 4.58% 2.00% 3.43% 4)
Volatility multiplier 1311.2 77.0 1374.9 1921.7 1357.1 596.9 493.4 492.9 129.1 1601.4 5)
Average weekly change. % 0.69% 0.23% 0.09% 0.48% 0.91% 0.46% 0.20% 1.20% 0.94% -0.05% 6)
Average weekly change. $ 216.1 4.9 27.4 336.2 243.2 157.6 21.5 129.0 60.8 -23.6 7)
                                         
6. Calculation of 10 weeks volatility multipliers STEP 6  
1) Calculate volatility multipliers for 10 weeks: weekly volatility multiplier x square root of 10
2) Calculate average prices changes for 10 weeks: average weekly change x 10
10 weeks volatility multiplier. $ 4146.455 243.347 4347.924 6077.06 4291.4 1887.6 1560.3 1558.8 408.36 5064.2
Average 10 weeks change. $ 2161.31 48.5267 274.2652 3362.186 2432.3 1576.4 215 1289.8 608.24 -236.17
                                         
7. Find correlations between the stocks STEP 7  
Create correlation matrix in which you calculate correlations between all stocks
For correlations calculations we use CORREL function
Correlation matrix
  AAPL C GE XOM AA T AXP CAT KO HD
AAPL 1.00 0.75 0.69 0.90 0.97 0.75 -0.04 0.91 0.82 0.83
C 0.75 1.00 0.66 0.82 0.84 0.52 0.33 0.72 0.66 0.62
GE 0.69 0.66 1.00 0.64 0.74 0.77 0.12 0.81 0.69 0.72
XOM 0.90 0.82 0.64 1.00 0.93 0.74 0.09 0.89 0.89 0.75
AA 0.97 0.84 0.74 0.93 1.00 0.78 0.06 0.94 0.86 0.79
T 0.75 0.52 0.77 0.74 0.78 1.00 -0.11 0.90 0.91 0.72
AXP -0.04 0.33 0.12 0.09 0.06 -0.11 1.00 0.05 0.00 -0.08
CAT 0.91 0.72 0.81 0.89 0.94 0.90 0.05 1.00 0.95 0.76
KO 0.82 0.66 0.69 0.89 0.86 0.91 0.00 0.95 1.00 0.65
HD 0.83 0.62 0.72 0.75 0.79 0.72 -0.08 0.76 0.65 1.00
                                         
9. Create two columns with 1 week and 10 weeks volatility multipliers STEP 8  
Make links to weekly and 10-weeks volatility multipliers which we calculated in steps 5 and 6, respectively 
We need this table for calculations with excel functions
Volatility ($) 1 week 1 weeks
AAPL 1311.22 4146.45
C 76.95 243.35
GE 1374.93 4347.92
XOM 1921.74 6077.06
AA 1357.05 4291.38
T 596.90 1887.56
AXP 493.40 1560.26
CAT 492.93 1558.79
KO 129.13 408.36
HD 1601.44 5064.20
                                         
9. Calculate VaR for 1 week and 10 weeks STEP 9  
1) Calculate portfolio volatility
All calculation are made with a single formula but if week look at each action separately it would be the following: 
- Multiply two matrices: volatility multipliers and correlations matrix
- Multiply the result by weekly volatility matrix calculated in step 8
- Calculate the square root from the result
For matrix multiplication use function MMULT
Make similar calculations for 10 weeks period
- Multiply matrices: 10 weeks volatility multiplier and correlation matrix
- multiply the result by 10 weeks volatility matrix calculated in step 8
- calculate the square root of the result
2) Calculate average change for portfolio
Simply sum up average weekly changes of all stocks calculated in step 5
Similarly sum up 10 weeks changes of the stocks calculated in step 6
3) Calculate VaR 
Calculate VaR using formula:
Average change of portfolio - ( K-coefficient x absolute value of portfolio volatility)
The calculation of absolute value with ABS function
  1 week 10 weeks
Portfolio volatility 8089.673 25581.8 1)
Average portfolio change 1173.199 11732 2)
Portfolio VaR -12133.1 -30346.3 3)
What does the result mean  
The loss of your portfolio will be not more than -12 133 $ or -4.47% for 1 week; and not more than -30 346 $ or -11.19% for 10 weeks with  0.95 probability
Portfolio value (just to remind)= 271 262 $
If any questions -  iamanalista@gmail.com