ЛИНЕЙН(извест_значен_у;извест_значен_х;конст;статистика)

Аргументиизвест_значен_у; извест_значен_х – це множини значень, які отримані в результаті спостережень. Обов’язковим є тільки масив {у}. Якщо множина {х} опущена, то передбачається що це масив {1, 2, 3,…} такого ж розміру, як масив {у}.Третій і четвертий аргументи можна опустити.

Значення коефіцієнтів лінії регресії функція ЛИНЕЙН повертає у вигляді вектора – рядка (a, b). Для його обчислення треба виділити дві суміжні клітинки у рядку електронної таблиці і застосувати формулу масиву.

Задача 1. Невелика фірма мала продажі на суму 3100 грн., 4500 грн., 4400 грн., 5400 грн., 7500 грн. і 3100 грн. за перші шість місяців звітного року. Побудувати рівняння прямої парної регресії і спрогнозувати, яким може буде обсяг продаж у дев’ятому місяці.

Для розв’язування задачі складена наведена нижче електронна таблиця.

Обчислені значення коефіцієнтів лінії регресії містяться у клітинках В6:С6. В ці клітинки записана формула масиву {=ЛИНЕЙН(B3:G3)}, де B3:G3 – діапазон клітинок, в якому знаходяться відомі суми щомісячних продаж. В результаті обчислень отримано рівняння регресії Обсяг продаж=1000*N+2000, де N –номер місяця. Якщо в це рівняння підставити N=9, то по прогнозу обсяг продаж у вересні місяці може сягнути 11000 грн.

Використання фінансових функцій.

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

В MS Excel аргументами фінансових функцій, які далі будуть розглядатися, є такі величини:

· ставка – процентна ставка за період;

· кпер– загальна кількість платежів або періодів виплат або платежів;

· плт – виплата, яка здійснюється кожний період і не змінюється за весь час виплати;

· пс– теперішня вартість, тобто загальна сума, що рівноцінна на теперішній момент серії майбутніх платежів;

· бс – майбутня вартість або баланс, який треба досягти після останньої виплати;

· тип – логічне значення (0 або1); число 0 або його відсутність, якщо виплата здійснюється в кінці кожного періоду і число 1, якщо на початку.

Значення аргументів ставка та кпер залежать від режиму виплат (методу нарахування відсотків). У таблиці 4.2.4 показані значення таких аргументів, де N – кількість років, а K – річна відсоткова ставка.

Таблиця 4.2.4 – Значення аргументів

Якщо аргумент функції має значення 0, то його можна не вказувати. Аргументи у списку аргументів розділяються крапкою з комою, і якщо аргумент функції пропускають , то в цьому списку два розділювача (у даному випадку ;) мають бути поруч.

Розрізняють кредитну і депозитну процентні ставки, кредитна ставка є вищою за депозитну. Процентна ставка має бути узгодженою з тривалістю періоду (див. вище наведену таблицю). У прикладах, що будуть далі розглядатися, прийнята місячна депозитна ставка 5%, а кредитна – 6%.

У клітинки електронної таблиці фінансові функції, як і будь-які інші, доцільно вводити за допомогою майстра вставки функції, який можна запустити командою Вставка - Функция або натисканням однойменної кнопки на панелі інструментів Стандартная.

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

1. Функція БС(ставка;кпер;плт;пс;тип) призначена для обчислення майбутньої вартості теперішніх інвестицій пс на основі періодичних постійних (рівних по величині сум) виплат плт і постійної процентної ставки.

Задача 1. Інвестор вкладає в бізнес 3000 грн. (чи відкриває на цю суму рахунок у банку) на умовах 5% ставки прибутку щомісяця. Яка вартість інвестиції через 24 місяця?

Розв’язок задачі:

=БС(5%;24;;-3000) Відповідь: 9675,30 грн.

Аргументу пс (-3000) надано від’ємне значення, і це означає не отримання, а вкладення грошей.

Задача 2. Клієнт відкриває рахунок у банку на умовах 5% ставки прибутку щомісяця, кладе на рахунок 3000 грн. і планує на початку кожного місяця забирати з рахунку 100 грн. Яка сума буде на рахунку через 24 місяця?

Розв’язок задачі:

=БС(5%;24;100;-3000;1) Відповідь: 5002,59 грн.

Аргументу плт (100) надано додатне значення, і це означає отримання грошей.

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

Задача 3. Позику 3000грн. беруть за умови повернення в кінці кожного місяця 300 грн. і процентної ставки 6%. Скільки місяців потрібно для повернення позики?

Розв’язок задачі:

=КПЕР(6%;300;3000) Відповідь: 15,7 місяців.

3. Функція ПЛТ(ставка;кпер;пс;бс;тип) призначена для визначення суми періодичних виплат для погашення боргу пс. Така виплата складається з двох частин:

а) основна виплата ОСПЛТ(ставка;период;кпер;пс;бс;тип);

б) виплати за процентами ПРПЛТ(ставка;период;кпер;пс;бс;тип).

Задача 4. Бізнесмен взяв у банку кредит на суму 3000 грн. терміном на 12 місяців за умови щомісячного погашення позики і місячної ставки 6%. Визначити величину щомісячних виплат і її складові в кінці 5 місяця.

Розв’язки задачі:

=ПЛТ(6%;12;3000) Відповідь: -357,33 грн.;

=ОСПЛТ(6%;5;12;3000) Відповідь: -224,51 грн.;

=ПРПЛТ(6%;5;12;3000) Відповідь: -133,32 грн.

Отримали від’ємні числа, це означає, що бізнесмен віддає гроші банку на погашення кредиту.

4. Функція ПС(ставка;кпер;плт;бс;тип) обчислює сьогоднішню вартість низки майбутніх надходжень (ренти) від банку. Це дає змогу визначити вигідність інвестицій (капіталовкладень) у деякий бізнес.

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

Задача 5. Нехай для ведення деякого бізнесу потрібно вкласти сьогодні 3500 грн., а далі бізнес протягом 5 місяців приноситиме по 1000 грн. доходу (ренти) в кінці місяця. Депозитна ставка банку 5%. Чи варто займатися цим бізнесом?

Розв’язок задачі:

=ПС(5%;5;1000) Відповідь: -4329,43 грн.

Вартість бізнесу (гранично допустима інвестиція) складає 4329,43 грн. Оскільки цю суму потрібно вкладати, число отримане від’ємним. Бізнес вигідний, бо для його ведення потрібно лише 3500 грн. Якби потрібно було більше, ніж 4329,43 грн., то такий бізнес був би збитковим.

5. Функція СТАВКА(кпер;плт;пс;тип;предположение)

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

Задача 6. Бізнесмен звертається до банку за позикою (кредитом)т на суму 3000 грн. на 12 місяців за умови періодичних виплат 300 грн. протягом року в кінці кожного місяця. Визначити процентну ставку позики.

Розв’язок задачі:

=СТАВКА(12;-300;3000) Відповідь: 2,92%.

Якщо місячна депозитна ставка встановлена на рівні 5%, то такий кредит для банку є невигідним. Банк кредиту не надасть.

6. Функція ЧПС(ставка; значение1;значение2;…)

застосовується при оцінці ефективності інвестицій і дозволяє обчислити сьогоднішню вартість різних рент: значение1;значение2;…, які поступають в кінці рівномірних періодів.

Задача 6. У бізнес треба вкласти сьогодні 10000 грн. На наступні 3 роки очікуються річні доходи від бізнесу 3000 грн., 4200 грн., 6300 грн. Витрати залучення капіталу 10% річних. Розрахувати сьогоднішню вартість бізнесу і оцінити , чи є цей бізнес вигідним.

Розв’язок задачі:

=ЧПС(10%;3000;4200;6300) Відповідь: 11307,29 грн.

Оскільки затрати, вкладені у бізнес, 10000 грн. менше за його сьогоднішню вартість 11307,29 грн., то такий бізнес є вигідним. За 3 роки ведення цього бізнесу очікується прибуток 1307, 29 грн.