При составлении бизнес-плана/бюджета проекта/компании необходимо рассчитать размер социальных взносов с фонда оплаты труда (далее – ФОТ).
Исчисление и уплата социальных взносов регулируется рядом нормативно-правовых документов, в том числе:
- Налоговый Кодекс РФ Часть 2 (Глава 11)
- ФЗ от 29.11.2010 N 326-ФЗ "Об обязательном медицинском страховании в Российской Федерации"
- ФЗ от 15.12.2001 N 167-ФЗ "Об обязательном пенсионном страховании в Российской Федерации"
- Федеральный закон от 29.12.2006 N 255-ФЗ "Об обязательном социальном страховании на случай временной нетрудоспособности и в связи с материнством"
- В части размеров взносов на 2020 год - Постановление Правительства РФ от 06.11.2019 N 1407 "О предельной величине базы для исчисления страховых взносов на обязательное социальное страхование на случай временной нетрудоспособности и в связи с материнством и на обязательное пенсионное страхование с 1 января 2020 г."
Поскольку этот пост направлен на демонстрацию способа расчета социальных взносов в Excel, я не буду заострять внимание на законодательных аспектах уплаты социальных взносов. По этой причине ограничимся тем, что социальные взносы представляют собой три вида платежей:
- На пенсионное страхование (ОПС)
- На медицинское страхование (ОМС)
- На социальное страхование (ОСС)
Взносы исчисляются и уплачиваются с большинства производимых в пользу сотрудников выплат (зарплаты, премии, отпускные и др.) и зависят в части ОПС и ОСС от суммы выплат, исчисляемой нарастающим итогом с начала года. Например, все выплаты до достижения суммы в 1,292 млн. по ОПС будут облагаться по ставке 22%, а все, что выше этой суммы – по ставке 10%.
Предельная база по каждому виду взносов ежегодно актуализируется в Постановлении Правительства РФ и на 2020 год (Постановление N 1407 от 06.11.2019) имеет следующие значения:
Для написания этого материала я искал изящное решение расчета социальных взносов в Excel с использованием встроенных функций, в частности ЕСЛИ() и пришел к выводу, что такой способ логично применять только в случае, когда мы считаем социальные взносы за весь период выплат сразу. При усложнении задачи до расчета взносов с каждой выплаты – количество вложенных формул увеличивается – нужно отработать проблему, когда очередная выплата превышает, с учетом накопления, предельную базу и фактически надо вычислять часть по базовой ставке, а часть по ставке после превышения предельной базы. В итоге, остановился на том, что наиболее простой способ решения – написание собственной функции при помощи VBA в Excel.
В целом, я пока не публиковал материалов по написанию макросов, хотя сам в работе достаточно часто их использую. Опять же, если не погружаться в детали, весь пакет программ MS Office (Excel, Word, Power Point…) имеет мощный инструмент, позволяющий расширить возможности программного пакета под потребности бизнеса/пользователя – встроенный язык программирования Visual Basic for Application (VBA).
Одна из возможностей этого инструмента – написание собственных пользовательских функций, которой мы и воспользуемся. Я покажу как использовать функцию внутри рабочей книги, при этом всегда можно перенести исходный код на уровень вашей рабочей машины, на которой установлен Excel, либо поделиться надстройкой с другими пользователями.
Итак, мы будем встраивать код в рабочую книгу. Для этого надо сохранить книгу в новом формате – «Книга Excelс поддержкой макросов (.xlsm)» (через «Файл-Сохранить как» и в зависимости от операционной системы, выбрать формат сохранения.
Далее необходимо вызвать редактор VBA, обычно это делается по нажатию горячих клавиш ALT + F11 (MAC: fn+alt+F11) либо через панель задач, нажав соответствующую кнопку во вкладке «Разработчик». Эта панель по - умолчанию скрыта, чтобы она появилась необходимо включить ее в настройках: «Файл-Параметры-Настроить ленту» и поставить «галочку» в правой части окна в области «Основные вкладки» на вкладе «Разработчик».
После сохранения книги и запуска редактора VBA необходимо создать новый модуль, куда мы поместим код нашей функции. Модуль можно создать через меню «Insert-Module», модуль появится в левой части окна в блоке «Project-VBA Project»:
После добавления модуля, необходимо по нему кликнуть и в правой части окна, появится окно редактирования кода. В него необходимо добавить следующий код:
Option Explicit
Function СОЦВЗНОС(начисление_т As Double, начисление_итог As Range, пр_база As Double, ставка_1, ставка_2)
Dim social_contr As Double 'итоговое начисление
Dim Total As Double
Total = WorksheetFunction.Sum(начисление_итог) 'общая сумма начислений за год сотруднику
If пр_база > Total Then
social_contr = начисление_т * ставка_1
ElseIf пр_база < Total And пр_база > Total - начисление_т Then
social_contr = (пр_база - (Total - начисление_т)) * ставка_1 + (Total - пр_база) * ставка_2
Else
social_contr = начисление_т * ставка_2
End If
СОЦВЗНОС = social_contr
End Function
В окне редактора VBA это должно выглядеть примерно так:
После копирования исходного кода в модуль, редактор VBA необходимо закрыть и сохранить результат. Если вы все сделали правильно, в рабочей книге должна появится функция СОЦВЗНОС():
Прежде чем воспользоваться собственной функцией, необходимо создать две таблицы. Первая со ставками социальных взносов и размером базы для начисления:
Предлагаю дать имена каждой ячейке, которая нам понадобиться для расчетов. Для ОПС имена будут следующие: ОПС_база, ОПС_Ставка1, ОПС_Ставка2. Правила работы с именами диапазонов в Excel уже есть на этом канале. Аналогично переименовываем ячейки по ОСС и ОМС. В случае изменения ставок или предельных баз, всегда можно будет внести корректировки.
Также, для понимания работы функции, нам необходимо создать таблицу с ФОТ условного сотрудника. Диапазон столбцов с января по декабрь. Общий размер ФОТ за год предлагаю сделать выше предельной базы, чтобы посмотреть как отрабатывает функция перехода на вторую ставку.
Вернемся к нашей функции СОЦВЗНОС. Ей, для расчета, необходимо передать следующие данные:
- начисление_т - начисление текущего месяца, на который выполняем расчет взносов
- начисление_итог - итоговое начисление ФОТ с начала года
- пр_база - размер предельной базы по данному взносу
- ставка_1 - размер ставки №1 в %-х по данному взносу
- ставка_2 - размер ставки №2 в %-х по данному взносу
Использовать самописную функциею также просто, как и встроенную. Чтобы упростить задачу, предлагаю воспользоваться мастером:
Для расчета взносов на ОПС за январь, необходимо передать нашей функции следующие данные:
Обратите внимание на параметр "начисление_итог", в функцию необходимо передать массив ячеек включая текущее начисление. Чтобы корректно копировать формулу в следующие ячейки, необходимо закрепить в массиве ссылку на первую ячейку, в нашем примере это $B$2
С полученной функцией можно работать также, как и со встроенными - копировать, растягивать. Можно применять сразу несколько в одной ячейке, например, чтобы рассчитать сразу все взносы: ОПС, ОСС, ОМС через "+".
