Скидка на подписку 20% + 4 месяца бесплатно | 8(800)505-89-86
Журнал "Финансовый директор"

Как рассчитать аннуитетные платежи в Excel: формула, примеры

  • 14 мая 2019
  • 247
  • Средний балл: 0 из 5
Как рассчитать аннуитетные платежи в Excel: формула, примеры

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

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

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

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

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

Форма в Excel для расчета аннуитетного платежаСкачайте и используйте готовую формулу

Для расчета равных перечислений по кредиту в программе есть специальная формула. Найти ее можно по следующему пути: «Формулы» > «Финансовые» > «ПЛТ».

=ПЛТ (Ставка; КПЕР; Пс), где

КПЕР – количество периодов платежей, Пс – приведенная (нынешняя) стоимость, то есть сумма кредита. В этой формуле ставку нужно будет разделить на 12, ведь речь идет о годовой ставке, а платежи компания будет делать ежемесячно.

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

  • сумма – 1,8 млн руб.;
  • процентная ставка – 25 процентов;
  • срок – 5 лет.

Сначала нам нужно вычислить КПЕР. В нашем случае этот показатель равен 60 периодам (5 лет умножаем на 12 месяцев). Начинаем заполнять непосредственно таблицу с расчетами. Первая графа – номера периодов (№ п/п). Теперь рассчитаем непосредственно аннуитетные платежи.

Для этого зададим программе Excel формулу для расчета аннуитетного платежа:

=ПЛТ(25%/12;60;1800000) или =ПЛТ(C5/12;C7;C3;0;0)

Получается, что сумма ежемесячного платежа по кредиту составляет 52 832,38 руб.



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

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

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

Ситуация 1. Меняется срок кредитования

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

= ПРПЛТ (25%/12;1;60) или =-$C$5/12*F10

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

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

=B13-C13

Наконец, определим сумму оставшегося долга в каждом месяце. Сумма кредита + Проценты + Дополнительные платежи – формула для расчета. Или конкретно в нашем примере:

=F10+D11+E11

Кроме того, обязательное условие для расчета с досрочным погашением – использование функции «ЕСЛИ». Она подскажет, достигла ли компания конца кредита.

=ЕСЛИ(F13=0;0;ЕСЛИ(F13<-$C$9;-F13+C14;B13))

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

Ситуация 2. Сокращается размер выплаты

В этой ситуации мы также будем использовать функцию «ЕСЛИ». Для нашего примера она будет выглядеть так:

=ЕСЛИ(E11<0;ПЛТ($C$5/12;$C$7-A12+1;F11);B11)

Во второй ситуации срок кредита останется неизменным, а вот сумма платежа сократится с 52,8 до 39,7 тыс. руб. При условии, что в течение пяти лет компания сделает два дополнительных платежа – 125 и 300 тыс. руб.

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

Единственное, что нужно будет изменить в прошлом примере – графу с периодом (№ п/п). Можно конкретизировать эту графу. Например, проставить конечную дату каждого месяца, если организация гасит кредит в конце месяца.

logo
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

"Финансовый директор" - единственный профессиональный ресурс по управлению финансами компании. Материалы подготовлены финансовыми директорами и экспертами. Пройдите короткую регистрацию и получите доступ

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войти через соц сети
Зарегистрироваться
×
Пожалуйста, войдите на сайт

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
И получить доступ на сайт Займет минуту!
Зарегистрироваться
Простите, что прерываем чтение

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

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Я тут впервые
или войдите через соц сети
Зарегистрироваться
×

Мы подобрали для вас книги:

Как решать нерешаемые задачи, посмотрев проблему с другой стороны Управление финансовой службой по KPI Как не потерять на налогах: проверенные способы
Скачать бесплатно Скачать бесплатно Скачать бесплатно
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.