People love financial modeling in Excel. It’s easy, fast and flexible, and Excel’s functions allow you to quickly tweak your financial model. However, when your data volume starts to get very large, a spreadsheet can quickly break down.
The Model
We have a basic model that might look like this in a spreadsheet software:

Given a set of past or projected cash flows and an opening balance, we want to calculate the closing and opening balance for each period.
The Closing Balance (CB) will be equal to the Opening Balance (OB) plus Revenue, less the Fixed Costs and the Variable Costs. The Opening Balance (OB) will be equal to the previous period’s Closing Balance (CB).
Now let’s bring the calculation to SQL!
Calculating in SQL
To avoid a recursive solution, which is slow and complicated, we’ll start by calculating the cumulative revenue less the costs. Calculating Change in Opening and Closing Balances
Let’s start by calculating change in balance:
Change = Revenue – Fixed Costs – Variable Costs
The SQL looks like this:
select
revenue - fixed_costs - variable_costs as change
from
transactions
Calculating Cumulative Change
Next, we want to calculate the cumulative of this number. In MySQL you can do this easily with stored variables, and in PostgreSQL you can use window functions. We’ll use a solution that works in both DBs: joining the table with itself.
with change as (
select
month
, revenue - fixed_costs - variable_costs as change
from
transactions
)
select
c1.month as month
, sum(c2.change) as cumulative_change
from
change c1
join change c2
on c1.month >= c2.month
The change CTE calculates the change between opening balance and closing balance for each month. By joining the result to itself with c1.month >= c2.month, we take a copy of all the results of the change query where the month is less than or equal to a given month, and join it to the original month in c1.
This means that for c1.month = February 2016, we join c2.month = February 2016 and January 2016. For c1.month = March 2016, we will join with the rows where c2.month = March 2016, February 2016 and January 2016.
Calculating Opening and Closing Balances
Now that we’ve got the cumulative change for each period, we’ll add that change to the opening balance to get the closing balance.
The closing balance for each period is calculated as the initial opening balance plus the change each period. The opening balance for each period is calculated as the initial opening balance plus the cumulative change in the last period.
select
cc_closing.month as month
, t1.initial_ob + cc_closing.cumulative_change as closing_balance
, t1.initial_ob + cc_opening.cumulative_change as opening balance
from
(select max(cash_on_hand_ob) as initial_ob from transactions) t1
join cumulative_change cc_closing
on true
join cumulative_change cc_opening
on cc_closing.month = cc_opening.month + interval ‘1 month’
Putting it all Together
Putting it all together, our final query looks like this
with change as (
select
month
, revenue - fixed_costs - variable_costs as change
from transactions
),
cumulative_change as (
select
c1.month as month
, sum(c2.change) as cumulative_change
from
change c1
join change c2
on c1.month >= c2.month
)
select
cc_closing.month as month
, t1.initial_ob + cc_closing.cumulative_change as closing_balance
, t1.initial_ob + cc_opening.cumulative_change as opening balance
from
(select max(cash_on_hand_ob) as initial_ob from transactions) t1
join cumulative_change cc_closing
on true
join cumulative_change cc_opening
on cc_closing.month = cc_opening.month + interval ‘1 month’
The Result
Using the final query, our results look like this:

And voilà! Now you can bring your cash flow and financial modeling to SQL when it becomes too big for your spreadsheet.