На другому аркуші - ковариационная матриця. Саме тут будемо розраховувати оптимальні ваги активів в нашому портфелі. А на третій сторінці підрахуємо очікувану прибутковість і ризик по окремих активів.
Обмовимо методологію розрахунків: розраховуємо показники на підставі зміни тижневих даних (наприклад, за останні два роки), які потім наводимо до річних. Для цього звертаємося до сайту Finam.ru (сервіс Експорт даних).
У Секції ринку вибираємо біржу, на якій торгується цікавить нас папір. У розділі Контракт вибираємо саму папір. Далі в Періодичності ставимо тиждень і дати - з якого числа і за яку хочемо отримати історію. Натискаємо Отримати і викачуємо на свій комп'ютер текстовий файл, який відкриваємо в Excel. При відкритті програма запитає, як саме його потрібно відкрити. Вказуємо необхідність поділу тексту на стовпці (в діалозі ставимо галочку навпроти Comma).
У відкритому файлі знадобиться лише стовпець з ціною закриття. Можливо, через налаштувань комп'ютер може прийняти ці значення як текстові Щоб вирішити проблему, потрібно замінити роздільник «точки» на «кому»: викликаємо сервіс по комбінації клавіш Ctrl + F і вказуємо в закладці Replace, що на що міняти, натискаємо на Replace All. Далі копіюємо ціни закриття і вставляємо їх в третій лист нашого файлу розрахунків під назвою емітента, а в сусідньому стовпці вводимо формулу розрахунку зміни тижневої прибутковості:
= (B3 - B2) / B2
= (B4 - B3) / B3
= (B5 - B3) / B3
Дублюємо формулу в усі осередки, поки не закінчаться дані цін закриттів. Так ми отримуємо зміну ціни в абсолютних значеннях; переводити в% не варто. Під цим стовпцем вводимо формулу очікуваної прибутковості цього активу:
= AVERAGE (C3: C104) * 52
Одночасно переводимо середньотижневим значення в річне, помноживши отриманий результат на 52 (на рік - 52 тижні) Після цього вираховуємо дисперсію прибутковості, вводячи формулу:
= VAR (C3: C104) * 52
Наводимо отримане значення до річного.
Далі заповнюємо другий лист, розрахунковий. Вертикально, навпаки емітента вводимо його очікувану прибутковість Потім навскоси, навпаки емітента вписуємо в осередку його дисперсію Для цієї назви емітентів краще розмістити і по вертикалі, і по горизонталі і на перетині одного і того назви вбити показник. У нашому прикладі для Аерофлоту призначена осередок С2, а для АвтоВАЗу - D3.
Потім потрібно розрахувати значення коваріації. У нашому випадку це показник тісноти зв'язку між прибутковістю цінних паперів, що входять в портфель На відміну від очікуваної прибутковості портфеля його ризик не є средневзвешаной величиною дисперсій входять до нього паперів. Справа в тому, що різні активи по-різному реагують на зміну кон'юнктури ринку. В результаті дисперсії прибутковості різних паперів в ряді випадків будуть погашати один одного, що призведе до зниження ризику портфеля. Останній залежить від того, в якому напрямку змінюються дохідності входять до нього активів при зміні кон'юнктури ринку і в якій мірі. Тому, формуючи портфель, інвестору необхідно знати, яким чином буде коригуватися прибутковість одного активу зі зміною дохідності іншого. Ступінь взаємозв'язку між прибутковістю двох цінних паперів можна визначити за допомогою коваріації. Тому в осередку на перетині двох активів вставляємо її формулу:
= COVAR (C3: C104; E3: E104)
