Ануїтет. Розрахунок періодичного платежу у MS EXCEL. Терміновий внесок

Розрахуємо у MS EXCEL суму регулярного платежу у разі накопичення певної суми. Зробимо це з використанням функції ПЛТ() , і безпосередньо за формулою ануїтетів. Також складемо таблицю регулярних платежів із розшифровкою накопиченої суми та нарахованих відсотків.

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

Завдання1

Потрібно накопичити за 5 років суму 1000000 руб. Початкова сума вкладу =0. Визначити величину регулярних поповнень вкладу, якщо відсоткова ставка становить 10% річних, поповнення вкладу проводиться щокварталу, капіталізація відсотків також щокварталу. Див. файл прикладу.

Розрахунок суми регулярного поповнення вкладу, зробимо спочатку за допомогою фінансової функції MS EXCEL ПЛТ().

Ця функція має такий синтаксис: ПЛТ(ставка; кпер; пс; [бс]; [тип]) PMT(rate, nper, pv, [fv], [type]) - англійський варіант.

Примітка. Функція ПЛТ() входить у надбудову "Пакет аналізу". Якщо ця функція недоступна або повертає помилку #ІМ'Я?, увімкніть або встановіть та завантажте цю надбудову (в MS EXCEL 2007/2010 надбудова «Пакет аналізу» включена за замовчуванням).

Примітка. Огляд усіх функцій ануїтету знайдете тут.

Перший аргумент – Ставка. Це відсоткова ставка саме у період, тобто. у разі за квартал, тобто. 10%/4 (в році 4 квартали). Кпер - загальна кількість періодів платежів поануїтету, тобто. 20 (4 кв. На рік * 5 років) Пс - Наведена вартість, тобто. вартість наведена до поточного моменту. У разі, це початкова сума на розрахунковий рахунок, тобто. 0. Бс - Майбутня вартість вкладу в кінці терміну (по закінченні числа періодів Кпер). Бс – необхідне значення залишку коштів після останнього внеску. У разі Бс = 1 000 000. Тип - число 0 чи 1, що означає, коли має здійснюватися нарахування %. 0 – наприкінці періоду, 1 – на початку. Якщо цей параметр опущений, він вважається =0 (наш випадок).

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

Рішення1 Отже, щоквартальний платіж може бути обчислений за формулою =ПЛТ(10%/4; 5*4; 0;1000000; 0), тобто. -39147, 13р. Знак мінус показує, що ми маємо різноспрямовані грошові потоки: накопичуємо гроші (тим самим забираємо їх з нашого бюджету), і отримуємо від банку +1000000, коли забираємо гроші наприкінці терміну. Альтернативна формула для розрахунку платежу: =(Пс*ставка*(1+ ставка)^ Кпер /((1+ ставка)^ Кпер -1)+ ставка /((1+ ставка)^ Кпер -1) * Бс) * ЯКЩО (Тип; 1 / (ставка +1); 1)

Якщо період нарахування відсотків та регулярних внесків не збігається

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

Таблиця поповнення вкладу

Складемо таблицю поповнення вкладу.

ануїтет

Вклад поповнюється з 2 джерел: перший – це регулярні внески, другий – нараховані за період відсотки (на накопичену до цього моменту суму вкладу). Для обчислення процентів, що регулярно нараховуються, використовується функція ПРПЛТ(ставка; період; кпер; пс; [бс]; [тип])

Таким чином, вклад регулярно поповнюється на величину =-ПЛТ(10%/4; 20; 0;1000000; 0) + ПРПЛТ(10%/4; період; 20; 0; 1000000; 0) , де період – це номер періоду , Який потрібно підрахувати величину поповнення. Той самий результат дає формула =-ОСПЛТ(10%/4; період; 20; 0; 1000000; 0)

Співвідношення величини внеску та нарахованих відсотків добре демонструє графік, наведений у прикладі .

періодичного

Примітка. У статті Ануїтет. Розрахунок періодичного платежу у MS EXCEL. Погашення позички (кредиту, позики) показано як розрахувати величину регулярної суми для погашення кредиту або позички у разі застосування ануїтетної схеми.

Завдання2

Потрібно накопичити за 5 років суму 1000000 руб. Визначити величину регулярних поповнень вкладу, якщо відсоткову ставку становить 10% річних, поповнення вкладу проводиться щокварталу, капіталізація відсотків також щоквартально, на рахунку вже міститься 100 000 крб.

Решение2 Накопичити з допомогою внесків нам потрібно всього 900 000руб. (1000000-100000). Щоквартальний платіж може бути обчислений за формулою = ПЛТ (10% / 4; 5 * 4; -100000; 1000000; 0), результат -32732,42р.

періодичного

Усі параметрифункції ПЛТ() вибираються аналогічно до попередньої задачі, крім значення ПС = -100000р., який вимагає пояснення. Згадаємо, що з ануїтету справедливо тотожність: ПС+СУММ(ОСПЛТ())+БС=0, тобто. ПС+(-900000р.) +1000000 = 0. Звідси отримаємо ПС = -100000р.

Примітка. У прикладному файлі також наведено розрахунок графіка приросту вкладу без використання формул ануїтету (див. стовпці K:O).