Сучасні комп’ютерні технології в економічній науці та практиці

1. Сучасні комп’ютерні технології в економічній науці та практиці

Доц. Кривенко В.І.
Кафедра електроніки та
обчислювальної техніки
1

2. Тема: Інформаційні технології імовірнісного та статистичного моделювання економічних процесів

1.
2.
3.
4.
5.
6.
7.
Питання
Генерація випадкових величин
Розрахунок числових характеристик розподілу
ймовірностей
Рішення задач статистичного аналізу в табличному
процесорі Excel
Технологія рішення задач дисперсійного аналізу
Рішення задач кореляційного аналізу
Технологія рішення задач регресійного аналізу
Технологія рішення задач фінансової математики в
табличному процесорі Excel
2

3. 1. Генерація випадкових величин

Генерація випадкових величин, розподілених за рівномірним
законом
Дискретний рівномірний розподіл – розподіл, для якого
ймовірності кожного із значень випадкової величини однакові, тобто
Р(ч) = 1/N, де N – кількість можливих значень випадкової величини.
В Excel випадкову величину, що розподілена за рівномірним
законом, генерує функція СЛЧИС(), яка відноситься до категорії
Математичні. Функція видає випадкове дійсне число у діапазоні
0÷1.
Для іншого діапазону слід застосувати формулу:
= СЛЧИС()*(b–a)+a, де a і b – відповідно задані нижня і верхня
межа діапазону.
Для генерації цілої випадкової величини, що має рівномірний
розподіл у діапазоні між двома заданими числами в Excel у категорії
Математичні є функція СЛУЧМЕЖДУ(Нижня_межа; Верхня_межа)
3

4. Приклади генерації випадкових величин

4

5. Генерація випадкових чисел, розподілених за різними законами

Для генерації випадкових чисел розподілених за різними законами У MS Excel є
спеціальний інструмент “Генерация случайных чисел”, який розміщений у
середовищі надбудови Пакет анализа. Вибір закону розподілу випадкових чисел у
цьому інструменті задається параметром Распределение.
1. Рівномірний розподіл характеризується верхньою та нижньою межами.
Ймовірність попадання змінної у відрізок фіксованої довжини залежить тільки від довжини відрізка і не
залежить від його розташування на інтервалі. Як правило, в додатках використовують рівномірний
розподіл в інтервалі [0,1].
2. Нормальний розподіл характеризується середнім значенням і стандартним
відхиленням.
Зазвичай програми для цього розподілу використовують середнє значення 0 і стандартне відхилення 1.
3. Розподіл Бернуллі характеризується ймовірністю успіху в даному випробуванні.
Випадкова величина приймає значення 0 або 1.Наприклад, при киданні гральної кістки або випаде 6
очок з імовірністю 1 / 6 або випаде не 6 очок з імовірністю 5 / 6, тобто випадкова величина приймає
значення 1 з ймовірністю 1 / 6 або 0 з імовірністю 5 / 6.
4. Біноміальний розподіл характеризується ймовірністю успіху для деякого числа
випробувань.
Наприклад, можна генерувати випадкові числа, що моделюють процес кидання монети з імовірністю
успіху у "k" випадків з "n" випробувань.
5. Розподіл Пуассона характеризується значенням Лямбда, рівним 1/середнє.
Розподіл Пуассона часто використовується для характеристики числа подій, що трапляються в одиницю
часу, наприклад, число телефонних з'єднань в хвилину.
6. Модельний розподіл характеризується нижньою і верхньою границею, кроком,
числом повторень значень і числом повторень послідовності.
7. Дискретний розподіл характеризується значенням і пов'язаним з ним інтервалом
вірогідності.
Інтервал повинен містити два стовпці: лівий містить значення, правий - ймовірно, пов'язані зі
значенням в цьому рядку. Сума ймовірностей повинна дорівнювати 1.
5

6. Для включення інструменту Генерация случайных чисел слід виконати команду:

• в Excel 1997-2003: Сервис → Пакет анализа → Генерация случайных чисел;
• в Excel 2007 і пізніших: стрічка Данные → панель Анализ → Анализ данных →
Генерация случайных чисел
Якщо інструмент недоступний, то:
• в Excel 1997-2003: Сервис → Надстройки
→ прапорець Пакет анализа → ОК;
• В Excel 2007 і пізніших: кнопка Office →
кнопка Параметры Excel → Надстройки →
кнопка Перейти → прапорець Пакет
анализа → ОК.
6

7.

2. Розрахунок числових характеристик
розподілу ймовірностей
Числовими характеристиками розподілу ймовірностей випадкових величин,
що дозволяють отримати наочне уявлення про розподіл є моменти і
квантилі.
• Першим моментом випадкової величини є математичне очікування або середнє
значення, яке характеризує центр розподілу ймовірностей.
• Другим моментом, що характеризує розкид випадкової величини відносно
математичного очікування є центральний момент випадкової величини, який
називається дисперсією. Величина, що дорівнює кореню квадратному із дисперсії
називається середньоквадратичним відхиленням.
• Для випадкових дійсних величин застосовують такі характеристики, як квантѝлі.
Квантилью Хρ випадкової величини, що має функцію розподілу F(x) називається
рішення Хρ рівняння F(x) = ρ, де ρ – задана ймовірність. Серед квантилей частіше
використовують медіану і квáртилі розподілу.
• Медіаною називають квантиль, що відповідає значенню ρ = 0,5.
• Верхньою квартилью називається квантиль, що відповідає значенню ρ = 0,75, а
нижньою – квантиль, що відповідає значенню ρ = 0,25.
В Excel для обчислення деяких числових характеристик дискретних розподілів
ймовірностей застосовуються функції СРЗНАЧ, ДИСПР, СТАНДОТКЛОНП, КВАРТИЛЬ і
ПЕРСЕНТИЛЬ із категорії СТАТИСТИЧЕСКИЕ
7

8.

Розрахунок числових характеристик дискретних випадкових
величин, закон розподілу яких заданий таблицею відповідностей
Відповідність між окремими можливими значеннями випадкової величини і їх
ймовірностями називається законом розподілу дискретної випадкової величини.
Закон розподілу дискретної випадкової величини може бути заданий таблицею:
Х
х1
х2
хn
Р
p1
p2
pn
При розподілі, що заданий таблицею, математичне очікування розраховується за
формулою:
М(X) = х1p1 + х2p2 + … + хnpn.
Дисперсія дискретної випадкової величини визначається за формулою:
D(X) = M(X2) – [M(X)]2.
Середньоквадратичне відхилення дискретної випадкової величини визначається за
формулою:
σ(X) = D(X)
8

9.

Приклад:
Щоденні витрати на обслуговування і рекламу автомобілів у автосалоні
складає у середньому 120 тис. грош. од., а число продаж Х автомобілів
підпорядковується закону розподілу, що заданий таблицею:
Х
0
1
2
3
4
5
6
7
Р
0,25
02
0,1
0,1
0,1
0,1
0,05
0,05
8
9
0,025 0,025
Вирахувати математичне очікування щоденного прибутку при ціні автомобіля
150 тис. грош. Од. (281,25 тис. грош. од.)
Рішення:
Прибуток визначається виразом: П = Кількість_продаж * Ціна – Витрати
Для розрахунку необхідно визначити математичне очікування кількості
автомобілів, що продаються за день
9

10.

10

11.

Розрахунок числових характеристик біноміального розподілу
Біноміальний розподіл – один із розповсюджених дискретних розподілів,
який застосовується як модель багатьох процесів.
Для визначення ймовірності окремого значення біноміального розподілу
або значення випадкової величини за заданою ймовірністю в Excel є функції
БИНОМРАСП та КРИТБИНОМ (категорія Статистичні).
Функція БИНОМРАСП застосовується для вирахування ймовірності в задачах
із фіксованим числом випробувань, коли результат будь-якого випробування
може мати одне із двох значень – успіх або невдача.
Функція КРИТБИНОМ застосовується для вирахування найменшого числа
успішних результатів випадкової величини, для якого інтегральний
біноміальний розподіл більше або дорівнює заданій величині (критерію).
11

12.

Приклад:
Банк видає 5 кредитів. Ймовірність неповернення кредиту дорівнює 0,2 для
кожного із позичальників. Потрібно скласти таблицю закону розподілу
кількості позичальників, які не повернуть кредит по закінченню терміну
кредитування.
Рішення:
12

13.

Розрахунок числових характеристик нормального закону
розподілу
В Excel для розрахунку значень характеристик нормального розподілення є спеціальні
функції : НОРМРАСП, НОРМСТРАСП, НОРМОБР, НОРМСТОБР і НОРМАЛИЗАЦИЯ (всі із
категорії Статистичні).
• Функція НОРМРАСП розраховує значення ймовірності нормальної функції розподілу
для заданого середнього і стандартного відхилення.
• Функція НОРМОБР розраховує квантилі для вказаного середнього і стандартного
відхилення (розв’язує рівняння F(x) = ρ).
• Функція НОРМАЛИЗАЦИЯ за заданим значення х і параметрам розподілу
розраховує нормальне значення, що відповідає х.
Приклад:
Магазин продає чоловічі костюми. Розподіл попиту за розмірами є нормальним із
математичним очікуванням М = 48 і σ(стандартне відхилення) = 2. Необхідно
розрахувати процент попиту на 50 розмір костюму за умови розкиду значень цієї
величини в інтервалі (49,51).
13

14.

Рішення
14

15.

3. Рішення задач статистичного аналізу в
Excel
Побудова вибіркової функції розподілу
На практиці можливі випадки, коли повне дослідження кожного об’єкту, що належить
деякій сукупності, неможливе. У цих випадках із усієї сукупності випадковим чином
відбирають обмежене число об’єктів, які і досліджуються. Висновки
розповсюджуються на всю сукупність.
Вся сукупність об’єктів, із якої здійснюється вибірка, називається генеральною
сукупністю. Сукупність випадково відібраних із генеральної сукупності об’єктів
називається вибірковою сукупністю.
В Excel для побудови вибіркової функції розподілу використовується спеціальна
функція ЧАСТОТА (категорія Статистичні) і інструмент із Пакету аналізу Гистограмма.
Функція ЧАСТОТА вираховує частоти появи випадкових величин в інтервалі значень і
видає
їх
масивом
чисел.
Параметри
функції:
ЧАСТОТА(масив_даних;
масив_інтевалів). Функція повинна задаватись як формула масиву: після виділення
діапазону результатів вводиться формула і натискаються клавіші Ctrl+Chift+Enter.
Інструмент Гистограмма слугує для розрахунку вибіркових і інтегральних частот
попадання даних у вказані інтервали значень. Результатом є таблиця і гістограма.
15

16.

Приклад:
Побудувати емпіричний розподіл рейтингу студентів за результатами екзаменів,
оцінених у балах для такої довільної вибірки: 48, 51, 64, 62, 55, 71, 74, 79, 80, 86, 91,
99, 83, 50.
Рішення із використанням функції ЧАСТОТА:
{=ЧАСТОТА(А3:А16; В3:В6)}
{=C3:C7/C8}
=D3
=D4+E3
=СУММ(С3:С7)
Вибіркова функція
розподілу має вид:
2, x 50
4, 50 x 70
F ( x) 5, 70 x 85
3, 85 x 100
0, x 100
16

17.

Рішення із використанням інструменту “Гистограмма”:
Вибіркова функція
розподілу має вид:
2, x 50
4, 50 x 70
F ( x) 5, 70 x 85
3, 85 x 100
0, x 100
17

18.

Обчислення основних статистичних характеристик
через використання вбудованих функцій Excel
• Функція СРЗНАЧ обчислює середнє арифметичне із одного або кількох
масивів чисел;
• Функція СРГАРМ обчислює середнє гармонічне множини чисел. Середнє
гармонічне – величина зворотна до середнього арифметичного зворотних
величин;
• Функція СРГЕОМ обчислює середнє геометричне значень масиву
додатних чисел. Цю функцію можна використовувати для обчислення
середніх показників динамічного ряду;
• Функція МЕДИАНА дозволяє визначити медіану заданої вибірки. Медіана
– це елемент вибірки, число елементів із значенням більше якого і менше
якого рівні. Наприклад, МЕДИАНА(5; 6; 8; 5; 9; 10; 8; 9) дорівнює 8;
• Функція МОДА визначає елемент вибірки, який найчастіше зустрічається у
вибірці (найбільш ймовірна величина).
18

19.

Функції Excel, що характеризують розсіювання
• Функція ДИСП дозволяє оцінити дисперсію за вибірковими даними –
степінь розкиду елементів вибірки відносно середнього значення;
• Функція СТАНДОТКЛОН обчислює стандартне відхилення – характеризує
степінь розкиду елементів вибірки відносно середнього значення;
• Функція ПЕРСЕНТИЛЬ дозволяє обчислити квантилі заданої вибірки.
Функції Excel, що дозволяють оцінити форму емпіричного
розподілу
• Функція ЭКССЦЕСС – обчислює оцінку ексцесу за вибірковими даними –
степінь виразності хвостів розподілу, тобто частоти появи значень вибірки
віддалених від середнього значення;
• Функція СКОС дозволяє оцінити асиметрію вибіркового розподілу –
величину, що характеризує несиметричність розподілу елементів вибірки
відносно середнього значення.
19

20.

Приклад:
В таблиці наведені відомості про щомісячну реалізацію продукції за періоди до і
після початку рекламної компанії.
Необхідно знайти середнє значення та стандартне відхилення наведених даних.
Рішення:
=СРЗНАЧ(C2:C8)
=СТАНДОТКЛОН(C2:C8)
=СРЗНАЧ(B2:B8)
=СТАНДОТКЛОН(B2:B8)
20

21.

Обчислення основних статистичних характеристик із
використанням вбудованих функції Excel через
застосування інструменту “Описательная
статистика” “Пакета Анализа”
“Описательная статистика” обчислює такі статистичні характеристики:
середнє, стандартну похибку (середнього), медіану, моду, стандартне
відхилення, дисперсію вибірки, ексцес, асиметричність, інтервал, мінімум,
максимум, суму, найбільше, найменше, рахунок (кількість), рівень надійності.
Приклад:
Задані вибірки зарплат основних
груп
працівників
банку:
адміністрації
(менеджерів),
персоналу по роботі із клієнтами,
технічних служб.
Необхідно
обчислити
основні
статистичні
характеристики
у
групах даних.
Адміністрація
Персонал по
роботі із
клієнтами
Технічні
працівники
4500
2100
3200
4000
2100
3000
3700
2000
2500
3000
2000
2000
2500
2000
1900
1900
1800
1800
1800
21

22.

Рішення:
22

23.

Перевірка статистичних гіпотез в Excel
Обчислення довірчого інтервалу для середнього значення
Для обчислення довірчого інтервалу в Excel можна скористатись вбудованою
функцією ДОВЕРИТ або інструментом “Описательная статистика” із
“Пакета анализа”
ДОВЕРИТ(альфа; станд_відхил; розмір), де параметри:
• альфа – рівень значимості, який використовується для обчислення
довірчої ймовірності;
• станд_відхил – стандартне відхилення генеральної сукупності для
інтервалу
даних
(передбачається
відомим
або
попередньо
розраховується);
• розмір – розмір вибірки.
Приклад:
Знайти межі 90% інтервалу для середнього значення, якщо за результатами
24 торгів середнє значення вартості долара склало 7,97 гривні, а
стандартне відхилення – 25 копійок.
23

24.

Рішення із використанням функції ДОВЕРИТ
Тут альфа визначається як альфа = 1 – 0,9. Отримано значення довірчого
інтервалу ±0,05 грн. Отже, із 90% рівнем надійності можна стверджувати,
що середня вартість долара лежить у діапазоні 7,92 грн. - 8,02 грн.
Приклад:
Дана вибірка вартості валюти: 7,95, 7,97, 7,98, 8,02, 7,93, 7,94, 8,00, (грн.).
Необхідно визначити межі 95% довірчого інтервалу для середнього.
24

25.

Рішення за допомогою інструменту “Описательная
статистика”
В результаті обчислень для довірчої ймовірності 0,95 отримаємо величину
довірчого інтервалу - ±0,030. Це означає, що із ймовірністю 0,95 для
генеральної сукупності середнє значення буде знаходитись в інтервалі
7,97±0,03 грн.
25

26.

Перевірка відповідності теоретичного розподілу із
використанням критерію згоди хі-квадрат
В MS Excel критерій ХІ-квадрат реалізований функцією ХИ2ТЕСТ. Ця
функція обчислює ймовірність збігу спостережуваних (фактичних) значень і
теоретичних (гіпотетичних) значень. Функція має параметри:
ХИ2ТЕСТ (фактичний_інтервал; очікуваний_інтервал), де
• фактичний_інтервал - діапазон даних, який містить результати
спостереження, що підлягають порівнянню з очікуваними значеннями;
• очікуваний_інтервал - діапазон даних, який містить теоретичні (очікувані)
значення для відповідних спостережуваних.
Для отримання правильних результатів необхідно, щоб обсяг вибірки був не
менше 40. Дані згруповані в інтервальний ряд з кількістю інтервалів не
менше 7, а кількість спостережень у кожному інтервалі (частот) не менше 5.
Приклад.
Перевірити відповідність вибіркових даних результатів здачі іспитів,
оцінених у балах: 48, 51,67, 70, 64, 71, 85, 79, 80, 83, 86, 91, 99, 56, 66, 65,
84, 84, 84, 75, 76, 77, 78, 80, 86, 88, 58, 69, 65, 81, 75, 78, 85, 80, 80, 83, 86,
80, 89, 60, 68, 55, 82, 64.71, 72, 72, 73, 74, 74, 79 нормальному закону
розподілу.
26

27.

Рішення
=J2*F2
{=ЧАСТОТА(A2:A52;F2:F11)}
=K2*$G$13/СУММ($K$2:$K$12)
{=НОРМРАСП(F2:F12;H15;H16;0)}
{=G2:G12/G13}
27

28.

4. Рішення задач дисперсійного аналізу в
Excel
Методи дисперсійного аналізу застосовуються для оцінки достовірності
відмінностей між кількома групами спостережень. Задача дисперсійного
аналізу полягає у дослідженні дії на випадково змінювану величину одного
або кількох незалежних чинників, що мають кілька градацій. В MS Excel для
проведення однофакторного дисперсійного аналізу застосовується
інструменти «Однофакторный дисперсионный анализ», «Двухфакторный
дисперсионный анализ с повторениями» та «Двухфакторный
дисперсионный анализ без повторений».
Приклад.
Необхідно виявити, чи впливає відстань від центру міста на степінь
заповнюваності готелів. Нехай відстань від центру розбито на 3 рівня: 1) до
3 км, 2) від 3 до 5 км, 3) більше 5 км.
Заповнюваність готелів
Відстань
Заповнюваність, в %
до 3 км
92,00%
98,00%
89,00%
97,00%
90,00%
94,00%
від 3 до 5 км
90,00%
86,00%
84,00%
91,00%
83,00%
82,00%
більше 5 км
87,00%
79,00%
74,00%
85,00%
73,00%
77,00%
28

29.

Рішення:
У результуючій таблиці
на
перетині
рядка
“Между группами” і
стовпця
“Р-Значение”
знаходиться величина
0,0002684, що <0,05.
Отже Критерій Фішера
значимий.
Вплив
фактора відстані від
центру
міста
на
заповнюваність готелів
доведена статистично.
29

30.

5. Рішення задач кореляційного аналізу в
Excel
Однією із задач статистики є вивчення зв’язку між деякими змінними, що
спостерігаються. Результати, що отримані при такому дослідженні,
дозволяють прогнозувати розвиток ситуації у випадку зміни конкретних
характеристик об’єкта, що вивчається, або процесу.
Задача подібного дослідження розв’язується методами кореляційного аналізу.
Метою рішення задачі є отримання кореляційної матриці
У MS Excel для здійснення кореляційного аналізу слугує інструмент
Корреляция, який дозволяє отримати кореляційну матрицю, що містить
коефіцієнти кореляції між різними параметрами.
Кореляційна матриця – це квадратна таблиця, на перетині відповідних рядків і
стовпців розташовані кореляційні коефіцієнти.
Приклад:
Є статистичні дані, що реєструють кількість вихідних і святкових днів у місяці в
період із січня по червень і суми коштів, що знімаються з рахунків.
Необхідно визначити, чи існує кореляція між кількістю вихідних днів і сумами,
що знімаються з рахунків.
30

31.

Рішення:
У
трикутній
матриці
коефіцієнт кореляції між
кількістю вихідних днів і
коштами, що зняті із рахунків
дорівнює r = 0,9114, тобто
можна передбачити, що існує
сильний прямий зв’язок.
31

32.

6. Технології рішення задач регресійного
аналізу в Excel
Регресія дозволяє проаналізувати дію на будь-яку залежну змінну однієї або кількох
незалежних змінних і дозволяє встановити аналітичну форму (модель) цієї
залежності.
Якщо розглядати залежність між однією залежною змінною Y і кількома
незалежними Х1, Х2, … , Хn , то мова йде про множинну лінійну регресію. В цьому
випадку рівняння регресії має вид:
Y= а0 + а1 Х1 + а2 Х2 + … + аn Хn ,
де а1, а2, …, аn - коефіцієнти при незалежних змінних, які необхідно розрахувати
(коефіцієнти регресії), а0 – константа.
При побудові регресійної моделі найважливішим моментом є оцінка її адекватності
(ефективності) і значимості, на основі яких можна судити про можливість
застосування в практиці отриманої моделі.
Мірою оцінки адекватності регресійної моделі є коефіцієнт детермінації R2 (Rквадрат), який визначає , з якою степені точності отримане рівняння регресії
апроксимує вихідні дані.
Значимість регресійної моделі оцінюється за допомогою критерію Фішера (Fкритерію). Якщо величина F-критерію значима (р < 0,05), то регресійна модель є
значимою.
У MS Excel для обчислення коефіцієнтів регресії слугує інструмент “Регрессия” із
Пакету аналізу. Він дозволяє отримувати коефіцієнти рівняння лінійної регресії , що
може включати до 16 незалежних змінних.
32

33.

Приклад:
Існують статистичні дані про витрати, пов’язані із рекламою по телебаченню,
рекламою у метро і обсяги реалізації продукції у гривнях , що наведені у
таблиці.
Необхідно знайти регресійні коефіцієнти для незалежних змінних Витрати
на рекламу по ТБ та Витрати на рекламу у метро. Для залежної змінної
Обсяг реалізації продукції і побудувати рівняння регресії.
Рішення:
Витрати
Витрати
на рекламу на рекламу
по ТБ
у метро
Обсяг
реалізації
125450
20000
1850000
260500
21000
2500000
150000
22000
1900000
300000
21000
3000000
280000
24000
2600000
290000
23000
2700000
140000
20000
1900000
33

34.

1. Коефіцієнт детермінізації R-квадрат = 0,974 (апроксимація задовільна).
2. Значимість F = 0,000648 (р < 0,05 – регресійна модель значима).
3. Y-пересечение а0 = 2106723,34.
4. а1 = 6,395 – коефіцієнт при незалежній змінній X1 - Витрати на рекламу по ТБ.
5. а2 = -54,194 – коефіцієнт при незалежній змінній X2 - Витрати на рекламу у метро.
З урахуванням отриманих даних рівняння регресії матиме вид: Y = 2106723,34 + 6,395X1 – 54,194X2.
34

35.

Оптимізація портфеля цінних паперів в Excel
Відомо, що ціна продажі акцій А, В, і С до початку майбутнього місяця складає
34,30; 74,87; 107,00 грн.
У розпорядженні інвестора є капітал 73 тис. грн.
Інвестора цікавить питання, акції якого емітента і якій кількості слід придбати
по сьогоднішньому курсу продажі, щоб із мінімальним ризиком отримати у
майбутньому місяці дохід від портфеля не менше 55,41% на вкладений
капітал.
Ретроспектива динаміки курсів: дивідент
Місяць
1
2
3
4
5
6
7
8
9
10
Курс А
130
85
53,5
35,42
31,44
34,74
33,4
40,96
35,01
35,89
Див. від А
1,2
2,86
48,2
23,94
4,96
0,04
11,3
20,82
1,33
0,26
Курс В
66
64,19
64,19
61,37
51,88
65,85
88,9
77,57
56,68
65,14
Див. від В
0,05
4,71
7,53
56,95
20,96
34,5
0,03
73,12
12,7
14,59
Курс С
48
60,95
60,95
58,23
50,38
51,87
72,1
105,95
73,14
89,94
Див. від С
19,42
16,71
7,24
47,11
2,24
30,32
50,8
114,84
25,2
25,59
35

36.

Технологічна послідовність комп’ютерного рішення задачі
1.
Економіко-статистичний аналіз даних.
a) Введення даних на робочий листок Excel.
b) Розрахунок рядів ефективності цінних паперів (ЦП).
c) Розрахунок середньої ефективності по кожному ЦП.
d) Розрахунок відхилень ефективності кожного ЦП від свого середнього.
e) Розрахунок коваріації.
2.
Побудова математичної моделі оптимізації портфеля цінних паперів.
3.
Формалізація математичної моделі на робочому листку Excel.
4.
Складання комп’ютерного аналога математичної моделі за допомогою
інструменту (надбудови) “Поиск решения” і виконання розрахунків.
5.
Економічна інтерпретація результатів.
36

37.

37

38.

Математична модель інвестора
Знайти Х = (Х1, Х2, Х3);
Z=0,1244X1X1+2*0,02X1X2+2*0,0123X1X3+0,1311X2X2+2*0,0056X2X3+0,1312X3X3 → min
За обмежень:
0,1632Х1+0,3734Х2+0,5742Х3=>0,5541;
X1+X2+X3<=1;
X1>=0; X2>=0; X3>=0.
38

39.

Складання комп’ютерного аналога математичної
моделі за допомогою інструменту (надбудови) “Поиск
решения” і виконання розрахунків.
Висновок: інвестору слід вкласти
10% капіталу в акції емітента В і 90%
капіталу – в акції емітента С.
39

40.

7. Технології рішення задач фінансової
математики в Excel
Розрахунок нарощеної суми за простим відсоткам
Під нарощеною сумою позики (депозиту, боргу) розуміють її початкова
сума плюс нараховані на неї до кінця терміну відсотки. Нарощена сума
розраховується як останній елемент прогресії, що має загальний член
P(1 + ni), тобто
S = P(1 + ni),
де Р – початкова сума;
n – кількість періодів;
і – ставка за період.
Розрахунок кількості днів в періоді, який заданий початковою і
кінцевою датами
Для розрахунку кількості днів між двома датами в Excel є функція:
ДНЕЙ360(Початкова_дата; Кінцева_дата; Метод)
40

41.

Розрахунок за простими змінними ставками
За час розрахункового періоду ставки можуть дискретно змінюватись у часі, при
цьому вони залишаються незмінними до наступної дискретної зміни. У цьому
випадку формула для розрахунку нарощеної суми має вид:
m
S P 1 nt it ,
t 1
де Р – початкова сума; it – ставка простих процентів в період з номером t = 1, …, m;
nt – тривалість t періоду нарахування за ставкою it.
Приклад:
У договорі, розрахованим на рік, прийнята ставка простих процентів на перший
квартал у розмірі 8% річних, а на кожний наступний на 0,5% менше ніж у
попередній. Визначити суму на рахунку у кінці року.
41

42.

Розрахунок реінвестування за простими процентами
Сума із нарахованими на неї процентами може бути знову інвестована під цю або
іншу процентну ставку. У випадку багатократного інвестування у короткострокові
депозити і застосування простої процентної ставки нарощування сума для всього
строку N = ∑ni розраховується за формулою:
m
S P 1 nt it ,
t 1
де nt – тривалість послідовності періодів реінвестування;
it – ставки, за якими здійснюється реінвестування.
Приклад:
На суму 100000 грош. од. нараховується 10% річних. Проценти прості, точні.
Розрахувати суму нарощування у кінці кварталу, якщо реінвестування відбувається
щомісячно протягом 1 кварталу (в році 365 днів).
42

43.

Дисконтування в Excel
Операція дисконтування полягає у розрахунку вихідної суми Р при заданій сумі S,
що відповідає закінченню фінансової операції. Нарахування за процентами у
вигляді різниці D = S – P називають дисконтом (скидкою). Дисконтна сума
розраховується за формулою:
P = S/(1 + ni).
Для дисконтування в Excel є вбудована функція із категорії «Финансовые»
Приклад:
Платіжне зобов'язання сплатити через 60 днів 200000 грн. з відсотками, що
нараховуються за ставкою простих відсотків I = 15% річних, було враховано за 10
днів до терміну погашення за обліковою ставкою 12%. Обчислити суму,
одержувану при обліку (число днів у році 365).
43

44.

Обчислення нарощування за складними процентами в Excel
Формула нарощування для складних процентів має вид:
S = P(1 + I)n,
де S – нарощена сума; I – річна ставка складних процентів; n – термін позики (кількість
періодів).
Для обчислення нарощеної суми в Excel є вбудована функція БС із категорії
«Финансовые»:
БС(Ставка; Кпер; Плт; Пс; Тип)
Для розв’язання зворотних задач в Excel є вбудовані функції СТАВКА, КПЕР (із категорії
«Финансовые»:
СТАВКА(Кпер; Плт; Пс; Бс; Тип) – вираховує ставку;
КПЕР(Ставка; Плт; Пс; Бс; Тип) – вираховує кількість періодів.
Обчислення номінальної та ефективної ставки процентів в Excel
Нарахування процентів за номінальною ставкою здійснюється за формулою:
S = P(1 + j/m)N,
де N – число періодів нарахування, N=mn; j – номінальна річна ставка складних
процентів; m – число нарахувань за рік.
Для
обчислення
ефективної
ставки
в
Excel
є
вбудована
функція
ЭФФЕКТ(Номинальная_ставка; Количество_периодов) із категорії «Финансовые».
Для обчислення номінальної ставки при заданій ефективній в Excel є вбудована функція
НОМИНАЛ(Эффективная_ставка; Количество_периодов) із категорії «Финансовые».
44

45.

Обчислення нарощеної суми при змінній процентній ставці
в Excel
Для обчислення в Excel нарощеної суми при змінній процентній ставці
застосовується функція:
БЗРАСПИС(Первичное; План),
де Первичное – поточне значення інвестицій; План – масив використовуваних
процентних ставок.
Приклад:
Клієнт зробив внесок у банк в сумі 1 тис. грош. од. під 30% річних строком на 1 рік.
Процентна ставка у першому кварталі складала 30% річних, в середині другого
кварталу вона знизилась до 25%, на початку четвертого кварталу вона знову
зросла до 30%. Яку суму отримає клієнт у кінці року?
45