Calculating the Performance of a Contributory Pension Taken as a Lump Sum

[Bigwig55]

I  was fortunate to have a pension and took a lump sum after over 20 years. It was a contributory pension that allowed me to contribute 1.5% of my salary monthly for a higher pension.

I'd like to calculate the performance as if it was an investment. I have each monthly investment amount, which is not consistent due to my salary changing. I also have the final lump sum amount. Is there a spreadsheet template that would allow me to put each individual amount (around 312) and the final amount to get a performance metric?

[Tonster521 · Accepted solution]

@Bigwig55 I think I understand your question. Essentially, you are trying to find a rate of return using your contributions to a defined benefit pension plan for 26 years and the lump sum amount that your plan developed. I have used the following calculator https://www.calculatorsoup.com/calculators/financial/future-value-annuity-calculator.php Please note the calculator is designed to develop a future value (i.e., your lump sum amount) for a series of deposits (i.e., your pension contributions,etc) based on given interest rate. I realize you are trying to find the interest rate (i.e., rate of return over 26 years). However, you can change the interest rate with one input and click calculate until you arrive at a future value close to your lump sum amount. You can make a few changes to the interest rate very easy as opposed to calculating 312 separate calculations with deceasing time values. For example, I used 3% as the Annual Nominal Interest Rate. As I understand your question, this is the interest rate (rate of return) you are trying to find. Next, I used 26 years for the number of periods with compounding 12 times per period. Next, I used starting pay at $50,000/year which will be $750/year or $62.50/month. Remember, if you are solving for monthly contributions and monthly compounding, you need to convert annual amounts to monthly by dividing by 12. Consistency is the key. Next, I grew your contributions (i.e., payment growth rate) by 3% annual or .25 monthly. This is where you may be slightly overstating your contributions since pay increases may occur annual as opposed to monthly. You be the judge of that dynamic. There is a difference, but it is not significant. The future value amount is $42,391.34 Compare this amount with your lump sum amount and make changes to the Annual Nominal Interest Rate until you are close. Please note that your question is more appropriate for a defined contribution plan such as a 401 K, IRA, etc. However, if your pension is a defined benefit pension plan (i.e., Career Earnings with employee contributions,etc.), there are other factors that are used to develop your pension as well as your lump sum amount. Your HR Dept. may help. If HR has outsourced that duty, ask the Plan Administrator.If the Plan Administrator has outsourced that duty and/or uses a third party,good luck. FYI, defined benefit pensions use concepts such as immediate payment or deferred, age, life expectancy, mortality credits, maybe career earnings or some other approach (i.e., last three years, best five years out of 10 years, etc.). So, if you are older(i.e., age 65 or greater,etc.), do not be surprised if your lump sum is significantly more than the future value of a series of deposits. Hope this helps.

[Bigwig55]

Yes, this is exactly what I am trying to do.  While the calculator won't allow me to be specific on each monthly contribution, it's a start.  I think the growth rate will lead to the most inaccuracies since it's not as consistent as the calculator would allow.

[Tonster521]

@Bigwig55 Thanks for the thumbs up. I agree the grow rate of your contributions may not be an even percentage from month to month or even year to year. However, you should arrive at a realistic average over the 26 year time period. You can use a future value of an annuity table to understand the concept. Here is a table https://www.accountingtools.com/articles/future-value-of-an-ordinary-annuity-table Using $50 K as your starting pay, your contribution in year 1 is $750. At 3%, this amount will grow to $1,617.44 after 26 years. Using $100 K in year 25, your contribution is $1,500. At 3%, this amount will grow to $1,545. For a quick calculation, you may use an average of your total contributions over 26 years (i.e., $1,500. $1,600, etc.). Then use various interest rates to get close to the lump sum value that you received. That will be your rate of return based on your contributions. You will be in the ballpark. Remember, you are comparing the future value of a series of deposits versus a lump sum amount which if developed based on a immediate pension (as opposed to deferred pension) has more factors than just an interest rate. Your current age is important as well as mortality (life expectancy) which are included in your lump sum amount. 

[DavidB191757]

Hello,

Use a spreadsheet to track your pension contributions and calculate investment performance. Create columns for monthly contributions, cumulative total, and fund value. Input data and use mass general patient portal formulas to analyze growth. Explore online templates for pre-built investment tracking tools.