Як розрахувати ануїтет в Excel: PMT, PV, FV, NPER

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

Що таке ануїтет і які задачі вирішує Excel

Ануїтет — це серія однакових платежів через однакові проміжки часу за сталої ставки. Прикладом може бути щомісячний внесок на накопичувальний рахунок або регулярна виплата з накопиченого капіталу.

Excel допомагає відповісти на чотири типові запитання:

• Якою має бути регулярна виплата або внесок?
• Який капітал потрібен сьогодні, щоб отримувати таку виплату?
• Яка сума накопичиться через певний час?
• Скільки періодів знадобиться, щоб досягти мети?

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

Яку функцію обрати: порівняльна таблиця

Спочатку визначте, що саме ви шукаєте. Далі оберіть функцію за таблицею.

Мета	Функція	Що вводимо	Що отримуємо
Знайти регулярну виплату або внесок	PMT	Ставку за період, кількість періодів, поточну суму	Суму одного платежу
Знайти потрібний капітал	PV	Ставку за період, кількість періодів, суму платежу	Поточну вартість потоку платежів
Знайти суму накопичення	FV	Ставку за період, кількість періодів, суму внеску	Майбутню вартість наприкінці строку
Знайти строк	NPER	Ставку за період, суму платежу, поточну суму	Кількість періодів
Знайти ставку	RATE	Кількість періодів, платіж, суму	Ставку за один період

Як правильно ввести ставку й кількість періодів

Найчастіша помилка в таких розрахунках — змішування річних і місячних одиниць. Функції Excel потребують, щоб ставка й кількість періодів були в одній одиниці часу.

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

Приклад запису: за ставки 5% річних і строку 20 років у формулу вводять 5%/12 та 20*12, тобто 240 періодів.

Зверніть увагу: залежно від регіональних налаштувань Excel роздільником аргументів може бути кома або крапка з комою.

Функція PMT: розрахунок регулярної виплати

Синтаксис: PMT(rate; nper; pv; [fv]; [type]). Функція припускає сталу ставку й сталу суму платежу.

• rate — ставка за один період;
• nper — загальна кількість періодів;
• pv — поточна сума;
• fv — бажана сума наприкінці строку (необов’язково);
• type — момент виплати (необов’язково).

Приклад із зовнішнього посібника (Spreadsheet Planet): капітал 100 000, ставка 5% річних, строк 20 років, виплата наприкінці місяця. Результат становить близько 659,96 на місяць. Це ілюстрація для цих вхідних умов, а не реальна пропозиція.

Запис формули: =PMT(5%/12; 20*12; -100000). Мінус перед сумою капіталу означає, що ви вносите ці гроші, тому виплата виходить додатною.

Перевірка результату: помножте виплату на кількість періодів. Сума 659,96 × 240 перевищує початковий капітал, і ця різниця пояснюється нарахованими відсотками.

Зверніть увагу: розрахунок PMT може не враховувати податки, резерви та комісії.

Функція PV: який капітал потрібен для бажаної виплати

PV розв’язує зворотну задачу: за відомою виплатою, ставкою та строком знаходить поточну вартість потоку платежів. Тобто показує, який капітал потрібен «на старті».

Умовний приклад: виплата 659,96 на місяць, ставка 5% річних, 20 років, виплата наприкінці місяця. Введіть дані в комірки:

• B1 — річна ставка: 5%;
• B2 — кількість років: 20;
• B3 — місячна виплата: −659,96.

Формула: =PV(B1/12; B2*12; B3). Очікуваний результат становить близько 100 000. Він збігається з вихідним прикладом із попереднього розділу, що підтверджує правильність розрахунку.

Функція FV: скільки накопичиться за регулярних внесків

FV обчислює майбутню вартість регулярних внесків за сталої ставки. Синтаксис: FV(rate; nper; pmt; [pv]; [type]).

Умовний приклад: щомісячний внесок 200, ставка 5% річних, 10 років, внески наприкінці місяця. Формула: =FV(5%/12; 10*12; -200). Результат становить близько 31 056. Це розрахунок за заданих умов, а не гарантований дохід.

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

Функція NPER: скільки періодів знадобиться

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

Умовний приклад: капітал 100 000, виплата 659,96, ставка 5%/12. Формула: =NPER(5%/12; 659,96; -100000). Результат становить близько 240 періодів, тобто 20 років. Це зворотна перевірка прикладу з розділу про PMT.

Результат виходить у періодах. Для місячних платежів поділіть його на 12, щоб отримати роки.

Функція RATE: яку ставку закладено в умови

RATE допомагає знайти ставку, коли відомі кількість періодів, сума платежу та загальна сума. Це зручно, наприклад, якщо потрібно з’ясувати, яка ставка прихована в запропонованих умовах.

Результат RATE є ставкою за один період. Якщо періоди місячні, то це місячна ставка. Номінальну річну ставку, яку отримують множенням місячної на 12, не варто плутати з ефективною річною. Наприклад, місячна ставка 5%/12 за місячного нарахування дає ефективну річну ставку близько 5,12% (власний розрахунок). Тому, порівнюючи пропозиції, дивіться, про яку саме ставку йдеться.

Параметр type: виплата на початку чи наприкінці періоду

Параметр type визначає момент платежу:

• 0 або пропуск — виплата наприкінці кожного періоду;
• 1 — виплата на початку кожного періоду.

Результат залежить від цього вибору. Для умов прикладу з PMT виплата на початку місяця (type = 1) дає близько 657,22 замість 659,96. Така різниця виникає, бо кожна виплата відбувається на один період раніше. Це умовна ілюстрація за нашими вхідними даними.

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

Чому результат від’ємний і як це виправити

Excel застосовує правило знаків: гроші, які ви віддаєте, — від’ємні, гроші, які ви отримуєте, — додатні. Тому результат функції може виходити зі знаком «мінус».

• Якщо ви вносите капітал, вводьте його з мінусом, і виплата буде додатною.
• Якщо результат від’ємний, а потрібне додатне число, поставте мінус перед функцією, наприклад =-PMT(...).
• Для зручності підпишіть комірки: «внесок», «виплата», «капітал» — і дотримуйтеся єдиного правила знаків у всій таблиці.

Обмеження розрахунків і типові помилки

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

Також пам’ятайте про такі обмеження:

• розрахунок може не враховувати податки, комісії та резерви, тому реальна сума може відрізнятися;
• результат не є прогнозом державної пенсії чи гарантією страховика;
• типові помилки — змішування річної та місячної ставки, неправильний знак і неврахований момент виплати.

Поширені запитання

Яку функцію використати для щомісячної виплати?

Функцію PMT: вона розраховує регулярну виплату за ставкою, кількістю періодів і сумою. Для місячних виплат введіть ставку за місяць і кількість місяців.

Чи можна рахувати зі змінною ставкою?

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

Чому результат негативний?

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

Чим відрізняється виплата на початку й наприкінці місяця?

Параметр type, що дорівнює 1, означає виплату на початку періоду, а 0 або пропуск — наприкінці. Від цього залежить підсумкова сума, тому завжди вказуйте type свідомо.

Висновки та перевірочний список

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

• Мета визначена: виплата, капітал, накопичення, строк чи ставка.
• Ставка й кількість періодів подані в одній одиниці часу.
• Знаю, нараховуються відсотки щомісяця чи за іншою схемою.
• Параметр type відповідає умовам: 0 або 1.
• Знаки чисел узгоджені: внески від’ємні, виплати додатні.
• Пам’ятаю, що результат не враховує податки, комісії й не є прогнозом реальної пенсії.

Приєднуйтесь до нашої спільноти