Value-At-Risk  
 
Данный механизм предназначен для оценки рисков портфеля по методике Value-At-Risk (VaR)
Если вам необходимо больше теоретического материала на эту тему можете посмотреть его в википедии по ссылке:
http://ru.wikipedia.org/wiki/Value_At_Risk
Если говорить вкратце, этот механизм помогает определить максимальные убытки по портфелю при заданной вероятности
Вначале мы формируем небольшой портфель из акций 10 компаний. 
В excel файле есть возможность менять параметры портфеля: количество каждого вида бумаг и доверительный интервал
Если необходимо открыть короткую позицию (зашортить), то количество бумаг указывается со знаком " - "
Механизм выдаст в качестве результата максимальные убытки по портфелю за 1 неделю и за 10 недель с заданной вероятностью 
                                         
1. Формирование портфеля ШАГ 1  
Выбираем акции 10 различных компаний и количество бумаг для покупки
В данном случае выбраны акции 10 крупных и небезызвестных американских корпораций
Количество каждой из бумаг выбрано абсолютно произвольно
Акция Тикер Кол-во Цена Объем Доля 
    штук $ $ %
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%
Всего       271 262 100.0%
Далее я загрузил динамику стоимости акций каждой из компаний за последние 24 периода (хотя лучше взять большее количество периодов) и рассчитал изменения цен за каждый период (столбец рядом с ценой)
Причем были взяты недельные данные (цена закрытия торгов в конце недели)
Это сделано для того, чтобы показатели динамики и волатильности смотрелись интереснее. Вообще можно выбирать любой интервал: минута, час, день, квартал, год и т.д.
                                         
Дата AAPL Измнен. C Измнен. GE Измнен. XOM Измнен. AA Измнен. T Измнен. AXP Измнен. CAT Измнен. KO Измнен. HD Измнен.
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%
Эти данные понадобятся нам для дельнейших расчетов
                                         
2. Выбор доверительного интервала ШАГ 2  
В голубую ячейку необходимо ввести доверительный интервал: от 0.90 до 0.99
Я выбрал 0.95
Это цифра - вероятность того, что возможные убытки по портфелю не превысят величину VaR, которую мы определим в самом конце
от 90% до 99.9%
Введите доверительный интервал 0.95
                                         
3. Рассчет коэффицента К ШАГ 3  
Коэффициент К - это обратное значение стандартного нормального распределения.
Мы определяем данный коэффициент с помощью функции НОРМСТОБР
Коэффициент К = 1.644854
                                         
4. Рассчет среднего изменения за неделю и стандартного отклонения ШАГ 4  
1) рассчитываем среднее недельное изменение по каждой бумаге 
2) рассчитываем стандартное отклонение по каждой бумаге при помощи функции СТАНДОТКЛОН
  AAPL C GE XOM AA T AXP CAT KO HD
Среднее недельное изменение  0.687% 0.233% 0.086% 0.481% 0.914% 0.455% 0.200% 1.199% 0.941% -0.051%
Стандартное отклонение 4.165% 3.693% 4.313% 2.750% 5.098% 1.725% 4.590% 4.583% 1.999% 3.426%
5. Рассчет недельных компонент волатильности ШАГ 5  
Просто выполняем следующие шаги
1) Делаем ссылки на количество акций в первой строке
2) Делаем ссылки на цены акций во второй строке
3) Рассчитываем стоимость позиции: цена х количество
4) Делаем ссылки на недельную волатильность
5) Рассчитываем компоненту недельной волатильности: недельная волатильности х стоимость позиции
6) Делаем ссылки на среднее изменение за неделю
7) Рассчитываем среднее изменение за неделю в $: среднее изменение за неделю х стоимость позиции
Инструмент AAPL C GE XOM AA T AXP CAT KO HD
Число акций 100 500 2000 1000 2000 1230 250 127 100 1500 1)
Цена акции, $ 314.8 4.2 15.9 69.9 13.3 28.1 43.0 84.7 64.6 31.2 2)
Стоимость позиции 31479.5 2084.0 31880.0 69880.0 26620.0 34612.2 10750.0 10755.6 6461.0 46740.0   3)
Недельн. волатильности 4.17% 3.69% 4.31% 2.75% 5.10% 1.72% 4.59% 4.58% 2.00% 3.43% 4)
Комп. недельн. волатильности 1311.2 77.0 1374.9 1921.7 1357.1 596.9 493.4 492.9 129.1 1601.4 5)
Средн. нед. измен. % 0.69% 0.23% 0.09% 0.48% 0.91% 0.46% 0.20% 1.20% 0.94% -0.05% 6)
Средн. недельн. измен. $ 216.1 4.9 27.4 336.2 243.2 157.6 21.5 129.0 60.8 -23.6 7)
                                         
6. Рассчет 10-недельных компонент волатильности ШАГ 6  
1) сначала рассчитываем компоненту десятинедельной волатильности: компонента недельной волатильности х квадратный корень из 10
2) далее рассчитываем среднее изменение цены за 10 недель: среднее изменение цены за неделю х 10 
Комп. 10-недельной. волат. $ 4146.455 243.347 4347.924 6077.06 4291.4 1887.6 1560.3 1558.8 408.36 5064.2
Средн.измен. за 10 недель. $ 2161.31 48.5267 274.2652 3362.186 2432.3 1576.4 215 1289.8 608.24 -236.17
                                         
7. Находим корреляции между ценами акций ШАГ 7  
Строим корреляционную матрицу, в которой просто находим корреляцию между всеми акциями
Для нахождения корреляции используем функцию КОРРЕЛ
Корреляционная матрица
  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
                                         
8. Строим два столбца с недельной и 10-недельной компонентами волатильностями ШАГ 8  
Просто делаем ссылки на недельную и 10-недельную компоненты волатильности, которые мы рассчитали в шагах 5 и 6 соответсвенно
Это таблица нужна для удобства расчетов с помощью функций excel
Волатильность ($) Недельн. 10-недел.
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. Находим VaR для 1 недели и для 10 недель ШАГ 9  
1) находим волатильность портфеля
Все действие записано одной формулой, но если разложить его по шагам, то последовательность следующая:
- умножаем две матрицы: компонента недельной волатильности и корреляционную матрицу
- умножаем полученный результат на матрицу с недельной волатильностью, полученную в 8 шаге
- вычисляем квадратный корень из полученного результата
Для перемножения матриц пользуемся функцией МУМНОЖ
Аналогичные рассчеты производим для 10-недельного периода
- перемножаем матрицы компоненты 10-недельной волатильности и коррелционную матрицу
- полученный результат умножаем на матрицу с 10-недельной волатильностью, полученную в 8 шаге
- из полученного результата вычисляем квадратный корень
2) Рассчитываем среднее изменение портфеля
Просто суммируем среднее недельное изменение каждой бумаги, вычисленное в шаге 5
Аналогично суммируем 10-недельное изменение каждой акции, вычисленное в шаге 6
3) Вычисляем VaR 
Вычисляем VaR по формуле:
Среднее изменение портфеля - ( Коэффициент К х абсолютное значение волатильности портфеля)
Абсолютное значение вычисляем при помощи функции ABS
  1 неделя 10 недель
Волатильность портф. 8089.673 25581.8 1)
Средн. измен. портф. 1173.199 11732 2)
VaR портфеля -12133.1 -30346.3 3)
Как интерпретировать результат:  
Потери по вашему портфелю окажутся не больше, чем  -12 133 $ или -4.47% за 1 неделю; и не больше  -30 346 $ или -11.19% за 10 недель с вероятностью 0.95
Совокупная стоимость портфеля (для напоминания) 271 262
Если есть вопросы -  iamanalista@gmail.com