Фінансова функція плт. Функція ПЛТ

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

Кількісний аналіз фінансових даних в Excel під час проведення таких операцій зводиться до обчислення таких основних характеристик:

· поточної величини (present value – PV)потоку платежів;

· Майбутньої величини (Future value - FV)потоку платежів;

· величини окремого платежу (payment – ​​P);

· Норми прибутковості (ціни) у вигляді процентної ставки (interest rate - r);

· Число періодів проведення фінансової операції (наприклад, років, місяців, і т.д.).

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

Табличний процесор Excel надає широкі можливості щодо моделювання подібних розрахунків за допомогою відповідних вбудованих у Excel фінансових функцій: БC(), КПЕР(), СТАВКА(), ПЛТ(), ПС().

Усі функції цієї групи мають однаковий набір базових аргументів:

ü процентна ставка (норма прибутковості або ціна позикових коштів);

ü термін (число періодів) проведення операції;

ü величина періодичного платежу;

ü початкова сума;

ü майбутня вартість (величина) коштів;

ü тип нарахування відсотків (1- початок періоду, 0 – кінець періоду).

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

Припустимо, Ваша фірма вирішила створити спеціальний фонд для погашення своїх довгострокових зобов'язань (кредитів, позик), термін погашення яких настане, наприклад, через 5 років шляхом періодичного (щорічного) поповнення депозиту в банку. Початкова сума депозиту становить 10 000 тис. руб. Обсяг щорічних платежів - 1000 тис. руб. Процентна ставка за банківським депозитом – 15%.

Необхідно визначити величину фонду на кінець 5-го року.

Для вирішення поставленого завдання виконайте такі дії:

1) Введіть вихідні дані на робочий лист. Діапазон таблиці С3:С6 містить вхідні дані до розрахунку величини фінансового фонду, тобто. майбутньої вартості вкладень (інвестицій).

2) Встановіть курсор у комірку робочого аркуша, де буде розрахована майбутня вартість вкладень (депозиту), у разі у комірку C7.

3) Виконайте команду Формули / Бібліотека функцій / Вставити функцію або натисніть Рядки формул . У категорії Фінансові зі списку виберіть опцію БС(). Натисніть кнопку ОК.

4) Excel виведе вікно введення аргументів вибраної функції. Введіть у кожне поле запиту посилання на комірку, яка містить потрібне значення.

Функція БС()– дозволяє визначити майбутню величину вкладу (Future Value – FV)на основі періодичних постійних платежів за заданих величин процентної ставки, числа періодів виплат і початкової суми вкладу.

Функція має наступний синтаксис:

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

де: норма- Відсоткова ставка (норма прибутковості за депозитом);

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

виплата- Величина періодичного платежу;

нз- Початкова вартість вкладень (депозиту);

тип– тип нарахування відсотків є необов'язковим аргументом.

(0 - Наприкінці періоду; 1 - На початку періоду). За замовчуванням нарахування відсотків здійснюється наприкінці періоду.

Слід звернути увагу до особливості завдання аргументов:

Якщо процентна ставка задається як абсолютна величина, вона повинна мати вигляд десяткового дробу, наприклад, як у прикладі: 15% – 0,15. Періодичний платіж та початкова сума задаються зі знаком мінус, т.к. у цій операції для фірми вони означають виплати (витрата) коштів. Такі правила застосовні всім фінансових функцій. Залежно від умови поставленої задачі, значення періодичного платежу та початкової вартості можуть бути введені у функцію як у вигляді позитивних, так і негативних величин. Це від того, який економічний суб'єкт проводить подібні розрахунки. Крім того, можна на етапі введення аргументів визначити кінцевий результат, який повертається функцією, який відображається внизу діалогового вікна введення аргументів.

MS Excel при розрахунках показників фінансових ануїтетів висловлює кожен показник виходячи з наступного співвідношення:

де: НC- Початкова (поточна) вартість вкладу;

БС i- Майбутня вартість вкладу через число періодів i;

норма- Відсоткова ставка (норма прибутковості);

виплата– періодичний платіж;

i- Порядковий номер періоду поведінки фінансової операції;

тип- Тип нарахування відсотків.

Таким чином, майбутня вартість вкладень визначається за такою формулою:

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

=БС (0,15; 5; -1000; -10000) (Повертається результат: 26855,95 тис. руб.).

Для банку, що визначає майбутню суму повернення коштів за цим депозитом, функція мала б такий вигляд:

=БС (0,15; 5; 1000; 10000) (Повертається результат: -26855,95 тис. руб.).

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

Припустимо, необхідно визначити кількість періодів платежів (у цьому прикладі – кількість років). Функція буде виглядати так:

=КПЕР (0,15;1000;10000;26855,95)(Повертається результат: 5),

де: 0,15 - Відсоткова ставка за депозитом; 1000 – періодичний платіж; 10000 - Початкова сума депозиту; 26855,95 - Майбутня величина депозиту.

Функція СТАВКА()- обчислює відсоткову ставку, яка в залежності від умов операції може виступати або як ціна, або як норма рентабельності даної операції. Має аргументи:

СТАВКА (кпер, пт, пс, [бс], [тип], [прогноз])

де: кпер- Обов'язковий. Загальна кількість періодів платежів для щорічного платежу;

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

пс- Обов'язковий. Наведена (поточна) вартість, тобто загальна сума, яка зараз рівноцінна ряду майбутніх платежів;

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

тип- Необов'язковий. Число 0 або 1, що означає, коли має здійснюватися виплата (0 або опущений – наприкінці періоду, 1 – на початку періоду;

прогноз- Необов'язковий. Ймовірна величина ставки. Якщо аргумент "прогноз" опущений, передбачається, що його значення дорівнює 10%.

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

=СТАВКА (5; -1000; -10000; 26855,95) (Повертається результат: 15%).

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

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

ПЛТ(ставка; кпер; пт; [бс]; [тип])

Де: ставка -обов'язковий аргумент. Процентна ставка за позикою;

кпер - пробезвальний аргумент. Загальна кількість виплат за позикою;

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

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

тип- Необов'язковий аргумент. Число 0 (нуль) або 1, що означає, коли має здійснюватися виплата.

Формула розрахунку періодичного платежу, виходячи з вищенаведеного співвідношення, матиме такий вигляд:

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

=ПЛТ (0,15; 5; -10000; 26855,95) (Повертається результат: -1000).

Отриманий негативний результат фірми означає відтік коштів. Для банку, відповідно, навпаки.

Функція ПС()– дозволяє визначити поточну (тобто на момент початку операції – present value) вартість ануїтету , якщо відомі 4 обов'язкові параметри (відсоткова ставка; число періодів; початкова вартість; майбутня вартість коштів). Має такі аргументи:

ПС (ставка, клер, пт, [бс], [тип])

де: ставка- Обов'язковий. Процентна ставка у період. Наприклад, якщо отриманий кредит на автомобіль під 10 відсотків річних та виплати здійснюються щомісяця, процентна ставка за місяць становитиме 10%/12 (0,83%). Як значення аргументу "ставка" потрібно ввести формулу 10%/12, 0,83% або 0,0083.

кпер- Обов'язковий. Загальна кількість періодів платежів для щорічного платежу. Наприклад, якщо отримано кредит на 4 роки на покупку автомобіля та платежі здійснюються щомісяця, то кредит має 4*12 (або 48) періодів. Як значення аргументу "кпер" у формулу потрібно ввести число 48.

пліт- Обов'язковий. Виплата, що проводиться у кожний період і не змінюється протягом усього щорічного платежу. Зазвичай аргумент "плт" складається з виплат в рахунок основної суми та платежів за відсотками, але не включає інші збори або податки. Наприклад, щомісячна виплата за кредитом у розмірі 10 000 грн. під 12 відсотків річних на 4 роки становитиме 263,33р. Як значення аргументу "плат" потрібно ввести у формулу число -263,33.

бс- Необов'язковий. Значення майбутньої вартості, тобто. бажаного залишку коштів після останнього платежу. Якщо аргумент "бс" опущений, передбачається, що він дорівнює 0 (наприклад, майбутня вартість позики дорівнює 0). Припустимо, що з певної мети потрібно накопичити 50 000 р. за 18 років: у цьому випадку майбутня вартість дорівнює 50 000 грн. Припустивши, що задана процентна ставка залишиться без змін можна визначити, яку суму необхідно відкладати щомісяця. Якщо аргумент "БС" опущений, необхідно використовувати аргумент "ПЛТ".

тип- Необов'язковий. Число 0 або 1, що означає, коли має здійснюватися виплата.

Для умови нашого завдання застосування цієї функції дозволяє отримати відповідь на запитання: Яку суму необхідно вкласти в банк на депозит, щоб отримати через 5 років величину вкладу 26855,95 тис. руб. при щорічному поповненні вкладу на 1000 тис. руб., Якщо річна банківська ставка становить 15%?».

Формула для визначення поточної (справжньої) вартості вкладу (ПС):

Для нашого прикладу синтаксис функції буде наступним:

=ПС (0,15; 5; -1000; 26855,95) (Повертається результат: -10000).

У разі, якщо періодичність виплат (надходжень) відмінна від річної, для будь-якої з розглянутих функцій у цьому розділі достатньо скоригувати відповідним чином аргументи норма, і кількість періодів ( i).

Припустимо, при щоквартальному поповненні вкладу та нарахування відсотків, функція ПС()набуде наступного вигляду:

=ПС (0,15 / 4; 5 * 4; -1000; 26855,95) (Повертається результат: 1035,09).

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

Для розрахунку величини ануїтетного платежу необхідно знати:

1. Суму кредиту.

2. Строк кредиту.

3. Величину відсотка за кредитом.

4. Періодичність нарахування відсотків за кредитом (щомісяця, щотижня і так далі), а точніше кількість платіжних періодів для виду відсотків.

Розглянемо приклад: нам потрібно розрахувати величину щомісячного ануїтетного платежу за кредитом у сумі 100 000.00 рублів, терміном на 2 роки, за ставкою 18 відсотків річних. У разі кількість платіжних періодів дорівнюватиме 12 оскільки у року 12 місяців (за умовою завдання ми розглядається річна відсоткова ставка).

Розрахунок величини ануїтетного платежу за допомогою формули

Для розрахунку ануїтетного платежу в Excel необхідно використовувати функцію ПЛТ.

В англійській версії Excel функція називається PMT.

Вона має 5 параметрів, з яких нам цікаві перші 3 ( ставка, кпер, пс), інші параметри є обов'язковими, тому їх ми вказувати не будемо.

Опис параметрів:

ставка - відсоткова ставка, наведена до одного платіжного періоду. Для нашого прикладу вона дорівнюватиме / =. Зверніть увагу, що відсоткова ставка має бути вказана у частках від 1. Разом отримуємо 0,015.

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

пс - сума кредиту.

В результаті отримуємо таку формулу: = ПЛТ (0,015; 24; 100000).

Отримане через формулу значення буде негативним, вносимо невелике виправлення =-ПЛТ(0,015; 24; 100000).

У результаті отримуємо величину ануїтетного платежу, що дорівнює 4 992,41 рубль.

Розрахунок величини ануїтетного платежу на VBA

Для розрахунку величини ануїтету необхідно використовувати функцію WorksheetFunction.Pmt, параметри цієї функції аналогічні тим, що використовуються функції для формул.

Розрахунок ануїтетного платежу на VBA за вказаним вище прикладом:

Annuitet = -WorksheetFunction.Pmt(0.015, 24, 100000)

Функція ПЛТ повертає суму періодичного платежу для ануїтету на основі сталості сум платежів та сталості процентної ставки. Функція має наступний синтаксис:

ПЛТ (ставка; кпер; пс; бс; тип).

Аргументи функції ПЛТ

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

Якщо вона виражена у відсотках за рік, то цю величину потрібно розділити на кількість періодів

Обов'язковий аргумент (постійна величина). Кількість, періодів виплат.

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

Обов'язковий аргумент. Наведена (поточна) вартість, тобто загальна сума, яка зараз рівноцінна ряду майбутніх платежів.

Примітка: Надається негативним числом у разі виплати коштів та позитивним – у разі їх отримання. Якщо аргумент опущений, він вважається рівним 0. І тут має бути зазначено значення аргументу пс.

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

Примітка: якщо аргумент "бс" опущений, передбачається, що він дорівнює 0 (наприклад, майбутня вартість позики дорівнює 0).

Необов'язковий аргумент. Вказує, коли має здійснюватись виплата.

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

Excel для Office 365 Excel для Office 365 для Mac Excel Online Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2016 для Mac Excel для Mac 2011 Excel для iPad Excel для iPhone для планшетів з Android Excel для телефонів з Android Excel Mobile Excel Starter 2010 Менше

ПЛТ - одна з фінансових функцій, що повертає суму періодичного платежу для ануїтету на основі сталості сум платежів та постійної процентної ставки.

Скористайтеся засобом розрахунку щомісячних виплат за позикою. При цьому ви дізнаєтесь, як використовувати функцію ПЛТ у формулі.

Синтаксис

ПЛТ(ставка; кпер; пс; [бс]; [тип])

Примітка: Докладніший опис аргументів функції ПЛТ див. у описі функції ПС.

Аргументи функції ПЛТ описані нижче.

Обов'язковий аргумент. Процентна ставка за позикою.

Кпер Обов'язковий аргумент. Загальна кількість виплат за позикою.

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

Необов'язковий аргумент. Майбутня вартість або баланс готівкою, яку потрібно досягти після останнього платежу. Якщо аргумент БЗ опущений, то передбачається, що він дорівнює 0 (нулю), тобто майбутнє значення позики дорівнює 0.

Тип Необов'язковий аргумент. Число 0 (нуль) або 1, що означає, коли має здійснюватися виплата.

Зауваження

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

Переконайтеся, що ви послідовні у виборі одиниць виміру для завдання аргументів "ставка" та "кпер". Якщо ви робите щомісячні виплати за чотирирічною позикою з розрахунку 12 відсотків річних, то використовуйте значення 12%/12 для завдання аргументу "ставка" та 4*12 для завдання аргументу "кпер". Якщо ви робите щорічні платежі з тієї ж позики, то використовуйте 12 відсотків для завдання аргументу "ставка" і 4 для завдання аргументу "кпер".

Порада. Для знаходження загальної суми, що виплачується протягом інтервалу виплат, помножте значення, що повертається функцією ПЛТ, на "кпер".

приклад

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового аркуша Excel. Щоб відобразити результати формул, виділіть їх та натисніть клавішу F2, а потім - клавішу ENTER. За потреби змініть ширину стовпців, щоб побачити всі дані.

Дані

Опис

	Річна процентна ставка
	Кількість місяців платежів
	Сума займу
Формула	Опис	Результат
ПЛТ(A2/12;A3;A4)	Щомісячний платіж за позикою відповідно до умов, зазначених як аргументи в діапазоні A2:A4.
ПЛТ(A2/12;A3;A4)	Щомісячний платіж за позикою відповідно до умов, зазначених як аргументи в діапазоні A2:A4, за винятком платежів, що підлягають сплаті на початку періоду.
Дані	Опис
	Річна процентна ставка
	Кількість місяців платежів
	Сума займу
Формула	Опис	Оперативний результат
ПЛТ (A12/12; A13 * 12; 0; A14)	Необхідна сума щомісячних платежів на виплату 50 000р. за 18 років.

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

Насамперед, треба сказати, що є два види кредитних платежів:

• Диференційовані;
• Ануїтетні.

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

При ануїтетної схеми використовується дещо інший підхід. Клієнт щомісяця вносить однакову суму загального платежу, що складається з виплат за тілом кредиту та оплати відсотків. Спочатку процентні внески нараховуються на всю суму позики, але в міру того, як тіло зменшується, скорочується нарахування відсотків. Але загальна сума оплати залишається незмінною з допомогою щомісячного збільшення величини виплат за тілом кредиту. Таким чином, з часом питома вага відсотків у загальному щомісячному платежі падає, а питома вага оплати по тілу зростає. При цьому загальний щомісячний платіж протягом усього терміну кредитування не змінюється.

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

Етап 1: розрахунок щомісячного внеску

Для розрахунку щомісячного внеску при використанні ануїтетної схеми в Екселі існує спеціальна функція – ПЛТ. Вона належить до категорії фінансових операторів. Формула цієї функції виглядає так:

ПЛТ (ставка; кпер; пс; бс; тип)

Як бачимо, зазначена функція має досить велику кількість аргументів. Щоправда, останні два з них не є обов'язковими.

Аргумент «Ставка»вказує на відсоткову ставку за певний період. Якщо, наприклад, використовується річна ставка, але платіж за позикою провадиться щомісяця, то річну ставку потрібно розділити на 12 і отриманий результат використовувати як аргумент. Якщо застосовується щоквартальний вид оплати, то цьому випадку річну ставку потрібно розділити на 4 і т.д.

"Кпер"означає загальну кількість періодів виплат за кредитом. Тобто, якщо позика береться на один рік із щомісячною оплатою, то кількість періодів вважається 12 якщо на два роки, то кількість періодів – 24 . Якщо кредит береться на два роки зі щоквартальною оплатою, то кількість періодів дорівнює 8 .

«Пс»вказує наведену вартість зараз. Говорячи простими словами, це загальна величина позики на початок кредитування, тобто та сума, яку ви берете в борг, без урахування відсотків та інших додаткових виплат.

«Бс»- Це майбутня вартість. Ця величина, яку складатиме тіло позики на момент завершення кредитного договору. У більшості випадків цей аргумент дорівнює «0» , оскільки позичальник наприкінці терміну кредитування має повністю розрахуватися з кредитором. Зазначений аргумент не є обов'язковим. Тому якщо він опускається, то вважається рівним нулю.

Аргумент «Тип»визначає час розрахунку: наприкінці чи початку періоду. У першому випадку він набуває значення «0» , а у другому – «1». Більшість банківських установ використовують саме варіант із оплатою наприкінці періоду. Цей аргумент також є необов'язковим, і якщо його опустити вважається, що він дорівнює нулю.

Тепер настав час перейти до конкретного прикладу розрахунку щомісячного внеску за допомогою функції ПЛТ. Для розрахунку використовуємо таблицю з вихідними даними, де вказано процентну ставку за кредитом ( 12% ), величина позики ( 500000 рублів) та термін кредиту ( 24 місяці). При цьому оплата провадиться щомісяця наприкінці кожного періоду.

Виділяємо елемент на аркуші, в який виводитиметься результат розрахунку, і клацаємо по піктограмі "Вставити функцію", Розміщену біля рядка формул.

Запуск віконця Майстри функцій. у категорії «Фінансові»виділяємо найменування «ПЛТ»і тиснемо на кнопку "OK".

Після цього відкривається вікно аргументів оператора ПЛТ.

В полі «Ставка»слід вписати величину відсотків у період. Це можна зробити вручну, просто поставивши відсоток, але у нас він вказаний в окремому осередку на аркуші, тому дамо на нього посилання. Встановлюємо курсор у полі, а потім клацаємо по відповідному осередку. Але, як ми пам'ятаємо, у нас у таблиці задано річну відсоткову ставку, а період оплати дорівнює місяцю. Тому ділимо річну ставку, а точніше посилання на комірку, в якій вона міститься, на число 12 , що відповідає кількості місяців на рік. Розподіл виконуємо прямо у полі вікна аргументів.

В полі "Кпер"встановлюється термін кредитування. Він у нас дорівнює 24 місяцям. Можна занести в поле число 24 вручну, але ми, як і в попередньому випадку, вказуємо посилання на місце розташування даного показника у вихідній таблиці.

В полі «Пс»вказується первісна величина позики. Вона рівна 500000 рублів. Як і попередніх випадках, вказуємо посилання елемент листа, у якому міститься даний показник.

В полі «Бс»вказується величина позики, після повної оплати. Як пам'ятаємо, це значення практично завжди дорівнює нулю. Встановлюємо в цьому полі число «0» . Хоча цей аргумент взагалі можна опустити.

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

Після того, як усі дані введені, тиснемо на кнопку "OK".

Після цього в комірку, яку ми виділили у першому пункті цього посібника, виводиться результат обчислення. Як бачимо, величина щомісячного загального платежу за позикою складає 23536,74 рубля. Нехай вас не бентежить знак "-" перед цією сумою. Так Ексель свідчить про те, що це витрата коштів, тобто, збиток.

Для того щоб розрахувати загальну суму оплати за весь термін кредитування з урахуванням погашення тіла позики та щомісячних відсотків, достатньо перемножити величину щомісячного платежу ( 23536,74 рубля) на кількість місяців ( 24 місяці). Як бачимо, загальна сума платежів за весь термін кредитування у нашому випадку склала 564881,67 рубля.

Тепер можна підрахувати суму переплати за кредитом. Для цього потрібно відібрати від загальної величини виплат за кредитом, включаючи відсотки та тіло позики, початкову суму, взяту в борг. Але ми пам'ятаємо, що перше з цих значень вже зі знаком «-» . Тому в нашому випадку виходить, що їх потрібно скласти. Як бачимо, загальна сума переплати за кредитом за весь термін склала 64881,67 рубля.

Етап 2: деталізація платежів

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

Для визначення величини оплати по тілу позики використовуємо функцію ОСПЛТ, Яка саме призначена для цих цілей. Встановлюємо курсор у комірку, що знаходиться у рядку «1»і в стовпці «Виплата за тілом кредиту». Тиснемо на кнопку "Вставити функцію".

Переходимо в Майстер функцій. у категорії «Фінансові»відзначаємо найменування «ОСПЛТ»і тиснемо кнопку "OK".

Запускається вікно аргументів оператора ОСПЛТ. Він має наступний синтаксис:

ОСПЛТ(Ставка; Період; Кпер; Пс; Бс)

Як бачимо, аргументи цієї функції майже повністю збігаються з аргументами оператора ПЛТ, тільки замість необов'язкового аргументу «Тип»додано обов'язковий аргумент «Період». Він вказує на номер періоду виплати, а в конкретному випадку на номер місяця.

Заповнюємо вже знайомі поля вікна аргументів функції ОСПЛТтими самими даними, що були використані для функції ПЛТ. Тільки враховуючи той факт, що в майбутньому застосовуватиметься копіювання формули за допомогою маркера заповнення, потрібно зробити всі посилання в полях абсолютними, щоб вони не змінювалися. Для цього потрібно поставити знак долара перед кожним значенням координат по вертикалі та горизонталі. Але легше це зробити, просто виділивши координати та натиснувши на функціональну клавішу F4. Знак долара буде розставлений у потрібних місцях автоматично. Також не забуваємо, що річну ставку потрібно розділити на 12 .

Але в нас залишається ще один новий аргумент, якого не мав функції ПЛТ. Цей аргумент «Період». У відповідне поле встановлюємо посилання на перший осередок стовпця «Період». Цей елемент листа містить у собі число «1», що означає номер першого місяця кредитування. Але на відміну від попередніх полів, у вказаному полі ми залишаємо посилання відносним, а не робимо з нього абсолютне.

Після того, як усі дані, про які ми говорили вище, введені, тиснемо на кнопку "OK".

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

Потім, як говорилося вище, слід скопіювати цю формулу інші осередки стовпця з допомогою маркера заповнення. Для цього встановлюємо курсор у нижній правий кут комірки, в якій міститься формула. Курсор перетворюється при цьому на хрестик, який називається маркером заповнення. Затискаємо ліву кнопку миші і тягнемо її до кінця таблиці.

У результаті всі осередки стовпця заповнені. Тепер у нас є графік виплати тіла позики помісячно. Як і говорилося вже вище, величина оплати за цією статтею з кожним новим періодом зростає.

Тепер нам потрібно зробити місячний розрахунок сплати за відсотками. Для цього будемо використовувати оператор ПРПЛТ. Виділяємо перший порожній осередок у стовпці «Виплата за відсотками». Тиснемо на кнопку "Вставити функцію".

У вікні, що запустилося Майстри функційу категорії «Фінансові»робимо виділення найменування ПРПЛТ. Виконуємо клацання по кнопці "OK".

Відбувається запуск вікна аргументів функції ПРПЛТ. Її синтаксис виглядає так:

ПРПЛТ(Ставка; Період; Кпер; Пс; Бс)

Як бачимо, аргументи цієї функції абсолютно ідентичні аналогічним елементам оператора ОСПЛТ. Тому просто заносимо у вікно ті самі дані, які ми вводили у попередньому вікні аргументів. Не забуваємо при цьому, що посилання на поле «Період»має бути відносною, а у всіх інших полях координати потрібно привести до абсолютного вигляду. Після цього клацаємо по кнопці "OK".

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

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

Тепер ми маємо розрахувати загальний щомісячний платіж. Для цього обчислення не слід вдаватися до якогось оператора, тому що можна скористатися простою арифметичною формулою. Складаємо вміст осередків першого місяця стовпців «Виплата за тілом кредиту»і «Виплата за відсотками». Для цього встановлюємо знак «=» в перший порожній осередок стовпця "Загальна щомісячна виплата". Потім клацаємо по двох вищевказаних елементів, встановивши між ними знак «+» . Тиснемо на клавішу Enter.

Далі з допомогою маркера заповнення, як і попередніх випадках, заповнюємо колонку даними. Як бачимо, протягом усієї дії договору сума загального щомісячного платежу, що включає платіж по тілу позики та оплату відсотків, складе 23536,74 рубля. Власне цей показник ми вже розраховували раніше за допомогою ПЛТ. Але в даному випадку це представлено наочно, саме як сума оплати по тілу позики і відсотках.

Тепер потрібно додати дані в стовпець, де щомісячно відображатиметься залишок суми за кредитом, який ще потрібно заплатити. У першому осередку стовпця «Залишок до виплати»розрахунок буде найпростішим. Нам потрібно відібрати від початкової величини позики, яка вказана в таблиці з первинними даними, платіж по тілу кредиту за перший місяць у розрахунковій таблиці. Але з огляду на той факт, що одне з чисел у нас уже йде зі знаком «-» , їх слід не відібрати, а скласти. Робимо це і тиснемо на кнопку Enter.

А ось обчислення залишку до виплати після другого та наступних місяців буде дещо складнішим. Для цього нам потрібно відібрати від тіла кредиту на початок кредитування загальну суму платежів за тілом позики за попередній період. Встановлюємо знак «=» у другому осередку стовпця «Залишок до виплати». Далі вказуємо посилання на комірку, де міститься початкова сума кредиту. Робимо її абсолютною, виділивши та натиснувши на клавішу F4. Потім ставимо знак «+» тому що друге значення у нас і так буде негативним. Після цього клацаємо по кнопці "Вставити функцію".

Запускається Майстер функцій, у якому потрібно переміститися в категорію "Математичні". Там виділяємо напис «СУМ»і тиснемо на кнопку "OK".

Запускається вікно аргументів функції СУМ. Зазначений оператор служить для того, щоб підсумовувати дані в осередках, що нам потрібно виконати в стовпці «Виплата за тілом кредиту». Він має наступний синтаксис:

СУМ(число1; число2; ...)

Як аргументи виступають посилання на комірки, в яких містяться числа. Ми встановлюємо курсор у полі «Число1». Потім затискаємо ліву кнопку миші і виділяємо на аркуші перші два осередки стовпця «Виплата за тілом кредиту». У полі, як бачимо, з'явилося посилання на діапазон. Вона складається з двох частин, розділених двокрапкою: посилання на першу комірку діапазону та на останню. Для того, щоб у майбутньому мати можливість скопіювати зазначену формулу за допомогою маркера заповнення, робимо першу частину посилання на абсолютний діапазон. Виділяємо її та тиснемо на функціональну клавішу F4. Другу частину посилання так і залишаємо відносною. Тепер при використанні маркера заповнення перший осередок діапазону буде закріплено, а останній буде розтягуватися в міру просування вниз. Це нам і потрібне для виконання поставлених цілей. Далі тиснемо на кнопку "OK".

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

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

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

Як бачимо, за допомогою програми Excel у домашніх умовах можна без проблем розрахувати загальний щомісячний кредитний платіж за ануїтетною схемою, використовуючи для цього оператор ПЛТ. Крім того, за допомогою функцій ОСПЛТі ПРПЛТможна зробити розрахунок величини платежів за тілом кредиту та за відсотками за зазначений період. Застосовуючи весь цей багаж функцій разом існує можливість створити потужний кредитний калькулятор, який можна буде використовувати не один раз для обчислення ануїтетного платежу.