Как вычислить в экселе пенсию

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

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

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

Dim social_contr As Double 'итоговое начисление

Total = WorksheetFunction.Sum(начисление_итог) 'общая сумма начислений за год сотруднику

social_contr = начисление_т * ставка_1

ElseIf пр_база Total - начисление_т Then

social_contr = (пр_база - (Total - начисление_т)) * ставка_1 + (Total - пр_база) * ставка_2

social_contr = начисление_т * ставка_2

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

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

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

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

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

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

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

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

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

Обратите внимание на параметр "начисление_итог", в функцию необходимо передать массив ячеек включая текущее начисление. Чтобы корректно копировать формулу в следующие ячейки, необходимо закрепить в массиве ссылку на первую ячейку, в нашем примере это $B$2

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

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

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

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

Пенсионный калькулятор в 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 байт

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

Методические указания для самоподготовки студентов к практическому занятию №4 по новым информационным технологиям. (1 курс, 2 семестр, Фармацевтический факультет) «Решение задач в табличном редакторе Excel. Функции. Мастер функций. Абсолютная и относительная адресация»

Мотивация цели:

· Будущему фармацевту необходимо обладать основными навыками работы с компьютером и с программами пакета MS Office.

· Понимание особенностей использования табличного редактора для правильного создания таблиц и проведения расчетов.

Цель самоподготовки:

После самостоятельной подготовки к занятию студент должен знать:

· Способы форматирования таблиц;

· Методы вставки функций и формул;

После самостоятельной подготовки к занятию студент должен уметь:

· Пользоваться табличным редактором;

План систематизации по разделу:

1. Решение задач, используя элементарные формулы

2. Решение задач, используя условное форматирование

Методические рекомендации по усвоению материала (схема ООД):

1. Прочитайте краткие теоретические сведения по данной теме.

2. Ответьте на вопросы для самоконтроля.

3. Сделайте основные задания для самоподготовки.

Литература.

1. Информатика. Под редакцией Н.В. Макарова. М., «Финансы и статистика», 2011г.

1. Турецкий В.Я. Математика и информатика:учеб. пособие для студентов высш. учеб. заведений, обучающихся по гуманитар. направлениям и специальностям. М.,Инфра-М, 2005г.

2. Могилев, А.В. Информатика:учеб. пособие для студентов высш. педагог. учеб. Заведений М., ACADEMIA, 2000г.

3. Каймин, В.А. Инфоpматика : учеб. для студентов высш. учеб. заведений, обучающихся по естеств.-науч. направлениям и специальностям. М., ИНФРА-М, 2007г.

4. Гиляревский, Р.С. Основы информатики : курс лекций.М.,Экзамен,2004г.

5. Олифер, В. Компьютерные сети. Принципы, технологии, протоколы : учеб. пособие для студентов высш. учеб. заведений, обучающихся по направлению "Информатика и вычислит. техника" и по специальностям "Вычислит. машины, комплексы, системы и сети", "Автоматизир. машины, комплексы, системы и сети", "Програм. обеспечение вычислит. техники и автоматизир. систем".СПб., Питер,2010г.

6. Лопин, В.Н. Система разработки баз данных Microsoft Access:учеб. пособие для студентов всех специальностей, Курск, КГМУ, 2004г.

7. Электронное пособие по дисциплине "Информатика" для студентов очных и заочных отделений фармацевтического факультета [Электронный ресурс]/авт.-сост.: Е.С. Кататьникова; ГОУ ВПО "Курск. гос. мед. ун-т", каф. физики, информатики и математики.-Курск:КГМУ,2010.-1 электрон. опт. диск (CD-ROM).

Вопросы для самоконтроля:

1. Что мы понимаем под формулой и какие символы она может содержать.

2. Для чего предназначены функции. Сколько их на какие группы разбиты.

3. Перечислить правила работы с формулами и функциями.

4. Способы и технология запуска программы Excel

5. Способы и технология выхода из Excel

6. Назвать основные компоненты экрана Excel.

7. Назначение и структура:

- строки основного меню;

- панели инструментов Стандартная;

- панели инструментов Форматирования;

- полосы перебора страниц рабочей книги;

8. Какие кнопки управления окном используются при работе с ним.

9. Состав основного меню Excel, структура и назначение каждой команды.

10. Контекстное меню: назначение, способы вызова и команды входящие в контекстное меню.

Задания для самоподготовки:

Задание 1. Пользуясь возможностями Excel , создать таблицу для расчета с клиентами за купленный ими товар. Оплата может производиться 2 частями: обязательная часть при покупке и вторая - позднее. За каждый день отсрочки начисляется наценка.

Правила расчета:

Предоплата обязательная = стоимость партии * процент обязательной предоплаты

Остаток = (стоимость партии – фактическая предоплата) + (стоимость партии – фактическая предоплата) * дни * наценка за день

Столбец ВСЕГО = оплата остаток + фактическая предоплата

ВНЕШНИЙ ВИД ТАБЛИЦЫ

Выполнение:

1. Переименуйте Лист 1 в «Задание 1»

2. Составить таблицу по образцу для расчета оплаты 5 партий товаров, для которых придумать названия, стоимость, фактическую предоплату, дни отсрочки.

3. Применить денежный формат там, где это необходимо

4. Заполнить первую строку формулами согласно заданным правилам (см. Правила расчета):

a. Выделите ячейку С6 и внесите в нее формулу: =В6*Е1

b. Проделайте такую операцию со всеми ячейками в этом столбце, используя соответствующие ячейки

c. Вычислите в столбце Оплата Остаток, используя правила расчета

d. Вычислить столбец ВСЕГО по соответствующему правилу расчета

e. Вычислить данные строки «ВСЕГО:» как суммы соответствующих столбцов.

5. Задать формат ячеек столбца «Дни» так, чтобы величина большая 10 дней выводилась красным.

i) выделите те ячейки, которые вам надо отформатировать

ii) нажмите Условное форматирование на вкладке Главная

iii) далее нажмите Правила выделения ячеек и выберите Другие правила

iv) выберите данные согласно рисунку и установите формат – заливка красным цветом:

v) нажмите Ок.

РЕЗУЛЬТИРУЮЩИЙ ВИД ТАБЛИЦЫ

7. Сохраните книгу в своей папке под именем «Лаб_2».

8. Не закрывайте книгу.

Задание 2. пользуясь возможностями Excel , создать таблицу для расчета заработка продавцов фирмы. Зарплата состоит из 2-х частей – фиксированного оклада и премии, которая зависит от фактического объема проданного каждым продавцом товара.

Правила расчета:

Премия = (продано – норма) * процент премии

Заработок = оклад + премия

Всего = сумма по столбцу

ВНЕШНИЙ ВИД ТАБЛИЦЫ

Порядок работы

1. Переименуйте Лист 2 в «Задание 2» и продолжайте работать на этом листе.

2. Составить таблицу по образцу для расчета заработка 5 продавцов, для которых придумать ФИО, оклад, продано.

3. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.

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

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

6. Подсчитать итоговые суммы по столбцам.

7. Сохраните книгу.

РЕЗУЛЬТИРУЮЩИЙ ВИД ТАБЛИЦЫ

Задание 3: пользуясь возможностями Excel , создать таблицу расчета пенсии по старости.

Правила расчета:

Пенсия = мин.зарплата *4

Прибавка = военный стаж * 30%мин.зарплаты + (трудовой стаж – 20)*10%мин.зарплаты

Всего = пенсия + прибавка

Итогорассчитываем с помощью автосуммы

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

Порядок работы

1. Переименуйте Лист 3 в Задание 3

2. Составить таблицу по образцу для расчета пенсии для 3 человек.

3. Заполнить таблицу формулами для расчета согласно заданным правилам.

4. Подсчитать итоговую сумму столбца.

5. Задать пользовательский формат столбцов «Стаж» так, чтобы значения выводились с подписью «лет»:

a. откройте меню «Формат ячеек», вкладку «Числовой формат», в которой выберите из списка «Числовые форматы» строку «Все форматы»

b. в поле ввода числового формата ввести строку Основной “ лет”

c. (пробел и слово Основной являются обязательными. )

6. Оформить рабочий лист согласно образцу, используя возможности форматирования ячеек.

7. Сохранить рабочую книгу.

РЕЗУЛЬТИРУЮЩИЙ ВИД ТАБЛИЦЫ

ПРИЛОЖЕНИЕ:

Дата добавления: 2018-10-27 ; просмотров: 465 ;

С 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 г. баллов, нужно:

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

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

По какой формуле считают будущую пенсию

Пенсионное законодательство в России реформировали несколько раз. Из-за этого кажется, что оно запутанное и разобраться в нем невозможно.

Но посчитать будущую пенсию по старости самостоятельно — реально. Это можно сделать совсем просто — на калькуляторе на сайте ПФР, хотя результаты там будут очень условными. Лучше прочитать эту статью и посчитать размер пенсии по специальной формуле.

Законодательное регулирование

Пенсию по старости начисляют по правилам, которые установлены:

Федеральным законом от 15.12.2001 № 167-ФЗ «Об обязательном пенсионном страховании в Российской Федерации».
Федеральным законом от 28.12.2013 № 400-ФЗ «О страховых пенсиях».
Постановлением Правительства РФ от 02.10.2014 № 1015 «Об утверждении правил подсчета и подтверждения страхового стажа для установления страховых пенсий».
Федеральным законом от 28.12.2013 № 424-ФЗ «О накопительной пенсии».
Федеральным законом от 27.12.2019 № 446-ФЗ «Об ожидаемом периоде выплаты накопительной пенсии на 2020 год».
Федеральным законом от 24.07.2002 № 111-ФЗ «Об инвестировании средств для финансирования накопительной пенсии в Российской Федерации».
Федеральным законом от 07.05.1998 № 75-ФЗ «О негосударственных пенсионных фондах».

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

Для госслужащих, военных, космонавтов, летчиков и их родственников пенсию начисляют по другому закону — Федеральному закону от 15.12.2001 № 166-ФЗ «О государственном пенсионном обеспечении в Российской Федерации».

Как узнать размер будущей пенсии

Государственная трудовая пенсия по старости состоит из двух частей: страховой и накопительной. Общая пенсия считается так:

Трудовая пенсия по старости = страховая пенсия + накопительная пенсия.

Чтобы рассчитать размер будущей страховой пенсии, нужно знать:

Продолжительность страхового — трудового — стажа, в который входят не только отработанные годы, но и социально значимые периоды: служба в армии, уход за детьми, инвалидами, пожилыми родственниками, проживание с супругом в месте, где не было работы.
Размер заработной платы до вычета НДФЛ в каждом отработанном календарном году.
Год выхода на пенсию. Это особенно важно, если человек собирается выйти на пенсию не сразу в 65 или 60 лет, а позже.
Индивидуальный пенсионный коэффициент за каждый отработанный год и в сумме — за всю трудовую жизнь.
Стоимость индивидуального пенсионного коэффициента на год выхода на пенсию.
Размер фиксированной части страховой пенсии, который установлен на год выхода на пенсию.

Все эти данные нужно подставить в формулу расчета пенсии:

СП = ИПК × СПК + (ФВ × КвФВ),

где СП — размер страховой пенсии по старости;

ИПК — индивидуальный пенсионный коэффициент;

СПК — стоимость одного пенсионного коэффициента — балла — по состоянию на день, с которого назначается страховая пенсия по старости. СПК на 2020 год равна 93 Р ;

ФВ — фиксированная выплата к страховой пенсии, на 2020 год — 5686,25 Р ;

КвФВ — коэффициент повышения ФВ, который применяется при отсрочке обращения за страховой пенсией.

Накопительная пенсия — это уже не коэффициенты, которые государство считает по своим правилам, а настоящие деньги. Их называют пенсионными накоплениями. Часть пенсионных взносов от работодателя с 2002 по 2013 годы оставалась на вашем счете в Пенсионном фонде РФ — из них сформировались пенсионные накопления.

С 2014 года власти ввели мораторий: заморозили накопительную часть пенсии. Все страховые взносы стали уходить на формирование страховой пенсии. Мораторий действует до конца 2022 года. Накопительная пенсия со взносов работодателя сейчас не формируется ни у кого. Но те деньги, что успели накопиться, остались за вами и составят прибавку к страховой пенсии.

Чтобы узнать размер ежемесячной накопительной пенсии, необходимо разделить пенсионные накопления на количество месяцев ожидаемого периода выплаты. Ожидаемый период — это норматив, в 2020 году он составляет 258 месяцев.

Допустим, Олег выходит на пенсию в 2020 году. Он заработал 100 баллов ИПК и у него есть 258 тысяч рублей пенсионных накоплений. Отсрочку решил не брать, а выйти сразу, поэтому КвФВ = 1 . Тогда ежемесячная пенсия Олега составит:

5686,25 Р + 93 Р × 100 + 258 000 Р / 258 = 5686,25 Р + 9300 Р + 1000 Р = 15 986,25 Р .

2.3 Задача: Расчет размера пенсионных накоплений

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

Использование средства подбора параметра

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

Рис. 6 - Таблица для расчета размера пенсионных накоплений

В этой таблице указаны возраст, начиная с которого в пенсионный фонд вносятся платежи (А2), величина ежемесячного взноса (В2), период отчислений, рассчитанный по формуле

то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).

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

Функция БС () возвращает будущее значение вклада, определяемое с учетом периодических постоянных платежей и постоянной процентной ставки. Синтаксис данной функции выглядит так:

БС (ставка; кпер: плата; нз: тип)

Аргументы функции: ставка — размер процентной ставки за период; кпер - общее число периодов выплат годовой ренты; плата - выплата, производимая в каждый период (это значение не может меняться на протяжении всего времени выплат), причем обычно плата состоит из основного платежа и платежа по процентам; нз — текущая стоимость или общая сумма всех будущих платежей, начиная с настоящего момента (по умолчанию — 0); тип — число, которое определяет, когда должна производиться выплата (0 — в конце периода, задается по умолчанию, 1 — в начале периода).

Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис » Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.

Рис. 7 Диалоговое окно ”Подбор параметра” с заданными параметрами

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

После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.

Рис. 8 - Результаты подбора параметра

Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью кнопки “Пауза”. Кнопка “Шаг” позволяет просмотреть промежуточные результаты вычисления.

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

2. Вызвать команду “Сервис » Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.

3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка

4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.

5. Нажать кнопку ОК, и нужный параметр будет подобран в диалоговом окне “Результат подбора параметра”. По окончании этого процесса в нем отобразятся результаты.

6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.

2.4 Применение функции подбора параметра при работе с диаграммами

Средство подбора параметра применяется и при работе с диаграммами. Как это делается, показано в следующем примере.

На основании данных о суммах выручки от продажи изделий в три региона нужно построить с помощью мастера диаграмм гистограмму (рис. 8) В интерактивном режиме с использованием мыши нужно настроить высоту полосы «Среднее», и посмотреть, как Ехсе1 изменит высоту полосы «Северск» для получения целевого значения. Далее необходимо дважды щелкнуть на последней полосе (один раз - для выбора ряда, а второй — для выбора полосы из ряда), которая представляет средние значения, и увеличить ее высоту путем перетаскивания маркеров размеров.

Когда кнопка мыши будет отпущена, откроется диалоговое окно “Подбор параметра. В поле “Установить в ячейке” появится имя ячейки В5, а в поле “Значение” — число, соответствующее последнему значению, которое отображалось в поле подсказки. Курсор ввода будет находиться в поле “Изменяя значение ячейки”, поэтому остается лишь ввести в данное поле значение В2. Нужно щелкнуть на кнопке ОК, после чего появится диалоговое окно “Результат подбора параметра”, в котором будет содержаться нужная информация. Щелкнуть на кнопке ОК для возврата на рабочий лист. В ячейках уже содержатся новые значения и что в соответствии с ними настроена высота полос гистограммы (рис. 9).

Рисунок 8 - Гистограмма “Выручка от продажи изделий в трех регионах”

Рисунок 9 - Гистограмма “Выручка от продажи изделий в трех регионах” c применением подбора параметров

2.5 Задача: Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Вычисляем радиус описанной окружности R по формуле:

где а = 2, b = 4, с_ = 5 – стороны треугольника

S – площадь треугольника

Площадь треугольника S вычисляется по формуле Герона:

где а = 2, b = 4, с_ = 5 – стороны треугольника

р – полупериметр треугольника

Полупериметр треугольника p вычисляем по формуле:

Пример вычисления радиуса описанной окружности в программе Excel показан на рисунке 10

Рисунок 10 - Вычисление радиуса описанной окружности по трем сторонам треугольника

Итак, радиус описанной окружности R по трем сторонам треугольника а, b, с вычислен. Если зафиксировать длины сторон b и с, то можно считать, что R вычисляется как функция а. Но Ехсе1 дает возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, надо определить величину а при R = 3. Для это нужно выделить ячейку В9, в которой вычисляется R. В меню нужно выбрать "Сервис/ Подбор параметра". Выводится диалоговое окно "Подбор параметра". Поле "Установить в ячейке:" уже содержит адрес выделенной ячейки B9. В поле "Значение:" нужно ввести 3. В поле "Изменяя значение ячейки:" нужно ввести адрес ячейки В2, содержащей величину стороны а (если щелкнуть мышью по этой ячейке, то в поле ввода окажется адрес $В$2). После нажатия кнопки "ОК" выводится новое окно "Результаты подбора параметра". Если увеличить разрядность числа в ячейке B9, то можно увидеть, что R достигло значения 2.9999172. При этом а = 1.515753171.

Результаты выполнения показаны на рисунке 11

Рисунок 11 - Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Единственное ли значение a соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Список используемой литературы

1. Справка MS Excel

2. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. – 493 с.

3. Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. – 400 с.

4. Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика, 2003. – 336 с.

Читайте также: