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.

Tuesday, August 14, 2012

A *REAL* Simple Employee Table for PostgreSQL Retirement Planning

There was a time ~ 15 to 20 years ago, when Retirement Plan Recordkeepers were saddled with less than friendly reporting tools.  We would collect data from these Recordkeepers (Quarterly) and design small, unique sets of tables for each plan, along with the output design (print) that would be understandable for each employee, to include Future Values, charts and other communication materials. At one point, we were running 500 Retirement Plans and ~100,000 statements, each with a different print style and most, with very different table designs.

My point?

The Table below is extremely simple and does not scratch the surface of a Recordkeeping system - but - it is just enough to run and test our initial PostgreSQL functions.  Any HRIS employee could do the same, with ease.  You wouldn't need a recordkeeper and could run it whenever you chose to communicate to either one - or many employees.  One day of work by one person in an HR Department can change the future of many.  'Too busy', is thin.

Younicycle has a built-in Table Editor that records mouse clicks, selections & entered text and then creates the SQL.  Anyone (yes, really) can create an SQL Table.  It may not make a lick of sense, but it can be done without knowing a bit of SQL.

The image below shows the Columns, default (automatic) values and NULL constraints.  Once again, there still are no values in the Table.  Also notice that I'm using double precision (instead of numeric) for many of the datatypes.  There are practical reasons for this that I'll hit on next week.

So, let's add some values.  See below and note that we do not need to enter values for start_date or annual_growth_rate as we have set Defaults that 'automatically enter' the stated values.

Pretty simple stuff.  If you are an Employee Benefits Manager, you certainly get much greater detail from your Recordkeeper and chances are high that it will include all of the above + more.

Next post, I'll show you how to write a query that will use those pgSQL Functions and the data.

* Note - obviously, you won't want to data enter values for 10 .... or 10,000 employees.  We have a data import dialog for that, which I'll show in a future post.

Friday, August 10, 2012

PostgreSQL & Retirement Planning (Part 2)

I stopped yesterday with a pgSQL function that will calculate a Future Value, based on a series of equal payments and a specific interest rate.  What I didn't cover was the Future Value of a current account balance.  We will want to be able to add the Future Value of a series of payments to the Future Value of a current balance.  Note that we could combine these two functions into one, but it is easier to check for a mistake if they are separate.  Also a little simpler to explain.

Let's say an employee participates in a 401(k) Plan and has a current balance.  Ideally, these funds will also grow and based on investment performance (growth rate (i)), years and amount, could make a substantial difference in their future retirement funds.  The pgSQL for that Function would be:

CREATE OR REPLACE FUNCTION
youraccount.fv(pv double precision, i double precision, n double precision)
RETURNS  double precision AS
\$BODY\$
DECLARE
result double precision;
BEGIN
result := pv * POWER((1+i), n);
-- result := POWER(pv * (1+i), n);
RETURN result;
END;
\$BODY\$
LANGUAGE 'plpgsql' IMMUTABLE

Notice that 'youraccount.' precedes the name of the function, which is 'fv'.  Younicycle's structure is called a 'schema' where each Account = a schema, so 'youraccount' means that this Function will only be loaded & visible within your account.  We call this a Private Function.

So, if your Account = \$1,000, the rate is 4% and the period (n) is 2 (year, let's say), then:
1000 x 1.04 x 1.04 = 1081.6.

The two Functions are usually created pretty easily by an Application Developer with even a modest level of knowledge of pgsql, but as of yet, no actual calculation on data has taken place.

We need a Table to hold the values and then apply the Functions to those values.

Now I have a choice.  I can make (1) a simple, 'flat table', similar (in appearance only) to a spreadsheet - or (2) I can create several tables, and 'design' a database.  #2 is more time consuming, requires a bit of thought - but is more flexible, powerful and easier to maintain. Option #1 will probably be more understandable to most people and will still 'work', so I'll go with #1.  If this was for 'Production', #2 would be the definite choice.

more, to include images later today.....

Thursday, August 9, 2012

PostgreSQL and Retirement Planning

If the snippet does not apply to you, then it certainly is relevant to someone that you do know.
extracted from:

Preparing for the Ultimate Vacation ( Scott Holsopple)

"Given the amount of time people spend on their vacation plans, it was alarming to see the Charles Schwab Retirement Income Study, which shows the number of people who have calculated how much income they’ll need in retirement.According to the study, 64 percent of respondents indicated they had less than one year of cash living expenses saved for retirement, and one-third of respondents said they had not even determined what their essential living expenses in retirement would be.

This study wouldn’t be so overly alarming except for the fact that the respondents were just five years away from retirement."

A dismal report, by any standard.  Nearly two-thirds that will be out of money after one year and one-third had not even determined their potential living expenses.

It's clear that very few have any idea how much money they should have available when they do retire.  Blindfolded and walking in the direction of a known cliff.  It may be too late for the 64% group, though they may need to consider working until age 70 and receive a Delayed Retirement Credit from their Social Security Retirement.

This series of posts will focus on those that are more than five years from retirement and still have a chance of achieving a measure of retirement security. Employers should provide these calculations for their employees as part of their ongoing Employee Communications.  Every single employee.

Younicycle includes an online database component & is built on PostgreSQL, which includes an SQL procedural language (PL/pgSQL) for creating Functions that can calculate the growth of money and use of money.  We have an built-in Editor that allows these Functions to be created online within an account.  The first Function will calculate the Future Value of a series of payments (savings).  At its simplest level, there are three factors:
• How much you will save per period.  In this case, let's assume the period is 'per year'.
• How many periods you will save (how many years).
• How fast will will your money grow? (interest rate per year).
This is a simple calculation for PostgreSQL, but to get it to work efficiently for a group of employees, we need a few, basic 'objects'.
• A Table
• A pgSQL Function
The code below is not difficult, but would likely be created by an Application Developer, with the 'meat' in red:

CREATE OR REPLACE FUNCTION fvtestpmt(payment double precision, interestRate double precision, periods double precision)
RETURNS double precision AS
\$BODY\$
BEGIN
return payment*(pow(1+interestRate, periods)-1)/interestRate;
END;
\$BODY\$
LANGUAGE plpgsql IMMUTABLE;

The image below shows how to create this simple, but powerful Function from within Younicycle.

But what can a Content Developer or a Human Resources Department do with this Function?  It still appears to be useless.

In the next Post, useless will start to become useful as I'll show you how to create a Table to store Employee values and generate a Query that will apply this function to those values.

Friday, August 3, 2012

When Do it Yourself (DIY) becomes Grey

I am an advocate of Do-it-Yourself (DIY), with limits.  I've determined that DIY dentistry is usually off the table and DIY installation of circuit breakers for my home is simply out of the question.  Nevertheless, each specific 'problem' can be evaluated and a judgement can be made on the time & costs required to complete the task.

"I can't" or "I don't know how" are often used as excuses to sit, do nothing and accomplish the same.  My first question to those responses is typically; "Did you try?", followed by "What did you try?"

All to often, the response is a variation of a Bart Simpson one-liner; "I can't promise that I'll try, but I'll try to try."

Younicycle has been designed to enable Content Developers that lack 'coding skills' to collaborate with Application Developers that have those skills.  WYSIWYG tools, in addition to traditionally styled code editors, should allow the yin and yang of project development to move briskly to success.

We are finding out that there is a grey area, where Application Devs are competent with (example):
• CSS
• JQuery
• SQL
......... but freeze in place ('I don't know how') when required to work with Php (example). The solutions available for such a person are:
1. Do not use Php.  Determine an alternative that will still accomplish the goal.
2. Learn Php.
3. Pay someone to do it for you.