=ПЛТ(B2/12;B3*12;-B1) возвращает ежемесячный платёж по кредиту B1 под годовую процентную ставку из B2 со сроком погашения B3 лет. Это функция ПЛТ (по-английски PMT). Ставка делится на 12, а годы умножаются на 12, потому что платежи ежемесячные. В таблицах ниже формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой.
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
=ПЛТ(B2/12;B3*12;-B1)Кредит $250,000 под 6,5% на 30 лет стоит $1,580.17 в месяц, а проценты за весь срок составляют $318,861.22, больше самого кредита. Замените B3 на 15: платёж вырастет до $2,177.77, но переплата сократится больше чем вдвое.
Синтаксис ПЛТ
=PMT(rate, nper, pv, [fv], [type])
rate(ставка): процентная ставка за период. Для ежемесячных платежей при годовой ставке используйтеrate/12.nper(кпер): число платежей. 30 лет ежемесячных платежей это30*12, то есть 360.pv(пс): текущая стоимость, сумма кредита.fv(бс, необязательный): сумма, которая остаётся в конце. 0 для полностью погашенного кредита, это значение по умолчанию; цель накоплений для плана сбережений.type(тип, необязательный): 0 (по умолчанию) для платежей в конце каждого периода, как у большинства кредитов; 1 для платежей в начале периода, как у аренды или лизинга.
ПЛТ предполагает фиксированную ставку и равные платежи. Она не учитывает налоги, страховку и комиссии, которые банк добавляет к ипотечному платежу.
Почему ПЛТ возвращает отрицательное число
Финансовые функции Excel (ПЛТ, ПС, БС, ЧПС, ВСД) обозначают знаком числа направление денег. Полученные деньги положительны, а выплаченные отрицательны. Кредит это деньги, которые вы получаете, поэтому =ПЛТ(B2/12;B3*12;B1) возвращает -1580,17: платёж уходит от вас.
Поэтому в формуле на этой странице стоит -B1: сумма кредита входит отрицательной, а платёж выходит положительным. =-ПЛТ(B2/12;B3*12;B1) делает то же самое. Выберите один вариант и используйте его везде, потому что то же правило знаков определяет fv: цель накоплений, которую вы получите, вводится отрицательной будущей стоимостью при нулевой текущей.
Сравнить сроки кредита
Поставьте сроки в столбец, сделайте сумму и ставку абсолютными ссылками и протяните формулу вниз, чтобы сравнить платежи и общую стоимость рядом.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
=ПЛТ($B$2/12;A4*12;-$B$1)Если растянуть кредит с 3 до 6 лет, платёж снизится с $876.13 до $489.56, а проценты вырастут с $3,540.58 до $7,248.66. Подробнее о знаках $ на странице абсолютные ссылки.
Сколько откладывать каждый месяц
С pv, равным 0, и будущей стоимостью ПЛТ отвечает на обратный вопрос: сколько откладывать каждый месяц, чтобы достичь цели. Цель это деньги, которые вы получите, поэтому она вводится отрицательным числом, чтобы взнос был положительным.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
=ПЛТ(B2/12;B3*12;0;-B1)Если вносить $301.66 в месяц в течение 60 месяцев под 4%, накопится 20 000; сами взносы дают около 18 100, а остальное добавляют проценты. Замените B2 на 0%, и взнос станет ровно 20 000, делённым на 60.
Проценты и основной долг: ПРПЛТ и ОСПЛТ
Каждый платёж гасит часть процентов и часть кредита. ПРПЛТ (IPMT) возвращает процентную часть одного платежа, а ОСПЛТ (PPMT) часть основного долга; вместе они равны ПЛТ. Обе принимают номер платежа вторым аргументом, и если протянуть их вниз, получится график погашения.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
=ПРПЛТ(6,5%/12;A2;360;-250000)В первом месяце из платежа $1,580.17 проценты составляют $1,354.17, и только $226.00 уменьшает долг. Часть основного долга понемногу растёт каждый месяц. В настоящей книге поставьте ставку, срок и сумму в ячейки и ссылайтесь на них со знаками $, как в предыдущих таблицах.
Попробуйте: платёж по автокредиту
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
Ваша очередь: В B5 посчитайте ежемесячный платёж по кредиту из B1 под годовую ставку из B2 на число лет из B3. Покажите его положительным числом.
Подсказка: и ставка, и число платежей должны быть месячными.
Частая ошибка: годовая ставка при ежемесячных платежах
Ставка и nper должны относиться к одному периоду. Если оставить ставку годовой, а считать месяцы, это 6,5% каждый месяц, и платёж получится примерно в десять раз больше:
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
=ПЛТ(B2;B3*12;-B1)Неверная формула требует $16,250.00 в месяц, а это просто проценты по ставке 6,5% в месяц. Другой вариант той же ошибки: ввести ставку как 6.5 вместо 6.5% или 0.065 (в русском Excel 6,5, 6,5% и 0,065): это 650% годовых. Если платёж выглядит нелепо, сначала проверьте единицы ставки. Для ежегодных платежей используйте годовую ставку и число лет как есть.
Часто задаваемые вопросы
Как посчитать ежемесячный платёж по кредиту в Excel?
Используйте =ПЛТ(rate/12; years*12; -amount), например =ПЛТ(6,5%/12;30*12;-250000) для кредита 250 000 на 30 лет под 6,5%. Она возвращает около 1580,17 в месяц.
Почему ПЛТ в Excel отрицательная?
Финансовые функции Excel обозначают знаком направление денег: полученные деньги положительны, а уплаченные отрицательны. Кредит приходит к вам, поэтому уходящий платёж отрицателен. Поставьте минус перед суммой кредита (-B1) или перед ПЛТ, чтобы показать его положительным числом.
Как посчитать переплату по кредиту в Excel?
Умножьте платёж на число платежей и вычтите кредит: =ПЛТ(B2/12;B3*12;-B1)*B3*12-B1. Для процентов, уплаченных за первый год, используйте =-ОБЩПЛАТ(B2/12;B3*12;B1;1;12;0).
Чем отличаются ПЛТ, ПРПЛТ и ОСПЛТ?
ПЛТ это весь платёж. ПРПЛТ это процентная часть одного платежа, а ОСПЛТ часть основного долга, и вместе они дают ПЛТ. Первые платежи в основном состоят из процентов.
Как посчитать, сколько откладывать каждый месяц, в Excel?
Передайте ПЛТ будущую стоимость вместо текущей: =ПЛТ(4%/12;5*12;0;-20000) это ежемесячный взнос, который за 5 лет при 4% годовых вырастет до 20 000.
