Расчет последнего дня выплаты в зависимости от годовых / квартальных сроков регулярных счетов.

Для регулярных годовых и квартальных счетов я откладываю подходящие частичные суммы каждый месяц и использую Excel для проверки текущего баланса этих частичных сумм. Я принимаю срок первой накладной как основу для расчета. Затем я вычисляю последнюю дату выплаты, используя следующие правила:

• День выплаты всегда 25-е число.
• Чтобы определить месяц, я проверяю, была ли дата первой накладной 25 числа или позже. Если да, месяц берется из даты окончания. В противном случае, дата выплаты была в прошлом месяце.
• Год последней выплаты определяется в зависимости от сегодняшней даты. Если платеж уже был произведен в этом году, то год последней выплаты – текущий. В противном случае, платеж был произведен в прошлом году.
• Для квартальных счетов также нужно использовать сегодняшнюю дату для определения месяца.
• Одна частичная сумма – это общая сумма, деленная на период (12 или 3 месяца).

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

Решение

Годовые счета

Теперь я опишу свое текущее решение для годовых счетов. Предположим, что срок первой накладной находится в ячейке A2, а «сегодняшняя» дата – в ячейке J1.

С помощью этой формулы я сначала определяю дату выплаты в текущем году:

B2=DATE(YEAR($J$1),IF(DAY(A2)>=25,MONTH(A2),MONTH(A2)-1), "25")

Затем я вычисляю последнюю дату выплаты. Если счет еще не был оплачен в этом году, я вычитаю один год из выплаты в текущем году. Чтобы получить ровно один год (некоторые годы – високосные), я вычисляю разницу между датами сегодня и сегодня минус один год:

F2=IF(B2>$J$1,B2-DATEDIF(DATE(YEAR($J$1)-1,MONTH($J$1),DAY($J$1)),$J$1, “D”),B2)

Расчет количества частичных сумм выглядит следующим образом:

G2=DATEDIF(C2,$J$1, “M”)

И на этом мы заканчиваем с годовой частью.

Квартальные счета

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

C9=INT(MOD(MONTH($J$1)-MONTH(B9)-1,12)/3)*3

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

D9=IF($J$1>B9,
      -DATEDIF($J$1,DATE(YEAR($J$1),MONTH($J$1)+C9,DAY($J$1)),"D"),
      DATEDIF(DATE(YEAR($J$1)-1,MONTH($J$1)+C9,DAY($J$1)),$J$1,"D"))

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

Затем просто вычитаем D9 из этой даты выплаты в этом году и рассчитываем количество частей на этой основе:

E9=B9-D9
F9=DATEDIF(E9,$J$1, “M”)

Улучшения

В моем решении есть несколько недостатков, которые нужно улучшить:

1. Расчет не учитывает выходные дни и праздничные дни. Если 25-е число месяца попадает на выходной день или праздничный, последняя дата выплаты должна быть перенесена соответственно. Например, 25.08.2024 – это воскресенье, а годовой счет изначально должен был быть оплачен 25.08.23, так что дата выплаты в 2024 году – 23.08.

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

3. В целом, решение определенно можно упростить и оптимизировать.