Analysis

Calculating Money Growth using PL/pgSQL

PL/pgSQL for-loops are a tool that we like to use at Periscope Data. To demonstrate their use, we’re going to determine the real value of $1000 in our savings account after 15 years.

Let’s say we deposited our money at the beginning of 2000. Over that time, our bank account provided interest rates on par with the effective fed fund rate of that year.

We have the tables interest_rates, with records of historical fed funds rates, and table inflation_rates, with records of historical inflation rates.

Because the interest is compound, we need to calculate the interest earned based on the total amount of money we have in the bank account, rather than the principal. This is where PL/pgSQL’s for-loops come in.

Setup

In order to do the calculation, we are going to create a function. We need to declare its name, input, and output type. What we would like to see as the output is a set of rows with year and the amount of money in the bank account that year. We can create that type as following:

createtype inf_int_rates

as (year int, infl_rate decimal, int_rate decimal);

In the function, we are going to use the result of a join between inflation_rates and interest_rates. So we will declare this type as well:

createtype inf_int_rates

as (year int, infl_rate decimal, int_rate decimal);

Now that we have set up these types, we are ready to write the actual function. The function declaration looks like:

createfunction get_money_trends

(initial_value decimal, year_start int)

returnssetof year_money as

$body$

...

$body$

language'plpgsql';

In the first part of the function, we need to declare some variables including the result row type, the variable used in the for-loop, and the variable to store the value of the money in.

declareresult year_money%rowtype;

declare infl_int inf_int_rates%rowtype;

declare val decimal := initial_value;

For-Loop

Now, we are ready to calculate the value of the money. First, we need the inflation rate and interest rate per year:

select inflation_rates.year,

inflation_rates.rate,

interest_rates.rate

from inflation_rates

join interest_rates

on inflation_rates.year= interest_rates.year

where inflation_rates.year> year_start

Next, we will go over each row of this result set to calculate the real value of money that year by

where My is the amount of money in the account in year y, and rinterest,rinflationrinterest,rinflation are interest and inflation rate, respectively. This translates into the following for-loop:

for infl_int in

select inflation_rates.year,

inflation_rates.rate,

interest_rates.rate

from inflation_rates

join interest_rates

on inflation_rates.year= interest_rates.year

where inflation_rates.year> year_start

loop

val :=

val * exp(infl_int.int_rate - infl_int.infl_rate);

result.year := infl_int.year;

result.money := round(val, 2);

returnnextresult;

end loop;

return next result yields a row with the current values in result, returning one row per year in this case.

Result

Putting all these together, the function looks like:

createfunction get_money_trends

(initial_value decimal, year_start int)

returnssetof year_money as

$body$

declareresult year_money%rowtype;

declare infl_int inf_int_rates%rowtype;

declare val decimal := initial_value;

begin

for infl_int in

select inflation_rates.year,

inflation_rates.rate,

interest_rates.rate

from inflation_rates

join interest_rates

on inflation_rates.year= interest_rates.year

where inflation_rates.year> year_start

loop

val :=

val * exp(infl_int.int_rate - infl_int.infl_rate);

result.year := infl_int.year;

result.money := round(val, 2);

returnnextresult;

end loop;

return;

end

$body$

language'plpgsql';

We ran the function and got the following result:

get_money_trends(1000, 2000);

So it looks like we lost about 5% of our initial savings, a poor investment! Thanks for reading - we hope this gives you a new tool for your SQL work.