account roll-up with begin and end values using lag() or similar

I need a query that will have an annual "begin_year" value, a few calculations to get the "end_year" value, and then the end_year value becomes the begin_year value for the next record.

In this simplified example, I am just incrementing by 10% each year. There are a lot more calculations to be done, I am simplifying to focus on the task to get the End_Year to become the next year's begin_year value...

Year   Begin Value    End Value
  1         100          110      (each end-value is a 10% increase from the begin value)
  2         110          121
  3         121          133.1
  4         133.1        146.4
 ... etc ...

One of my many attempts to have the Begin_Value be a lag() of the End_value causes a catch-22 where the end_value is not defined because it is defined after the BeginValue definition. When the begin and end values rely on each other, it seems impossible to do?

(the DatesAndNumbers table is just a table with records, 0, 1, 2, 3, 4...10 )

select dan.theNumber   the_year 
     , beginValue.amount as Begin_Balance
     , endValue.amount   as End_Balance
 from datesAndNumbers dan
  outer apply (
   select case when dan.theNumber =  0 then 100
               else lag(endValue.amount, 1, 100) over (order by dan.theNumber) 
          end amount 
  )  beginValue
  outer apply (
     select beginValue.amount * 1.10 as amount 
  )  endValue
where dan.theNumber between 0 and 10
order by dan.theNumber

I have tried many attempts, I am hoping to do this in one query, not a cursor or while loop. Looking closely at lag() to pull down the previous record's end-value, however, the end-value is calculated based on the begin-value, thus creating what seems to be an impossible dependency.

This is SQL Server 2018

Solution

Try this example

create table test (yn int,change_pct float,change_val float);
insert into test values
 (1,10,11.1)
,(2,10,12.2)
,(3,10,13.3)
,(4,10,14.4)
,(5,10,-15.5)
,(6,10,-16.6)
;
with r as (
  select yn,cast(100.0 as float) as begin_value,change_pct,change_val
     ,100*(1+change_pct/100)+change_val as end_value
  from test where yn=1
  union all
  select r.yn+1 yn
     ,r.end_value as begin_value
     ,t.change_pct,t.change_val
     ,r.end_value*(1+t.change_pct/100)+t.change_val as end_value
  from r inner join test t on t.yn=(r.yn+1)
)
select * from r;

Result

yn	begin_value	change_pct	change_val	end_value
1	100	10	11.1	121.1
2	121.1	10	12.2	145.41
3	145.41	10	13.3	173.251
4	173.251	10	14.4	204.9761
5	204.9761	10	-15.5	209.97371
6	209.97371	10	-16.6	214.371081

Fiddle here