How to Calculation of Average Pension Earnings in Excel

The rules for calculating pensions periodically change, and the calculation method is updated. Now, the size of pension provision and the right to it depend on the number of points. Let's delve deeper into this.

 

Components of a Pension

The insurance pension (previously known as the labor pension) is calculated using the formula:

number of points * cost per point.

The cost changes annually and is determined by a Government Decree. Citizens entitled to pension provision have earned no less than thirty points during their working life. The total pension amount includes an insurance part and a fixed payment (previously known as the base part). The amount of the fixed payment is also determined at the state level.

Therefore, you only need to calculate the points, which depend on the salary.

Conversion of Pension Rights Acquired Before 2002

To calculate the pension coefficient before 2002, one needs to find the size of the insurance pension and divide it by 64.1 (the cost of a point as of January 1, 2015). The calculation requires the following indicators:

• Tenure before 2002;
• Average monthly earnings (taken from 2000-2001 or any 60 months before 2002);
• Tenure before 1991.

The first indicator is considered as a tenure coefficient and cannot exceed 0.75.

Examples:

1. A man began his working activity in January 1976. Total tenure – 26 years. The tenure coefficient is 0.55 + 0.01 * (26-25), or 0.56.
2. For a woman under the same conditions, the calculation looks like this: 0.55 + 0.01 * (26-20), or 0.61.
3. If the tenure is less than 20 years for women or 25 years for men, then the tenure coefficient is 0.55.

The calculation of the average earnings for a pension is carried out using the "earnings ratio." This ratio compares an individual's average monthly salary to the national average salary for the same period.

A citizen provided a salary certificate for 60 months from May 1, 1986, to April 30, 1991.

The average earnings for pension calculation are determined by the formula:

The national average monthly salary is 230.1.

The earnings ratio is 1.2. The law sets a maximum limit for this coefficient at 1.2. Therefore, when assessing pension rights, it is considered not as 1.38 but 1.2.

To determine the pension amount from the average earnings (earnings ratio):

1. The calculated pension for citizens with a tenure coefficient exceeding 0.55 is determined by multiplying the tenure coefficient, the average monthly salary coefficient, and 1671. If the resulting amount is less than 660, subtract 450. The amount 1671 represents the average monthly salary for the period from July 1, 2001, to September 30, 2001 (a constant value);
2. If the tenure coefficient is 0.55, then the formula is: (0.55 * average monthly salary coefficient * 1671 - 450) * (tenure before 2002 / 25) for men. For women, the multiplier is (tenure before 2002 / 20). If the calculated value is less than 660, for men, it is 210 * (tenure before 2002 / 25), and for women, it is 210 * (tenure before 2002 / 20).

A woman retired in 2015 with a total tenure of 35 years. Before 2002, it was 22 years. This is more than twenty years. Therefore, the tenure coefficient calculation is as follows:

Assuming the earnings ratio is 1.2, since the tenure coefficient is greater than 0.55, the formula for the calculated pension looks like this:

The woman started working in 1980, so she has tenure before 1991. When considering valorization, an additional 10% is added to the calculated pension, plus 1% for each full year of work before 1991.

She worked for 11 years from 1980 to 1991.

The pension capital is annually indexed. As of December 31, 2014, the index value is 5.6148. Let's find the pension rights in monetary terms for the period before 2002, considering the allowance and indexation:

Convert to points by dividing by 64.1.

This represents part of a citizen's pension rights before 2002. When calculating the pension, the number of points is multiplied by the cost of 1 point valid on the calculation date.

Calculation of Individual Pension Coefficient for the Period from 2002 to 2015

1. It is necessary to find the total amount of insurance contributions made during this period.
2. The insurance part of the labor pension as of December 31, 2014, is calculated as follows: contributions amount / 228 (expected lifespan).
3. The Individual Pension Coefficient is determined by dividing the insurance part by 64.1.

In other words, the insurance part of the labor pension is the pension calculated under the "old" rules minus the accumulative part and fixed surcharge (established by the state).

Pension Points from 2015

They are calculated for each year of work experience. To calculate, take the salary on which insurance contributions are levied. Contributions to the Insurance Fund are 22%. 16% are allocated for forming the insurance (10%) and accumulative (6%) parts of the labor pension by age. Let's assume a citizen does not want to separately form the accumulative part.

To find the number of points earned in 2015, you need to:

Download the calculation of average earnings for pension

The Individual Pension Coefficient for different periods is summed and multiplied by the cost of a point valid on the retirement date.

This is a simplified calculation without considering increasing coefficients, interrupted tenure, and so on.