Контроль оплаты по договорам: остаток, долг и лимит в 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: там есть платежи, остаток, подсветка истекающих договоров и сводка по контрагентам. Структуру легко подстроить: добавить колонки под ваши условия.

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