Зміст
![Аннуитетный платеж по кредиту в Microsoft Excel](https://cdn.daad.org.ua/images/8058/b7a2dabb27c7c3d8f02509e65fa06ccd.png)
Перш, ніж брати позику, непогано було б розрахувати всі платежі по ньому. Це вбереже позичальника в майбутньому від різних несподіваних неприємностей і розчарувань, коли з'ясується, що переплата занадто велика. Допомогти в даному розрахунку можуть інструменти програми Excel. Давайте з'ясуємо, як розрахувати ануїтетні платежі по кредиту в цій програмі.
Розрахунок оплати
Перш за все, потрібно сказати, що існує два види кредитних платежів:
- Диференційовані;
- Ануїтетні.
При диференційованій схемі клієнт вносить в банк щомісяця рівну частку виплат по тілу кредиту плюс платежі за відсотками. Величина процентних виплат щомісяця зменшується, так як зменшується тіло позики, з якого вони розраховуються. Таким чином і загальний щомісячний платіж теж зменшується.
При ануїтетній схемі використовується дещо інший підхід. Клієнт щомісяця вносить однакову суму загального платежу, який складається з виплат по тілу кредиту і оплати відсотків. Спочатку процентні внески нараховуються на всю суму позики, але в міру того, як тіло зменшується, скорочується і нарахування відсотків. Але загальна сума оплати залишається незмінною за рахунок щомісячного збільшення величини виплат по тілу кредиту. Таким чином, з плином часу питома вага відсотків в загальному щомісячному платежі падає, а питома вага оплати по тілу зростає. При цьому сам загальний щомісячний платіж протягом усього терміну кредитування не змінюється.
Якраз на розрахунку ануїтетного платежу ми і зупинимося. Тим більше, це актуально, так як в даний час більшість банків використовують саме цю схему. Вона зручна і для клієнтів, адже в цьому випадку загальна сума оплати не змінюється, залишаючись фіксованою. Клієнти завжди знають скільки потрібно заплатити.
Етап 1: Розрахунок щомісячного внеску
Для розрахунку щомісячного внеску при використанні ануїтетної схеми в Ексель існує спеціальна функція – ПЛТ . Вона відноситься до категорії фінансових операторів. Формула цієї функції виглядає наступним чином:
=ПЛТ (ставка;кпер;пс;БС;тип)
Як бачимо, зазначена функція має досить великою кількістю аргументів. Правда, останні два з них не є обов'язковими.
Аргумент»Ставка" вказує на процентну ставку за певний період. Якщо, наприклад, використовується річна ставка, але платіж за позикою проводиться щомісяця, то річну ставку потрібно розділити на 12 і отриманий результат використовувати як аргумент. Якщо застосовується щоквартальний вид оплати, то в цьому випадку річну ставку потрібно розділити на 4 і т. д.
«Kper» позначає загальну кількість періодів виплат за кредитом. Тобто, якщо позика береться на один рік з щомісячною оплатою, то число періодів вважається 12 , якщо на два роки, то число періодів – 24 . Якщо кредит береться на два роки з щоквартальною оплатою, то число періодів дорівнює 8 .
«Пс» вказує теперішню вартість на даний момент. Говорячи простими словами, це загальна величина позики на початок кредитування, тобто, та сума, яку ви берете в борг, без урахування відсотків та інших додаткових виплат.
" Бс» &8212; це майбутня вартість. Ця величина, яку буде складати тіло позики на момент завершення кредитного договору. У більшості випадків даний аргумент дорівнює «0» , так як позичальник на кінець терміну кредитування повинен повністю розрахуватися з кредитором. Зазначений аргумент не є обов'язковим. Тому, якщо він опускається, то вважається рівним нулю.
Аргумент " Тип» визначає час розрахунку: в кінці або на початку періоду. У першому випадку він приймає значення «0» , а в другому – «1» . Більшість банківських установ використовують саме варіант з оплатою в кінці періоду. Цей аргумент теж є необов'язковим, і якщо його опустити вважається, що він дорівнює нулю.
Тепер настав час перейти до конкретного прикладу розрахунку щомісячного внеску за допомогою функції ПЛТ. Для розрахунку використовуємо таблицю з вихідними даними, де вказана процентна ставка по кредиту ( 12% ), величина позики ( 500000 рублів ) і термін кредиту ( 24 місяці ). При цьому оплата проводиться щомісяця в кінці кожного періоду.
- Виділяємо елемент на аркуші, в який буде виводитися результат розрахунку, і клацаємо по піктограмі " Вставити функцію» , розміщену біля рядка формул.
- Проводиться запуск віконця майстри функцій . В категорії »фінансові" виділяємо найменування " ПЛТ» і тиснемо на кнопку «OK» .
- Після цього відкривається вікно аргументів оператора ПЛТ
.
У полі»Ставка" слід вписати величину відсотків за період. Це можна зробити вручну, просто поставивши відсоток, але у нас він вказаний в окремій комірці на аркуші, тому дамо на неї посилання. Встановлюємо курсор в поле, а потім натискаємо по відповідній комірці. Але, як ми пам'ятаємо, у нас в таблиці задана річна процентна ставка, а період оплати дорівнює місяцю. Тому ділимо річну ставку, а вірніше посилання на клітинку, в якій вона міститься, на число 12 , що відповідає кількості місяців у році. Розподіл виконуємо прямо в поле вікна аргументів.
У полі «Kper» встановлюється термін кредитування. Він у нас дорівнює 24 місяців. Можна занести в поле число 24 вручну, але ми, як і в попередньому випадку, вказуємо посилання на місце розташування даного показника у вихідній таблиці.
У полі «Пс» вказується первісна величина позики. Вона дорівнює 500000 рублів . Як і в попередніх випадках, вказуємо посилання на елемент листа, в якому міститься даний показник.
У полі " Бс» вказується величина позики, після повної його оплати. Як пам'ятаємо, це значення практично завжди дорівнює нулю. Встановлюємо в даному полі число «0» . Хоча цей аргумент можна взагалі опустити.
У полі " Тип» вказуємо на початку або в кінці місяця проводиться оплата. У нас, як і в більшості випадків, вона проводиться в кінці місяця. Тому встановлюємо число «0» . Як і у випадку з попереднім аргументом, в дане поле можна нічого не вводити, тоді програма за замовчуванням буде вважати, що в ньому розташоване значення рівне нулю.
Після того, як всі дані введені, тиснемо на кнопку «OK» .
- Після цього в осередок, яку ми виділили в першому пункті даного керівництва, виводиться результат обчислення. Як бачимо, величина щомісячного загального платежу за позикою становить 23536,74 рубля . Нехай вас не бентежить знак «-» перед цією сумою. Так Ексель вказує на те, що це витрата грошових коштів, тобто, збиток.
- Для того, щоб розрахувати загальну суму оплати за весь термін кредитування з урахуванням погашення тіла позики і щомісячних відсотків, досить перемножити величину щомісячного платежу ( 23536,74 рубля ) на кількість місяців ( 24 місяці ). Як бачимо, загальна сума платежів за весь термін кредитування в нашому випадку склала 564881,67 рубля .
- Тепер можна підрахувати суму переплати по кредиту. Для цього потрібно відняти від загальної величини виплат по кредиту, включаючи відсотки і тіло позики, початкову суму, взяту в борг. Але ми пам'ятаємо, що перше з цих значень вже зі знаком «-» . Тому в конкретно нашому випадку виходить, що їх потрібно скласти. Як бачимо, загальна сума переплати по кредиту за весь термін склала 64881,67 рубля .
![Переход в Мастер функций в Microsoft Excel](https://cdn.daad.org.ua/images/8058/7f8b15ab935dd698b79259943389c8ce.png)
![Переход в окно аргументов функции ПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/f38e452426f9856ddebd2322ddce4ffd.png)
![Окно аргументов функции ПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/bdbacce57a9a9364f67d0b4a53ea5c2a.png)
![Результат расчета ежемесячного платежа в Microsoft Excel](https://cdn.daad.org.ua/images/8058/62f295717177dfd9f24adaf07883f4c7.png)
![Общая величина выплат в Microsoft Excel](https://cdn.daad.org.ua/images/8058/9e3641c4ec15621c250b0a5e6fe6cab4.png)
![Сумма переплаты по кредиту в Microsoft Excel](https://cdn.daad.org.ua/images/8058/affb95bd3e91e3b4c21659a22bc4f2d1.png)
Урок: Майстер функцій в Ексель
Етап 2: деталізація платежів
А тепер за допомогою інших операторів Ексель зробимо помісячну деталізацію виплат, щоб бачити, скільки в конкретному місяці ми платимо по тілу позики, а скільки становить величина відсотків. Для цих цілей креслимо в Ексель таблицю, яку будемо заповнювати даними. Рядки цієї таблиці будуть відповідати відповідному періоду, тобто, місяцю. Враховуючи, що період кредитування у нас становить 24 місяця, то і кількість рядків теж буде відповідним. У стовпцях вказана виплата тіла позики, виплата відсотків, загальний щомісячний платіж, який є сумою попередніх двох колонок, а також сума, що залишилася до виплати.
![Таблица выплат в Microsoft Excel](https://cdn.daad.org.ua/images/8058/b0e26ed921dbe0e4a00a81e13d1ff994.png)
- Для визначення величини оплати по тілу позики використовуємо функцію ОСПЛТ , яка якраз призначена для цих цілей. Встановлюємо курсор в осередок, яка знаходиться в рядку «1» і в стовпці " виплата по тілу кредиту» . Тиснемо на кнопку «Вставити функцію» .
- Переходимо в Майстер функцій . В категорії «фінансові» відзначаємо найменування «ОСПЛТ» і тиснемо кнопку «OK» .
- Запускається вікно аргументів оператора ОСПЛТ. Він має наступний синтаксис:
=ОСПЛТ(Ставка; період; Кпер; Пс; Бс)
Як бачимо, аргументи даної функції майже повністю збігаються з аргументами оператора ПЛТ , лише замість необов'язкового аргументу »Тип" додано обов'язковий аргумент »період" . Він вказує на номер періоду виплати, а в нашому конкретному випадку на номер місяця.
Заповнюємо вже знайомі нам поля вікна аргументів функції ОСПЛТ тими самими даними, що були використані для функції ПЛТ . Тільки з огляду на той факт, що в майбутньому буде застосовуватися копіювання формули за допомогою маркера заповнення, потрібно зробити всі посилання в полях абсолютними, щоб вони не змінювалися. Для цього потрібно поставити знак долара перед кожним значенням координат по вертикалі і горизонталі. Але легше це зробити, просто виділивши координати і натиснувши на функціональну клавішу F4 . Знак долара буде розставлений в потрібних місцях автоматично. Також не забуваємо, що річну ставку потрібно розділити на 12 .
- Але у нас залишається ще один новий аргумент, якого не було у функції ПЛТ
. Цей аргумент
»період"
. У відповідне поле встановлюємо посилання на першу клітинку стовпця
»період"
. Даний елемент листа містить в собі число
«1»
, яке позначає номер першого місяця кредитування. Але на відміну від попередніх полів, у вказаному полі ми залишаємо посилання відносним, а не робимо з нього абсолютним.
Після того, як всі дані, про які ми говорили вище, введені, тиснемо на кнопку «OK» .
- Після цього в комірці, яку ми раніше виділили, відобразиться величина виплати по тілу позики за перший місяць. Вона складе 18536,74 рубля .
- Потім, як уже говорилося вище, нам слід скопіювати дану формулу на інші осередки стовпця за допомогою маркера заповнення. Для цього встановлюємо курсор в нижній правий кут осередку, в якій міститься формула. Курсор перетворюється при цьому в хрестик, який називається маркером заповнення. Затискаємо ліву кнопку миші і тягнемо його вниз до кінця таблиці.
- Врешті-решт усі клітинки стовпця заповнені. Тепер ми маємо графік виплати тіла позики помісячно. Як і говорилося вже вище, величина оплати по даній статті з кожним новим періодом збільшується.
- Тепер нам потрібно зробити місячний розрахунок оплати за відсотками. Для цих цілей будемо використовувати оператор ПРПЛТ . Виділяємо першу порожню клітинку в стовпці " виплата відсотків» . Тиснемо на кнопку «Вставити функцію» .
- У вікні, що запустилося майстри функцій в категорії »фінансові" виробляємо виділення найменування ПРПЛТ . Виконуємо клацання по кнопці «OK» .
- Відбувається запуск вікна аргументів функції ПРПЛТ
. Її синтаксис виглядає наступним чином:
=ПРПЛТ(Ставка; період; Кпер; Пс; Бс)
Як бачимо, аргументи даної функції абсолютно ідентичні аналогічним елементам оператора ОСПЛТ . Тому просто заносимо у вікно ті ж дані, які ми вводили в попередньому вікні аргументів. Не забуваємо при цьому, що посилання в поле »період" повинна бути відносною, а у всіх інших полях координати потрібно привести до абсолютного виду. Після цього клацаємо по кнопці «OK» .
- Потім результат розрахунку суми оплати за відсотками за кредит за перший місяць виводиться у відповідну клітинку.
- Застосувавши маркер заповнення, виробляємо копіювання формули в інші елементи стовпця, таким способом отримавши помісячний графік оплат за відсотками за позику. Як бачимо, як і було сказано раніше, з місяця в місяць величина даного виду платежу зменшується.
- Тепер нам належить розрахувати загальний щомісячний платіж. Для цього обчислення не слід вдаватися до будь-якого оператора, так як можна скористатися простою арифметичною формулою. Складаємо вміст осередків першого місяця стовпців " виплата по тілу кредиту» і " виплата за відсотками» . Для цього встановлюємо знак «=» до першої порожньої комірки стовпця "Загальна щомісячна виплата" . Потім натискаємо по двом вищевказаним елементам, встановивши між ними знак «+» . Тиснемо на клавішу Enter .
- Далі за допомогою маркера заповнення, як і в попередніх випадках, заповнюємо колонку даними. Як бачимо, протягом усієї дії договору сума загального щомісячного платежу, що включає платіж по тілу позики і оплату відсотків, складе 23536,74 рубля . Власне цей показник ми вже розраховували раніше за допомогою ПЛТ . Але в даному випадку це представлено більш наочно, саме як сума оплати по тілу позики і відсоткам.
- Тепер потрібно додати дані в стовпець, де буде щомісяця відображатися залишок суми по кредиту, який ще потрібно заплатити. У першій комірці стовпця " залишок до виплати» розрахунок буде найпростіший. Нам потрібно відняти від початкової величини позики, яка вказана в таблиці з первинними даними, платіж по тілу кредиту за перший місяць в розрахунковій таблиці. Але, враховуючи той факт, що одне з чисел у нас вже йде зі знаком «-» , то їх слід не відняти, а скласти. Робимо це і тиснемо на кнопку Enter .
- А ось обчислення залишку до виплати після другого і наступних місяців буде трохи складніше. Для цього нам потрібно відняти від тіла кредиту на початок кредитування загальну суму платежів по тілу позики за попередній період. Встановлюємо знак «=» у другій комірці стовпця " залишок до виплати» . Далі вказуємо посилання на клітинку, в якій міститься первісна сума кредиту. Робимо її абсолютної, виділивши і натиснувши на клавішу F4 . Потім ставимо знак «+» , так як друге значення у нас і так буде негативним. Після цього натискаємо на кнопку " Вставити функцію» .
- Запускається Майстер функцій , в якому потрібно переміститися в категорію »Математичні" . Там виділяємо напис «сума» і тиснемо на кнопку «OK» .
- Запускається вікно аргументів функції Сум
. Зазначений оператор служить для того, щоб підсумувати дані в осередках, що нам і потрібно виконати в стовпці
" виплата по тілу кредиту»
. Він має наступний синтаксис:
=sum(число1; число2;...)
В якості аргументів виступають посилання на осередки, в яких містяться числа. Ми встановлюємо курсор у поле " Число1» . Потім затискаємо ліву кнопку миші і виділяємо на аркуші перші два осередки стовпця " виплата по тілу кредиту» . В поле, як бачимо, відобразилася посилання на діапазон. Вона складається з двох частин, розділених двокрапкою: посилання на першу клітинку діапазону і на останню. Для того, щоб в майбутньому мати можливість скопіювати зазначену формулу за допомогою маркера заповнення, робимо першу частину посилання на діапазон абсолютної. Виділяємо її і тиснемо на функціональну клавішу F4 . Другу частину посилання так і залишаємо відносної. Тепер при використанні маркера заповнення перша комірка діапазону буде закріплена, а остання буде розтягуватися в міру просування вниз. Це нам і потрібно для виконання поставлених цілей. Далі тиснемо на кнопку «OK» .
- Отже, результат залишку кредитної заборгованості після другого місяця виводиться в клітинку. Тепер, починаючи з даної комірки, виробляємо копіювання формули в порожні елементи стовпця за допомогою маркера заповнення.
- Помісячний розрахунок залишків до оплати по кредиту зроблений за весь кредитний період. Як і належить, на кінець терміну ця сума дорівнює нулю.
![Вставить функцию в Microsoft Excel](https://cdn.daad.org.ua/images/8058/4c3a6723c746ad77ef861251b933b87e.png)
![Переход в окно аргументов функции ОСПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/a326a284b9e744248cd94c9b055be0f5.png)
![Окно аргументов функции ОСПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/fceac44614180e2e84361ffa472a97db.png)
![Аргумент Период в окне аргументов функции ОСПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/245fd9fb66869376fddee8e2b242fb9a.png)
![Результат вычисления функции ОСПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/5bf4730e46eaeaa97dddf3147f0c0e01.png)
![Маркер заполнения в Microsoft Excel](https://cdn.daad.org.ua/images/8058/5bd07d74c54fa682c5d683613a2ebc01.png)
![Величина оплаты тела кредита помесячно в Microsoft Excel](https://cdn.daad.org.ua/images/8058/06ae555449170491a5bfbc5aa8ab9797.png)
![Переход в Мастер функций в программе Microsoft Excel](https://cdn.daad.org.ua/images/8058/efc24621ff51986689e5cb847a82d43b.png)
![Переход в окно аргументов функции ПРПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/1e393d33753da48593bbd18294db25ab.png)
![Окно аргументов функции ПРПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/210169f459137a8571c0e6fc57c00fcd.png)
![Результат вычисления функции ПРПЛТ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/cdac3e354114f54f21e733ed78f8441f.png)
![График выплат по процентам за кредит в Microsoft Excel](https://cdn.daad.org.ua/images/8058/40c629d5fc8358b025636239be1c0865.png)
![Сумма общего ежемесячного платежа в Microsoft Excel](https://cdn.daad.org.ua/images/8058/8a384fb97057d9e621d2448f511ea15f.png)
![Общая сумма ежемесячного платежа в Microsoft Excel](https://cdn.daad.org.ua/images/8058/74a408e17ee6492139f294b3d4717bd0.png)
![Остаток к выплате после первого месяца кредитования в Microsoft Excel](https://cdn.daad.org.ua/images/8058/281171886e96440b2817aa9861ecc49b.png)
![Вставить функцию в программе Microsoft Excel](https://cdn.daad.org.ua/images/8058/13e3d56b2a8f3d808a81da2931c1fbc5.png)
![Переход в окно аргументов функции СУММ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/f6f8e8b0fa406220fad9105ef127f073.png)
![Окно аргументов функции СУММ в Microsoft Excel](https://cdn.daad.org.ua/images/8058/0401a5d77d3b88514126857f1c8aa063.png)
![Маркер заполнения в программе Microsoft Excel](https://cdn.daad.org.ua/images/8058/9700563098b376fc584c4063ad1c9659.png)
![Расчет остатка к выплате по телу кредита в Microsoft Excel](https://cdn.daad.org.ua/images/8058/4f322622d482284a3c85b52fef26a66e.png)
Таким чином, ми зробили не просто розрахунок оплати по кредиту, а організували своєрідний кредитний калькулятор. Який буде діяти за ануїтетною схемою. Якщо у вихідній таблиці ми, наприклад, поміняємо величину позики і річної процентної ставки, то в підсумковій таблиці відбудеться автоматичний перерахунок даних. Тому її можна використовувати не тільки один раз для конкретного випадку, а застосовувати в різних ситуаціях для розрахунку кредитних варіантів за ануїтетною схемою.
![Исходные данные изменены в программе Microsoft Excel](https://cdn.daad.org.ua/images/8058/728e5c4392f8f07613dbbc815913468e.png)
Урок: фінансові функції в Excel
Як бачимо, за допомогою програми Excel в домашніх умовах можна без проблем розрахувати загальний щомісячний кредитний платіж за ануїтетною схемою, використовуючи для цих цілей оператор ПЛТ . Крім того, за допомогою функцій ОСПЛТ і ПРПЛТ можна зробити розрахунок величини платежів по тілу кредиту і за відсотками за вказаний період. Застосовуючи весь цей багаж функцій разом, існує можливість створити потужний кредитний калькулятор, який можна буде використовувати не один раз для обчислення ануїтетного платежу.