Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Сложная формула для расчета взносов в Фонды
 
#1
16.10.2017 17:23:33

Здравствуйте!

Есть одна задачка, может кто-то подскажет решение.

В конце года нужно выплатить бонус сотруднику. Т.к. компания белая, все вознаграждения выплачиваются официально, на них начисляются взносы в фонды. Руководство компании не говорит, сколько заплатить сотруднику. Оно говорит иначе: мы готовы потратить на вознаграждение сотруднику 500к (к примеру). Соответственно нужно рассчитать, сколько из этих денег само вознаграждение, а сколько взносы в ПФР, ФСС и т.д. Казалось бы не столь сложно, но:

1.       Доход сотрудника до достижения суммы в 876000 руб. накопленным итогом с начала года облагается взносом в ПФР 22%, все, что сверх 876000 руб. облагается по 10% ставке.

2.       Доход сотрудника до достижения суммы 755000 руб. накопленным итогом с начала года облагается взносом 2,9%, доход сверх этой суммы не облагается вовсе.

3.       Взносы в мед. Страх (5,1%) и по несчастным случаям (0,2%) остаются неизменными.

4.       В 2018 году предельные суммы для начисления взносов опять изменятся.

Проблема возникает при попытке посчитать, сколько взносов нужно заплатить из общей суммы, которую компания готова потратить на сотрудника. Пытался решить это посредством формул ЕСЛИ, но запутался окончательно, формула идиотская получается. Плюс выдает циклические ссылки и считает некорректно. С макросами, к сожалению, не дружу. Собственно, вопрос, имеет ли эта задача решение силами excel, макросом или формулами? Буду благодарен за решение или направление для поиска, всю голову сломал уже. В поиске пытался найти, но, видимо, такой проблемы еще не было))

Изменено: vlad325 - 18.10.2017 12:33:28
 
 
 
#2
16.10.2017 17:51:31
Вот пример формулы.
Изменено: TheBestOfTheBest - 16.10.2017 17:56:59 (замена файла, причесал остальные столбцы)
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#3
16.10.2017 18:02:30
Доброе время суток
Цитата
TheBestOfTheBest написал:
Вот пример формулы.
А не маловато ли удержали, коллега?
 
 
 
#4
16.10.2017 18:58:56
Андрей VG, поправьте меня, если я ошибаюсь, но по-моему так считать не правильно. Вы посчитали отчисления от 500 т., тогда на руки получилось 309715, с НДФЛ 355995. Но отчисления должны считаться от 355995, а это в сумме уже не даст 500 т.
Тут наверное надо использовать подбор параметра? Тогда получится, что вознаграждение 384458 (до вычета НДФЛ) и отчисления 115542
Изменено: webley - 16.10.2017 19:27:58 (небольшая ошибка в файле. Подбор параметра: Установить в Е18 значение 500000 меняя D18)
 
 
 
#5
16.10.2017 19:03:49
Извините, что встреваю, но отчисления на социалку, насколько я помню, считаются ДО вычета НДФЛ. Типа начислили - 100, со 100 сверху считаем социалку, потом вычитаем 13% подоходного и на руки - 87.
Кому решение нужно - тот пример и рисует.
 
 
 
#6
16.10.2017 19:04:57
Нет, даже многовато, если полагать, что 500 000 это уже со взносами. Хотя... Согласен, надо еще допиливать, но не хочется ...
Изменено: TheBestOfTheBest - 16.10.2017 19:07:59
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#7
16.10.2017 19:20:43
TheBestOfTheBest, не в плане критики, но у вас накопленный доход в формуле не участвует
 
 
 
#8
16.10.2017 19:29:15
Цитата
Пытливый написал:
отчисления на социалку, насколько я помню, считаются ДО вычета НДФЛ
а никто и не спорит :)
 
 
 
#9
16.10.2017 19:29:26
Да, но если его добавить, то что останется для ТС?
Попробовал написать UDF, не пойму почему не сходится с #3 ?
Изменено: TheBestOfTheBest - 17.10.2017 10:21:08
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#10
16.10.2017 21:36:21
Коллеги, я, поскольку не бухгалтер, рассуждал так. С суммы премии выплачиваются все налоги, по условию.
Тогда для ПФР 390 + 500 - 876 = 14, та сумма которая облагается по 10% = 1,4, соответственно по 22% будет
(500 - 14) * 0,22 = 106,92, и всего 108,32
Для ФСС 500 - (390 + 500 - 755) = 365, так как свыше 755 не облагается, получим 10,585
По мед. страх без изменений, то есть 25,5
По НС аналогично 1
Всего, в представленном файле ошибка в сумме, уплатить налогов 145,405
Начислений в расчётку 354,595
И с начислений подоходный, итог 308,498
Собственно, задача по ПФР сводится к нахождению длины пересечения отрезков
0; 876 и 390; 890 для 22% налога, и положительного остатка от общей суммы 890 и разницей с верхней планкой 876
Для ФСС - аналогично
 
 
 
#11
16.10.2017 23:08:08
Андрей VG, там пограничные суммы 876 и 755 надо сравнивать не с 390 + 500, а с 390 + некий X (который является составной частью 500, и который является суммой причитающейся сотруднику до вычета НДФЛ; помимо этого X в сумму 500 входят отчисления в фонды). Отсюда и циклическая ссылка, т.к этот X мы собственно говоря и  ищем.

У меня подбором параметра получилось  382056,99 - сотруднику до вычета НДФЛ,  117943,01 -  в фонды.
 
 
 
#12
17.10.2017 01:36:44
Задача показалась интересной.
Насколько я понимаю, т.н. з/п гросс - это з/п до выплат НДФЛ, но после прочих комплементарных выплат.
Итак:
зп гросс                 349 162
пфр                       76 816
фсс                         10 126
ффомс                     17 807
нс                                698
ндфл                       45 391

Итого выплаты,       500 000

Я не совсем понял тему с премиями. Формулы в приложенном файле достаточно гибкие: если поменяете значение зп гросс, то все остальное будет пересчитываться благодаря моим любимым формулам макс/мин. Впрочем, обещать ничего не могу, делал ночью (возможно есть ошибки).
Также, не забываем, что при изменении зп гросс для подгонки итоговых выплат нужно использовать goal seek (впрочем, вы это уже делали).
С уважением,
Федор/Все_просто
 
 
 
#13
17.10.2017 07:48:47
Мне кажется, что Equio, очень четко сформулировал задачу, хотя итог у меня с ним не сходится - можно Ваш файл посмотреть?
Все_просто, в Вашем результате НДФЛ фигурирует два раза (я правильно понимаю, что доход до налогов включает НДФЛ?) и при расчете отчислений не участвует накопленный доход (а он то как раз и приводит к использованию предельных сумм)
 
 
 
#14
17.10.2017 08:59:02
webley, возможно я не так понял. Особой разницы нет: накопленный доход или нет. Если вы строите подробную финмодель с начислениями в течение года - это одно, а если вам нужно просто понять, как распределить фонд на сотрудника - это другое. Если у вас есть максимум 500к, вы можете потратить эти средства хоть за 12 месяцев, хоть за 1 месяц и расчеты никак меняться не будут.
С уважением,
Федор/Все_просто
 
 
 
#15
17.10.2017 10:14:14
Спасибо огромное всем за участие! Похоже, задача эта решения не имеет вовсе. По крайней мере формулами. Уважаемый Equio совершенно прав. Сумма, которую готовы потратить на вознаграждение сотрудника, состоит, условно, из двух частей - непосредственно вознаграждения (выплата + НДФЛ) и страховые взносы, начисленные на это вознаграждение. И обе эти суммы плавающие, т.к. зависят от накопленного итога, потому что именно от него зависит размер страховых отчислений. Как раз и получается циклическая ссылка. Условно, если накопленный доход за год 600к, то из 500к, которые мы готовы потратить на сотрудника, 155к будут облагаться взносами в полном размере (22+2,9+5,1+0,2), 121к (больше 755, но меньше 876) облагаться без учета ФСС (22+5,1+0,2) и остальное (10+5,1+0,2). Проблема как раз в том, чтобы сделать форму расчета, так как сотрудников больше сотни. Чтобы можно было ввести 4 значения (лимиты выплат по ПФР и ФСС, сумму накопленного дохода с начала года и сумму, которую компания хочет потратить на вознаграждение) и получить на выходе сумму к выплате сотруднику и сумму страховых взносов с этого начисления. На данном этапе, видимо, придется считать все вручную по каждому.
 
 
 
#16
17.10.2017 10:21:07
Цитата
vlad325 написал:
Похоже, задача эта решения не имеет вовсе. По крайней мере формулами
а чем вам Поиск решения Подбор параметра не подходит?
Изменено: webley - 17.10.2017 10:24:57
 
 
 
#17
17.10.2017 10:36:23
Цитата
webley написал:
а чем вам Поиск решения Подбор параметра не подходит?
Спасибо огромное! Вроде бы считает то, что нужно. Потестирую еще и отпишусь. Но в любом случае, спасибо Вам за помощь!
 
 
 
#18
17.10.2017 10:46:37
можно немного макросов для удобства добавать
 
 
 
#19
17.10.2017 10:51:40
Доделал UDF, первый параметр сумма дохода от которой считаем взносы, второй параметр 0-сумма указана без взносов, 1 -сумма указана со взносами.
Неизлечимых болезней нет, есть неизлечимые люди.
 
 
 
#20
17.10.2017 11:06:39
У Вас почему-то не учитывает накопленный итог. При его изменении начисленные взносы не изменяются. Спасибо!
 
 
 
#21
17.10.2017 11:07:46
Цитата
webley написал:
можно немного макросов для удобства добавать
Спасибо Вам, добрый человек)
 
 
 
#22
17.10.2017 17:02:16
webley, Лист1(2), установить в B12 значение 0, меняя B11.
 
 
 
#23
17.10.2017 17:26:48
Equio, у вас медстраховка 5,9%, а должно быть 5,1%. Если поправить - тогда сходится
 
 
 
#24
17.10.2017 17:32:37
Ок. Я взял из первого поста топикстартера.
 
 
 
#25
18.10.2017 08:57:32
Цитата
Equio написал:
некий X
Понял, спасибо. Фактически это решение уравнения (только относительно ФСС, для ПФР также, чуть по сложнее)
k1 * X + Кфсс * IF(Предшествующая > ФСС; 0; MIN(ФСС; X + Предшествующая) - Предшествующая ) = 500 000
Отсюда и потребность в итерациях. Честно говоря, не вижу, как решить без них :(
 
 
 
#26
20.10.2017 17:35:06
Разрешите высказаться бухгалтеру.
Общая сумма начисленной сотруднику заработной платы и соответствующих страховых взносов (y) является кусочно-линейной монотонно возрастающей функцией от начисленной заработной платы (x) (если, конечно, законодатели не получали двоек по математике в начальной школе).
Соответственно, обратная функция x(y) также является монотонно возрастающей кусочно-линейной функцией и может вычисляться как непосредственно, так и итерационно. А может и формулой, ключевым моментом которой будет разбиение входящего параметра (y) на интервалы, для каждого из которых будет вычисляться линейная функция.
Упоминание про НДФЛ к делу не относится, так как НДФЛ является не начислением, а удержанием.
Владимир
 
 
 
#27
21.10.2017 11:48:50
Поскольку вопрос имеет практический характер (исходные данные соответствуют законодательству), приложил пример. Входные параметры выделены желтым цветом.
Владимир
 
 
 
#28
14.11.2017 20:17:51
 В связи с появлением  данных о предельной базе страховых взносов в 2020 году откорректировал "калькулятор для работодателя". Входные параметры выделены желтым цветом. Год задавать в ячейке A2.
Изменено: sokol92 - 01.12.2020 14:54:44
Владимир
 
 
 
#29
25.09.2018 15:38:13
Добавил 2019 год.
Владимир
 
 
 
#30
25.09.2018 15:39:32
Сразу бы до 25-го, чтобы не обновлять каждый год :)
 
 
 
Страницы: 1 2 След.
Наверх