Лабораторна робота № 1 на тему MS Excel, фінансові функції та побудова діаграм

Лабораторна робота № 1 Тема. MS Excel. Фінансові функції. Побудова діаграм

Мета. Набути практичні навики обробки та використання економічної інформації.

Теоретична довідка

Фінансові функції призначені для розв’язування задач щодо визначення прибутків, планування фінансової діяльності, аналізу інвестицій тощо. Деякі визначення необхідні для розуміння роботи фінансових функцій.

Інвестиція – вкладання грушей у бізнес на певних умовах.

Ануїтет – рівні один одному грошові платежі, що виплачуються через певні проміжки часу в рахунок погашення отриманого кредиту, позики і відсотків за ним.

Існує два способи нарахування відсотків: прості і складні відсотки. Простими називаються відсотки у випадку їх нарахування в кінці всього терміну, складними в кінці кожного періоду.

Розглянемо аргументи фінансових функцій та їх скорочені назви:

Ставка – відсоткова ставка за період (виражається у відсотках);

Кпер - це загальне число періодів платежів по ануїтету. Період може бути рівним дню, місяцю, кварталу, півроку, року тощо.

Плт - це виплата, яка проводиться за кожен період (періодична виплата), це значення не може змінюватись протягом всього терміну виплат. Зазвичай плт складається з основного платежу і платежу по відсотках, але не включає інших податків і зборів. Якщо аргумент опущений, повинне бути вказане значення аргументу пс.

Пс - це приведена до теперішнього моменту вартість або загальна сума, яка на даний момент рівноцінна ряду майбутніх платежів (початкова сума). Якщо аргумент Пс опущений, то він вважається рівним 0. В цьому випадку повинне бути вказане значення аргументу Плт.

Бс - необхідне значення майбутньої вартості або залишку засобів після останньої виплати (майбутня сума). Якщо аргумент бс опущений, то він вважається рівним 0 (наприклад, бс для позики рівне 0).

Тип - число 0 або 1, що позначає, коли повинна проводитися виплата. Якщо аргумент «тип» опущений, то він вважається рівним 0, тобто виплата проводиться в кінці терміну, якщо 1 – то на початку.

Предположение – початкове припущення (за замовчуванням рівне 10 %);

Перейдемо до безпосереднього розгляду фінансових функцій та їх синтаксису.

БС (ставка;кпер;плт;пс;тип). Функція Бс (Майбутня вартість) (FV– англ. варіант) призначена для обчислення майбутньої вартості теперішніх інвестицій на основі постійних періодичних виплат і постійної відсоткової ставки. Якщо кпер рівний 1 (без періодичних виплат), то відсотки нараховуються вкінці всього терміну і такий варіант називається прості відсотки. Якщо виплати проводяться періодично з нарахуванням відсотків за кожен період, то такий варіант називається складними відсотками.

Приклад 1 (прості відсотки): Сума в розмірі 4000 грн. внесена на депозит на 10 місяців під 14 % річних. Яка сума буде в кінці терміну.

=БС(14%*10/12;1;;-4000)

Результат. 4 466,67 грн.

Приклад 2 (складні відсотки): Кредит у розмірі 20000 грн. виданий на півтора року під 25% річних з щоквартальним нарахуванням. Визначити суму кінцевого платежу.

=БС(25%/4;1,5*4;;20000)

Результат -28 774,22 грн.

СТАВКА(кпер;плт;пс;бс;тип;предположение) (Rate– англ. варіант) повертає відсоткову ставку за ануїтетом за один період. СТАВКА обчислюється шляхом ітерації і може давати нульове значення або декілька значень.  У такому випадку варто змінити початкове припущення.

Приклад 3: нам відомо кількість періодів (10), початкова (1000 грн.) та  кінцева (1800) сума, а необхідно знайти відсоткову ставку, тоді функція запишеться наступним чином

=Ставка(10;;-1000;1800).

Результат 6 % за період.

КПЕР(ставка;плт;пс;бс;тип) (NPER– англ. варіант) повертає загальну кількість періодів виплати для інвестиції на основі періодичних постійних виплат і постійної відсоткової ставки. Функція аналогічна попередній.

Приклад 4: видана позика у розмірі 1500 грн. за умови повернення вкінці кожного місяці по 120 грн. Скільки місяців буде тривати виплата

=КПЕР(4%;120;-1500).

Результат – 17.67.

ПЛТ(ставка;кпер;пс;бс;тип) (PMT– англ. варіант) повертає суму періодичного платежу для ануїтету на основі постійності сум платежів і постійної відсоткової ставки. Виплати, що повертає функція ПЛТ, включають основні платежі і платежі по відсотках, але не включають податків, резервних платежів або комісій, що іноді пов'язуються з позикою.

Приклад 5: Фізична особа взяла кредит на суму 3500 грн. терміном на 10 місяців за умови щомісячного погашення і місячної відсоткової ставки 5 %. Визначити величину щомісячних виплат.

Формула =ПЛТ(5%;10;3500).

Результат – -453,27 грн.

Як уже було відзначено платіж включає основний платіж і платіж за відсотками. Для кожного з періодів це співвідношення буде різним (з кожним наступним платежем доля відсоткового платежу зменшується). Для обчислення складових платежу використовують дві функції:

ОСПЛТ(ставка;період;кпер;пс;бс;тип) (PPMT– англ. варіант) повертає величину платежу в погашення основної суми за інвестиціями за даний період на основі постійності періодичних платежів і постійності процентної ставки. Де період номер періоду, за який проводяться виплати. Причому період  не може бути більшим ніж кпер.

ПРПЛТ(ставка ;период;кпер;пс;бс;тип) (IPMT– англ. варіант) повертає суму платежів відсотків за інвестиціями за даний період на основі постійності сум періодичних платежів і постійності процентної ставки.

Приклад 6: доповнимо попередній приклад. Необхідно обчислити розмір складових платежу в кінці 4-го місяця.

Формули =ОСПЛТ(5%;4;10;3500), =ПРПЛТ(5%;4;10;3500)

Результат -322,13 грн., -131,14 грн.

Важливо:

• При використанні фінансових функцій доцільно записувати аргументи в клітинки та присвоювати їм відповідні імена для кращого розуміння та уникнення помилок;

• Клітинка де записано розмір відсоткової ставки повинна бути відсоткового типу, оскільки Excel сприймає 5 в клітинці числового типу як 500 %, крім цього необхідно додавати знак відсотків у формули до значень відсоткового типу;

• Якщо, наприклад, річна відсоткова ставка рівна 15% і відсотки нараховуються, що пів року то аргумент Ставка рівний 15/2 так як він передбачає відсоткову ставку за період тобто пів року;

• Також необхідно бути уважним і звертати увагу, який результат повертає функція (за який період) і перевести результат у необхідний нам формат;

• При записі варто зважати на знак, тобто якщо ми віддаємо гроші, то сума повинна бути від’ємною, одержуємо – додатною.

• Деякі фінансові функції активуються тільки після вибору пакету Пакет анализа. Для цього необхідно перейти Сервис-Надстройки або Кнопка Office-Параметры Excel-Надстройки-Перейти (для MS Office 2007 і вище).

Технологія виконання

1. Скопіювати на Робочий стіл  файл з описом lr_2  і відкрити його.

2. На Робочому столі створити папку LR_2  <Власне прізвище>, в яку будете зберігати результати роботи і яку потім не забудьте скопіювати на сервер у власну папку.

3. Викликати табличний процесор MS Excel і створити нову книгу у файлі з іменем
LR_2  <Власне прізвище>.xls.

4. Кожна нова задача оформлюється на новому аркуші назва якого співпадає із назвою відповідної функції. Усі дані в задачі розміщуються в клітинки із відповідною назвою. (див. Рис. 2.1).

5. Задача 1 (БС). На депозит поставлено 5000 грн. під n* відсотків річних на 5 років. Відсотки нараховуються в кінці кожного місяця (у випадку складних відсотків). Яка сума буде на рахунку після закінчення терміну для складних і простих відсотків  (простими називаються відсотки у випадку їх нарахування в кінці всього терміну, складними – кожного періоду).

5.1. Побудувати таблицю залежності кінцевої суми від відсоткової ставки в діапазоні від n до n+20%  для простих та складних відсотків.

5.2. Побудувати на основі таблиці точкову діаграму на окремому аркуші із назвою «БС_Діаграма». Додати лінію тренду. Підібрати відповідний тип апроксимації для кожної з кривих. Встановити параметр відображати рівняння лінії тренду.

6. Задача 2 (СТАВКА). Під яку відсоткову ставку річних надати кредит у розмірі 100 + n грн., щоб через рік отримати 130+ n грн. Відсотки нараховуються раз на місяць?

6.1. Обчисліть ефективну річну відсоткову ставку.

6.2. Побудувати таблицю та Гістограму на цьому ж аркуші залежності річної відсоткової ставки від кількості періодів (1-6).

8. Задача 3. (КПЕР) Через який час (в роках) сума рівна 6000 грн. досягне суми 10000 грн. при нарахуванні за складною відсотковою ставкою n % річних на початку кожного кварталу.

8.1. Побудувати таблицю, на основі попереднього завдання, залежності кількості років від розміру періодичних виплат (100, 200, 300, 400, 500 грн.). Тільки  враховуючи, що виплати відбуваються не в кінці всього терміну, а в кінці кожного періоду рівними частинами.

8.2. Побудувати на основі даної таблиці графік залежності на цьому ж аркуші. Встановити параметри: показувати значення, змінити фон та текстуру, встановити Y-похибку в обидві сторони у розмірі 5 відсотків.

9. Задача 4. (ПЛТ, ОСПЛТ, ПРПЛТ) Банк видав кредит в розмірі 1000 грн. під n відсотків річних на 3 роки. Визначити розмір щомісячних виплат за ануїтетом.

9.1. Побудувати таблицю залежності Основної виплати та відсоткової виплати за кожен із періодів  (1;36)

9.2. Побудувати кругову об’ємну діаграму складових виплат (на тому ж аркуші) за n‑ий період, змінити колір областей, легенду розмістити внизу, встановити параметри, відображати значення та долі

9.3. Побудувати звичайну гістограму залежностей відсоткових та основних виплат від періоду на окремому листі "Графік виплат". Параметри діаграми: назвати діаграму "Графік виплат", та відповідно осі, розмістити легенду внизу, змінити колір заливки, встановити число категорій між підписами – 5.

10. Задача 5 (ПС, СТАВКА). Для деякого бізнесу потрібно вкласти 3200 грн. А бізнес протягом 5 місяців буде приносити по 1000 грн. доходу в кінці місяця. Депозитна ставка n % банку. Чи варто займатися цим бізнесом.

10.1. Оцінити яка відсоткова ставка прибутковості даного бізнесу.

10.2. Побудувати гістограму залежності розміру відсоткової ставки від типу вкладів (бізнес, банк).

11. Необхідно врахувати, що формат клітинок повинен відповідати змісту (клітинки, які містять грошову суму повинні бути грошового типу, відсотки – процентного і т.д.).

12. Переглянути створену таблицю в режимі Предварительный просмотр і зберегти результати роботи. Результат кожного з аркушів повинен бути розміщений на одній сторінці.

Контрольні запитання

1. Яке призначення функції БС?

2. Яке призначення функції ПС?

3. Яке призначення функції СТАВКА?

4. Яке призначення функції КПЕР?

5. Яке призначення функції ПЛТ?

6. Яке призначення функції ПРПЛТ?

7. Яке призначення функції ОСПЛТ?

8. Що ми називаємо "Рядом даних"?

9. Які ви знаєте види апроксимації?

10. Які визнаєти види діаграм?

11. Що таке лінія тренду?

12. Як відображається похибка на діаграмах?

13. Що таке легенда?

14. Що таке лінії сітки?

15. Яка зміст у додатніх і від’ємних значеннях результатів фінансових функцій?

16. Що таке прості та складні відсотки?

З повагою ІЦ “KURSOVIKS”!