Роздрукувати сторінку

Главная \ Методичні вказівки \ Методичні вказівки \ 234 Методичні вказівки до практичної роботи №2 - Застосування вбудованих функцій табличного процесора MS Excel

Методичні вказівки до практичної роботи №2 - Застосування вбудованих функцій табличного процесора MS Excel

« Назад

Практична робота № 2

Тема: Застосування вбудованих функцій табличного процесора MS Excel для розрахунку ефективності капіталовкладень та амортизації обладнання.

Мета: оволодіти практикою застосування вбудованих функцій табличного процесора MS Excel для розрахунку ефективності капіталовкладень та амортизації обладнання.

ТЕОРЕТИЧНІ ВІДОМОСТІ

Табличний процесор MS Excel має широкий спектр вбудованих функцій фінансового аналізу. Розглянемо наступні фінансові функції: функції для аналізу інвестицій, функції для обчислення швидкості обороту, функції для обчислення амортизації, наведемо перелік функцій для кожної з перелічених категорій із зазначенням їх аргументів та прикладів застосування у фінансовому аналізу.

Зауваження: нижче наведені фінансові функції для MS Excel 2000 і у дужках відповідне позначення функцій у MS Excel 2003.

Функції для аналізу інвестицій

Функція ПЗ (ПС). Ця функція обчислює поточну вартість ряду рівних за величиною періодичних виплат або одноразової виплати та має наступний синтаксис:

=ПЗ (ставка; число періодів; виплата; майбутнє значення; тип), де

- ставка - відсоткова або облікова ставка;

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Зауваження: якщо результат - від’ємне значення, це вказує на витрачені нами кошти.

Функція НПЗ (ЧПС). Обчислює, поточну вартість ряду рівних за величиною періодичних виплат або одноразової виплати. Але має такі відмінності: допускає використання виплат змінної величини; всі платежі і надходження рівномірно розподіляються по періодах та проводяться в кінці періодів. Якщо вартість вкладення виплачується авансом, то не потрібно цю суму включати як аргумент плата, її віднімають від значення функції. З другого боку, якщо вартість повинна бути виплачена в кінці першого періоду, її необхідно включити як аргумент плата з від’ємним знаком. Ця функція має такий синтаксис:

=НПЗ (ставка; плата1; плата2;...; плата29),

де ставка - відсоткова або облікова ставка; плата1,...,плата29 - періодичні виплати, що змінюються.

Функція БЗ (БС) обчислює для деякого майбутнього моменту часу величину вкладення, яка утворюється в результаті одноразової виплати або ряду постійних періодичних виплат. Ця функція має синтаксис:

=БЗ (ставка; число періодів; виплата; поточне значення; тип), де

- ставка - відсоткова або облікова ставка;

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Для обчислення майбутнього значення ряду виплат використовується аргумент виплата, аргумент поточне значення служить для визначення майбутнього значення одноразової виплати.

Функція ППЛАТ (ПЛТ) обчислює розмір періодичної виплати, необхідний для погашення позики за вказане число періодів. Ця функція має наступний синтаксис:

=ППЛАТ (ставка; число періодів; поточне значення; майбутнє значення; тип), де

- ставка - відсоткова або облікова ставка;

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Функція ПЛПРОЦ (ПРПЛТ) обчислює платежі за відсотками за заданий період при сталих періодичних виплатах та сталій відсотковій ставці. Ця функція має наступний синтаксис:

=ПЛПРОЦ (ставка; період; число періодів; поточне значення; майбутнє значення; тип),де

- ставка - відсоткова або облікова ставка;

- період - номер конкретної періодичної виплати;

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Функція ОСНПЛАТ (ОСПЛТ) обчислює основні платежі за заданий період при постійних періодичних виплатах та сталій відсотковій ставці. Якщо ви обчислите ПЛПРОЦ та ОСНПЛАТ за один і той же період, то сумуючи ці результати, можемо отримати загальний розмір виплати. Ця функція має такий синтаксис:

=ОСНПЛАТ(ставка; період; число періодів; поточне значення; майбутнє значення; тип) де ,

- ставка - відсоткова або облікова ставка;

- період - номер конкретної періодичної виплати;

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Функція КПЕР обчислює число періодів, яке необхідне для погашення позики при заданій величині періодичних виплат. Ця функція має наступний синтаксис:

=КПЕР(ставка; виплата; поточне значення; майбутнє значення; тип), де

- ставка - відсоткова або облікова ставка;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду).

Функції для обчислення швидкості обороту

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

Функція НОРМА (СТАВКА) дозволяє визначити швидкість обороту (норму прибутку) вкладень за рядом постійних періодичних виплат або за одноразовою виплатою. Синтаксис:

=НОРМА(число періодів; виплата; поточне значення; майбутнє значення; тип; прогноз) де,

- число періодів - термін вкладання;

- виплата - постійні періодичні виплати;

- поточне значення - поточна вартість вкладення;

- майбутнє значення - майбутній об’єм вкладу в кінці терміну;

- тип - число, яке означає, коли повинна проводитися виплата (0 - в кінці періоду; 1- на початку періоду);

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

Функція ВНДОХ (ВСД) дозволяє визначити швидкість обороту (норму прибутку) вкладень, враховуючи вартість вкладення та непостійність виплат. Має наступний синтаксис:

=ВИДОХ (значення; прогноз), де

- значення - це масив або посилання на діапазон комірок, що містять числа;

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

Можна використовувати тільки один аргумент значення, і він повинен включати хоч одне позитивне та одне від’ємне значення. Ця функція припускає, що всі виплати проводяться в кінці періоду, та повертає еквівалентну відсоткову ставку за цей період.

Функція МВСД обчислює модифіковану внутрішню швидкість обороту. Вона бере до уваги вартість коштів, що позичаються для фінансування інвестиції та передбачає, що ви реінвестуєте кошти, які поступають від цього вкладення. Функція МВСД передбачає, що всі виплати проводяться в кінці періодів, і повертає еквівалентну відсоткову ставку за один період. Ця функція має такий синтаксис:

= МВСД (значення; фінансова_ставка; ставка_реінвестування), де

- значення - це масив або посилання на діапазон комірок, що містять числа;

- фінансова_ставка - це ставка, за якою ви позичаєте кошти, необхідні для вкладення;

- ставка_реінвестування - це ставка, за якою ви інвестуєте кошти, які надходять від вкладення.

Функції для обчислення амортизації

Функція АМР (АПЛ) дозволяє визначити прямолінійну амортизацію майна за один період. Метод прямолінійної амортизації припускає, що амортизація постійна для будь-якого конкретного одиничного періоду на протязі корисного часу життя майна. Ця функція має наступний синтаксис:

=АМР (вартість; залишок; час_життя), де

- вартість - початкова вартість майна;

- залишок - залишкова вартість після повної амортизації (іноді її називають ліквідною вартістю майна);

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

Функція ДДОБ обчислює амортизацію майна, використовуючи метод двократного обліку амортизації, при якому амортизація має змінну величину - більшу в початкові періоди та меншу в останні періоди експлуатації. При використанні цього методу амортизація обчислюється як відсоток від чистої балансової вартості майна (початкова вартість майна за вирахуванням амортизації за попередні роки). Ця функція має наступний синтаксис:

= ДДОБ (вартість; залишок; час_життя; період; коефіцієнт), де

- вартість - початкова вартість майна;

- залишок - залишкова вартість після повної амортизації (іноді її називають ліквідною вартістю майна);

- час_життя - кількість періодів, на протязі яких майно амортизується (іноді його називають часом повної амортизації);

- період - період, для якого потрібно визначити амортизацію;

- коефіцієнт - є необов’язковим аргументом і по умовчанню має значення 2, яке позначає звичайний метод двократного обліку амортизації, значення 3 задає трьохкратний облік амортизації.

Функція ПДОБ (ПУО) обчислює величину амортизації майна за повний або будь-який частковий термін експлуатації, використовуючи метод двократного обліку, або з будь-яким вказаним коефіцієнтом. Ця функція має наступний синтаксис:

=ПДОБ(вартість; залишок; час_життя; початок;кінець; коефіцієнт; без_перемикання) де,

- вартість - початкова вартість майна;

- залишок - залишкова вартість після повної амортизації (іноді її називають ліквідною вартістю майна);

- час_життя - кількість періодів, протягом яких майно амортизується (іноді його називають часом повної амортизації);

- початок - це період, після якого починається часовий інтервал обчислення амортизації;

- кінець - останній період часового інтервалу обчислення амортизації;

- коефіцієнт - це коефіцієнт обліку амортизації;

- без_перемикання - це значення, яке визначає необхідність перемикання на прямолінійну амортизацію, коли величина прямолінійної амортизації перевищує величину амортизації, яка обчислюється методом обліку амортизації.

Останні два аргументи є необов’язковими. Якщо коефіцієнт пропущений, то приймає як значення 2 та використовує метод двократного обліку амортизації. Якщо аргумент без_перемикання пропущений або дорівнює нулю, то переключається на прямолінійну амортизацію, коли величина прямолінійної амортизації перевищує величину амортизації, яка обчислюється методом обліку амортизації. Щоб уникнути такого переключення, в якості аргументу без_перемикання вказують значення 1.

Функція АМГД (АСЧ) обчислює величину амортизації майна за заданий період методом прискореної амортизації. При використанні цього метода повна амортизація (вартість майна мінус його ліквідна вартість) розподіляється по періодах відповідно до вагових коефіцієнтів кожного періоду. Ця функція має такий синтаксис:

=АМГД (вартість; залишок; час_життя; період), де

- вартість - початкова вартість майна;

- залишок - залишкова вартість після повної амортизації (ліквідна вартість);

- час_життя - кількість періодів, протягом яких майно амортизується (час повної амортизації);

- період - це період, для якого необхідно обчислити амортизацію.

Аргументи час життя та період повинні задаватися в однакових одиницях вимірювання.

ЗАВДАННЯ ТА ПОРЯДОК ВИКОНАННЯ РОБОТИ

Розв’язати задачу: При якій річній ставці банку вигідно надати кредит підприємству в сумі 5 000 000 грн., якщо керівництво підприємства зобов’язується повернути борг через рік – 2 000 000.грн., через два роки – 1 900 000 грн., через три роки - 1600 000 грн.?
Розробити форми подання вхідної та вихідної інформації у вигляді таблиць.
За допомогою функції НПЗ розрахувати чистий поточний об’єм вкладу. Для автоматизації заповнення таблиці у комірці, що містить назви строків надання кредиту, ввести слова "рік", "роки", "років" в залежності від кількості років за допомогою функції ЕСЛИ.
Застосувати засіб "Підбір параметра" для визначення оптимальної річної відсоткової ставки і проаналізувати, яка ефективність надання кредиту.

Хід виконання

Побудуйте таблицю (рис. 3.1) в табличному процесорі MS Excel.

Для автоматизації заповнення таблиці у комірці, що містить назви строків надання кредиту, введіть таку формулу в комірку С5.

=ЕСЛИ(B5=1;"рік";ЕСЛИ(И(B5>=2;B5<=4);"роки";"років"))

Дана формула виводить в залежності від числових значень вмісту комірки В6 слово "рік", "роки" або "років".

В комірку В6 введемо довільне значення річної облікової ставки, наприклад 8%. Для розрахунку чистого поточного об’єму в комірку В7 вводимо функцію =НПЗ(B6;B2:B4).

Визначте чи вигідно буде при даній річній ставці банку надати кредит підприємству. Для цього уведіть у комірку В8 наступну умову:

=ЕСЛИ (B1<B7;”Вигідно банку надати кредит підприємству”; ЕСЛИ(В7=В1;”Варіанти рівносилі”;”Банку невигідно надати кредит підприємству”))

Для визначання відсоткової ставки введіть команду Сервіс-Підбір параметра та заповніть поля вводу вікна "Підбір параметра".

В результаті дії програми "Підбір параметра" було розраховано річну відсоткову ставку для суми займу 5000000 грн. на термін 3 роки при змінних величинах щорічних виплат, яка становить 5%. Це значення автоматично з’явиться в комірці В6.

Підприємство бере кредит 100 000 грн. Необхідно розрахувати розміри основних платежів, сплати за відсотками загальної щорічної сплати та залишку боргу (таблиця 3.1.) на прикладі позики 100 000 грн. строком на 5 років при річній ставці 3%, застосувавши функції ПЛПРОЦ та ОСНПЛАТ.

Таблиця 3.1.

Роки

Розмір основних платежів, грн.

Розмір сплати за відсотками, грн.

Загальна сума платежів, грн

Залишок боргу, грн. (загальний борг складається з суми позики та сплати за відсоткам)

=ОСНПЛАТ (3%;1;5;100000)

отримуємо результат 18835,50

=ПЛПРОЦ (3%;1;5;100000)

отримуємо результат 3000,00

=ОСНПЛАТ(3%;1;5;

100000)+ ПЛПРОЦ (3%;1;5;

100000), отримуємо результат 21835,46

отримуємо результат 87341,83

=ОСНПЛАТ (3%;2;5;100000)

отримуємо результат

19400,52

=ПЛПРОЦ (3%;2;5;100000)

отримуємо результат

2434,94

=ОСНПЛАТ(3%;2;5;

100000)+ ПЛПРОЦ(3%;2;5;

100000) отримуємо результат

21835,46

отримуємо результат 65506,37

=ОСНПЛАТ(3%;3;5;100000)

отримуємо результат

19982,54

=ПЛПРОЦ (3%;3;5;100000)

отримуємо результат

1852,92

=ОСНПЛАТ(3%;3;5;

100000)+ ПЛПРОЦ(3%;3;5;

100000) отримуємо результат

21835,46

=100000+ ПЛПРОЦ (3%;1;5;-100000)+ ПЛПРОЦ (3%;2;5;-100000)+ ПЛПРОЦ (3%;3;5;-100000)+ ПЛПРОЦ (3%;4;5;-100000)+ ПЛПРОЦ (3%;5;5;-100000) – ОСНПЛАТ (3%;1;5;-100000)- ПЛПРОЦ (3%;1;5;-100000)- ОСНПЛАТ (3%;2;5;-100000)- ПЛПРОЦ (3%;2;5;-100000)- ОСНПЛАТ (3%;3;5;-100000)- ПЛПРОЦ (3%;3;5;-100000)

отримуємо результат 43670,91

=ОСНПЛАТ(3%;4;5;100000)

отримуємо результат

20582,01

=ПЛПРОЦ (3%;4;5;100000)

отримуємо результат

1253,44

=ОСНПЛАТ(3%;4;5;

100000)+ ПЛПРОЦ(3%;4;5;

100000) отримуємо результат

21835,46

=100000+ ПЛПРОЦ (3%;1;5;-100000)+ ПЛПРОЦ (3%;2;5;-100000)+ ПЛПРОЦ (3%;3;5;-100000)+ ПЛПРОЦ (3%;4;5;-100000)+ ПЛПРОЦ (3%;5;5;-100000) – ОСНПЛАТ (3%;1;5;-100000)- ПЛПРОЦ 3%;1;5;-100000)- ОСНПЛАТ (3%;2;5;-100000)- ПЛПРОЦ (3%;2;5;-100000)- ОСНПЛАТ (3%;3;5;-100000)- ПЛПРОЦ (3%;3;5;-100000)- ОСНПЛАТ (3%;4;5;-100000)- ПЛПРОЦ (3%;4;5;-100000)

отримуємо результат 21835,46

=ОСНПЛАТ (3%;5;5;100000)

отримуємо результат

21199,50

=ПЛПРОЦ(3%;5;5;100000)

отримуємо результат

635,98

=ОСНПЛАТ(3%;5;5;

100000)+ ПЛПРОЦ(3%;5;5;

100000) отримуємо результат

21835,46

отримуємо результат 00000,00

Для визначення розміру грошових коштів на рахунку наприкінці двох років, якщо фізична особа вкладе по 300 грн. щомісячно, поточна сума внеску 7200 грн. при річній ставці 6%, застосуйте фінансову функцію БЗ. Формула =БЗ(6%;2;300*12;7200) повертає значення 15505,97 грн.
Для визначення розміру річної відсоткової ставки за один період виплат, якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн., застосуйте фінансову функцію НОРМА. Формула =НОРМА(7*12;-150;8000) повертає значення розміру щомісячної відсоткової ставки в 1%, а для розрахунку річної відсоткової ставки значення 1% поможемо на 12 і отримаємо значення 12%.
Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Формула =ВИДОХ(120000;25000;27000;35000;38000;40000) повертає внутрішню швидкість обороту, яка становить 11%. Якщо бар’єрна ставка дорівнює 10%, можна розглядати купівлю кондомініуму як привабливе вкладення.
Припустимо, що ви погодилися на купівлю кондомініуму за 120 000 $. Протягом наступних 5 років ви очікуєте отримати 25000$, 27000$, 35000$, 38000$ та 40000$ чистого рентного прибутку. Фінансова ставка дорівнює 10%, а ставка реінвестування - 8%. Використовуючи формулу =МВСД(120000;25000;27000;35000;38000;40000;10%;8%), ми отримаємо модифіковану внутрішню швидкість обороту, яка дорівнює 10%.
Припустимо, що необхідно визначити амортизацію приладу з початковою вартістю 5000 грн, яка має час життя 5 років (60 місяців) і ліквідну вартість 100 грн. Формула =ДДОБ(5000;100;60;1) повідомляє, що амортизація методом двократного обліку за перший місяць складає 166,67 грн.
Припустимо, що ви купили обладнання на суму 15000 грн. в кінці першого кварталу поточного року і це майно через 5 років буде мати ліквідну вартість 2000 грн. Щоб визначити амортизацію цього майна за наступний рік (з 4 по 7 квартал його використання), введіть формулу =ПДОБ(15000;2000;20;3;7). Амортизація за цей період складе 3670,55 грн.
Для розрахунку величини амортизації обладнання за 10 років експлуатації загальною початковою вартістю 340000 дол.США та остаточною вартістю 10000 дол.США методом рівномірної амортизації застосуйте наступну формулу =АМР(340000;10000;10), яка повертає значення амортизації за кожен рік у сумі 33000 грн.
Для розрахунку величини амортизації по рокам обладнання за 10 років експлуатації загальною початковою вартістю 340000 дол.США та остаточною вартістю 10000 дол.США методом постійного обліку амортизації застосуйте функцію АМГД. У таблиці 3.2. наведені формули і результати обчислення.

Таблиця 3.2.

Рік експлуатації	Формула
1	=АМГД(340000;10000;10;1) отримуємо результат 60000 грн.
2	=АМГД(340000;10000;10;2) отримуємо результат 54000 грн.
3	=АМГД(340000;10000;10;3) отримуємо результат 48000 грн.
4	=АМГД(340000;10000;10;4) отримуємо результат 42000 грн.
5	=АМГД(340000;10000;10;5) отримуємо результат 36000 грн
6	=АМГД(340000;10000;10;6) отримуємо результат 30000 грн.
7	=АМГД(340000;10000;10;7) отримуємо результат 24000 грн.
8	=АМГД(340000;10000;10;8) отримуємо результат 18000 грн.
9	=АМГД(340000;10000;10;9) отримуємо результат 12000 грн
10	=АМГД(340000;10000;10;10) отримуємо результат 6000 грн

Зберегти результати на сервері.
Оформити звіт, зробити висновки і виконати контрольні завдання.

Контрольні завдання

Визначити розмір грошових коштів на рахунку наприкінці 3 років, якщо фізична особа вкладе по 200 грн. щомісячно, загальна сума внеску 7800 грн. при річній ставці 9%.
Визначити розмір річної відсоткової ставки за один період виплат, якщо необхідно отримати 8000 грн. на протязі 7 років при щомісячній сплаті 150 грн.
Розрахувати величину амортизації обладнання за 10 років експлуатації загальної початкової вартістю 340000 грн. та остаточною вартістю 1000 грн. методом рівномірної амортизації.
Визначити розміри основних платежів по рокам на прикладі позики 100000 грн. строком на 5 років при річній ставці 3%.
Визначити розміри сплати по відсоткам загальної щорічної сплати на прикладі позики 100000 грн. строком на 5 років при річній ставці 3%.
За допомогою функції НПЗ розрахувати чистий поточний об’єм вкладу при річній відсотковій ставці 10%, щомісячній сплаті 400 грн. строком на 7 років.
Визначити кількість періодів виплат (у роках) кредиту розміром 30000 грн. при річній ставці 12%, щомісячній сплаті 380 грн.
Визначити розмір щомісячної сплати кредиту, розміром 30000 грн. при річній відсотковій ставці 9%, строк сплати кредиту 7 років.

З повагою ІЦ "KURSOVIKS"!