Friday, August 17, 2012

Retirement Planning & PostgreSQL - a Simple Query

 Let's review the basic elements that go into estimating the Future Value of of our Retirement Savings and add a bit of my own functional experience.  The end goal is to estimate the future value, based on what is happening today.
  1. The duration money will grow (periods)
  2. Current account value, along with the annual additional savings
  3. The Rate of Growth, per period
    Let's look at #1, duration.

    The duration of one year, rounded to the nearest second  is 365 days, 5 hours, 48 minutes and 46 seconds.
    Since I forget where I have put my car keys, it's pretty unlikely I'll remember the factoid above, so I'll use 365.25 days in a year.  Does this lack of precision affect the outcome?  Well, if we are looking at a span of 30 years, I'll be off by about 6 1/2 hours or approximately 2 seconds per day.  Between now and then, an earthquake could change the spin of the earth by that amount.  Remember, we're not shutting down a nuclear reactor, we're only estimating a number. 

    #2, the Account Value and subsequent annual savings

    An HR Department can generally request a data feed from their Recordkeeper for account values on any given day or may be able to pull a report from the Recordkeeper's server for the valuation at the close of business for the prior day.  By the time you get the data, load the data, run the calculations and either post the results or print and disseminate to employees, the Account value is a bit stale (dated).  The subsequent savings in future years is even less precise.  The employee will most likely change the amount of the payment frequently, if for no other reason that the payment is generally a % of Wage and not a specified amount.  Deductions are probably per pay (not annual) & employer match can have entirely different timing. 
    • 2012:  Annual wage ($40,000) * 4% = $1,600
    • 2013: Annual Wage ($41,000) * 4% = $1,640
    Wage inflation, coupled with unknown future events, will most likely cause that annual savings amount to vary.  So, do what?  It won't be accurate, so give up?  Of course not, run the calculations every year and as that person gets closer to retirement, the number should become more meaningful/accurate.

    #3, the Rate of Growth per period

    Make the assumption reasonable (prudent) and I generally suggest 3% net of inflation.  Let's do a look-back on this item (inflation).  A person that is now 65 years old, probably paid about $0.27 for a gallon of gas 45 years ago.  If you had told that 20 year that he'd be paying $3.90 today, your credibility would be in doubt.
    Using a rate of growth net of inflation will remove an unknown factor (inflation), put the estimate in today's dollars and make it meaningful.  I do have customers that insist on incorporating a higher rate of growth and an inflation estimate, but it is a source of confusion for employees and does not improve the estimate in the slightest.  More on that in a subsequent post.

    The probability of any assumed rate of growth actually equaling the final, historical rate is very close to nil.  Look no further than our own Governments' growth rate assumptions and 'success rate' to verify this statement - and they have hundreds of enrolled actuaries and economists crunching numbers, all day, every day.

    As with #2 (Account value and subsequent savings), run the calculations every year and inflation becomes a non-factor.

    Summary
    We're making a reasonable estimate.  As that person gets closer to retirement , the calculated values should become more accurate.

    If you've been following this series of posts, you'd probably like to see some simple  SQL, with results.
    I named the query 'blog_fvs' and here it is:

    SELECT "blog_fvs"."ee_id" as "ee_id", round("youraccount".fv("blog_fvs"."ee_acct_bal" + "blog_fvs"."rollover_balance" + "blog_fvs"."er_match_balance", 0.03, 65 - ("blog_fvs"."run_date" - "blog_fvs"."date_of_birth") / 365.25) + "youraccount".fvtestpmt("blog_fvs"."annual_wage" * ("blog_fvs"."ee_pct_contribution" + "blog_fvs"."er_match"), 0.03, 65 - ("blog_fvs"."run_date" - "blog_fvs"."date_of_birth") / 365.25)) as "total_future_value"
    FROM
        "youraccount"."blog_fvs" AS "blog_fvs"
    LIMIT ALL
    OFFSET 0

    It would take me an hour to type that query, but it took me about 3 minutes as the sql is auto generated with clicks and a nominal amount of typing.  We'll make a video of that and post early next week.

    Believe me, if I can do it from scratch, anyone can.  Even better, you won't need to even create the query or the original Table.  I will make an SaaS Solution (once I am done) and you will be able to click a button and install the entire solution in 10 seconds.  All you'll need to do is import your own data.

    The answer for the Future Value of our hypothetical person with the following data:
    Birth date: 06/25/1963
    Run date: 08/14/2012
    Employee Account Balance: 17,948.26
    Rollover Account Balance: 151.38
    Employer Match balance: 4,766.82
    (Total Balance of : $22,866.46)
    Assumed growth Rate 3%
    Employee Annual Wage: 50,000
    Employee contribution: 6% of wage
    Employer match: 3% of wage
    Assumed retirement age: 65

    = $126,263



    OK, now we're getting somewhere, though there's still much to be done:
    1. How much retirement income will that chunk of money provide?
    2. How will that 'combine with Social Security?
    3. What will be the eventual Retirement income
    4. Will that be enough?
    5. What does that employee need to do NOW to get to that eventual Retirement Income need?
    It may take an additional 15 or 20 posts, but stick with me.  We are in the process of beginning to do a Retirement Gap Analysis that will answer the question "What should I do ... now?  How much should I save ... now?"  Now that is meaningful Employee communications.

    When we are done, you will be able to install the entire Solution with the click of a button, so hang in there.  You can make an enormous difference in the future retirement of your employees.  

    I think that is good.

    Thanks for reading.

    No comments:

    Post a Comment