обчислення ануїтетного платежу в Microsoft Excel

Зміст

• Розрахунок оплати
  • Етап 1: Розрахунок щомісячного внеску
  • Етап 2: деталізація платежів
• Питання та відповіді

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

Розрахунок оплати

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

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

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

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

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

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

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

=ПЛТ (ставка;кпер;пс;БС;тип)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

Урок: Майстер функцій в Ексель

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

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

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

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

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

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

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

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


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

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


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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

    =sum(число1; число2;...)

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


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

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

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

Урок: фінансові функції в Excel

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