## The High-Performance SQL Blog

# Calculating Money Growth using PL/pgSQL

Calculating Money Growth using PL/pgSQLPL/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

*, with records of historical inflation rates.*

**table 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:

createtypeinf_int_rates

as(yearint, infl_rate decimal, int_rate decimal);

In the function, we are going to use the result of a join between * inflation_rates* and

*. So we will declare this type as well:*

**interest_rates**createtypeinf_int_rates

as(yearint, 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:

createfunctionget_money_trends

(initial_value decimal, year_start int)

returnssetofyear_moneyas

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.

declareresultyear_money%rowtype;

declareinfl_int inf_int_rates%rowtype;

declareval 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:

selectinflation_rates.year,

inflation_rates.rate,

interest_rates.rate

frominflation_rates

joininterest_rates

oninflation_rates.year=interest_rates.year

whereinflation_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

**, and**

*y***are interest and inflation rate, respectively. This translates into the following for-loop:**

*rinterest,rinflationrinterest,rinflation*forinfl_intin

selectinflation_rates.year,

inflation_rates.rate,

interest_rates.rate

frominflation_rates

joininterest_rates

oninflation_rates.year=interest_rates.year

whereinflation_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;

endloop;

* 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:

createfunctionget_money_trends

(initial_value decimal, year_start int)

returnssetofyear_moneyas

body$$

declareresultyear_money%rowtype;

declareinfl_int inf_int_rates%rowtype;

declareval decimal :=initial_value;

begin

forinfl_intin

selectinflation_rates.year,

inflation_rates.rate,

interest_rates.rate

frominflation_rates

joininterest_rates

oninflation_rates.year=interest_rates.year

whereinflation_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;

endloop;

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.