Функция ПЛТ в Excel

Выполнение расчетов с помощью финансовых функций

В группу данных операторов входит более 50 формул. Мы отдельно остановимся на десяти самых востребованных из них. Но прежде давайте рассмотрим, как открыть перечень финансового инструментария для перехода к выполнению решения конкретной задачи.

Переход к данному набору инструментов легче всего совершить через Мастер функций.

1. Выделяем ячейку, куда будут выводиться результаты расчета, и кликаем по кнопке «Вставить функцию», находящуюся около строки формул.

Запускается Мастер функций. Выполняем клик по полю «Категории».

Открывается список доступных групп операторов. Выбираем из него наименование «Финансовые».

В Мастер функций также можно перейти через вкладку «Формулы». Сделав переход в неё, нужно нажать на кнопку на ленте «Вставить функцию», размещенную в блоке инструментов «Библиотека функций». Сразу вслед за этим запустится Мастер функций.

Имеется в наличии также способ перехода к нужному финансовому оператору без запуска начального окна Мастера. Для этих целей в той же вкладке «Формулы» в группе настроек «Библиотека функций» на ленте кликаем по кнопке «Финансовые».

Досрочное погашение с уменьшением срока или выплаты

Если потребуется уменьшить срок кредитования, то придется производить дополнительные вычисления с помощью оператора ЕСЛИ. Так можно будет контролировать нулевой баланс, который не должен быть достигнут раньше окончания сроков выплаты.

Чтобы снизить выплаты, нужно пересчитывать взнос за каждый предыдущий месяц.

Пример расчета суммы переплаты по кредиту в excel

В этой задаче надо подсчитать сумму, которую переплатит человек, взявший кредит 50000 рублей по процентной ставке 27% на 5 лет. Всего в год заемщик производит 12 выплат. Решение:

1. Составить исходную таблицу данных.

2. Из общей суммы выплат отнять первоначальный размер суммы по формуле «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Ее надо вставить в строку формул сверху главного меню программы.
3. В итоге в последней строке созданной таблички появится сумма переплат. Заемщик переплатит 41606 рублей сверху.

Расчет платежей в excel по дифференцированной схеме погашения

Дифференцированный способ оплаты предполагает, что:

• сумма основного долга распределена по периодам выплат равными долями;
• проценты по кредиту начисляются на остаток.

Формула расчета дифференцированного платежа:

ДП = ОСЗ / (ПП ОСЗ * ПС)

• ДП – ежемесячный платеж по кредиту;
• ОСЗ – остаток займа;
• ПП – число оставшихся до конца срока погашения периодов;
• ПС – процентная ставка за месяц (годовую ставку делим на 12).

Составим график погашения предыдущего кредита по дифференцированной схеме.

Входные данные те же:

Составим график погашения займа:

Остаток задолженности по кредиту: в первый месяц равняется всей сумме: =$B$2. Во второй и последующие – рассчитывается по формуле: =ЕСЛИ(D10>$B$4;0;E9-G9). Где D10 – номер текущего периода, В4 – срок кредита; Е9 – остаток по кредиту в предыдущем периоде; G9 – сумма основного долга в предыдущем периоде.

Выплата процентов: остаток по кредиту в текущем периоде умножить на месячную процентную ставку, которая разделена на 12 месяцев: =E9*($B$3/12).

Выплата основного долга: сумму всего кредита разделить на срок: =ЕСЛИ(D9 Итоговый платеж: сумма «процентов» и «основного долга» в текущем периоде: =F8 G8.

Внесем формулы в соответствующие столбцы. Скопируем их на всю таблицу.

Сравним переплату при аннуитетной и дифференцированной схеме погашения кредита:

Красная цифра – аннуитет (брали 100 000 руб.), черная – дифференцированный способ.

Финансовые функции

• НАКОПДОХОД (ACCRINT) – Определяет накопленный доход по ценным бумагам с периодической выплатой процентов.
• НАКОПДОХОДПОГАШ (ACCRINTM) – Находит накопленный доход по ценным бумагам, процент по которым выплачивается в срок вступления в силу.
• АМОРУМ (AMORDEGRC) – Вычисляет величину амортизации для каждого периода, используя коэффициент амортизации (французская система бухучета).
• АМОРУВ (AMORLINC) – Вычисляет величину амортизации для каждого отчетного периода (французская система бухучета).
• ДНЕЙКУПОНДО (COUPDAYBS) – Определяет количество дней между началом периода купона и датой соглашения.
• ДНЕЙКУПОН (COUPDAYS) – Определяет число дней в периоде купона, который содержит дату соглашения.
• ДНЕЙКУПОНПОСЛЕ (COUPDAYSNC) – Находит число дней от даты соглашения до срока следующего купона.
• ДАТАКУПОНПОСЛЕ (COUPNCD) – Находит следующую дату купона после даты соглашения.
• ЧИСЛКУПОН (COUPNUM) – Определяет количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу.
• ДАТАКУПОНДО (COUPPCD) – Выдает предыдущую дату купона перед датой соглашения.
• ОБЩПЛАТ (CUMIPMT) – Вычисляет общую выплату, произведенную между двумя периодическими выплатами.
• ОБЩДОХОД (CUMPRINC) – Вычисляет общую выплату по займу между двумя периодами.
• ФУО (DB) – Вычисляет амортизацию имущества на заданный период, используя метод постоянного учета амортизации.
• ДДОБ (DDB) – Вычисляет величину амортизации имущества для указанного периода при использовании метода двукратного учета амортизации или иного явно указанного метода.
• СКИДКА (DISC) – Вычисляет норму скидки для ценных бумаг.
• РУБЛЬ.ДЕС (DOLLARDE) – Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.
• РУБЛЬ.ДРОБЬ (DOLLARFR) – Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.
• ДЛИТ (DURATION) – Находит ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам.
• ЭФФЕКТ (EFFECT) – Вычисляет действующие ежегодные процентные ставки.
• БС (FV) – Вычисляет будущее значение вклада.
• БЗРАСПИС (FVSCHEDULE) – Вычисляет будущее значение начального вклада при изменяющихся сложных процентных ставках.
• ИНОРМА (INTRATE) – Определяет ставку доходности полностью обеспеченной ценной бумаги.
• ПРПЛТ (IMPT) – Вычисляет величину выплаты прибыли на вложения за данный период.
• ВСД (IRR) – Вычисляет внутреннюю ставку доходности (отдачи) для серии потоков денежных средств.
• ПРОЦПЛАТ (ISPMT) – Вычисляет выплаты за указанный период инвестиции.
• МДЛИТ (MDURATION) – Определяет модифицированную длительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.
• МВСД (MIRR) – Определяет внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разную ставку.
• НОМИНАЛ (NOMINAL) – Определяет номинальную годовую процентную ставку.
• КПЕР (NPER) – Определяет общее количество периодов выплаты для данной ссуды.
• ЧПС (NPV) – Вычисляет чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.
• ЦЕНАПЕРВНЕРЕГ (ODDPRICE) – Находит цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.
• ДОХОДПЕРВНЕРЕГ (ODDFYIELD) – Находит доход по ценным бумагам с нерегулярным первым периодом.
• ЦЕНАПОСЛНЕРЕГ (ODDLPRICE) – Определяет цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.
• ДОХОДПЕРВНЕРЕГ (ODDFYIELD) – Находит доход по ценным бумагам с нерегулярным первым периодом.
• ЦЕНАПОСЛНЕРЕГ (ODDLPRICE) – Определяет цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.
• ДОХОДПОСЛНЕРЕГ (ODDFYIELD) – Определяет доход по ценным бумагам с нерегулярным последним периодом.
• ПЛТ (PMT) – Вычисляет величину выплаты по ссуде за один период.
• ОСПЛТ (PPMT) – Вычисляет величину выплат на основной капитал для вклада в заданный период.
• ЦЕНА (PRICE) – Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, по которым производится периодическая выплата процентов.
• ЦЕНАСКИДКА (PRICEDISC) – Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, на которые сделана скидка.
• ЦЕНАПОГАШ (PRICEMAT) – Вычисляет цену за 100 рублей нарицательной стоимости ценных бумаг, по которым выплачивается прибыль в момент вступления в силу.
• ПС (PV) – Вычисляет приведенную (к настоящему моменту) стоимость инвестиции.
• СТАВКА (RATE) – Вычисляет процентную ставку по аннуитету за один период.
• ПОЛУЧЕНО (RECEIVED) – Вычисляет сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.
• АПЛ (SLN) – Вычисляет величину непосредственной амортизации имущества за один период.
• АСЧ (SYD) – Возвращает величину амортизации актива за данный период, рассчитанную методом суммы годовых чисел.
• РАВНОКЧЕК (TBILLEQ) – Вычисляет эквивалентный облигации доход по казначейскому чеку.
• ЦЕНАКЧЕК (TBILLPRICE) – Вычисляет цену за 100 рублей нарицательной стоимости для казначейского чека.
• ДОХОДКЧЕК (TBILLYIELD) – Вычисляет доход по казначейскому чеку.
• ПУО (VDB) – Вычисляет величину амортизации имущества для явно указанного или соответствующего периода при использовании метода разового учета амортизации.
• ЧИСТВНДОХ (XIRR) – Вычисляет внутреннюю ставку доходности запланированных непериодических денежных потоков.
• ЧИСТНЗ (XNPV) – Вычисляет чистую текущую стоимость инвестиции, вычисляемую на основе ряда поступлений наличных, которые не обязательно являются периодическими.
• ДОХОД (YIELD) – Вычисляет доход от ценных бумаг, по которым производятся периодические выплаты процентов.
• ДОХОДСКИДКА (YIELDDISC) – Вычисляет годовой доход по ценным бумагам, на которые сделана скидка. Пример – казначейские чеки.
• ДОХОДПОГАШ (YIELDMAT) – Вычисляет годовой доход от ценных бумаг, процент по которым выплачивается в срок погашения.

Финансовые функции (справка)

Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2022 означает, что данная функция доступна в выпуске Excel 2022 и всех последующих версиях.

Возвращает накопленный процент по ценным бумагам с периодической выплатой процентов.

Возвращает накопленный процент по ценным бумагам, процент по которым выплачивается в срок погашения.

Возвращает величину амортизации для каждого учетного периода, используя коэффициент амортизации.

Возвращает величину амортизации для каждого учетного периода.

Возвращает количество дней от начала действия купона до даты соглашения.

Возвращает количество дней в периоде купона, который содержит дату расчета.

Возвращает количество дней от даты расчета до срока следующего купона.

Возвращает порядковый номер даты следующего купона после даты соглашения.

Возвращает количество купонов между датой соглашения и сроком вступления в силу.

Возвращает порядковый номер даты предыдущего купона до даты соглашения.

Возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат.

Возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами.

Возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка.

Возвращает величину амортизации актива за данный период, используя метод двойного уменьшения остатка или иной явно указанный метод.

Возвращает ставку дисконтирования для ценных бумаг.

Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.

Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.

Возвращает продолжительность Маколея для ценных бумаг, по которым выплачивается периодический процент.

Возвращает фактическую (эффективную) годовую процентную ставку.

Возвращает будущую стоимость инвестиции.

Возвращает будущее значение первоначальной основной суммы после применения ряда (плана) ставок сложных процентов.

Возвращает процентную ставку для полностью инвестированных ценных бумаг.

Возвращает проценты по вкладу за данный период.

Возвращает внутреннюю ставку доходности для ряда потоков денежных средств.

Вычисляет выплаты за указанный период инвестиции.

Возвращает модифицированную продолжительность Маколея для ценных бумаг с предполагаемой номинальной стоимостью 100 рублей.

Возвращает внутреннюю ставку доходности, при которой положительные и отрицательные денежные потоки имеют разные значения ставки.

Возвращает номинальную годовую процентную ставку.

Возвращает общее количество периодов выплаты для инвестиции.

Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) первым периодом купона.

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) первым периодом купона.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг с нерегулярным (коротким или длинным) последним периодом купона.

Возвращает доход по ценным бумагам с нерегулярным (коротким или длинным) последним периодом купона.

ПДЛИТ

Возвращает количество периодов, необходимых инвестиции для достижения заданного значения.

Возвращает регулярный платеж годичной ренты.

Возвращает платеж с основного вложенного капитала за данный период.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым выплачивается периодический процент.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, на которые сделана скидка.

Возвращает цену за 100 рублей номинальной стоимости ценных бумаг, по которым процент выплачивается в срок погашения.

Возвращает приведенную (к текущему моменту) стоимость инвестиции.

Возвращает процентную ставку по аннуитету за один период.

Возвращает сумму, полученную к сроку погашения полностью инвестированных ценных бумаг.

ЭКВ.СТАВКА

Возвращает эквивалентную процентную ставку для роста инвестиции.

Возвращает величину амортизации актива за один период, рассчитанную линейным методом.

Возвращает величину амортизации актива за данный период, рассчитанную методом суммы годовых чисел.

Возвращает эквивалентный облигации доход по казначейскому векселю.

Возвращает цену за 100 рублей номинальной стоимости для казначейского векселя.

Возвращает доходность по казначейскому векселю.

Возвращает величину амортизации актива для указанного или частичного периода при использовании метода сокращающегося баланса.

Возвращает внутреннюю ставку доходности для графика денежных потоков, не обязательно носящих периодический характер.

Возвращает чистую приведенную стоимость для денежных потоков, не обязательно носящих периодический характер.

Возвращает доход по ценным бумагам, по которым производятся периодические выплаты процентов.

Возвращает годовой доход по ценным бумагам, на которые сделана скидка (например, по казначейским векселям).

Возвращает годовую доходность ценных бумаг, по которым процент выплачивается в срок погашения.

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.

Формула вычисления оптимального ежемесячного платежа по кредиту в excel

Задача с таким условием: клиент зарегистрировал счет в банке на 200000 рублей с возможностью ежемесячного пополнения. Нужно посчитать количество платежа, который человек должен вносить каждый месяц, чтобы через 4 года на его счету оказалось 2000000 рублей. Ставка составляет 11%. Решение:

1. Составить табличку по исходным данным.

2. В строку ввода Эксель ввести формулу «=ПЛТ(B3/B5;B6*B5;-B2;B4)» и нажать «Enter» с клавиатуры. Буквы будут отличаться в зависимости от ячеек, в которых размещена таблица.
3. Проверить, что сумма взноса автоматически посчиталась в последней строке таблицы.

Обратите внимание! Таким образом, чтобы на счету клиенту через 4 года накопилось 2000000 рублей по ставке 11%, ему нужно каждый месяц вносить по 28188 рублей. Минус в сумме свидетельствует о том, что клиент несет убытки, отдавая деньги в банк.

Функция ставка

Вычисляет процентную ставку по займу или инвестиции, базируясь на величине будущей стоимости. Полное описание функции здесь.

• Кпер — общее число периодов платежей для ежегодного платежа.
• Плт — выплата, производимая в каждый период; это значение не может меняться в течение всего периода выплат. Обычно аргумент «плт» состоит из основного платежа и платежа по процентам, но не включает других налогов и сборов. Если он опущен, аргумент «пс» является обязательным.
• Пс — приведённая (текущая) стоимость, т. е. общая сумма, которая на данный момент равноценна ряду будущих платежей.
• Бс (необязательный аргумент) — значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если аргумент «бс» опущен, предполагается, что он равен 0 (например, будущая стоимость для займа равна 0).
• Тип (необязательный аргумент) — число 0 (нуль), если платить нужно в конце периода, или 1, если платить нужно в начале периода.
• Прогноз (необязательный аргумент) — предполагаемая величина ставки. Если аргумент «прогноз» опущен, предполагается, что его значение равно 10%. Если функция СТАВКА не сходится, попробуйте изменить значение аргумента «прогноз». Функция СТАВКА обычно сходится, если значение этого аргумента находится между 0 и 1.

Этап 2: детализация платежей

Сумму переплаты можно посчитать помесячно. В итоге человек поймет, сколько денег каждый месяц он будет тратить на кредит. Расчет по детализации выполняется следующим образом:

1. Составить исходную таблицу на 24 месяца.

2. Поставить курсор в первую ячейку таблицы и вставить функцию «ОСПЛТ».

3. Заполнить аргументы функции аналогичным образом.

4. При заполнении поля «Период» нужно сослаться на первый месяц в табличке, указав ячейку 1.

5. Проверить, что первая ячейка в графе «Выплата по телу кредита» заполнилась.
6. Чтобы заполнить все строки первого столбца, необходимо растянуть ячейку до конца таблицы

7. Выбрать функцию «ПРПЛТ» для заполнения второго столбца таблицы.
8. Заполнить все аргументы в открывшемся окошке в соответствии со скриншотом ниже.

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

10. Чтобы посчитать «Остаток к выплате», надо сложить процентную ставку с выплатой по телу кредита и растянуть до конца таблички, чтобы заполнить все месяцы кредитования.

Дополнительная информация! При расчете остатка на формулу надо навешивать знаки долларов, чтобы она не съехала при растягивании.