Використання формул Excel для визначення обсягів платежів та заощаджень

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

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

Розрахунок щомісячних платежів для погашення заборгованості за кредитною карткою

Припустимо, що залишок до оплати становить 5400 доларів США під 17% річних. Поки заборгованість не буде погашена повністю, ви не зможете розраховуватись карткою за покупки.

За допомогою функції ПЛТ (ставка; КПЕР; ПС)

= ПЛТ (17% / 12; 2 * 12; 5400)

отримуємо щомісячний платіж у розмірі 266,99 доларів США, який дозволить сплатити заборгованість за два роки.

• Аргумент "ставка" - це процентна ставка на період погашення кредиту. Наприклад, у цій формулі ставка 17% річних ділиться на 12 - кількість місяців на рік.
• Аргумент КПЕР 2*12 – це загальна кількість періодів виплат за кредитом.
• Аргумент ПС або наведеної вартості складає 5400 доларів США.

Розрахунок щомісячних платежів з іпотеки

Уявіть будинок вартістю 180 тисяч доларів США під 5% річних на 30 років.

За допомогою функції ПЛТ (ставка; КПЕР; ПС)

= ПЛТ (5% / 12; 30 * 12; 180000)

отримано суму щомісячного платежу (без урахування страховки та податків) у розмірі 966,28 доларів США.

• Аргумент "ставка" становить 5%, поділених на 12 місяців на рік.
• Аргумент КПЕР складає 30*12 для іпотечного кредиту терміном на 30 років із 12 щомісячними платежами, що оплачуються протягом року.
• Аргумент ПС становить 180 000 (нинішня величина кредиту).

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

Необхідно зібрати гроші на відпустку вартістю 8500 доларів за три роки. Процентна ставка заощаджень становить 1,5%.

За допомогою функції ПЛТ (ставка; КПЕР; ПС; БС)

отримуємо, щоб зібрати 8500 доларів США за три роки, необхідно відкладати по 230,99 доларів США щомісяця.

• Аргумент "ставка" становить 1,5%, поділених на 12 місяців - кількість місяців на рік.
• Аргумент КПЕР складає 3*12 для дванадцяти щомісячних платежів за три роки.
• Аргумент ПС (наведена вартість) становить 0, оскільки відлік починається з нуля.
• Аргумент БС (майбутня вартість), яку потрібно досягти, становить 8500 доларів США.

Тепер припустимо, що ви хочете зібрати 8500 доларів США на відпустку за три роки, і вам цікаво, яку суму необхідно покласти на рахунок, щоб щомісячний внесок становив 175,00 доларів США. Функція ПС розрахує розмір початкового депозиту, що дозволить зібрати бажану суму.

За допомогою функції ПС(ставка; КПЕР; ПЛТ; БС)

ми дізнаємося, що необхідний початковий депозит у розмірі 1969,62 доларів США, щоб можна було відкладати по 175,00 доларів США на місяць та зібрати 8500 доларів США за три роки.

• Аргумент "Ставка" складає 1,5%/12.
• Аргумент КПЕР складає 3*12 (або дванадцять щомісячних платежів за три роки).
• Аргумент ПЛТ становить -175 (необхідно відкладати по 175 доларів США на місяць).
• Аргумент БС (майбутня вартість) складає 8500.

Розрахунок терміну погашення споживчого кредиту

Уявіть, що ви взяли споживчий кредит на суму 2500 доларів США та погодилися виплачувати по 150 доларів США щомісяця під 3% річних.

За допомогою функції КПЕР(ставка; ПЛТ; ПС)

= КПЕР (3% / 12; -150; 2500)

з'ясовуємо, що з погашення кредиту необхідно 17 місяців кілька днів.

• Аргумент "Ставка" складає 3%/12 щомісячних платежів за рік.
• Аргумент ПЛТ становить -150.
• Аргумент ПС (наведена вартість) складає 2500.

Розрахунок суми першого внеску

Скажіть, що ви хотіли б купити автомобіль за $ 19 000 по 2,9% відсотковій ставці протягом трьох років. Ви хочете зберегти щомісячні платежі на рівні $350 на місяць, тож вам потрібно з'ясувати свій аванс. У цій формулі результатом функції PV є сума кредиту, яка віднімається від покупної ціни, щоб отримати аванс.

За допомогою функції ПС(ставка; КПЕР; ПЛТ)

= 19000-ПС (2,9% / 12; 3 * 12; -350)

з'ясовуємо, що перший внесок має становити 6946,48 доларів.

• Спочатку у формулі вказується ціна покупки у розмірі 19 000 доларів США. Результат функції ПС буде віднімається з ціни покупки.
• Аргумент "Ставка" складає 2,9%, поділених на 12.
• Аргумент КПЕР складає 3*12 (або дванадцять щомісячних платежів за три роки).
• Аргумент ПЛТ становить -350 (необхідно буде виплачувати по 350 доларів США на місяць).

Оцінка динаміки збільшення заощаджень

Починаючи з 500 доларів США на рахунку, скільки можна зібрати за 10 місяців, якщо класти на депозит по 200 доларів США на місяць під 1,5% річних?

За допомогою функції БС (ставка; КПЕР; ПЛТ; ПС)

отримуємо, що за 10 місяців вийде сума 2517,57 доларів США.

• Аргумент "Ставка" складає 1,5%/12.
• Аргумент КПЕР складає 10 (місяць).
• Аргумент ПЛТ складає -200.
• Аргумент ПС (наведена вартість) складає -500.

Функція КПЕР для розрахунку кількості періодів погашення в Excel

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

Приклади, як використовувати функцію КПЕР в Excel

Приклад 1. Вкладник вніс депозит під 16% річних у сумі 120000 рублів з щомісячної капіталізацією вкладу (прості відсотки). Скільки років знадобиться для накопичення 300 000 рублів?

Формула для розрахунку:

• B3/B4 – процентна ставка у період капіталізації;
• 0 - числове значення, що характеризує щомісячний платіж (додаткове поповнення депозитного рахунку не провадиться);
• B2 – початкова інвестиція;
• -B5 - кінцева сума після закінчення договору.

Повернений функцією КПЕР результат поділено на кількість періодів капіталізації в році для розрахунку кількості років, необхідних для накопичення необхідної суми. Результат розрахунків:

Вкладник має залишати гроші на депозитному рахунку протягом майже 6 років.

Розрахунок реальної суми боргу з відсотками та переплатою в Excel

приклад 2.Клієнту банку було видано кредит у сумі 10000 рублів під 23% річних з щомісячною оплатою 700 рублів. Скільки грошей отримає банк після закінчення терміну кредитного договору?

Формула для розрахунку:

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

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

За останній неповний період необхідно повернути таку частину тіла кредиту:

Розрахуємо відсотки, що залишилися до сплати:

Оскільки платіж включає оплату тіла кредиту і відсотків, нарахованих за період, визначимо розмір останнього платежу за формулою:

Загальна сума, яку отримає банк, становитиме 11796 рублів, а розмір останнього платежу – 597 рублів.

Розрахунок термінів погашення кредиту за допомогою функції КПЕР

Приклад 3. Банк видав кредит у сумі 35000 рублів під 27% річних. Обсяг щомісячного платежу становить 1500 рублів. Через скільки місяців клієнт виплатить 50% кредиту?

Вихідна таблиця даних:

На підставі тотожності ануїтетних платежів (сума величини платежу на погашення тіла кредиту за всі періоди, тіла кредиту та майбутньої вартості дорівнює нулю, тобто ЗАГАЛЬДОХІД+ПС+БС=0) використовуємо таку формулу:

Вираз -B2*(1-50%)) характеризує майбутню вартість і отримано з рівняння:

Для виплати 50% кредиту потрібно буде вносити щомісячний платіж протягом приблизно 20 місяців.

Особливості використання функції КПЕР в Excel

Функція КПЕР використовується для вирішення фінансових завдань спільно з функціями ПЛТ, БС, СТАВКА, ПС та має наступний синтаксичний запис:

= КПЕР (ставка; плт; пс; [бс]; [тип])

Опис аргументів (перші три аргументи – обов'язкові для заповнення):

• ставка – числове значення, що характеризує ставку за період виплат (для позичок) чи капіталізації (для депозитних вкладів). Аргумент може бути зазначений у вигляді дробового числа або як значення у процентному форматі (наприклад, 14,5% або 0,145 – еквівалентні варіанти запису). Якщо за умови завдання зазначена річна ставка, необхідно виконати перерахунок за формулою Rп=Rг/12, де Rп – ставка у період, Rg – річна ставка, 12 – число місяців на рік.
• пт – числове значення, що відповідає сумі виплати за період, яка є фіксованою величиною (прості відсотки).
• пс - числове значення, що характеризує поточну вартість інвестиції (наприклад, сума, видана кредитною організацією в борг клієнту, або сума коштів, покладених на депозитний рахунок до банку).
• [БС] – числове значення, що відповідає майбутній вартості інвестиції. Наприклад, цей аргумент може характеризувати суму, яку отримає вкладник після закінчення дії договору депозитного вкладу. Якщо аргумент явно не вказано або набуває значення 0 (нуль), функція КПЕР поверне кількість періодів виплат до повного погашення заборгованості. Аргумент є обов'язковим для заповнення, за замовчуванням приймається значення 0.
• [Тип] - необов'язковий аргумент, що характеризує спосіб виплат (0 - виплата на кінець періоду, 1 - виплата на початок періоду).

1. Функція КПЕР повертає код помилки #ЧИСЛО! У разі, якщо сума платежу за кожний період менша, ніж добуток початкової суми інвестиції та ставки за період, при цьому майбутня вартість інвестиції дорівнює 0 (ситуація при розрахунку кількості періодів для повного повернення заборгованості), а виплата провадиться наприкінці періоду (тобто, аргумент [тип] або явно вказаний як 0 (нуль).
2. Зазначена особливість роботи функції КПЕР випливає з алгоритму, який вона використовує для розрахунку:
3. Усі аргументи функції КПЕР повинні вказуватися як числових значень чи конвертованих у числа текстових термін. Інакше розглянута функція повертатиме код помилки #ЗНАЧ!.

1. Фактично, функція КПЕР дозволяє визначити кількість періодів, після закінчення останнього з яких майбутня вартість інвестиції набуде зазначеного значення.
2. У разі кредиту вважається, що заборгованість погашена повністю, якщо майбутня вартість інвестиції дорівнює 0 (нулю).
3. Також функція КПЕР дозволяє визначити кількість періодів капіталізації депозитного вкладу, необхідних для досягнення необхідної суми накопичень.
4. Для розрахунку кількості періодів виплати заборгованості з нульовою відсотковою ставкою можна використати формулу =A1/A2, де A1 – майбутня вартість, A2 – фіксована сума виплат за період.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади