PSS Pension Calculation: Excel Example Step-by-Step
Calculating your PSS (Public Service Salaries) pension can provide valuable insights into your future financial planning. While it can seem daunting, especially when considering the complexities of Excel, breaking down the process step-by-step can make it manageable. Let's dive into an example of how to calculate your PSS pension using Excel.
Before we start, ensure you have the following details on hand: your current age, expected retirement age, current annual salary, and the annual inflation rate. You can use the government's pension formula to calculate your potential pension amount, which we'll explain in the following sections.
Understanding the PSS Pension Formula
The PSS pension is calculated using the following formula: Pension = (Average final salary × Pensionable service) / 1.25. Let's break down the terms to understand each component before diving into our Excel example.
The 'Average final salary' is the highest average of your annual salary for any consecutive three-year period in your career. 'Pensionable service' is the number of years you've worked in the public service, including those in which you took a leave of absence.
Calculating Average Final Salary
The first step in our Excel example will be to calculate your average final salary. Suppose you've been working for 15 years, and your annual salary has been increasing by 3% each year. In cell A1, enter your starting salary, e.g., $50,000.
In cell B1, insert the following formula: =A1*(1+(Growth rate))^(Year-1), replacing 'Growth rate' with 0.03 (for 3%) and 'Year' with the corresponding year. Drag this formula down to cell M1 to cover all 15 years. Now, follow these steps to find the highest average salary over three years:
- Select cells B1:M1.
- Click 'Insert' > 'PivotTable'.
- Choose an empty table and enable 'Add this data to the Data Model'.
- In the PivotTable, drag 'Year' to 'Rows' and 'Annual Salary' to 'Values'.
- Right-click on 'Sum of Annual Salary' and choose 'Value Field Settings'.
- Select 'Average' and click 'OK'.
- Right-click on 'Average of Annual Salary' and choose 'Move to other fields'.
- Drag the new 'Average of Annual Salary' field to 'Rows'.
You'll now see a table summarizing the average salary for each three-year period. Locate the highest average salary as your 'Average final salary'.
Calculating Pensionable Service
In cell A2, enter your start year, e.g., 2007. In cell B2, enter the following formula: =IF(B1>=Retirement Year, 0, (Ret Year - B1) + 1), replacing 'Retirement Year' with your expected retirement year. Drag this formula down to cell M2 to calculate your pensionable service in years.
You can use decimal values for partial years of service, e.g., 0.75 for seven months. Sum the values in cells B2:M2 to find your total pensionable service.
Now that you've calculated both components of the PSS pension formula, you can plug these values into the formula to find your estimated pension: =Average final salary × Pensionable service / 1.25.
Adjusting for Inflation
It's crucial to account for inflation when planning for the future. Use the following formula to adjust your estimated pension for inflation: Adjusted pension = Pension × (1 + Inflation rate)^(Retirement years).
Calculating Retirement Years
In cell A3, enter your current age, e.g., 35. In cell B3, enter this formula: =Retirement Age - A3, replacing 'Retirement Age' with your expected retirement age. This will give you the number of years until you retire.
Calculating Adjusted Pension
In cell B4, enter your estimated pension, and in cell C4, enter the annual inflation rate, e.g., 0.02 (for 2%). In cell D4, enter the following formula: =B4*(1+C4)^B3. This will give you your adjusted pension amount, taking inflation into account.
It's essential to revisit and update your calculations periodically, as changes in your salary, expected retirement age, and inflation rate can significantly impact your estimated pension. Regularly reviewing your PSS pension projection will help you make informed decisions about your financial future.
Using Excel to calculate your PSS pension enables you to easily adjust variables and understand how your decisions today can impact your retirement tomorrow. By comparing different scenarios, you can develop a comprehensive financial strategy that sets you up for a comfortable retirement. The process may seem complex at first, but breaking it down step-by-step, as shown in this example, can demystify the calculation and put you in the driver's seat of your financial future.