Расчет аннуитетных платежей: формула, использование excel

Погашение задолженности по займу

В 2016 году общая сумма задолженности населения по кредиту превышала в 10 000 миллиардов рублей. Большая часть банковских организаций обговаривает условия возвращения взятых взаймы средств перед их выдачей. Существует две основных формы погашения задолженности по займу:

• дифференцированными платежами;
• аннуитетными платежами.

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

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

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

     Последний из рассматриваемых вариантов будет расчёт кредита с нерегулярными платежами, это когда на повышенную процентную ставку вам предоставляют лояльную программу вносить платежи на нерегулярной основе и без определений сумм взносов. Согласно таким кредитным программам банк может вам выделять еще дополнительно денег на ваши нужды, но для расчётов такой структуры кредитования производить расчёты нужно с точностью до дня, а не до месяца.     Ну вот в принципе и всё, единственно что хочу сказать, что подсчёт сколько точно дней находится между двумя указанными датами, лучше производить при помощи функции ДОЛЯГОДА.

     А на этом у меня всё! Я очень надеюсь, что всё о создании кредитного калькулятора в Excel вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

     Не забудьте поблагодарить автора!

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

Пример 1. Для покупки ноутбука в будущем, стоимость которого составляет 55000 рублей, было решено сделать депозит в банке и по истечению срока действия договора забрать требуемую сумму. Процентная ставка – 20% годовых, срок действия – 12 месяцев (капитализация – каждый месяц). Определить, какую сумму должен внести вкладчик.

Формула для расчета:

• B2/12 – ставка на период выплат (12 – число месяцев в году);
• B3 – число периодов выплат;
• 0 – фиксированная сумма выплат по депозиту (неизвестна, так как вкладчик может забрать деньги по окончанию действия договора, то есть, спустя 12 месяцев);
• B4 – сумма, получаемая вкладчиком по окончанию действия договора.

То есть, на депозит требуется внести примерно 45105 рублей.

Ежегодная капитализация

Д = В х (1 П)^Т, где

В – сумма вклада;

Т – срок вклада в годах.

год	без капитализации	с капитализацией
Деньги во вкладе	Начисленныепроценты	Деньги во вкладе	Начисленныепроценты
1	100 000	10 000	100 000	10 000
2	100 000	10 000	110 000	11 000
3	100 000	10 000	121 000	12 100
4	100 000	10 000	133 100	13 310
5	100 000	10 000	146 410	14 641
ИТОГО		50 000		61 051

При этом, за пять лет разница между двумя вкладами составила более 11 000 рублей.

Кроме рассмотренных выше периодов начислений капитализации банки могут предлагать и другие, например, раз в полгода, раз в 10, 20, 100, 200, 400 дней. Здесь условия ограничиваются лишь фантазией банковских работников, отвечающих за депозитные программы.

Как рассчитать будущую стоимость в Excel — примеры формул

Базовая формула Excel FV очень проста, верно? Теперь давайте посмотрим, как настроить его для обработки нескольких наиболее распространенных сценариев.

Формула FV для периодических платежей

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

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

Чтобы преобразовать годовую процентную ставку в периодическую, разделите годовую ставку на количество периодов в году:

• Ежемесячные платежи: оценивать = годовая процентная ставка / 12
• Квартальные платежи: оценивать = годовая процентная ставка / 4
• Полугодовые платежи: оценивать = годовая процентная ставка / 2

Чтобы получить общее количество периодов, умножьте срок в годах на количество периодов в году:

• Ежемесячные платежи: Например = нет. лет * 12
• Квартальные платежи: Например = нет. лет * 4
• Полугодовые платежи: Например = нет. лет * 2

Теперь давайте посмотрим, как это работает на практике. Предположим, вы ежемесячно инвестируете 200 долларов в течение 3 лет с годовой процентной ставкой 6%. Исходные данные вводятся в эти ячейки:

• Годовая процентная ставка (B2): 6%
• Количество лет (B3): 3
• Ежемесячный платеж (В4): -200
• Периодов в году (B5): 12

Чтобы рассчитать будущую стоимость этих инвестиций, формула в B7 выглядит следующим образом:

=БС(В2/В5, В3*В5, В4)

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

Формула FV для единовременных инвестиций

Если вы решите инвестировать деньги в качестве единовременного единовременного платежа, формула будущей стоимости будет основываться на текущей стоимости (пв), а не периодическая оплата (пмт).

Итак, мы настроили наши образцы данных следующим образом:

• Годовая процентная ставка (C2): 7%
• Количество лет (C3): 5
• Текущая стоимость (C4): -1000

Формула для расчета будущей стоимости инвестиций:

=FV(C2, C3, ,C4)

Обратите внимание, что:

• Сумма инвестиций (пв) — отрицательное число, потому что это отток.
• пмт аргумент равен 0 или опущен.

Если периоды начисления сложных процентов для ваших инвестиций не являются годовыми, то для точного определения будущей стоимости вам необходимо внести следующие коррективы в формулу:

• За оцениватьразделите годовую процентную ставку на количество периодов начисления процентов в году.
• За Напримерумножьте количество лет на количество периодов начисления процентов в году.

В качестве примера, давайте найдем будущую стоимость вышеупомянутых инвестиций с процентной ставкой, начисляемой ежемесячно. Для этого делим годовую процентную ставку (С2) на 12 и умножаем количество лет (С3) на 12:

=БС(С2/12, С3*12, ,С4)

или же

=FV(C2/C5, C3*C5, ,C4)

Где C5 — количество периодов начисления процентов в год:

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

Чтобы сравнить объем роста, вызванный различными периодами начисления сложных процентов, вам необходимо предоставить различные оценивать а также Например к функции ФВ.

Чтобы все расчеты выполнялись по одной формуле, выполните следующие действия:

• Введите количество периодов начисления сложных процентов в год в поле B2.
• Расположите свои данные, как показано на изображении ниже.
• Введите следующую формулу в C2 и перетащите ее вниз через C6:=БС($F$2/B2, $F$3*B2, ,$F$4)

Обратите внимание, что мы блокируем ссылки на годовую процентную ставку ($F$2), количество лет ($F$3) и сумму инвестиций ($F$4) знаком доллара ($), чтобы они не сместились при копировании. вниз по формуле

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как определить полную стоимость займа

По новым требованиям законодательства для определения полной стоимости кредита используется новая формула:

ПСК = ставка процента за базовый период (i) * число периодов за 1 календарный год (ЧБП) * 100

Например, мужчина оформил кредит на сумму 200 тыс. долларов на 1 год по ставке 22%. При этом ему пришлось внести единовременную комиссию 2,7 тыс. долларов.

Для определения полной стоимости займа формируется график:

Базовый период (БП) представлен интервалом времени, встречающимся в графике наиболее часто. На примере он представлен 28 днями. В этом случае число периодов составляет: 365/28=13. Только после этого рассчитывается ставка базового периода:

В результате легко рассчитывается полная стоимость кредита (ПСК), которая составляет 22%. Благодаря использованию новой формулы данный показатель равен годовой ставке по займу.

Расчет платежеспособности заемщика кредита по функции ПС в Excel

Пример 2. Зарплата клиента МФО составляет 25000 рублей. Причина обращения – полное отсутствие денег. До следующей зарплаты осталось 16 дней. Какую минимальную сумму кредита может взять заемщик, если микрозайм выдается под 2% в день, а минимальная сумма, на которую он может прожить в месяц, составляет 12000 рублей?

Формула для расчета:

• 365*B4/B3 – пересчет процентной ставки на указанный период дней (деньги нужны до зарплаты, значит период кредитования – 16 дней);
• 1 – количество периодов выплат (в МФО, как правило, устанавливают один период выплат – по окончанию срока кредитования);
• 0 – фиксированная сумма выплат в каждом из периодов (явно не указан, поскольку используем аргумент ;
• B5-B2 – максимальная сумма, которую сможет выплатить заемщик по кредиту при условии, что у него должно остаться не менее 12000 рублей после оплаты задолженности.

То есть, чтобы заемщик не попал в так называемую «долговую яму» или зависимость от МФО, ему не следует брать в долг более чем 9000 рублей.

Расчет цены с учетом маржи и себестоимости

Теперь, зная, чем маржа отличается от наценки, можем вывести формулу цены. Как посчитать цену, зная маржу и себестоимость?

Если М=(Ц-С)/Ц, то Ц=С/(1-М).

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

Составим таблицу для расчетов и предварительно укажем задаваемые параметры (если говорить математическими терминами – заполним дано).

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

Рассчитаем отсрочку по формуле. Умножим себестоимость на банковскую ставку, поделим на число дней в году и на количество дней отсрочки.

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

Теперь можем рассчитать расчетную цену без НДС по экономической формуле.

Ну и цену с НДС (1,18 – это коэффициент НДС).

Далее – валовый доход (разница цены и себестоимости).

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

Наценка действительно получилась одинаковой. Значит, мы на верном пути.

Процент менеджера: 5% от расчетной цены.

Аналогично: ретро-бонус – это 15% от расчетной цены.

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

Маржинальный доход считается как разница валового дохода и затрат.

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

Маржа действительно получилась 40%. Значит все верно. Теперь, пользуясь таблицей, можем изменять значение маржи или себестоимости и сразу видеть все показатели. При этом будет сохранять равенство всех наценок и проверочное значение маржи.

Попробуем изменить маржу на 50% (в задаваемых параметрах). Смотрим, что получается.

Или изменим себестоимость первого товара.

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

Использование функции БС() в случае накопления вклада

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

1. Предполагается, что капитализация процентов происходит также периодически с процентной ставкой равной величине СТАВКА;
2. Процентная ставка указывается за период (если период равен месяцу, а задана годовая ставка =10%, то СТАВКА =10%/12);
3. По умолчанию аргумент Тип=0, т.е. пополняющие счет вклады делаются в конце каждого периода. Если Тип=1, то пополняющие счет вклады делаются в начале каждого периода;
4. Начальная сумма вклада ПС м.б. =0, но тогда суммы дополнительных взносов ПЛТ не должны быть =0;
5. Суммы дополнительных взносов м.б. =0, но тогда Начальная сумма вклада ПС не должна быть =0.

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

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

Из формулы видно, будущая стоимость состоит из 2-х составляющих: будущая стоимость инвестиции ПС (вычисляется по формуле ) и будущая стоимость периодических равновеликих взносов ПЛТ (вычисляется по формуле ).

Примечание
. При БС=0 (начальная инвестиция =0) Будущая стоимость не зависит от параметра Тип.

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

В результате расчетов получим следующий график накопления вклада (см. файл примера Лист Накопление
).

Примечание
. Функцию БС()
можно также использовать для вычисления баланса на конец периода (см. файл примера Лист Накопление, столбец G
). Для этого используйте выражение = БС(ставка; кпер; плт; ; )/ (1+ставка*тип)

Примечание
. При Тип=1 (начисление процентов в начале периода), баланс на конец последнего периода не равен БС (как при Тип=0), т.к. учитывается начисление процентов на следующий день после окончания последнего периода! Т.е. к балансу на конец последнего периода прибавляется величина =БС(ставка; кпер; плт; ; )*ставка

#3 Срок окупаемости инвестиций с учетом ликвидационной стоимости

Срок окупаемости с учетом ликвидационной стоимости (англ. Bail-Out Payback Period) – представляет собой период возврата денежных средств с учетом остаточной стоимости активов, созданных в инвестиционном проекте. При осуществлении инвестиционного проекта могут создаваться активы, которые могут быть проданы (ликвидированы) в результате этого срок окупаемости проекта существенно сокращается.

где:

IC  (Invest Capital) – первоначальные инвестиционные затраты в проекте;

RV   (Residual Value)­– ликвидационная стоимость активов проекта;

CF_i (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.

Ликвидационная стоимость может, как увеличиться в результате создания новых активов, так и уменьшаться за счет износа.

Пройдите наш авторский курс по выбору акций на фондовом рынке → обучающий курс

Бесплатный Экспресс-курс «Оценка инвестиционных проектов с нуля в Excel» от Ждановых. Получить доступ

Текущая стоимость ренты

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

Пример текущей стоимости аннуитета​

Предположим, что человек может получить ренту с выплатой 50,000 25 долларов в течение следующих 6 лет при ставке дисконта XNUMX %, или паушальный платеж в размере 650,000 XNUMX долларов США. Какой лучший вариант? Текущая стоимость аннуитета выглядит следующим образом: 

Текущая стоимость​

=$50,000×0.06 1−((1+0.06) 25)

= 639,168 долларов США

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

#1 Срок окупаемости инвестиций (PP). Формула

где:

IC  (Invest Capital) – первоначальные инвестиционные затраты в проекте;

CF_i (Cash Flow) – денежный поток от проекта в i-й период времени, который представляет собой сумму чистой прибыли и амортизации.

Для расчета денежного потока необходимо воспользоваться следующими формулами:

или

где:

А (Amortization) – амортизация, вид денежного потока, который не является затратами;

NP (Net Profit) – чистая прибыль инвестиционного проекта.

★ Программа InvestRatio — расчет всех инвестиционных коэффициентов в Excel за 5 минут(расчет коэффициентов Шарпа, Сортино, Трейнора, Калмара, Модильянки бета, VaR) + прогнозирование движения курса

Порядок расчета займа с помощью калькулятора

Существует 2 варианта расчета займа
Первый — предварительный расчет, когда вы хотите взять наличные в кредит. Для данного расчета дата первого платежа не нужна. Ее можно оставить по умолчанию. Она не влияет на размер ежемесячного платежа.
Сумма займа — она прописана в кредитном договоре и берется без учета первоначального взноса на товар или услугу.
Процентная ставка — номинальная ставка по займу без учета комиссий и страховки. Берется из договора займа. Можно ввести 3 знака после запятой.
Выражается без деления на сто.
Срок — целое число месяцев на которе берется займ. Если у вас 2 года к примеру, то нужно ввести 24 месяца
Второй вариант — расчет существующего займа
Далее идет поле — дата первого платежа. Этот параметр уже важен когда вы взяли кредит
Для взятой ссуды важен расчет по дате. Т.е при построении графика указывается дата очередной выплаты — номер дня в месяце.
Расчет с учетом дат важен при досрочных погашениях. От даты досрочного внесения средств зависит то, в каком месяце будет новый уменьшенный платеж.