Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Рассчитать суммы в зависимости от ставки НДС
 
#1
26.05.2026 13:48:31
Добрый день! В очередной раз хочу попросить помощи у специалистов форума.

Задача состоит в следующем: рассчитать суммы платежей без НДС так, чтобы они совпадали с итоговой суммой поставки без НДС, с учётом разных ставок НДС и в зависимости от сальдо на начало года.

Условия расчёта:

Условие 1. Если имеется сальдо на начало года, то сумма сальдо без НДС рассчитывается по ставке 20%

Условие 2. Если сальдо на начало года отрицательное:

  • сумма платежа, равная сальдо на начало, рассчитывается по ставке 20%;

  • все платежи за новую поставку (сверх суммы сальдо) рассчитываются по ставке 22%.

Условие 3. Если сальдо на начало года положительное:

  • сумма платежа, равного разнице между сальдо на начало и суммой поставки, рассчитывается по ставке 22%, но без учёта суммы доплаты 2% в текущем году (22%-20%);

  • остальные платежи рассчитываются по ставке 22%.

Файл с примером приложила, выделила в нём цветом какие цифры должны получаться в итоге по разным вариантам. Надеюсь, что доступно объяснила. Буду очень благодарна, если кто-то сможет помочь с формулами для автоматического решения данной задачи.
Изменено: Lene - 26.05.2026 13:49:58
 
 
 
#2
26.05.2026 17:26:22
Цитата
написал:
выделила в нём цветом какие цифры должны получаться
А они точно правильно посчитаны?

ЗЫ В файле 92 тысячи имён. Не многовато? :)
 
 
 
#3
26.05.2026 19:16:58
Цитата
написал:
А они точно правильно посчитаны?
Да, точно, разницы между суммой поставки и суммой платежа без НДС быть не должно
 
 
 
#4
26.05.2026 19:20:27
Цитата
написал:
ЗЫ В файле 92 тысячи имён. Не многовато?
Исходный файл сам по себе очень большой, в нём много вкладок, я оставила только нужный лист
 
 
 
#5
27.05.2026 10:16:13
Цитата
написал:
А они точно правильно посчитаны?
Да, точно,
В таком случае, для марта и апреля логика отличается.
 
 
 
#6
27.05.2026 12:40:59
Цитата
написал:
В таком случае, для марта и апреля логика отличается.
Спасибо Вам большое, что откликнулись. Логика действительно отличается, потому что здесь разные варианты оплаты:

в 1-м случае (март) - была предоплата на начало года, в феврале поставка товара и в марте окончательный расчёт;
во 2-м случае (апрель) - была предоплата на начало года 50%, в январе авансировали ещё 20%, в марте поставка товара и в апреле окончательный расчёт. При этом сальдо на начало года по ставке НДС 20%, второй платеж по ставке НДС 22%, третий платеж по ставке НДС 22%, но включает доплату 2% НДС с суммы сальдо на начало года.

В итоге, после всех расчётов с поставщиком, общая сумма платежей без НДС должна быть равна общей сумме поставки без НДС
 
 
 
#7
27.05.2026 12:41:27
Вариант для случая, когда платёж не равен сальдо.
 
 
 
#8
27.05.2026 13:34:47
Цитата
написал:
В итоге, после всех расчётов с поставщиком, общая сумма платежей без НДС должна быть равна общей сумме поставки без НДС
При разных ставках так не будет.
Поставка 100, платёж 100.
Вычитаем разные НДС.
Поставка 100-22, платёж 100-20.
Разница 2.
 
 
 
#9
27.05.2026 13:35:47
Цитата
написал:
Вариант для случая, когда платёж не равен сальдо.
Спасибо Вам, а не подскажите ещё как можно сделать, чтобы сальдо на конец не осталось? Например, сумма платежа в апреле должна быть 2 391, а не 2 500, чтобы разницы в 109 на конец не было
 
 
 
#10
27.05.2026 14:01:09
Lene, потестируйте вариант в файле

пс: из-за округления может набежать погрешность
 
 
 
#11
27.05.2026 14:07:56
Подкрутил сальдо на конец, что бы это ни значило :)))
 
 
 
#12
27.05.2026 14:18:17
Цитата
написал:
потестируйте вариант в файле
Спасибо Вам огромное, уже не первый раз Вы мне помогаете! Пока всё считается, как надо, вроде постаралась все возможные варианты в файле предусмотреть
 
 
 
#13
27.05.2026 14:24:31
Цитата
написал: Подкрутил сальдо на конец, что бы это ни значило ))
Спасибо Вам:)
 
 
 
#14
28.05.2026 12:33:22
Lene, пожалуста
если всё устраивает
немного оптимизировано:
=ОКРУГЛ(G4/(1+$B13)-(МЕДИАНА(-G4;СУММЕСЛИ($C$3:F$3;$D$3;$C4:F4)+$C4;)+МЕДИАНА(;$C4-СУММЕСЛИ($C$3:D$3;$E$3;$C4:D4);--E4))*(1/(1+$A13)-1/(1+$B13));2)
 
 
 
#15
28.05.2026 14:49:00
ПавелW, спасибо Вам большое. Сейчас как раз вносила Ваши формулы в свои расчёты и выявился ещё такой нюанс. Если сумма поставки меньше, чем сальдо на начало периода + сумма платежа, т.е. остаётся ещё предоплата на конец периода, то в таком случае в следующем месяце получается отрицательное значение, которого быть не должно. Пример в файле выделила красным шрифтом
 
 
 
#16
28.05.2026 15:28:30
Цитата
... как можно сделать, чтобы сальдо на конец не осталось?
... Если сумма поставки меньше, чем сальдо на начало периода + сумма платежа, т.е. остаётся ещё предоплата на конец периода, то в таком случае в следующем месяце получается отрицательное значение, которого быть не должно
Lene, у вас похоже противоречие условий, т.е.
если поставить значение в G4  2000 (или в H4  22000) что должно быть в "Суммы без НДС"?

Вообще на мой взгляд напрашивается коррекция в 2% не платежей, а поставок (но вам видней))
 
 
 
#17
28.05.2026 15:50:36
Цитата
написал:
если поставить значение в G4  2000 (или в H4  22000) что должно быть в "Суммы без НДС"?
Если поставить платёж 2000, то сумма без НДС должна быть 1393, т.е. сумма доплаты в 2% здесь не участвует, т.к. это "чистый" НДС

Цитата
написал:
Вообще на мой взгляд напрашивается коррекция в 2% не платежей, а поставок (но вам видней))
По законодательству сумма поставки рассчитывается только по ставке НДС текущего года 22%, а т.к. сумма платежей должна равняться сумме поставки, то возникает необходимость доплаты 2% НДС на ранее произведённую предоплату
Изменено: Lene - 28.05.2026 15:51:25
 
 
 
#18
28.05.2026 16:09:32
Цитата
должна быть 1393
Lene, в каком месяце? в изначальном примере вы корректировали платежи в следующим за поставкой месяце
Цитата
возникает необходимость доплаты 2% НДС на ранее произведённую предоплату
тогда почему бы не вносить коррекцию в платежи сразу по мере их появления?
 
 
 
#19
28.05.2026 16:18:41
Цитата
написал:
в каком месяце? в изначальном примере вы корректировали платежи в следующим за поставкой месяце
Платёж может быть, как в следующем за поставкой м-це, так и в этом же, всё зависит от условий договора с поставщиком

Цитата
тогда почему бы не вносить коррекцию в платежи сразу по мере их появления?
Это опять же зависит от поставщиков, в основном они выставляют счёт-фактуры на доплату в последнем м-це
 
 
 
#20
28.05.2026 16:24:41
Собственно решение уже предложено, поэтому файл смотреть не стал, может там логика и правильная. Но, фраза про доплату в 2% зацепила
Цитата
написал:
необходимость доплаты 2% НДС на ранее произведённую предоплату
Там вроде 1,67% нужно к ранее уплаченной сумме?
СуммаПредОцена*1,2
Новая суммацена*1,22
Новая сумма= СуммаПредО*1,22/1,2
Доплата =СуммаПредО*(1,22/1,2-1)
Доплата =СуммаПредО/60
Доплата =СуммаПредО*1,67%
 
 
 
#21
28.05.2026 16:43:06
Lene, возможно вы не поняли
Цитата
сразу по мере их появления
На примере "Вариант 3": Сальдо на начало года 5000, поступил платеж в январе 2000
в D15 сейчас значение 1639,34. если уже здесь учесть эту коррекцию новое значение будет 1571,03
...и далее уже не заморачиваеся))
 
 
 
#22
28.05.2026 19:04:02
Цитата
написал:
если уже здесь учесть эту коррекцию новое значение будет 1571,03
Я поняла Вашу идею, это на самом деле было бы проще, но дело в том, что у нас уже есть реальная ситуация на примере одного поставщика, там получилось именно так: в ноябре прошлого года сделали предоплату по ставке НДС 20%, в январе этого года поставщик выставил счёт-фактуру на ещё один аванс уже по ставке 22%, а доплату 2% включил в окончательный платёж.
Я уже поняла, что формульно такую задачу не получится решить. Нужно, наверное, будет в таких случаях вручную корректировать суммы
 
 
 
#23
28.05.2026 19:49:35
ПавелW, а если вначале условие сделать, что при сумме платежа с НДС равной нулю должен быть ноль, а дальше уже по Вашим формулам считать, как думаете, должно по идее тогда всё работать?
 
 
 
#24
30.05.2026 17:26:07
Цитата
при сумме платежа с НДС равной нулю должен быть ноль, а дальше уже по Вашим формулам
Lene, ну, думаю если надо, вы с этим и сами справитесь ) . Что то вроде: =ЕСЛИ( условие ; 0 ; формула )
Цитата
зависит от поставщиков, в основном они выставляют счёт-фактуры на доплату
Нужно, наверное, будет в таких случаях вручную корректировать суммы
как вариант добавить какой-нибудь "опознавательный знак" что была коррекция платежа
см файл
 
 
 
#25
31.05.2026 13:10:27
Цитата
написал:
ну, думаю если надо, вы с этим и сами справитесь ) . Что то вроде: =ЕСЛИ( условие ; 0 ; формула )
ПавелW, да, я уже попробовала такой вариант, в итоге отрицательные значения обнулились, но корректировки самих сумм при этом всё равно нужно делать вручную в тех периодах, где месяц платежа совпадает с месяцем поставки

Цитата
написал:
как вариант добавить какой-нибудь "опознавательный знак" что была коррекция платежа
ПавелW, от души благодарю Вас за профессиональную помощь! Опробую на практике ещё этот вариант решения задачи
 
 
 
Страницы: 1
Читают тему
Наверх