График платежей в Excel - шаблон
Как составить график и контролировать возврат денег
График платежей в Excel помогает видеть, когда клиент должен внести деньги, сколько уже поступило и какой остаток еще предстоит оплатить. Для такой работы полезно хранить согласованный план отдельно от фактических поступлений: один взнос покупатель может закрыть несколькими переводами.
Мы подготовили шаблон графика платежей в Excel для одного договора. В нем можно указать сроки и суммы взносов, заносить оплаты и получать остаток задолженности на выбранную дату. Формулы также показывают недоплату и просроченную часть платежей.
В файле уже заполнен учебный пример. Перед использованием замените его своими данными. Шаблон рассчитан на учет согласованных сумм без автоматического начисления процентов, пеней и комиссий.
Что входит в шаблон
Книга содержит два листа. На листе «График» находятся условия договора, плановые взносы и сводные результаты. На листе «Оплаты» записывается каждое фактическое поступление с датой и указанием взноса, к которому оно относится.
В верхней части графика видно:
- сколько денег поступило в счет платежей по графику;
- какой фактический остаток долга сохраняется;
- какая сумма просрочена на дату расчета;
- совпадает ли сумма плановых взносов с распределяемым долгом;
- есть ли оплаты, которые не привязаны к плановым взносам.
Подготовлено 36 строк плановых платежей и 200 строк записей об оплатах. Для одного договора с ежемесячными или нерегулярными взносами этого может быть достаточно. При выходе за эти пределы потребуется расширить диапазоны формул.
Для каких платежей подходит таблица
Шаблон можно использовать для покупки в рассрочку, согласованной оплаты услуг частями или возврата фиксированной суммы долга. Даты и размеры взносов вводятся вручную, поэтому платежи могут различаться между собой.
Если нужно сначала согласовать условия с клиентом, посмотрите, как оформить график платежей по договору рассрочки. Когда речь идет о новом порядке возврата уже существующего долга, пригодится материал «График погашения задолженности».
Файл предназначен для оперативного учета. В нем нет расчета процентов по ставке, учета возвратов товара, списаний и автоматического обмена с банком. Для таких операций потребуются дополнительные расчеты и другой порядок ведения данных.
Как заполнить график платежей в Excel
Укажите исходные данные
На листе «График» заполните желтые ячейки: клиента, договор, сумму договора и сумму, уже оплаченную до начала этого графика. Если ранее оплат не было, введите 0. Остаток, который нужно распределить по будущим взносам, рассчитывается автоматически.
В ячейке C9 установите дату среза. В расчет попадут оплаты, совершенные не позднее этой даты. В учебном примере указано 3 октября 2026 года. Для проверки текущего состояния обновляйте дату вручную; для сверки за прошлый период можно указать нужную прошедшую дату.
Заполните плановые взносы
В строках 15–50 листа «График» введите сроки в столбце B и суммы в столбце C. Номера платежей уже проставлены. Расчетные столбцы справа менять не нужно.
В поле «Не распределено в плане» должен получиться ноль. Положительное значение означает, что часть исходного долга еще не включена в график. Отрицательное — что сумма взносов превышает исходный остаток. При делении суммы с копейками скорректируйте последний платеж, чтобы итог точно сошелся.
Записывайте фактические оплаты
На листе «Оплаты» внесите дату, номер планового взноса и фактически полученную сумму. При необходимости укажите номер платежного документа и комментарий. По одному взносу разрешено несколько записей: формулы сложат их.
Сумму, уже указанную в поле «Оплачено ранее», повторно в журнал не добавляйте. Иначе она уменьшит задолженность второй раз.
Пример заполнения графика
Цена покупки — 120 000 ₽, ранее оплачено 20 000 ₽. Оставшиеся 100 000 ₽ распределены на четыре взноса по 25 000 ₽. В счет первого взноса клиент перевел 10 000 ₽ 20 сентября и еще 5 000 ₽ 30 сентября. На 3 октября расчет выглядит так:
| Дата платежа | План, ₽ | Зачтено, ₽ | Не оплачено, ₽ | Просрочено, ₽ |
|---|---|---|---|---|
| 30.09.2026 | 25 000 | 15 000 | 10 000 | 10 000 |
| 30.10.2026 | 25 000 | 0 | 25 000 | 0 |
| 30.11.2026 | 25 000 | 0 | 25 000 | 0 |
| 30.12.2026 | 25 000 | 0 | 25 000 | 0 |
| Итого | 100 000 | 15 000 | 85 000 | 10 000 |
Общий остаток составляет 85 000 ₽. Из него просрочено только 10 000 ₽, а срок оплаты остальных 75 000 ₽ еще не наступил. Добавлять сумму просрочки к остатку повторно не нужно.
Как рассчитываются остаток и просрочка
В файле используются обычные формулы Excel. Например, исходный остаток в ячейке C10 рассчитывается как сумма договора за вычетом уже внесенной оплаты: C7 − C8. Фактический остаток в H6 равен исходному остатку за вычетом поступлений, учтенных на дату среза.
Для заполненной строки смысл расчета неоплаченной части взноса можно показать короткой формулой:
=МАКС(0;C15-E15)
Здесь C15 — плановая сумма первого взноса, E15 — сумма, зачтенная в его погашение. Если поступило больше плана, неоплаченная часть остается равной нулю, а превышение отражается статусом «Сверх плана».
Просроченная сумма появляется только после истечения срока оплаты. Для заполненной строки принцип расчета такой:
=ЕСЛИ(B15<$C$9;F15;0)
B15 содержит срок, C9 — дату среза, F15 — неоплаченную часть взноса. В сам последний день оплаты платеж еще не считается просроченным. В готовом файле к формулам добавлены проверки заполнения ячеек.
Для суммирования оплат по номеру взноса и дате применяется функция СУММЕСЛИМН. Поэтому два перевода по 10 000 ₽ и 5 000 ₽ учитываются как 15 000 ₽ по одному платежу, а более поздние поступления не попадают в расчет за предыдущую дату.
Как учитывать оплату нескольких взносов
Шаблон относит деньги к тому номеру взноса, который вы указали на листе «Оплаты». Он самостоятельно не определяет юридическую очередность погашения и не переносит излишек на следующие строки.
Например, клиент перечислил 35 000 ₽: 10 000 ₽ нужно зачесть в остаток первого взноса, а 25 000 ₽ — во второй. Внесите две строки с одной датой и одинаковым номером банковского документа, указав номера взносов 1 и 2. Сумма этих строк должна составить 35 000 ₽.
Если все 35 000 ₽ записать только на первый взнос, появится превышение плана, а второй останется неоплаченным. Проверьте назначение поступления и согласованные условия, после чего исправьте распределение.
Как учитывать выходные и праздники
В таблицу следует вводить применимый срок оплаты. По общему правилу статьи 193 ГК РФ окончание срока в нерабочий день переносится на следующий рабочий день. Применимость этого правила нужно учитывать при составлении конкретного графика.
В шаблоне нет производственного календаря и автоматического переноса дат. Количество дней просрочки считается как календарная разница между датой среза и указанным сроком, пока соответствующая недоплата не закрыта.
Что проверить перед использованием
Начните с заполненного примера, чтобы понять работу формул. Затем удалите учебные данные из желтых ячеек и внесите сведения по своему договору. Расчетные столбцы и номера взносов сохраните.
Проверьте основные ошибки, которые влияют на результат:
- Дата записана текстом. Вводите ее как дату Excel, например 30.10.2026. При неправильном типе данных может появиться сообщение об ошибке.
- Первоначальный взнос учтен дважды. Вычтите его через поле «Оплачено ранее» и не дублируйте в журнале.
- Оплата отнесена к другому номеру. Сверьте номер взноса и назначение перевода.
- Дата среза устарела. Более поздние оплаты не будут учтены, пока вы ее не измените.
- Записи внесены за пределами подготовленных строк. Они не попадут в итог, пока диапазоны формул не будут расширены.
Сохраняйте платежные документы и резервную копию файла. Если условия изменились, зафиксируйте договоренность с клиентом и сохраните прежнюю версию графика. Исправление ячеек само по себе не подтверждает согласование новых сроков.
Как подготовить график для договора
Для приложения к договору обычно нужны согласованные даты и суммы, сведения о сторонах и связь с основным документом. Рабочие статусы, журнал поступлений и расчет задолженности на текущую дату можно оставить в учетном файле.
Скопируйте нужные плановые данные в документ, проверьте общую сумму и оформите согласование с клиентом. Если хотите сохранить готовый расчет в Word без дальнейшего пересчета, используйте вставку значений или обычную таблицу.
Когда удобнее перейти к программе
Этот шаблон ведет один договор. Если клиентов много, появляются отдельные файлы, в которых нужно обновлять даты, искать просрочки и проверять историю оплат. При совместной работе также важно понимать, кто и когда внес запись.
В Дебиторке данные связаны с карточкой клиента и отдельного долга. В базе можно видеть задолженность, дату и сумму следующего платежа, а в графике — план, фактические оплаты и остатки. В карточке клиента предусмотрено автоматическое напоминание о просрочке.
Excel подходит для самостоятельного контроля нескольких обязательств. Когда нужен общий список клиентов и ежедневная работа с большим количеством графиков, познакомьтесь с программой для учета платежей клиентов и возможностями Дебиторки.