Как посчитать отсрочку в excel

Обновлено: 07.07.2024

Главная Статьи Проекты Планирование платежей

Планирование платежей

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

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

Длительность планового периода – календарная неделя (7 дней).

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

На листе «Пример1» представлено решение задачи несколькими способами.

2. Оплата. Пример1!C13

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

3. Оплата. Пример1!C14

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

4. Оплата. Пример1!C15

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

Определение доли оплат на основании норматива отсрочки ($B15):

Определение источника затрат для слагаемых:

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

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

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

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

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

Предварительная кредиторская задолженность. Пример2!C21:

Окончательная кредиторская задолженность. Пример2!C39:

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

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

Идея решения такова:

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

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

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

За исключением формулы частичного платежа в периоде остальные расчеты в задачах планирования платежей без учета лимитов достаточно просты и понятны. Основной смысл заключен в определении исходного периода начислений с использованием функций вычисляемой адресации. Сложность расчетов платежей с учетом лимитов задолженности обусловлена итерационным алгоритмом вычислений. В данном случае возможно, что решение с использованием VBA было бы даже проще для понимания. Для перевода планового периода в другую длительность вместо использования в формулах константы 7 лучше завести именованную ячейку. Это сделает модель более гибкой и облегчит читаемость формул. Применение нескольких условий для одного контрагента (частичная отсрочка и/или предоплата) потребует усложнения формул определения периодов начислений. Описанный в задаче метод, к сожалению, не применим для задачи расчета платежей на основе фактически полученных счетов , в случае, когда отсрочка не является константой. В этом случае требуется представить исходные данные в другом виде и предусмотреть расчет или ввод даты оплаты непосредственно рядом с начислением.

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

Excel – это универсальный аналитическо-вычислительный инструмент, который часто используют кредиторы (банки, инвесторы и т.п.) и заемщики (предприниматели, компании, частные лица и т.д.).

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

Как рассчитать платежи по кредиту в Excel

Ежемесячные выплаты зависят от схемы погашения кредита. Различают аннуитетные и дифференцированные платежи:

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

Чаще применяется аннуитет: выгоднее для банка и удобнее для большинства клиентов.

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

Ежемесячная сумма аннуитетного платежа рассчитывается по формуле:

А – сумма платежа по кредиту;
К – коэффициент аннуитетного платежа;
S – величина займа.

Формула коэффициента аннуитета:

К = (i * (1 + i)^n) / ((1+i)^n-1)

где i – процентная ставка за месяц, результат деления годовой ставки на 12;
n – срок кредита в месяцах.

В программе Excel существует специальная функция, которая считает аннуитетные платежи. Это ПЛТ:

Заполним входные данные для расчета ежемесячных платежей по кредиту. Это сумма займа, проценты и срок.
Составим график погашения кредита. Пока пустой.
В первую ячейку столбца «Платежи по кредиту» вводиться формула расчета кредита аннуитетными платежами в Excel: =ПЛТ($B$3/12; $B$4; $B$2). Чтобы закрепить ячейки, используем абсолютные ссылки. Можно вводить в формулу непосредственно числа, а не ссылки на ячейки с данными. Тогда она примет следующий вид: =ПЛТ(18%/12; 36; 100000).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Проведем расчет процентов по кредиту в Excel и вычислим эффективную процентную ставку, имея следующую информацию по предлагаемому банком кредиту:

Рассчитаем ежемесячную процентную ставку и платежи по кредиту:

Заполним таблицу вида:

Комиссия берется ежемесячно со всей суммы. Общий платеж по кредиту – это аннуитетный платеж плюс комиссия. Сумма основного долга и сумма процентов – составляющие части аннуитетного платежа.

Сумма основного долга = аннуитетный платеж – проценты.

Сумма процентов = остаток долга * месячную процентную ставку.

Остаток основного долга = остаток предыдущего периода – сумму основного долга в предыдущем периоде.

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

взяли кредит 500 000 руб.;
вернули в банк – 684 881,67 руб. (сумма всех платежей по кредиту);
переплата составила 184 881, 67 руб.;
процентная ставка – 184 881, 67 / 500 000 * 100, или 37%.
Безобидная комиссия в 1 % обошлась кредитополучателю очень дорого.

Эффективная процентная ставка кредита без комиссии составит 13%. Подсчет ведется по той же схеме.

Расчет полной стоимости кредита в Excel

Согласно Закону о потребительском кредите для расчета полной стоимости кредита (ПСК) теперь применяется новая формула. ПСК определяется в процентах с точностью до третьего знака после запятой по следующей формуле:

ПСК = i * ЧБП * 100;
где i – процентная ставка базового периода;
ЧБП – число базовых периодов в календарном году.

Возьмем для примера следующие данные по кредиту:

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

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

Далее находим ЧБП: 365 / 28 = 13.

Теперь можно найти процентную ставку базового периода:

У нас имеются все необходимые данные – подставляем их в формулу ПСК: =B9*B8

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

ПСК по новой формуле совпала с годовой процентной ставкой по кредиту.

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

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

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

Ниже на рисунке представлен пример простейшего кредитного калькулятора в Excel, который вычисляет сколько придется платить банку ежемесячно для погашения кредита. Пользователь должен указать соответственные значения условий кредитования в ячейках B2:B4, а в ячейке B6 получим результат вычисления функции ПЛТ. Ежемесячный платеж по кредиту вычисляется следующей формулой:

Функция ПЛТ имеет 3 обязательных аргумента для заполнения и 1 опциональный:

Ставка – номинальная процентная ставка годовых разделена на число периодов капитализации в году. В данном примере кредит капитализируется ежемесячно, значит процентная ставка указана в ячейке B3 делится на число 12 (месяцев).
Количество периодов (кпер) – количество платежей по кредиту на протяжении всего срока погашения кредита. То есть пользователь кредитного калькулятора должен указать число лет кредитования, а платежи будут проплачиваться ежемесячно. Поэтому число лет указано в ячейке B4 умножается на число 12 (месяцев).
Приведенная стоимость (ПС) – нынешняя сумма долга перед банком. Функции Excel предназначенные для расчетов кредитов, в том числе и функция ПЛТ по принципу действия основываются на расчетах потоков налички. Если текущее значение кредита и взносы платежей будут считаться как приход и расход наличных средств, тогда легко понять почему и когда результаты вычислений этих функций будут положительными или отрицательными числами. В данном примере банк дал вам взаймы сумму 215 000, для вас данная статья потока наличных средств ставиться на приход, а это положительное числовое значение. Результат вычисления функции ПЛТ является отрицательным, так как ежемесячные платежи долга по кредиту для вас это расход.
Тип – определение выплаты в начале (1) или конце(0) периода.

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

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

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

Функция ПЛТ изначально не знает, что значит введенное число – количество: лет, месяцев или дней. Она воспринимает, что ставка дисконтирования и количество лет касаются одного и того же периода.

Как составить график платежей по кредиту в Excel

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

Ниже на рисунке представлен фрагмент графика. Он состоит из следующих столбцов:

Номер – порядковый номер текущего платежа по кредиту. В ячейке D4 введите число 1, а в следующей ячейке D5 введите формулу =D4+1 и скопируйте ее в остальные нижние ячейки аж до D363 (данный график будет содержать 360 платежей).
Сумма платежа – вычисляется м помощью функции ПЛТ в ячейке =$B$7 и округляется до копеек. Хоть Exel выполняет вычисления с точностью до большого количества разрядов после запятой, в графике можно указать только сумму до копеек. Это значит, что в конце периода до полного погашения кредита останется лишь только небольшая сумма для расчета с банком. В ячейке E4 введите формулу =-ОКРУГЛ($B$7;2) и скопируйте ее в нижние ячейки аж до E636.
Тело кредита – часть платежа закрывающая долг кредита без учета процентов. В ячейке F4 введите формулу =E4-G4 и скопируйте ее до F363.
Сумма процентов – часть платежа составляющая проценты начисленные за пользование кредитом. По данному столбцу мы видим сколько зарабатывает банк каждый месяц с данной долгосрочной сделки, то есть его валовая прибыль от выдачи кредита заемщику. Сумма кредита после оплаты предыдущего платежа умножается на годовую процентную ставку, умноженную на 12. Результат вычисления округляется до копеек (двух разрядов после запятой). В ячейке G4 введите следующую формулу =ОКРУГЛ(H3*$B$4/12;2) и скопируйте ее в нижние ячейки аж до G363.
Долг по кредиту – сумма остатка долга по кредиту после каждого платежного взноса заемщика. В ячейке H3 введите формулу =B3, которая возвращает начальную сумму долга кредита. Формула =H3-F4 введена в остальных ячейках данного столбца. Она уменьшает сумму долга только на первую часть (тело кредита) каждого платежа. Вторую часть сумму процентов банк оставляет себе.

В примере указанному на втором рисунке срок кредитования составляет 15 лет, а на первом рисунке – срок 30 лет. Уменьшение периода погашения кредита увеличивает сумму ежемесячного платежа.

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

Для этого выделите диапазон ячеек D4:H363 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

Ниже представлена формула использована в условном форматировании изображена ниже на рисунке:

Данная формула сравнивает текущий порядковый номер платежа в столбце D с количеством лет указанным в ячейке $B$5 умноженным на 12. Если же номер платежа больше, формула возвращает значение ИСТИНА и применяется белый цвет шрифта. Если же вместо этого номер платежа меньше от общего количества платежей или равен ему, не применяется ни каких изменений в форматировании значений для этой группы ячеек.

Автор - enzo
Дата добавления - 13.04.2016 в 07:34

, почему тогда у Тойоты оплата в следующем месяце?
UPD. В общем, как в образце , почему тогда у Тойоты оплата в следующем месяце?
UPD. В общем, как в образце Pelena

"Черт возьми, Холмс! Но как. "
ЯД 41001765434816

, почему тогда у Тойоты оплата в следующем месяце?
UPD. В общем, как в образце Автор - Pelena
Дата добавления - 13.04.2016 в 08:56
Большое сорри. Сам запутался. начисления все идут от конца месяца. т.е (-30;0) диапазон где начисления и оплата совпадают.
Большое сорри. Сам запутался. начисления все идут от конца месяца. т.е (-30;0) диапазон где начисления и оплата совпадают. enzo
Большое сорри. Сам запутался. начисления все идут от конца месяца. т.е (-30;0) диапазон где начисления и оплата совпадают. Автор - enzo
Дата добавления - 13.04.2016 в 09:13
Если нет, то заполните ещё пару строк вручную Автор - Pelena
Дата добавления - 13.04.2016 в 09:15 почему тогда у Тойоты оплата в следующем месяце?
UPD. В общем, как в образце

Формула глаза радует)) Спасибо работает. вникуаю в нее) Пока тему не закрываю , очень интересно посмотреть и на другие варианты (если будут)
[moder]Будут. Если Вы еще немного объясните. я, например, не такой догадливый сегодня, как Лена.

почему тогда у Тойоты оплата в следующем месяце?
UPD. В общем, как в образце

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

Есть таблица начислений.. Возьмем к примеру позицию Тойота. По нему в феврале начислили 300 , отсрочка по Тойоте 30 дней, т.е 300 мы будем платить в марте. Начисления считать с конца месяца т.е если отсрочка попадает в диапазон от (-30;0 дней) то начисление и оплата совпадают. Все что больше или меньше сдвигается. Шаг в месяце - 30 дней.

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