Контроль оплаты по договорам: остаток, долг и лимит в Excel
Когда договоров немного, оплату можно помнить. Когда их двадцать, а оплата идёт частями — аванс, этапы, окончательный расчёт — легко перепутать, сколько ещё можно заплатить и не превысили ли мы сумму договора. Рассмотрим, как организовать контроль оплаты по договорам в Excel: остаток, задолженность и лимит в одной таблице.
Оговорка: речь о внутреннем управленческом учёте. Бухгалтерский учёт и расчёты с контрагентами ведутся по установленным правилам и в специализированных системах; таблица помогает видеть картину, но не заменяет учётные документы. Если у договора есть особые условия (штрафы, индексация, валютные расчёты), учитывайте их отдельно.
Три показателя, которые нужно видеть
- Сумма договора (лимит). Максимум, на который стороны договорились.
- Оплачено. Сумма всех платежей по договору на текущую дату.
- Остаток. Лимит минус оплачено: сколько ещё можно оплатить без превышения.
Отдельный показатель — задолженность. Её можно понимать по-разному: как неоплаченные суммы по актам (мы приняли услуги, но не оплатили) или как переплата. Заранее решите, какой смысл вы вкладываете, и назовите столбец понятно: «Принято по актам», «Долг по актам».
Схема из двух листов
Практичнее всего разделить данные:
- Лист «Договоры»: по одной строке на договор. Номер, контрагент, сумма, срок, ответственный.
- Лист «Платежи»: по одной строке на платёж. Номер договора, дата, сумма, номер документа об оплате, комментарий.
Так платежи добавляются простым списком, а итоги по договору считаются формулами.
Пошаговая настройка
- Шаг 1. На листе «Платежи» создайте таблицу с колонками: номер договора, дата платежа, сумма, назначение.
- Шаг 2. На листе «Договоры» добавьте столбец «Оплачено» с формулой СУММЕСЛИ: суммировать значения в столбце «Сумма» листа «Платежи», где номер договора совпадает с номером в текущей строке. Например, =СУММЕСЛИ(Платежи!A:A;A2;Платежи!C:C).
- Шаг 3. Добавьте столбец «Остаток»: =D2-E2, где D — сумма договора, E — оплачено.
- Шаг 4. Добавьте столбец «Процент оплаты»: =E2/D2, формат — проценты.
- Шаг 5. Добавьте столбец «Превышение лимита»: если оплачено больше суммы договора, показываем разницу, иначе ноль: =ЕСЛИ(E2>D2;E2-D2;0).
- Шаг 6. Настройте условное форматирование: красным выделяйте строки с превышением, жёлтым — договоры, где остаток менее 10 процентов, чтобы вовремя планировать закрытие.
- Шаг 7. Для договоров с актами добавьте отдельный лист «Акты» и столбец «Принято по актам». Задолженность считается как «принято по актам» минус «оплачено», если разница положительна.
Мини-пример
Условные данные:
- Договор № 15: сумма 600 000, оплачено 450 000, остаток 150 000, процент оплаты 75. Всё в порядке.
- Договор № 22: сумма 200 000, оплачено 205 000, превышение 5 000. Красный: нужно выяснить, откуда расхождение (допсоглашение не внесено, ошибочный платёж?).
- Договор № 31: сумма 100 000, принято по актам 80 000, оплачено 50 000. Долг по актам — 30 000; остаток по лимиту — 50 000.
Хорошо видно: остаток и задолженность — разные числа и отвечают на разные вопросы. Остаток — «сколько ещё можем заплатить по договору», задолженность — «сколько мы уже должны за принятое».
Как учесть допсоглашения
Если сумма договора менялась, не переписывайте старое значение. Добавьте столбцы «Сумма по допсоглашениям» и «Итоговая сумма» и считайте лимит по итоговой. Так сохраняется история, а при проверке видно, откуда взялась новая цифра.
Как использовать таблицу в работе
Сама по себе таблица ничего не контролирует, если на неё никто не смотрит. Закрепите несколько простых правил.
- Перед каждым платежом ответственный проверяет остаток по договору. Платёж больше остатка — вопрос к руководителю или к экономисту.
- Раз в месяц фильтруйте таблицу по превышению и по малому остатку, выгружайте список в отдельный лист и обсуждайте его на планёрке.
- Когда остаток близок к нулю, а работы продолжаются, инициируйте допсоглашение заранее, а не после превышения.
- Платежи вносит один ответственный, а не все подряд: так меньше дублей.
Эта логика реализована в реестре договоров с платежами и остатком: остаток и подсветка считаются сами, вам остаётся только вносить платежи.
Что показывать в сводке
Раз в месяц полезно смотреть на три среза.
- Топ договоров по сумме остатка: где ещё много запланированных платежей.
- Договоры с превышением лимита или нулевым остатком при действующем сроке.
- Суммы по контрагентам: сколько оплачено каждому за период.
Такая сводка помогает планировать бюджет на следующий месяц и вовремя замечать договоры, по которым расчёты идут быстрее, чем исполнение.
Частые ошибки
- Платежи хранят в голове или в разных файлах.
- Номера договоров в платежах записаны по-разному («15», «№15», «15/2026»), и СУММЕСЛИ не находит совпадения. Используйте выпадающий список из реестра.
- Не учитывают допсоглашения: лимит не соответствует фактической сумме.
- Путают остаток по договору и остаток по акту.
- Не фиксируют возвраты и корректировки — оплата получается завышенной.
- Нет столбца «ответственный»: превышение видно, а исправлять некому.
- Используют формулы в одной ячейке на весь столбец без таблицы: при добавлении строк формулы не подтягиваются.
Как не собирать всё вручную
Конструкция с двумя листами и подсветкой уже реализована в проекте Реестр договоров в Excel: там есть платежи, остаток, подсветка истекающих договоров и сводка по контрагентам. Структуру легко подстроить: добавить колонки под ваши условия.
Если часть договоров связана с командировками и авансовыми отчётами, посмотрите учёт командировок, а для общей картины по срокам — бесплатный светофор сроков. Сводные материалы собраны в разделе сводных таблиц.