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
result double precision;
result := pv * POWER((1+i), n);
-- result := POWER(pv * (1+i), n);
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.....