Очень часто в своей работе приходится следить за исполнением контрактов, а в случае нарушения сроков Подрядчиком рассчитывать размер пени. Кто-то рассчитывает размер пени вручную, кто-то пользуется сторонними онлайн сервисами, мне же проще видеть и держать всегда под рукой историю начисленных пеней и штрафов.
Поэтому в своей работе я использую шаблон созданный в Excel.
Как можно было заметить в этом году ЦБ РФ очень часто меняется ключевая ставка и каждый раз отслеживать ее изменение заходя на официальный сайт трата времени.
В этой статье мы рассмотрим как создать шаблон расчета пени за нарушение сроков выполнения контракта и создадим обновляемый запрос на сайт ЦБ РФ по текущей ключевой ставке или ставке, действовавшей на определенную дату.
Итак, для начала нам нужно определиться какие исходные данные для расчета нам нужны.
В качестве примера возьмем произвольные данные.
Как правило в контрактах прописывают стандартные условия по пеням "Пеня начисляется за каждый день просрочки выполнения Подрядчиком обязательства, предусмотренного Контрактом, начиная со дня, следующего после дня истечения установленного Контрактом срока исполнения обязательства и устанавливается в контракте в размере одной трехсотой действующей на дату уплаты пени ключевой ставки Центрального банка Российской Федерации от цены Контракта"
Исходя из этого описания и будем производить наш расчет
Для начала найдем количество дней просрочки, использовав формулу =РАЗНДАТ() (как она работает мы разбирали ранее)
Если Вы обратили внимание, то после формулы добавляем еще один день. Это необходимо сделать чтобы в наш диапазон вошел день начала периода просрочки и расчет наш был правильный.
Затем, пока своими силами, на сайте ЦБ РФ находим ключевую ставку , действовавшую на последний день просрочки. В нашем случае, это 5,50%.
И в столбце размер пени записываем формулу нашего расчета, которая будет выглядеть так:
"=A15*D15*E15/300"
, что соответствует "2 000 000,00 × 29 × 1/300 × 5,50% =10 633,33"
Прибавляем дополнительные штрафные санкции, если есть, и получаем итоговый результат.
С первой и основной частью нашего шаблона мы разобрались. Теперь давайте упростим себе задачу по автоматическому запросу ключевой ставки на сайт ЦБ РФ.
В книге создадим новый лист и на нем сформируем этот самый запрос на сайт ЦБ РФ. Для этого перейдем на вкладке в раздел Данные, в пункте Создать запрос в раскрывающемся списке выберем Из других источников > Из Интернета
Появится окно, в котором нам необходимо вставить ссылку на сайт ЦБ РФ где размещаются данные по ключевой ставке. Я предварительно скопировал его из адресной строки браузера https://www.cbr.ru/hd_base/KeyRate/?UniDbQuery.Posted=True&UniDbQuery.From=01.01.2021&UniDbQuery.To=18.06.2021
Если обратить внимание то внутри ссылки есть две даты "01.01.2021" и "18.06.2021". Вот их то мы и поправим. Первая дата это начальная дата, которая в нашем запросе отвечает с какого момента нам начинать искать размер ключевой ставки. Соответственно вторая дата, это конечная дата нашего запроса.
Изменим в ссылке дату "18.06.2021" на "31.12.2021" или любую, которую Вам удобно, впоследствии ее можно будет изменить.
Нажимаем ОК.
Появится окно, в котором ищем и выбираем необходимые нам параметры отображения. В нашем случае они скрываются в "Table 2"
Жмем загрузить и получаем результат с 01.01.2021 по текущий день (у меня это 07.07.2021) с отображением ключевой ставки по датам.
Далее для обновления списка Вам просто нужно нажать по таблице правой кнопкой мыши и нажать Обновить
И с такими параметрами таблица будет работать до 31.12.2021.
Для того чтобы изменить период запроса нужно всего лишь изменить начальную и конечную дату нашего запроса. А сделать это можно также во вкладке Данные > Создать запрос > Параметры источника данных ...
В появившемся окне нажимаем кнопку Изменить источник данных...
В адресной строке меняем даты и жмем ОК
Вот так без лишних действий мы можем сделать расчет пени за нарушение сроков исполнения контракта и автоматически сформировать запрос на ключевую ставку с официального сайта ЦБ РФ.
И самое главное расчет у Вас сохранится и Вы всегда сможете освежить свою память открыв его и посмотреть когда и с какими параметрами выставляли пеню.