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.....

No comments:

Post a Comment