Как рассчитать пенсионный фонд в экселе

Опубликовано: 02.05.2024

Сегодня очень много говорится о накопительной пенсии. Правительство готовит к запуску Гарантированный пенсионный план (ГПП). Основное нововведение – это «добровольность» накопительной пенсии. Это значит, что каждый из нас будет сам решать, довольствоваться ли в преклонном возрасте исключительно социальной пенсией или дополнительно создавать пенсионные накопления.

Социальная пенсия уже сейчас слишком мала, чтобы быть гарантией безбедного существования в старости. Вряд ли в будущем ситуация будет лучше. Поэтому заботиться о достойной пенсии придется самостоятельно. Других вариантов нет.

После решения заниматься своими пенсионными накоплениями немедленно встает вопрос. А сколько нужно накопить, чтобы не почувствовать себя «у разбитого корыта»?

Пенсионный калькулятор в EXCEL

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

Размер необходимых пенсионных накоплений (к моменту выхода на пенсию)
Размер ежегодных отчислений, необходимых для формирования накоплений

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

После выхода на пенсию предполагается, что у пенсионера остается консервативный инвестиционный портфель. Значительная часть такого портфеля должна состоять из надежных долговых инструментов (фонды облигаций, облигации, депозиты и т.п.). Пенсионер каждый год изымает из портфеля заранее запланированную сумму (скорректированную на инфляцию).

Для правильного расчета потребуется:

Планируемый возраст выхода на пенсию
Текущий возраст
Планируемый размер ежегодной пенсии
Срок дожития
Доходность инвестиционного портфеля ДО пенсии
Доходность инвестиционного портфеля ПОСЛЕ пенсии
Средняя инфляция

Срок дожития

Отдельного комментария требует срок дожития. Необходимо указать, сколько лет вы планируете получать пенсию из своих накоплений. Что будет после того, как эти деньги закончатся? Ничего особенного … вы продолжите жить, довольствуясь только социальной пенсией и, возможно, опираясь на взаимопомощь внутри семьи. В любом случае можно указать срок достаточно длинный. Типичными сроками дожития являются 20 или 30 лет.

Пример расчета пенсионных накоплений

Предположим, будущий пенсионер собирается получать на пенсии 600 тыс. руб. ежегодно (50 тыс. в меся) за счет своих накоплений. Сегодня ему 35 лет. Выйти на пенсию он собирается в 65 лет, как и положено всей мужской части населения.

Для этого ему потребуется инвестировать ежегодно до выхода на пенсию 114 тыс. рублей.

Предположения, на которых основывается расчет:

Доходность инвестиционного портфеля до выхода на пенсию – 14%
Доходность портфеля после выхода на пенсию – 11%
Средняя инфляция – 8% (мы всегда стараемся брать несколько завышенные цифры)
Стартовые накопления – 100 тыс. руб. (в 35 лет)

В возрасте 45 лет для достижения аналогичных доходов на пенсии придется откладывать ежегодно уже 245 тыс. руб. В 55 лет – 674 тыс. в год.

Калькулятор подтверждает, что время – главный союзник инвестора. Начинайте инвестировать как можно раньше!

Досрочный выход на пенсию

Другое применение калькулятора - расчет необходимых инвестиций для досрочного выхода на пенсию.

Например, в том же случае (возраст - 35 лет) будущий пенсионер решил выйти на пенсию раньше - уже в 55 лет. Что для этого необходимо сделать?

Достаточно инвестировать ежегодно 328 тыс. руб. (примерно 27 тыс. руб. в месяц) и через 20 лет можно заниматься любимым делом, получая 60 тыс. в месяц от инвестиций собственных пенсионных накоплений.

При расчете досрочного выхода на пенсию не забудьте увеличить срок дожития - ведь пенсионных накоплений должно будет хватить на более длинный срок.

Ограничения калькулятора

Калькулятор дает только примерные значения. Точные значения назвать невозможно по разным причинам:

Неизвестна точная доходность инвестиционного портфеля
Неизвестен точный размер инфляции

Кроме того, важно понимать, что все результаты расчетов даны в сегодняшних цифрах (не скорректированы на размер инфляции). Это значит, что ежегодно необходимо индексировать размер пополнений портфеля на размер инфляции. Аналогичным образом дела обстоят со снятиями во время пенсии.

Расчеты в калькуляторе предполагают, что все пополнения портфеля осуществляются в начале каждого года единовременно. Если вы планируете пополнять портфель в середине года, в конце года или, например, ежемесячно, то сумма ежемесячных пополнений должна быть несколько выше указанной (разница зависит от ставки доходности портфеля).

UPDATE 17.02.2020

Опубликована новая версия калькулятора с расчетом Процента снятия средств на момент выхода на пенсию.

Калькулятор размера пенсионных накоплений в EXCEL
Файл: retirement_calculator_rev2.xlsx
Размер: 58185 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

При составлении бизнес-плана/бюджета проекта/компании необходимо рассчитать размер социальных взносов с фонда оплаты труда (далее – ФОТ).

Исчисление и уплата социальных взносов регулируется рядом нормативно-правовых документов, в том числе:

Налоговый Кодекс РФ Часть 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»:

После добавления модуля, необходимо по нему кликнуть и в правой части окна, появится окно редактирования кода. В него необходимо добавить следующий код:

Function СОЦВЗНОС(начисление_т As Double, начисление_итог As Range, пр_база As Double, ставка_1, ставка_2)

В окне редактора VBA это должно выглядеть примерно так:

После копирования исходного кода в модуль, редактор VBA необходимо закрыть и сохранить результат. Если вы все сделали правильно, в рабочей книге должна появится функция СОЦВЗНОС():

Прежде чем воспользоваться собственной функцией, необходимо создать две таблицы. Первая со ставками социальных взносов и размером базы для начисления:

Предлагаю дать имена каждой ячейке, которая нам понадобиться для расчетов. Для ОПС имена будут следующие: ОПС_база, ОПС_Ставка1, ОПС_Ставка2. Правила работы с именами диапазонов в Excel уже есть на этом канале. Аналогично переименовываем ячейки по ОСС и ОМС. В случае изменения ставок или предельных баз, всегда можно будет внести корректировки.

Также, для понимания работы функции, нам необходимо создать таблицу с ФОТ условного сотрудника. Диапазон столбцов с января по декабрь. Общий размер ФОТ за год предлагаю сделать выше предельной базы, чтобы посмотреть как отрабатывает функция перехода на вторую ставку.

Вернемся к нашей функции СОЦВЗНОС . Ей, для расчета, необходимо передать следующие данные:

начисление_т - начисление текущего месяца, на который выполняем расчет взносов
начисление_итог - итоговое начисление ФОТ с начала года
пр_база - размер предельной базы по данному взносу
ставка_1 - размер ставки №1 в %-х по данному взносу
ставка_2 - размер ставки №2 в %-х по данному взносу

Использовать самописную функциею также просто, как и встроенную. Чтобы упростить задачу, предлагаю воспользоваться мастером:

Для расчета взносов на ОПС за январь, необходимо передать нашей функции следующие данные:

С полученной функцией можно работать также, как и со встроенными - копировать, растягивать. Можно применять сразу несколько в одной ячейке, например, чтобы рассчитать сразу все взносы: ОПС, ОСС, ОМС через "+".

С 2015-го года расчет пенсий в Российской Федерации ведется по-новому. Теперь размер пенсионного обеспечения и право на него зависит от числа баллов. Рассмотрим подробнее.

Из чего состоит пенсия

Страховая пенсия (раньше ее называли трудовой) рассчитывается по формуле:

число балов * стоимость одного балла .

Стоимость ежегодно меняется и утверждается Постановлением Правительства. Право на пенсионное обеспечение имеют те граждане, которые заработали за свою трудовую жизнь не менее тридцати баллов. Общий размер пенсии включает страховую часть и фиксированную выплату (ранее – базовую часть). Размер фиксированной выплаты тоже утверждается на государственном уровне.

То есть рассчитывать нужно только баллы. А их количество зависит от зарплаты.

Конвертация пенсионных прав, приобретенных до 2002 года

Чтобы рассчитать пенсионный коэффициент до 2002 г., нужно найти размер страховой пенсии и разделить на 64,1 (стоимость балла на 1 января 2015). Для расчета нужны показатели:

стаж до 2002;
среднемесячный заработок (берутся 2000-2001 гг. или любые 60 мес. до 2002 г.);
стаж до 1991 г.

Первый показатель учитывается в виде стажевого коэффициента. Он не может превышать 0,75.

Мужчина начал трудовую деятельность с января 1976 года. Общий стаж – 26 лет. Стажевый коэффициент равен 0,55 + 0,01 * (26-25), или 0,56.
Для женщины при таких же условиях расчет выглядит так: 0,55 + 0,01 * (26-20), или 0,61.
Если трудовой стаж менее 20 лет (для женщин) или 25 лет (для мужчин), то стажевый коэффициент равняется 0,55.

Исчисление среднего заработка для пенсии производится через «отношение заработков». Это отношение среднемесячной зарплаты гражданина к среднемесячной зарплате в государстве за такой же временной промежуток.

Гражданин представил в ПФ справку о зарплате за 60 месяцев с 01.05.1986 по 30.04.1991.

Средний заработок при начислении пенсии рассчитывается по формуле:

Среднемесячная зарплата по стране – 230,1.

Отношение заработков: 1,2. Закон установил максимальный порог для данного коэффициента: 1,2. Поэтому при оценке пенсионных прав учитывается не 1,38, а 1,2.

Как определить размер пенсии от среднего заработка (отношения заработков):

Расчетная пенсия для граждан со стажевым коэффициентом свыше 0,55 вычисляется как произведение стажевого коэффициента, коэффициента среднемесячной зарплаты и 1671 р. Если полученная величина оказывается менее 660 р., то нужно отнять 450 р. Сумма 1671 рублей это СЗП - среднемесячная заработная плата в Росси за период 01.07.01-30.09.01 (постоянная величина);
Если стажевый коэффициент равен 0,55, то применяется формула вида: (0,55 * коэффициент среднемесячной зарплаты * 1671 – 450) * (стаж до 2002 / 25). Это для мужчин. Для женщин второй множитель – (стаж до 2002 / 20). Если расчетная величина окажется меньше 660 р., то для мужчин – 210 * (стаж до 2002 / 25), для женщин – 210 * (стаж до 2002 / 20).

Женщина вышла на пенсию в 2015 году. Общий стаж – 35 лет. До 2002 года – 22 г. Это больше двадцати лет. Значит, формула расчета стажевого коэффициента следующая:

Предположим, что отношение заработков – 1,2. Так как стажевый коэффициент больше 0,55, то формула для расчетной пенсии выглядит так:

Женщина устроилась на работу в 1980 г. Следовательно, у нее есть стаж до 1991 г. При учете валоризации нужно будет добавить к расчетной пенсии 10% и по 1% за каждый полный год трудовой деятельности до 1991 г.

11 лет она проработала с 1980 по 1991 г.

Пенсионный капитал ежегодно индексируется. На 31.12.2014 величина индекса – 5,6148. Найдем пенсионные права в рублевом виде за период до 2002 года с учетом надбавки и индексации:

Переведем в баллы. Для этого нужно разделить на 64,1.

Это часть пенсионных прав гражданина до 2002 года. При начислении пенсии количество баллов умножат на стоимость 1 балла, принятую на дату расчета.

Расчет ИПК за период с 2002 по 2015 гг.

Необходимо найти за данный период сумму перечисленных страховых взносов.
Рассчитывается страховая часть трудовой пенсии на 31.12.2014 г.: сумму взносов / 228 (период дожития).
Находим ИПК: страховую часть / 64,1.

По-другому: страховая часть трудовой пенсии – это пенсия, рассчитанная по «старым» правилам «минус» накопительную часть и фиксированную доплату (устанавливается государством).

Пенсионные баллы с 2015 года

Рассчитываются за каждый год трудового стажа. Для расчета берется зарплата, на которую начисляются страховые взносы. Отчисления в ФС – 22%. 16% идут на формирование страховой (10%) и накопительной (6%) части трудовой пенсии по старости. Предположим, что гражданин не хочет формировать накопительную часть отдельно.

Чтобы найти количество заработанных за 2015 г. баллов, нужно:

ИПК за разные периоды складываются и умножаются на стоимость балла, принятую на дату выхода на пенсию.

Это упрощенный расчет без учета повышающих коэффициентов, прерванного стажа и т.п.

Советы по Excel
Функции Excel
Формулы Excel
Советы по словам
Советы по Outlook

Как рассчитать дату выхода на пенсию по дате рождения в Excel?

Предположим, сотрудник выйдет на пенсию в возрасте 60 лет, как вы можете рассчитать дату выхода на пенсию с даты рождения в Excel?

Рассчитайте дату выхода на пенсию с даты рождения по формуле

Например, у меня есть список дат рождения людей, если их пенсионный возраст составляет 60 лет, следующая формула может помочь вам рассчитать дату их выхода на пенсию, пожалуйста, сделайте следующее:

1. Введите приведенную ниже формулу в пустую ячейку рядом с диапазоном данных, например, C2, затем перетащите дескриптор заполнения вниз к ячейкам, в которых вы хотите применить эту формулу, и пятизначные числа отобразятся, как показано на следующем снимке экрана:

2. Затем вы должны преобразовать эти числа в формат даты, выбрать числа и выбрать Короткое свидание из Общие выпадающий список под Главная вкладка, и все числа были преобразованы в нормальный формат даты, см. снимок экрана:

Ноты:

1. Эта формула может рассчитать дату выхода на пенсию человека, который в последний день месяца ему исполняется 60 лет, то есть, если он родился 5, он выйдет на пенсию 10.

2. В приведенной выше формуле E2 это ячейка даты рождения, которую вы используете, и 60 пенсионный возраст человека выйдет на пенсию, вы можете изменить его по своему усмотрению.

Рассчитайте дату выхода на пенсию и оставшийся год от даты рождения с помощью формул

Иногда вам может потребоваться рассчитать дату выхода на пенсию, которая соответствует точному дню рождения через 60 лет с даты рождения, например, если человек родился 5 мая 18 года, точная дата выхода на пенсию будет 1980 мая. 5 г. Для решения этой задачи EDATE функция может оказать вам услугу.

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

2. Затем вы должны отформатировать результаты как формат даты, выбрать числа и выбрать Короткое свидание из Общие выпадающий список под Главная вкладка, и все числа были преобразованы в нормальный формат даты, см. снимок экрана:

3. Если вы хотите рассчитать оставшиеся годы до даты выхода на пенсию, примените приведенную ниже формулу, а затем перетащите дескриптор заполнения вниз, чтобы заполнить эту формулу в другие ячейки, см. Снимок экрана:

Советы: Если вы хотите сохранить целую часть оставшегося года, используйте эту формулу: = ЦЕЛОЕ (ГОД (СЕГОДНЯ (); C2)).

Рассчитайте дату выхода на пенсию по дате рождения с помощью удобной функции

Если у вас есть Kutools for Excel, с этим Добавить годы к дате функция, вы также можете легко рассчитать дату выхода на пенсию, которая совпадает с точным днем рождения через 60 лет с даты рождения.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Щелкните ячейку, в которой вы хотите разместить результат. А затем нажмите Kutools > Формула Помощник > Формула Помощник, см. снимок экрана:

2. В Формула Помощник диалоговом окне выполните следующие действия:

Выберите Время из файла ФормулаТип раскрывающийся список;
Затем выберите Добавить годы к дате из Выберите формулу список;
В правой Ввод аргументов В разделе «Дата выхода на пенсию» выберите список ячеек, по которым вы хотите рассчитать дату выхода на пенсию, в текстовом поле Дата и время, а затем введите возраст выхода на пенсию в поле Число коробка.

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

Пенсионная реформа