Приклад формування оптимального портфеля в середовищі Microsoft Excel
Розглянемо розв'язання задачі формування оптимального портфеля цінних паперів на прикладі з використанням табличного процесора Microsoft Excel. Умова задачі. Припустимо, інвестор має дані щодо динаміки курсу акцій п'яти різних емітентів (позначимо їх як Аку, Ак2,..., Ак5 відповідно) за 12 календарних періодів, які наведені в табл. 2. Також будемо вважати, що для інвестора задача полягає в забезпеченні середньої дохідності портфеля на рівні 6 одиниць.
Таблиця 2. Динаміка курсу акцій протягом 12 періодів
Завдання. Визначити оптимальну структуру портфеля, що може бути сформований з цих акцій, який би гарантував отримання середньої дохідності на заданому рівні та при цьому забезпечував би мінімізацію ризику. Розв'язок Використаємо модель Марковіца в постановці, що передбачає мінімізацію ризику портфеля за умови фіксованого рівня дохідності. Як інструментарій пошуку розв'язку задачі пропонуємо використати табличний процесор Microsoft Excel.
Рис.1. Графік динаміки курсу акцій
Як видно з графіка, акції першої компанії мають досить стійку тенденцію до зростання курсу, четвертої — до зменшення. Курси акцій решти компаній коливаються з незначним переважанням тенденції зростання або падіння. Крок 2. Обчислення дохідності всіх акцій. Для цього спочатку слід порахувати різницю курсів акцій кожної компанії, що виникає протягом кожного з періодів. Очевидно, що таких різниць буде на одну менше, ніж даних щодо курсу, тобто одинадцять, як показано в табл. 3. Таблиця 3. Дані про динаміку дохідності акцій за періодами та середня дохідність
Для обчислення середніх значень дохідності[4] доцільно скористатися стандартною функцією =СРЗНАЧ(), аргументом якої будуть посилання на відповідні діапазони робочого листа з обчисленими значеннями дохідності акцій. Крок 3. Обчислення коваріаційної матриці для рядів, у яких наведено дохідність відповідних акцій. Для цього слід скористатися пунктом Анализ данних з меню Сервис. При цьому в діалоговому вікні, що з'явиться (його орієнтовний вигляд показано на рис.2), слід обрати пункт Ковариация і натиснути кнопку ОК.
Рис.2. Діалогове вікно Аналіз даних Після натиснення кнопки діалогове вікно Анализ данних зникне, а з'явиться вікно Ковариация, загальний вигляд якого наведено на рис. 3.
Рис. 3. Діалогове вікно Ковариация Припустимо, що обчислені дані щодо дохідності акцій перебувають у діапазоні з адресою І2:М13, причому в першому рядку цього діапазону міститься текстова інформація про назви акцій. Тоді у віконці для редагування з підписом Вхідний інтервал слід задати адресу діапазону $І$2:$М$13 (вона генерується автоматично, якщо після появи діалогового вікна виділити потрібний діапазон на робочому листі). Опцію Группирование слід залишити по столбцам і вставити галочку в Метки в первой строке. Параметри виведення результатів обчислень можна обрати з трьох варіантів: Виходной интервал — інформація виводиться на поточний лист у діапазон, верхню ліву координату якого слід задати; Новийрабочий лист — у поточній робочій книзі створюється новий робочий лист, назву якого слід задати; Новая рабочая книга — для виведення інформації створюється нова робоча книга (її назва спочатку обирається автоматично програмою, а потім може бути змінена під час збереження). Як показано на рис. 3, у нашому прикладі обрано першу опцію і визначено координату верхнього лівого кута діапазону — $0$3, що приведе до виводу результатів праворуч від діапазону даних. Натиснувши після здійснення всіх зазначених процедур кнопку ОК отримаємо результати обчислень у вигляді табл.3. Числа в таблиці округлено до другого знаку після коми. Таблиця 3. Результати роботи процедури коваріантного аналізу
Як видно з табл. 3, в результаті ми отримали трикутну матрицю, на діагоналі якої лежать значення дисперсії курсів відповідних акцій, а нижче діагоналі — показники попарної коваріації відповідних акцій. Проте для подальшого аналізу нам треба мати квадратну матрицю, в якій елементи заповнено також над діагоналлю. Це можна зробити або "вручну", або, якщо матриця достатньо велика, скористатися іншим прийомом. Спочатку скопіювати всю вихідну матрицю в інший діапазон. Потім відразу, не змінюючи позиції активної комірки, викликати команду Специальная вставка з меню Правка. В діалоговому вікні, що з'явиться, обрати опції Пропускать пустив ячейки та Транспонировать, бажано ще Значений, як показано на рис. 4. Натиснувши кнопку ОК, отримаємо повну коваріаційну матрицю, вигляд якої наведено в табл. 4.
Рис. 4. Вигляд діалогового вікна Специальная вставка для прискореного формування квадратної матриці на базі трикутної
Таблиця 4. Ковариційна матриця
Отримана коваріаційна матриця необхідна для застосування будь-якого з підходів до пошуку оптимального розв'язку моделей Марковіца або Тобіна. Отже, висвітлені три кроки обов'язкові. Далі розглянемо різні технічні способи пошуку оптимального розв'язку моделі Марковіца. Варіант 1. Обчислення оптимального розв'язку моделі Марковіца у явному вигляді. Організуємо обчислення, скориставшись формулою (4). Для ефективнішої організації роботи скопіюємо коваріаційну матрицю на окремий робочий лист, також туди перенесемо дані про середню дохідність акцій і організуємо допоміжні дані так, як зображено на рис.5.
Рис.5. Вихідні дані для розв’язку моделі Марковіца в наявній формі.
|
