Какой функцией excel можно рассчитать величину аннуитетных платежей чпс плт осплт

Обновлено: 07.07.2024

По-настоящему мощным инструментом Excel является благодаря своей уникальной многофункциональности и умению решать задачи людей из разных профессиональных областей. Excel незаменим для менеджеров и экономистов, предпринимателей и финансистов, бухгалтеров и аналитиков, математиков и инженеров. Универсальность ему придают специфические встроенные функции, которые те или иные специалисты используют в своих расчётах.

Одна из самых больших и популярных категорий функций - финансовые. В последней версии Excel есть 55 функций, относящихся к этой группе. Многие из них специфические и узконаправленные, но некоторые могут пригодиться практически каждому. Одна из таких базовых функций - ПЛТ (PMT).

Как гласит официальная справка, функция ПЛТ возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянной процентной ставки. Если Вас смущает специфический термин "аннуитет" - не пугайтесь. Иными словами, с помощью функции ПЛТ можно рассчитать сумму, которую нужно будет выплачивать каждый месяц при условии, что процент по кредиту не изменится и платежи вносятся регулярно равными суммами.

Синтаксис функции

Функция имеет следующий синтаксис:

ПЛТ(ставка; кпер; пс; [бс]; [тип])

Разберем по очереди все аргументы:

Ставка. Обязательный аргумент. Представляет процентную ставку за период. Самое главное здесь - не ошибиться в пересчете размера ставки на нужный период. Если предполагается погашать кредит ежемесячными платежами, а ставка годовая - то ее нужно перевести в месячную, разделив на 12. Если же, например, кредит гасится 1 раз в квартал, то годовую ставку нужно поделить на 4 (и получить таким образом ставку за 1 квартал). Ставку можно указать в процентах или в сотых долях.
Кпер. Обязательный. Этот аргумент представляет собой число расчетных периодов (сколько раз будет вноситься платёж в счёт погашения кредита). Как и ставка, этот аргумент зависит от того, какой расчетный период принят для вычислений. Если кредит получен на 5 лет с платежами 1 раз в месяц, то Кпер = 5*12 = 60 периодов . Если же на 3 года, с платежами 1 раз в квартал - то Кпер = 3*4 = 12 периодов .
Пс . Обязательный. Сумма кредита, то есть объем долга, который нужно будет погасить будущими платежами.
[бс]. Необязательный. Сумма долга, которая должна остаться неоплаченной после истечения всех расчетных периодов. Обычно этот аргумент равен 0 (кредит должен быть погашен полностью). Так как аргумент необязательный, то его можно не указывать (в таком случае он будет принят равным нулю).
[тип]. Необязательный. Обозначает момент произведения выплаты - в начале или в конце периода. Для первого случая нужно указать единицу, а для второго ноль (или вообще пропустить этот аргумент). В большинстве случаев используется второй вариант - выплаты в конце периода, а значит чаще всего этот аргумент можно опустить.

Особенностью синтаксиса функции является указание направления денежного потока. Если денежный поток входящий (например, сумма полученного кредита, указанная в аргументе Пс), то необходимо указывать его как положительное число. Исходящие потоки наоборот, указываются как отрицательные числа (например, после вычисления функция ПЛТ вернет отрицательный результат, так как размер платежа по кредиту - это исходящий денежный поток).

Примеры использования

Задача 1. Расчет суммы выплат по кредиту

Предположим, что в банке получен кредит на сумму 1 000 000 руб. под 17,5% годовых на срок 6 лет. Кредит будет погашаться равными платежами ежемесячно на протяжении всего срока займа. К концу срока будет выплачена вся сумма долга. Первый платеж будет внесен в конце первого периода. Необходимо найти величину ежемесячного платежа.

Итак, нам известна годовая ставка, а кредит будет погашаться ежемесячно. Значит для расчета нам потребуется перевести годовую ставку в месячную, разделив 17,5% на 12 месяцев. В первый аргумент записываем 17,5%/12 .

Кредит получен на 6 лет. Выплачивается ежемесячно. Значит, количество периодов выплат = 6*12. Во второй аргумент записываем 72 .

В третий аргумент пишем сумму кредита. Она равна 1 000 000 руб. (для займополучателя это входящий денежный поток, указываем его как положительное число).

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

Формула примет вид:

Результат вычисления равен -22526,05 руб . Число отрицательное, так как платеж по кредиту для займополучателя является исходящим денежным потоком. Именно такую сумму нужно будет вносить каждый месяц для погашения кредита, описанного в условии.

В статье рассмотрены финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , ПС() , БС() , а также ОБЩДОХОД() и ОБЩПЛАТ() , которые используются для расчетов параметров аннуитетной схемы.

Данная статья входит в цикл статей о расчете параметров аннуитета. Перечень всех статей на нашем сайте об аннуитете размещен здесь .

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

Немного теории

Аннуитет (иногда используются термины «рента», «финансовая рента») представляет собой однонаправленный денежный поток, элементы которого одинаковы по величине и производятся через равные периоды времени (например, когда платежи производятся ежегодно равными суммами).

Каждый элемент такого денежного потока называется членом аннуитета , а величина постоянного временного интервала между двумя его последовательными элементами называется периодом аннуитета . В широком смысле, аннуитетом может называться как сам финансовый инструмент, так и сумма периодического платежа. Исторически вначале рассматривались равные ежегодные денежные поступления (период между платежами принимался равным одному году), что и послужило основой для именования денежного потока аннуитетом («год» на латинском языке — anno). В дальнейшем, в качестве периода стал выступать любой промежуток времени, но прежнее название сохранилось. Сейчас период аннуитета чаще всего равен одному месяцу.

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

На картинке ниже приведен пример погашения кредита (100 000 руб.) ежемесячными платежами в течение 5 лет при ставке 15%. Для погашения тела кредита и начисленных процентов потребуется произвести 60 платежей (5 лет*12мес в году). Сумма ежемесячного платежа = 2378,99руб. См. файл примера Лист Аннуитет (ПЛТ) . Как видно из графика платежей, банк в первые периоды получает платежи, идущие на погашение %, а тело кредита сокращается медленно (см. статью Сравнение графиков погашения кредита дифференцированными и аннуитетными платежами в MS EXCEL ).

Если каждый элемент аннуитета имеет место в конце соответствующего периода, аннуитет называется аннуитетом постнумерандо (Ordinary Annuity); если в начале периода — аннуитетом пренумерандо (Annuity Due). Обычно используется аннуитет постнумерандо.

Примечание . В функциях MS EXCEL для указания типа аннуитета предусмотрен специальный необязательный параметр [тип] . По умолчанию тип =0 (выплаты в конце периода), что соответствует аннуитету постнумерандо. Если тип =1, то предполагается аннуитет пренумерандо (выплаты в начале периода).

Часто в расчетах используют понятие аннуитетный коэффициент (А):

A = -Ставка * (1+ Ставка)^Кпер / (1-(1+ Ставка)^ Кпер ) / (1+ Ставка*Тип)

где: Ставка — процентная ставка за период; Кпер — общее количество периодов выплаты; Тип – для аннуитета постнумерандо Тип=0, для пренумерандо Тип=1.

Чтобы вычислить член аннуитета (величину регулярного платежа) нужно использовать формулу =А*ПС, где ПС – это начальная сумма кредита. Специфика аннуитета (равенство денежных поступлений) позволяет вывести стандартизованные формулы, существенно упрощающие счетные процедуры. Об этих формулах и об их использовании в MS EXCEL и пойдет речь ниже.

Параметры функций аннуитета

Финансовые функции ПЛТ() , ОСПЛТ() , ПРПЛТ() , КПЕР() , СТАВКА() , БС() , ПС() , а также ОБЩДОХОД() и ОБЩПЛАТ() тесно связаны между собой, т.к. все они вычисляют параметры аннуитета и, соответственно, используют один и тот же набор аргументов. В этом можно убедиться, перечислив все функции вместе с аргументами:

ПЛТ(ставка; кпер; пс; [бс]; [тип]) ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) ПРПЛТ(ставка; период; кпер; пс; [бс]; [тип]) КПЕР(ставка; плт; пс; [бс]; [тип]) СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) БС(ставка; кпер; плт; [пс]; [тип]) ПС(ставка; кпер; плт; [бс]; [тип])

ПЛТ (английское название функции: PMT, от слова payment ). Регулярный платеж, осуществляемый каждый период. Платеж – постоянная величина, она не меняется в течение всего срока аннуитета. Ставка (англ.: RATE, interest). Процентная ставка за период , чаще всего за год или за месяц. Обычно задается через годовую ставку, деленную на количество периодов в году. При годовой ставке 10% месячная ставка составит 10%/12. Ставка не изменяется в течение всего срока аннуитета. Кпер (англ.: NPER). Общее число периодов платежей по аннуитету . Если кредит взят на 5 лет, а выплаты производятся ежемесячно, то всего 60 периодов (12 мес. в году * 5 лет) Бс (англ.: FV, future value). Будущая стоимость в конце срока аннуитета (по истечении числа периодов Кпер). Бс - требуемое значение будущей стоимости или остатка средств после последней выплаты. Например, в случае расчета аннуитетного платежа для полной выплаты ссуды к концу срока Бс = 0, т.к. ссуда в конце срока должна быть полностью погашена. Пс (англ.: PV, present value). Приведенная стоимость , т.е. стоимость приведенная к определенному моменту (часто к текущему, т.е. настоящему времени). Если взят кредит и производятся регулярные выплаты по аннуитетной схеме, то Приведенная стоимость – это сумма кредита. Если планируется регулярно вносить равновеликие платежи на счет в банке (и период начисления % совпадает с периодом платежей), то Приведенную стоимость также нужно указывать = 0. Тип (англ.: type). Число 0 или 1, обозначающее, когда должна производиться выплата (и соответственно начисление процентов). 0 – в конце периода, 1 – в начале. Подробнее см. раздел Немного теории в начале статьи о постнумерандо и пренумерандо или статьи с примерами, указанные выше.

Все 6 аргументов (параметров аннуитета) связаны между собой выражением:

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

Примечание . Формула 1 работает, если Ставка не равна 0. Если ставка равна 0, то вместо Формулы 1 действует гораздо более простое выражение: ПЛТ * Кпер + ПС + БС = 0 (в этом случае схема платежей перестает быть аннуитетом и превращается в беспроцентную ссуду).

О направлениях денежных потоков и знаках ПС, БС и ПЛТ

Вышеуказанная Формула 1 предполагает, что знаки денежных потоков (+/-) указываются с учетом их направления. Например, банк выдал кредит (ПС>0), клиент банка ежемесячно вносит одинаковый платеж (ПЛТ ПЛТ() возвращает отрицательные значения, если ПС>0.

Тождество аннуитета

Если Тип=0, то для функций MS EXCEL справедливо тождество: ОБЩДОХОД(за все периоды) + ПС + БС = 0

Это тождество можно переписать в другом виде: СУММ(ОСПЛТ()) + ПС + БС = 0. В случае использования аннуитетной схемы погашения кредита (сумма кредита =ПС), выражение СУММ(ОСПЛТ()) вычисляет общую сумму платежей, идущих на оплату основной суммы долга (тело кредита). В случае полного погашения кредита БС=0, а тождество превращается в ПС=-СУММ(ОСПЛТ()).

Функции MS EXCEL для расчета параметров аннуитета

Теперь кратко рассмотрим функции MS EXCEL. Для того, чтобы нижесказанное было понятным, необходимо предварительно ознакомиться с теорией аннуитета, понятиями Будущая и Приведенная стоимость.

Функция ПЛТ(ставка; кпер; пс; [бс]; [тип]) рассчитывает величину регулярного платежа на основе заданных 5 аргументов.

Примечание . Английский вариант функции: PMT(rate, nper, pv, [fv], [type]), т.е. PayMenT – платеж.

Для понимания работы формулы приведем эквивалентное ей выражение для расчета платежа:

Формула 2 есть не что иное, как решение Формулы 1 относительно параметра ПЛТ.

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

Если процентная ставка = 0, то Формула 2 упростится до =(ПС + БС)/Кпер

Если Тип=0 (выплата в конце периода) и БС =0, то Формула 2 заметно упрощается:

В случае применения схемы аннуитета для выплаты ссуды платеж включает денежную сумму в счет погашения части ссуды и сумму для оплаты начисленных за прошедший период процентов, поэтому функция ПЛТ() связана с ОСПЛТ() и ПРПЛТ() соотношением ПЛТ = ОСПЛТ + ПРПЛТ (для каждого периода).

Примечание . В файле примера на листе Зависимости ПЛТ() приведены графики: Зависимость суммы платежа от размера ссуды, Зависимость суммы платежа от ставки, Зависимость суммы платежа от срока ссуды. Также в файле примера приведены некоторые задачи.

Функция ОСПЛТ(ставка; период; кпер; пс; [бс]; [тип]) используется для вычисления регулярных сумм идущих на погашение основной суммы долга практически с теми же аргументами, что и ПЛТ() . Т.к. сумма идущая на погашение основной суммы долга изменяется от периода к периоду, то необходим еще один аргумент период , который определяет к какому периоду относится сумма.

Примечание . Английский вариант функции: PPMT(rate, per, nper, pv, [fv], [type]), т.е. Principal Payment – платеж основной части долга.

В случае применения схемы аннуитета для выплаты ссуды для каждого периода действует равенство: ОСПЛТ =ПЛТ – ПРПЛТ, т.к. платеж включает сумму в счет погашения части ссуды (ОСПЛТ) и сумму для оплаты начисленных за прошедший период процентов (ПРПЛТ). Сумму, идущую на погашение основной суммы долга также можно вычислить, зная величину платежа (ПЛТ), период (Период), общее количество периодов (Кпер) и ставку (СТАВКА):

Вышеуказанная формула работает при БС=0. При ТИП=1 (платеж в начале периода) и n=1 (первый платеж), ПРПЛТ=ПЛТ Если БС<>0, то формула усложнится:

Функцию ОСПЛТ() часто применяют при составлении графика платежей по аннуитетной схеме (см. Выплата основной суммы долга в аннуитетной схеме. Расчет в MS EXCEL )

Примечание . В файле примера на листе Аннуитет (без ПЛТ) определена аналитическая зависимость суммы идущей на погашение долга от номера периода.

Функция ПРПЛТ (ставка; период; кпер; пс; [бс]; [тип]) используется для вычисления регулярных сумм идущих на погашение процентов за ссуду используется с теми же аргументами, что и ОСПЛТ() .

Примечание. Английский вариант функции: IPMT(rate, per, nper, pv, [fv], [type]), т.е. Interest Payment – выплата процентов.

В случае применения схемы аннуитета для выплаты ссуды для каждого периода действует равенство: ПРПЛТ =ПЛТ – ОСПЛТ

Сумму, идущую на погашение процентов за ссуду, можно вычислить зная: величину платежа (ПЛТ), период (Период), общее количество периодов (Кпер) и ставку (СТАВКА):

Вышеуказанная формула работает при БС=0. При ТИП=1 (платеж в начале периода) и n=1 (первый платеж), ПРПЛТ=0 Если БС<>0, то формула усложнится:

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

Функцию ПРПЛТ() часто применяют при составлении графика платежей по аннуитетной схеме (см. Аннуитет. Расчет в MS EXCEL выплаченных процентов за период ).

Функция КПЕР(ставка; плт; пс; [бс]; [тип]) позволяет вычислить количество периодов, через которое текущая сумма вклада (пс) станет равной заданной сумме (бс) при известной процентной ставке за период (ставка) и известной величине пополнения вклада (плт). При этом предполагается, сумма пополнения вклада вносится регулярно в каждый период, тогда же происходит и начисление процентов. Сумма пополнения вклада может быть равна 0 (вклад не пополняется, рост вклада осуществляет только за счет капитализации процентов). Бс (будущая стоимость) может быть =0 или опущена. Также функцию КПЕР() можно использовать для определения количества периодов, необходимых для погашения долга по ссуде (погашение осуществляется регулярно равными платежами, ставка не изменяется весь срок, на который выдана ссуда, процент начисляется каждый период на остаток ссуды).

Примечание . Английский вариант функции: NPER(rate, pmt, pv, [fv], [type]), т.е. Number of Periods – число периодов.

Эквивалентная формула для расчета платежа:

Если ставка равна 0, то: Кпер = (Пс + Бс) /ПЛТ

Подробнее про функцию можно прочитать в статье Аннуитет. Расчет в MS EXCEL количества периодов .

Функция СТАВКА(кпер; плт; пс; [бс]; [тип]; [предположение]) возвращает процентную ставку по аннуитету.

Примечание . Английский вариант функции: RATE(nper, pmt, pv, [fv], [type], [guess]), т.е. Number of Periods – число периодов.

Подробнее про функцию можно прочитать в статье Аннуитет. Определяем процентную ставку в MS EXCEL .

Функция БС(ставка; кпер; плт; [пс]; [тип]) возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки. Например, если у Вас сейчас на банковском счете сумма ПС (ПС м.б. =0) и вы ежемесячно вносите одну и туже сумму ПЛТ, то функция вычислит остаток на Вашем банковском счете через Кпер месяцев (предполагается, что капитализация процентов происходит также ежемесячно с процентной ставкой равной величине СТАВКА).

Примечание . Английский вариант функции: FV(rate, nper, pmt, [pv], [type]), т.е. Future Value – будущая стоимость.

Вычисления в функции БС() производятся по этой формуле:

Если СТАВКА =0, то Будущую стоимость можно определить по формуле БС= - ПЛТ * Кпер + ПС

Подробнее про функцию можно прочитать в статье Аннуитет. Определяем в MS EXCEL Будущую Стоимость .

Функция ПС(ставка; кпер; плт; [бс]; [тип]) возвращает приведенную (к текущему моменту) стоимость инвестиций . Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на настоящий момент равноценна ряду будущих регулярных выплат ПЛТ за количество периодов Кпер. Также предполагается, что капитализация процентов происходит также регулярно с процентной ставкой равной величине СТАВКА.

Примечание . Английский вариант функции: PV(rate, nper, pmt, [fv], [type]), т.е. Present Value – будущая стоимость.

Вычисления в функции ПС() производятся по этой формуле:

Если СТАВКА =0, то Приведенную стоимость можно определить по формуле ПС=-БС-ПЛТ*Кпер

Функции ОБЩДОХОД() и ОБЩПЛАТ() Аргументы функций ОБЩДОХОД() и ОБЩПЛАТ() несколько отличаются от рассмотренных выше. Но на самом деле разница только в их названии: кол_пер – это кпер; нз – это пс. Нач_период и кон_период – это «начальный период» и «конечный период».

Функция ОБЩДОХОД(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) сумму, выплачиваемую в погашение основной суммы займа в промежутке между двумя периодами ( нач_период и кон_период ).

Примечание . Английский вариант функции: CUMPRINC(rate, nper, pv, start_period, end_period, type) returns the CUMulative PRincipal paid for an investment period with a Constant interest rate.

Функция ОБЩПЛАТ(ставка; кол_пер; нз; нач_период; кон_период; тип) возвращает кумулятивную (нарастающим итогом) величину процентов, выплачиваемых по займу в промежутке между двумя периодами выплат ( нач_период и кон_период ).

Примечание . Английский вариант функции: CUMIPMT(rate, nper, pv, start_period, end_period, type) returns the CUMulative Interest paid on a loan between start_period and end_period.

Общую сумму выплат по займу между двумя периодами (Нач_период и кон_период) можно найти сложив результаты возвращаемые ОБЩПЛАТ() и ОБЩДОХОД() с одинаковыми аргументами, что эквивалентно ПЛТ*(кон_период - Нач_период+1).

Прежде, чем брать заем, неплохо было бы рассчитать все платежи по нему. Это убережет заёмщика в будущем от различных неожиданных неприятностей и разочарований, когда выяснится, что переплата слишком большая. Помочь в данном расчете могут инструменты программы Excel. Давайте выясним, как рассчитать аннуитетные платежи по кредиту в этой программе.

Расчет оплаты

Прежде всего, нужно сказать, что существует два вида кредитных платежей:

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

При аннуитетной схеме используется несколько другой подход. Клиент ежемесячно вносит одинаковую сумму общего платежа, который состоит из выплат по телу кредита и оплаты процентов. Изначально процентные взносы насчитываются на всю сумму займа, но по мере того, как тело уменьшается, сокращается и начисление процентов. Но общая сумма оплаты остается неизменной за счет ежемесячного увеличения величины выплат по телу кредита. Таким образом, с течением времени удельный вес процентов в общем ежемесячном платеже падает, а удельный вес оплаты по телу растет. При этом сам общий ежемесячный платеж на протяжении всего срока кредитования не меняется.

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

Этап 1: расчет ежемесячного взноса

Для расчета ежемесячного взноса при использовании аннуитетной схемы в Экселе существует специальная функция – ПЛТ. Она относится к категории финансовых операторов. Формула этой функции выглядит следующим образом:

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

Аргумент «Ставка» указывает на процентную ставку за конкретный период. Если, например, используется годовая ставка, но платеж по займу производится ежемесячно, то годовую ставку нужно разделить на 12 и полученный результат использовать в качестве аргумента. Если применяется ежеквартальный вид оплаты, то в этом случае годовую ставку нужно разделить на 4 и т.д.

«Кпер» обозначает общее количество периодов выплат по кредиту. То есть, если заём берется на один год с ежемесячной оплатой, то число периодов считается 12, если на два года, то число периодов – 24. Если кредит берется на два года с ежеквартальной оплатой, то число периодов равно 8.

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

Аргумент «Тип» определяет время расчета: в конце или в начале периода. В первом случае он принимает значение «0», а во втором – «1». Большинство банковских учреждений используют именно вариант с оплатой в конце периода. Этот аргумент тоже является необязательным, и если его опустить считается, что он равен нулю.

Теперь настало время перейти к конкретному примеру расчета ежемесячного взноса при помощи функции ПЛТ. Для расчета используем таблицу с исходными данными, где указана процентная ставка по кредиту (12%), величина займа (500000 рублей) и срок кредита (24 месяца). При этом оплата производится ежемесячно в конце каждого периода.

«Вставить функцию»

В поле «Ставка» следует вписать величину процентов за период. Это можно сделать вручную, просто поставив процент, но у нас он указан в отдельной ячейке на листе, поэтому дадим на неё ссылку. Устанавливаем курсор в поле, а затем кликаем по соответствующей ячейке. Но, как мы помним, у нас в таблице задана годовая процентная ставка, а период оплаты равен месяцу. Поэтому делим годовую ставку, а вернее ссылку на ячейку, в которой она содержится, на число 12, соответствующее количеству месяцев в году. Деление выполняем прямо в поле окна аргументов.

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

В поле «Пс» указывается первоначальная величина займа. Она равна 500000 рублей. Как и в предыдущих случаях, указываем ссылку на элемент листа, в котором содержится данный показатель.

В поле «Бс» указывается величина займа, после полной его оплаты. Как помним, это значение практически всегда равно нулю. Устанавливаем в данном поле число «0». Хотя этот аргумент можно вообще опустить.

В поле «Тип» указываем в начале или в конце месяца производится оплата. У нас, как и в большинстве случаев, она производится в конце месяца. Поэтому устанавливаем число «0». Как и в случае с предыдущим аргументом, в данное поле можно ничего не вводить, тогда программа по умолчанию будет считать, что в нем расположено значение равное нулю.

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

А теперь с помощью других операторов Эксель сделаем помесячную детализацию выплат, чтобы видеть, сколько в конкретном месяце мы платим по телу займа, а сколько составляет величина процентов. Для этих целей чертим в Экселе таблицу, которую будем заполнять данными. Строки этой таблицы будут отвечать соответствующему периоду, то есть, месяцу. Учитывая, что период кредитования у нас составляет 24 месяца, то и количество строк тоже будет соответствующим. В столбцах указана выплата тела займа, выплата процентов, общий ежемесячный платеж, который является суммой предыдущих двух колонок, а также оставшаяся сумма к выплате.

ОСПЛТ

«1»

«Выплата по телу кредита»

«Вставить функцию»

Как видим, аргументы данной функции почти полностью совпадают с аргументами оператора ПЛТ, только вместо необязательного аргумента «Тип» добавлен обязательный аргумент «Период». Он указывает на номер периода выплаты, а в нашем конкретном случае на номер месяца.

Таким образом, мы произвели не просто расчет оплаты по кредиту, а организовали своеобразный кредитный калькулятор. Который будет действовать по аннуитетной схеме. Если в исходной таблице мы, например, поменяем величину займа и годовой процентной ставки, то в итоговой таблице произойдет автоматический пересчет данных. Поэтому её можно использовать не только один раз для конкретного случая, а применять в различных ситуациях для расчета кредитных вариантов по аннуитетной схеме.

Как видим, при помощи программы Excel в домашних условиях можно без проблем рассчитать общий ежемесячный кредитный платеж по аннуитетной схеме, используя для этих целей оператор ПЛТ. Кроме того, при помощи функций ОСПЛТ и ПРПЛТ можно произвести расчет величины платежей по телу кредита и по процентам за указанный период. Применяя весь этот багаж функций вместе, существует возможность создать мощный кредитный калькулятор, который можно будет использовать не один раз для вычисления аннуитетного платежа.

Мы рады, что смогли помочь Вам в решении проблемы.

Отблагодарите автора, поделитесь статьей в социальных сетях.

Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Платежи по кредитам удобнее и быстрее рассчитывать с Microsoft Office Excel. На ручное вычисление уходит гораздо больше времени. В данной статье речь пойдет об аннуитетных платежах, особенностях их расчета, преимуществах и недостатках.

Что такое аннуитетный платеж

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

Причем проценты по кредиту уже включены в общую сумму, вносимую в банк.

Классификация аннуитета

Аннуитетные платежи можно разделить на следующие виды:

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

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

Преимущества и недостатки аннуитетных платежей

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

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

Без недостатков не обошлось:

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

Из чего состоит платеж по кредиту?

Аннуитетный платеж имеет следующие составляющие части:

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

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

Основная формула аннуитетного платежа в Excel

Как и говорилось выше, в Microsoft Office Excel можно работать с различными типами платежей по кредитам и ссудам. Аннуитет не является исключением. В общем виде формула, с помощью которой можно быстро вычислить аннуитетные взносы, выглядит следующим образом:

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

Основные значения формулы расшифровываются так:

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

Для усвоения информации достаточно привести несколько примеров использования данной формулы. О них пойдет речь далее.

Примеры использования функции ПЛТ в Excel

Приведем простое условие задачи. Необходимо посчитать ежемесячный кредитный платеж, если банк выдвигает процент в размере 23%, а общая сумма составляет 25000 рублей. Кредитование продлится на протяжении 3-х лет. Задача решается по алгоритму:

Составить общую таблицу в Excel по исходным данным.

Активировать функцию ПЛТ и ввести для нее аргументы в соответствующее окошко.
В поле «Ставка» прописать формулу «В3/В5». Это и будет процентная ставка по взятому кредиту.
В строке «Кпер» написать значение в виде «В4*В5». Это будет общее количество выплат за весь срок кредитования.
Заполнить поле «Пс». Здесь нужно указать первоначальную сумму, взятую в банке, прописав значение «В2».

Удостовериться, что после нажать «ОК» в исходной таблице посчиталось значение «Ежемесячный платеж».

Дополнительная информация! Отрицательное число свидетельствует о том, что заемщик расходует деньги.

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

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

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

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

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

Особенности использования функции ПЛТ в Excel

В общем виде данная формула записывается следующим образом: =ПЛТ(ставка; кпер; пс; [бс]; [тип]). У функции есть следующие особенности:

Когда рассчитываются ежемесячные взносы, в рассмотрение берется исключительно годовая ставка.
Указывая размер процентной ставки, важно сделать перерасчет, опираясь на число взносов за год.
Вместо аргумента «Кпер» в формуле указывается конкретное число. Это период выплат по задолженности.

Расчет оплаты

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

Этап 1: расчет ежемесячного взноса

Чтобы в Excel посчитать сумму, которую нужно вносить каждый месяц по кредиту с фиксируемой ставкой, необходимо:

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

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

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

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

Расчет аннуитетных платежей по кредиту в Excel

За вычисление аннуитета в Excel отвечает функция ПЛТ. Принцип вычисления в общем виде заключается в выполнении следующих шагов:

Составить исходную таблицу данных.
Построить график погашения долга для каждого месяца.
Выделить первую ячейку в столбике «Платежи по кредиту» и ввести формулу расчета «ПЛТ ($В3/12;$В$4;$В$2)».
Получившееся значение растянуть для всех столбцов таблички.

Расчет в MS Excel погашение основной суммы долга

Аннуитетные платежи должны вноситься ежемесячно определенными суммами. Причем процентная ставка не изменяется.

Вычисление остатка суммы основного долга (при БС=0, тип=0)

Предположим, что кредит на 100000 рублей берется на 10 лет под 9%. Необходимо рассчитать сумму основного долга в 1 месяце 3-го года. Решение:

Составить таблицу данных и вычислить ежемесячный платеж по приведенной выше формуле ПС.
Рассчитать долю платежа, необходимую для погашения части долга, по формуле «=-ПМТ-(ПС-ПС1)*ставка=-ПМТ-(ПС +ПМТ+ПС*ставка)».
Посчитать сумму основного долга за 120 периодов по известной формуле.
Используя оператор ПРПЛТ найти количество процентов, выплаченных за 25 месяц.
Проверить результат.

Вычисление суммы основного долга, которая была выплачена в промежутке между двумя периодами

Такой расчет лучше сделать простым способом. Нужно использовать следующие формулы для вычисления суммы в промежутке за два периода:

=«-БС(ставка; кон_период; плт; [пс]; [тип]) /(1+тип *ставка)».
= «+ БС(ставка; нач_период-1; плт; [пс]; [тип]) /ЕСЛИ(нач_период =1;1; 1+тип *ставка)».

Обратите внимание! Буквы в скобках заменяются конкретными значениями.

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

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

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

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

Уменьшение выплат кредитования

Кредитный калькулятор с нерегулярными выплатами

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

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

Расчет периодического платежа в MS Excel. Срочный вклад

В Excel можно быстро посчитать размер регулярных выплат при условии, что уже накопилась фиксированная сумма. Данное действие выполняется с использованием функции ПЛТ после составления исходной таблицы.

Заключение

Таким образом, аннуитетные платежи проще, быстрее и эффективнее рассчитывать именно в Эксель. За их вычисление отвечает оператор ПЛТ. С подробными примерами можно ознакомиться выше.

Читайте также: