UPDATE #2

The answer can be found here: https://stackoverflow.com/questions/60766448/update-1-how-to-refer-to-the-previous-value-of-a-computed-column-in-sql-serve

I'll post as an answer here as well so I can close this question with an answer that hopefully can help others in the future.

I was lucky enough to find people willing to help me. Thank you @Squirrel and @Nick from StackOverflow!

I updated the fiddle with observations made by @Squirrel in the comments: http://sqlfiddle.com/#!18/e0ef2c/2

UPDATE #1

I was able to get some evolution in my case with a user defined scalar function:

create function [dbo].[tpx]
(
    @qx numeric(15, 10)
)
returns numeric(15, 10)
as
begin

    declare @tpx as numeric(15, 10) = 1;

    return @tpx * ( 1 - @qx );

end;

I was not able to update my fiddle for some reason, but my query is now like this:

use GESTAO_ATUARIAL;

select
case when row_number() over (order by id) = 1 then
    1
else
    lag(tpx, 1, 1) over (order by id) * (1 - lag(qx, 1) over (order by id))
end as tpx_final
from (
    select
        t.id,
        t.ben,
        t.mensalizacao,
        t.qx,
        (lag(dbo.tpx(qx), 1, 1) over (order by id)) as tpx,
        t.vx,
        t.valor_ben
    from (
        select
            aux.Id as id,
            dbo.CalcularIdade(aux.[DataNascimento], getdate()) + row_number() over (order by aux.Id) - 1 as idade,
            case when aux.Sexo = 'M' and aux.CondicaoParticipacao = 'N' then
                cast(mrt.Masculino as numeric(7,5))
            when aux.Sexo = 'M' and aux.CondicaoParticipacao = 'S' then
                inv.Masculino
            when aux.Sexo = 'F' and aux.CondicaoParticipacao = 'S' then
                inv.Feminino
            else
                mrt.Feminino
            end as qx,

            case when (row_number() over (order by aux.Id)) = 1 then
                1
            else
                (1 / power( 1 + .03370, row_number() over (order by aux.Id) - 1))
            end as vx,
            case when aux.Sexo = 'M' then
                men.FatorMensalizacaoMasculino
            else
                men.FatorMensalizacaoFeminino
            end as mensalizacao,
            cast(aux.ValorBeneficio * ( 1 + 0.03284) as numeric(10,2)) as valor_ben,
            aux.TipoBeneficio as ben

        from Mortalidade mrt
        left join AuxilioAlimentacaoParticipantes aux
        on mrt.Idade >= dbo.CalcularIdade(aux.[DataNascimento], getdate())

        left join MortalidadeInvalidos inv
        on mrt.Idade = inv.Idade

        left join FatorMensalizacao men
        on mrt.Idade = men.Idade

        where aux.Id = 1
    ) as t
) as s

But the results are still a little off, the first three rows are okay, but the following rows present completely wrong results.

At this point I do not really know if this is the correct approach to solve this problem in SQL Server.

Results:

What it should be:

ORIGINAL

I am trying to transfer my IAS 19 tool that calculates the obligations of the company with its employees' benefits from MS Excel to MS SQL Server.

Yesterday, I achieved significant progress, but when I thought I had succeeded completely, I've gotten stuck in a problem that needs a little more experience than what I have with SQL Server.

In order to calculate the obligations according to IAS 19, we have to use life tables which shows, for each age, the probability "that a person of that age will die before his or her next birthday" (Wikipedia). Those probabilities are segregated by male or female.

I have a table with the data of the participants of some benefit plan and also have the life table with the q(x) calculated for each age from 0 to 120 years of age.

Basically, when I need to calculate the obligations for a person, let's say, aged 54 I have to grow this person older in the simulation from 54 to 120. If the person is 25, it goes from 25 to 120. To summarize, I have to make the person go from their current age to the last age of a life table (Let me know if I am not being clear enough here).

I achieved this result by making a LEFT JOIN with the life table to the left. To make the age increment by one I used the following, so the age would not be frozen in the current age:

dbo.ComputeAge(bp.[BirthDate], getdate()) + row_number() over (order by bp.Id) - 1 as age

I am not able to calculate for everybody on my database (~15,000 rows) at once. I didn't pass to this stage yet.

My problem is that I also must compute a variable called tpx which depends directly on its previous value and the previous value of q(x).

For the first row, independently of the age of the beneficiary, tpx is always 1, then it is calculated using it's previous value and the previous value of q(x).

Let's give an example:

Male person aged 54: q(x), according to the life table, is 0.0025568.

In the year 2020, tpx is 1. In the year 2021, tpx is tpx[t-1 = 2020] * ( 1- q(x)[t-1 = 2020])

My first attempt of calculating tpx was

case when (row_number() over (order by id)) = 1 then
    1
else
    lag(tpx, 1)  over (order by id) * (1 - lag(qx, 1) over (order by id))
end as tpx

I prepared a fiddle for this: http://sqlfiddle.com/#!18/bae0a/6

Also, sorry for my bad English.