Periscope Data
The world’s fastest visualization platform for data analysts.

Calculating Money Growth using PL/pgSQL

May 7, 2016

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:

create type 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:

create type 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:

create function get_money_trends
    (initial_value decimal, year_start int) 
    returns setof 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.

declare result 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

My+1=My⋅erinterest−rinflation,My+1=My⋅erinterest−rinflation

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);
return next result;
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:

create function get_money_trends
    (initial_value decimal, year_start int) 
    returns setof year_money as
$body$
declare result 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);
return next result;
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.

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?