Стандартні фінансові функції

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

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

• Функція ПЗ повертає поточне значення внеску, яке визначається дисконтуванням (зведенням до вартості в поточний момент) надходжень за цим внеском. Якщо поточна вартість надходжень перевищує внесок, то таке вкладення вважають вдалим. Синтаксис функції такий: ПЗ(Ставка;Кпер;Выплата;Бз;Тип).

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

Якщо вартість внеску виплачують авансом, її не включають у список аргументів Значение, а віднімають від значення функції НПЗ. Функція НПЗ має дві суттєві відмінності від функції ПЗ:

• НПЗ допускає використання виплат змінної величини, у той час як ПЗ припускає, що значення виплат постійні;

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

• Функція БЗ повертає майбутнє значення внеску на основі періодичних постійних платежів і постійної процентної ставки. Синтаксис функції такий: БЗ(Ставка;Кпер;Выплата;Нз;Тип).

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

• Функція КПЕР обчислює кількість періодів, необхідних для погашення позики при заданій величині періодичних виплат. Синтаксис функції такий: КПЕР(Ставка;Кпер;Нз;Бз;Тип).

Варіанти завдань

Варіант 1

1. На робочому аркуші з ім’ям «Телефони» побудуйте таблицю, яка містить відомості про продаж телефонів.

2. Для кожної марки телефонів визначте: а) мінімальну та максимальну ціну реалізації; б) мінімальну та максимальну кількість реалізованих телефонів.

3. Для кожного покупця визначте кількість придбаних ним моделей n і загальну кількість товарів N (СЧЁТЕСЛИ, СУММЕСЛИ).

4. Для кожної марки телефонів обчисліть середню ціну та стандартне відхилення (СРЗНАЧ,СТАНДОТКЛ).

5. Протягом місяця в середньому реалізується 20 одиниць кожної з моделей марки PANASONIC. Пропонується придбати 100 одиниць довільної моделі за 1500 у. о. Скориставшись табличними даними, визначте моделі, які доцільно придбати в такій ситуації. Рівень річної процентної ставки – 7 %.

Наприклад, місячний прибуток від продажу моделі КХ-100 становить 20*(18-1500/100) = 60, норма дорівнює 7%/12, КПЕР = 100/20 = 5 та ПЗ = ПЗ (7%/12;5;60) = -294,82. Оскільки ПЗ за модулем менше суми внеску 1500, купувати цю модель недоцільно.

6. У таблицю зведено дані про проекти з інвестування коштів на розробку нових моделей телефонів. Проекти потребують різних сум авансу і передбачають річні прибутки (збитки) у розмірах, що наведені у графі «Значення». Визначте незбиткові проекти.

Наприклад, для першого проекту НПЗ становить НПЗ(5%;-1000; 1000;2000) – 2000 = -317,68 < 0, тому цей проект збитковий.

7. За даними таблиці обчисліть суми, які будуть нагромаджені на банківському рахунку через 5, 10, 15, 20 років, якщо розмір щомісячних перерахувань становить 200, 500, 1000 у.о. Виплати здійснюються на початку періоду. Річна процентна ставка – 11 %. Наприклад, якщо сума внеску становить 200 у.о., то через 5 років буде нагромаджено суму Б3(11%/12; 5*12; -200;1) = = 16049,40.

8. У таблиці наведено дані про отримані підприємством позики і терміни їх погашення за роками. Виплати повинні здійснюватися наприкінці кожного місяця. Обчисліть розмір виплат для кожної з позик.

Наприклад, для першої позики розмір щомісячних виплат становить ППЛАТ(С2/12;В2*12;В2) = -59,05.

9. У таблиці наведено дані про отримані підприємством кредити і умови їх погашення. Виплати здійснюються щомісяця наприкінці періоду. Обчисліть термін погашення кредитів і визначте ті з них, які будуть виплачені протягом першого півріччя.

Наприклад, для першого кредиту розміром 3000 у. о. термін погашення КПЕР (4%/12;-300;3000) = 10,2 місяців.

Варіант 2

1. Побудуйте на аркуші «Замовлення» таблицю з інформацією про замовлення фірми.

2. Розрахуйте кількість та вартість виконаних і невиконаних замовлень та їх вартісну частку в загальній сумі (СЧЁТЕСЛИ,СУММЕСЛИ),

частку =С2/СУММ($С$2:$С$3); при цьому використайте формат відсотків.

3. Визначте для кожного клієнта середнє значення вартості замовлення як частку вартості й кількості зроблених ним замовлень.

4. Наприклад, кількість замовлень, зроблених фірмою «Гранд», обчисліть за формулою =СЧЁТЕСЛИ(Замовлення!$В$2:$Б$11;»Гранд»), їх вартість – за формулою =СУММЕСЛИ (Замовлення!$О$2:$Б$10; «Гранд»;Замовлення!$Р$2:$Р$ 11), середнє значення – за формулою =ВЗ/В2.

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

6. Обчисліть комісійні кожного з виконавців з розрахунку 8 % обсягу наданих послуг. Обчисліть сумарні комісійні всіх виконавців.

Наприклад, Іванов надав послуг на суму =СУММЕСЛИ(Замовлення!$Е$2:$Е$11;A2; 3aмовлення!$F$2:$F$11), а його комісійні становлять =В2*$С$1. Сумарну вартість наданих послуг обчисліть за формулою =СУММ(В2:В4).

7. Нехай середній місячний прибуток становить 180 гр. од. Визначте, яка сума нагромадиться на банківському рахунку через 1, 2, 3, 4, 5 років при рівні процентної ставки 5, 8, 10 % річних, якщо щомісячна виплата становитиме 15 % прибутку.

Наприклад, сума нагромаджень через рік при 5 % річних становитиме =БЗ($А2/12;В$ 1 * 12;-180* 15%).

8. Організація одержала кілька позик на різні терміни під 8 % річних. Розрахуйте розмір щомісячних виплат для всіх позик.

Наприклад, розмір місячної виплати для першої позики становить =ППЛАТ(8%;С2*12;В2).

9. За даними п. 7 про щомісячні виплати за позиками визначте щомісячний та щорічний розміри всіх виплат у першому, другому та третьому роках.

Наприклад, щомісячний розмір виплат у другому році становить =СУММЕСЛИ(Аркуш7!С2:С6;»<=2»;Аркуш7!D2:D6) (якщо таблиця п. 7 розміщується на аркуші Аркуш7), загальний розмір виплат за другий рік становить =С2*12, а разом за три роки =СУММ(ВЗ:DЗ).

Варіант З

1. На аркуші «Залишки» підготуйте таблицю з даними про залишки товарної продукції на складі та визначіть їх вартість.

2. Обчисліть сумарний залишок товару одного найменування в кількісному та вартісному вираженні і визначте середню вартість 1 м².

Наприклад, залишки паркету на складі обчисліть за формулою =СУММЕСЛИ (Залишки!$В$2:$В$11;А2;Залишки!D2:$D$11), загальну вартість – за формулою =СУММЕСЛИ(Залишки!$В$2:$В$1l;A2;3aлишки!$F$2:$F$l1), середню вартість 1 м² – за формулою =С2/В2.

3. Для кожної країни-виробника визначте її частку в загальній вартості товарів.

Наприклад, вартість бельгійських товарів обчисліть за формулою =СУММЕСЛИ (Залишки!$С$2:$С$11;А2;Залишки!$Р$2:$Р$11); сумарну вартість товарів – за формулою =СУММ(В2:В5), а частку бельгійських товарів – за формулою =В2/$В$6. До стовпця «Частка» застосуйте формат відсотків.

4. Для кожного найменування товару визначте кількість виробників, мінімальну, максимальну та середню ціну товару.

5. Виділяється $40000 на реалізацію одного з проектів, відомості про які наведено в таблиці. Виберіть з них незбиткові.

Наприклад, для першого проекту його поточну вартість розрахуйте за формулою =П3(C2;D2;E2).

6. Визначте, при якому щомісячному внеску – $ 1000, $ 2000, $ 3000, $ 4000 чи $ 5000 – позику розміром $ 40000 можна погасити менше як за рік, якщо рівень процентної ставки становитиме 5, 8 та 10%.

Наприклад, якщо внесок становить $ 1000 при рівні процентної ставки 5 %, то термін погашення позики за роками обчисліть за формулою =КПЕР($А$3/12;-В2;40000)/12.

7. Порівняйте розміри виплат з погашення позики розміром $ 40000, узятої на 5 років, якщо вони виплачуватимуться наприкінці кожного місяця, кварталу чи року. Обчислення здійсніть для рівнів процентної ставки 3, 5, 8 і 6 %.

Наприклад, при ставці 3 % і щоквартальній виплаті розмір виплат обчисліть за формулою =ППЛАТ($А$3/4;5*4;40000).

8. Визначте, яка сума нагромадиться на банківських рахунках, якщо прибуток за перший рік з п.5 покласти в банк на 3, 6, 9, 12 місяців при тих же рівнях процентної ставки.

Наприклад, для річного прибутку 12400 у. о. і процентній ставці З % суму нагромаджень за три місяці обчисліть за формулою =БЗ($С$3/12;E$2;0;-$D$3).

Варіант 4

1. Створіть на аркуші «Автомобілі» таблицю з інформацією про продаж автомобілів у першому кварталі поточного року.

2. Обчисліть кількість і вартість сплачених та несплачених замовлень, а також їх вартісну частку в загальній сумі.

Наприклад, кількість сплачених замовлень обчисліть за формулою =СУММЕСЛИ(Автомобші!$G$2:$G$11 ;А2;Автомобілі!$Е$2:$Е$11), їх вартість – за формулою =СУММЕСЛИ(Автомобілі!$G$2:$G$1l;A2;Aвтомобілі!$F$2:$F$l1), сукупну вартість замовлень – за формулою =СУММ(С2:СЗ), а частку – за формулою =С2/СУММ($С$2:$С$3). При цьому скористайтесь форматом відсотків.

3. Визначте кількість і вартість автомобілів, проданих у кожному місяці.

Наприклад, кількість проданих автомобілів у січні обчисліть за формулою =СУММЕСЛИ(Автомобілі!В2:В11;А2;Автомобілі!$Е$2:$Е$11), їх вартість – за формулою =СУММЕСЛИ(Автомобілі!$В$2:$В$11;A2;Aвтомобілі!$F$2:$F$11).

4. Визначте вартість мінімального і максимального замовлень у кожному місяці.

5. З’ясуйте, яка марка автомобіля користується найбільшим попитом, обчисливши кількість проданих автомобілів кожної марки та їх частку в загальній кількості.

Наприклад, кількість проданих автомобілів марки BMW-750 обчисліть за формулою =СУММЕСЛИ(Автомобілі!$С$2:$С$11;А2;Автомобілі! $Е$2:$Е$11), загальну кількість проданих автомобілів – за формулою =СУММ(В2:В5), а частку автомобілів BMW-750 у загальній кількості – за формулою =В2/$В$6.

6. Суму 28240 у. о. у розмірі 5 % обсягу продаж у першому кварталі покладено в банк під 6 % річних. Обчисліть прибуток від такого вкладення, якщо рахунок закрити через 1, 2,..., 5 років.

Наприклад, суму нагромаджень через рік обчисліть за формулою =БЗ(6%;А2;0;-28240), а прибуток – за формулою =В2-28240.

7. Для закупівлі автомобілів у кількох банках було взято позики на різні терміни і під різний відсоток. Обчисліть суму щомісячних виплат за всіма позиками.

Наприклад, суму місячної виплати для першої позики обчисліть за формулою =ППЛАТ(С2/12;D2*12;В2).

8. За даними п. 7 про щомісячні виплати за позиками визначте загальну суму коштів, необхідну у першому, другому та третьому році для їх погашення щомісячно та за весь рік.

Наприклад, щомісячну суму виплат у другому році обчисліть за формулою =СУММЕСЛИ(Аркуш7!D2:D6;»<=2»;Аркуш7!Е2:Е6) (якщо таблиця п. 7 розміщується на аркуші Аркуш7); загальну суму виплат за другий рік – за формулою =С2*12, сумарні виплати за три роки – за формулою =CУMM(B3:D3).

Варіант 5

1. Підготуйте на аркуші «Путівки» таблицю з даними про результати продажу туристичних путівок і обчисліть загальну вартість проданих путівок.

2. Оцініть вартість однієї доби перебування в кожній країні.

Наприклад, середньодобову вартість перебування у Болгарії можна обчислити за формулою

=СУММЕСЛИ(Путівки!$В$2:$В$П;А2;Путівки! $D$2:$D$11), СУММЕСЛИ(Путівки!$В$2: $В$11;А2;Путівки! $С$2:$С$11).

3. Визначте найпопулярніший тур, обчисливши кількість проданих путівок у кожному напрямі. Наприклад, кількість проданих путівок до Болгарії обчисліть за формулою =СУММЕСЛИ(Путівки!$В$2:$В$11;А2;Путівки!$Е$2:$Е$11).

4. Визначте частку кожної країни в загальній вартості проданих путівок.

Наприклад, вартість проданих путівок до Болгарії обчисліть за формулою =СУММЕСЛИ(Путівки!$В$2:$В$11;А2;Путівки!$D$2:$D$11), сумарну вартість проданих путівок – за формулою =СУММ(В2:В5); частку проданих путівок до Болгарії – за формулою =В2/$В$6. При цьому застосуйте відсотковий формат для стовпця «Частка».

5. Визначте середню, мінімальну та максимальну вартості путівок для кожного напряму туру.

6. До реалізації пропонуються кілька проектів з розвитку нових напрямів турів. Проекти потребують однакової суми інвестицій, що становить $ 3000, розраховані на 5 років, але характеризуються різними рівнями прибутків/збитків для кожного року реалізації. Визначте найприбутковіший проект. Рівень процентної ставки – 8 %.

Наприклад, для проекту «Панама» скористайтесь формулою =HП3(8%;B3:F3).

7. Порівняйте розміри виплат з погашення позики розміром $ 3000, узятої на 5 років, якщо вони виплачуватимуться наприкінці кожного місяця, кварталу, року. Обчислення здійсніть для рівнів процентної ставки 6, 8, 10 і 12 %.

Наприклад, при ставці 6 % і щоквартальній виплаті розмір виплат обчисліть за формулою =ППЛАТ($А$3/4;5*4;3000). 8. Визначте, при якому розмірі щомісячних виплат позику розміром $ 3000 буде погашено менш як за рік. Розгляньте рівень процентної ставки 6, 8, 10 і 12%.

Наприклад, термін виплати (у роках) для розміру виплати 100 і рівня процентної ставки 6 % обчисліть за формулою =КПЕР($А$3/12; -В2;3000)/12.

Варіант 6

1. Створіть на аркуші «Принтери» таблицю з інформацією про продаж принтерів у першому кварталі поточного року.

2. Обчисліть кількість і вартість сплачених та несплачених замовлень, а також їх вартісну частку в загальній сумі.

Наприклад, кількість сплачених замовлень обчисліть за формулою =СУММЕСЛИ(Принтери!$G$2:$G$11;А2;Принтери!$Е$2:$Е$11), їх вартість – за формулою =СУММЕСЛИ(Принтери!$G$2:$G$1 l;A2;Принтери!$F$2:$F$l 1), вартість всіх замовлень – за формулою =СУММ(С2:СЗ), частку – за формулою =С2/СУММ($С$2:$С$3). При цьому скористайтесь форматом відсотків.

3. Визначте кількість та вартість принтерів, проданих у кожному місяці.

Наприклад, кількість проданих принтерів у січні обчисліть за формулою =СУММЕСЛИ(Принтери!В2:В11;А2;Принтери!$Е$2:$Е$11), їх вартість – за формулою =СУММЕСЛИ(Принтери!$В$2:$В$11;А2;Принтери!$F$2:$F$11).

4. Визначте вартість мінімального і максимального замовлень в кожному місяці.

5. З’ясуйте, який вид принтерів користується найбільшим попитом, обчисливши кількість проданих принтерів кожного виду та їх частку в загальній кількості.

Наприклад, кількість проданих принтерів Minolta PagePro обчисліть за формулою =СУММЕСЛИ(Принтери!$С$2:$С$11;А2;Принтери!$Е$2:$Е$11), загальну кількість проданих принтерів – за формулою =СУММ(В2:В5), частку принтерів Minolta PagePro у загальній кількості – за формулою =В2/$В$6.

6. Суму 7167 грн у розмірі 3 % обсягу продаж у першому кварталі покладено в банк під 5 % річних. Обчисліть прибуток від такого вкладення, якщо рахунок закрити через 1, 2,..., 5 років.

Наприклад, суму нагромаджень через рік обчисліть за формулою =БЗ(5%;А2;0;-7167), прибуток – за формулою =В2-7167.

7. Для закупки принтерів у кількох банках взято позики на різний термін і під різні відсотки. Обчисліть розмір щомісячних виплат за всіма позиками.

Наприклад, розмір місячної виплати для першої позики обчисліть за формулою =ППЛAT(C2/12;D2*12;B2).

8. За даними п. 7 про щомісячні виплати за позиками визначте загальну суму коштів, необхідну в першому, другому, третьому році для їх погашення щомісячно та за весь рік.

Наприклад, щомісячний розмір виплат у другому році обчисліть за формулою =СУММЕСЛИ(Аркуш7!D2:D6;»<=2»;Аркуш7!Е2:Е6) (якщо таблиця п. 7 розміщується на аркуші «Аркуш7»), загальну суму виплат за другий рік – за формулою =С2*12; сумарні виплати за три роки – за формулою =CУMM(B3:D3).