R: Calculate Rolling Balance after Sales and Instalments where each Instalment depends on previous ending balance

user_7154421 0
Viewed 73

I am trying to calculate a rolling ending balance that is affected by two types of outflows : a) One that is not calculated, its discretionary drawdown (I call it "Sales" in my example below) b) Another one that is calculated based on the outstanding balance after the above Sale happened.

For example, I have the following table :

Installment <- c(0,0,0.0645722605862428,0.067112766587962,0,0)
Amount <- c(1612233.62, 1612233.62, 1612233.62,1612233.62,1612233.62,1612233.62)
Date <- as.Date(c('2022-01-31','2022-02-28','2022-03-31','2022-04-30','2022-05-31','2022-06-30'))
Sales  <- c(0,0,444640,444640,0,0)

df <-  data.frame(Date, Amount, Installment,Sales)

[Resulting Table that Stack overflow doesn't allow me to embed the image][1] [1]: https://i.stack.imgur.com/FF5sN.png

Basically what I would like to do without entering a For-Loop, is to calculate a column called "Ending Balance" that for each date, will be derived with the below logic:

  1. Take the Opening Balance (for the first date it will be same amount of the "Amount" column in the data.frame
  2. Withdraw from that Opening Balance the Sales in the "Sales" column
  3. Calculate the Instalment by multiplying to the resulting amount from the above step, the ratio in the "Installment" column of the data.frame
  4. Calculate the Ending Balance as the simple subtraction of the resulting amount from step 2 minus the calculated Instalment in step 3 above
  5. For the next date, the opening balance will of course be the resulting amount in step 4 above

In other words, I would like to end up with the following table which I'm hardcoding here below

Installment <- c(0,0,0.0645722605862428,0.067112766587962,0,0)
Amount <- c(1612233.62, 1612233.62, 1612233.62,1612233.62,1612233.62,1612233.62)
Date <- as.Date(c('2022-01-31','2022-02-28','2022-03-31','2022-04-30','2022-05-31','2022-06-30'))
Sales  <- c(0,0,444640,444640,0,0)
Opening_Balance_After_Sales <- c(1612233.62,1612233.62,1167593.62 ,647559.46,604099.95,604099.95) 
Installment_Amount <-  c(0,0,75394.16,43459.51,0,0)
Ending_Balance <- c(1612233.62,1612233.62,1092199.46,604099.95,604099.95,604099.95)
  
df <-  data.frame(Date, Amount, Installment,Sales,Opening_Balance_After_Sales,Installment_Amount,Ending_Balance)

I am asking this question as a last chance before doing a for-loop on each date.

balance rolling-computation r
1 Answers

To capture changing states rather than static cumulative arithmetic, in base R, you may have to use for loops. Consider initializing columns than running iteration across rows:

output_df <- within(
  input_df, {
    # INITIALIZE COLUMNS
    Opening_Balance_After_Sales <- Amount
    Installment_Amount <- 0
    Ending_Balance <- Amount
    
    # ROLLING CALCULATIONS
    for(i in 2:nrow(input_df)) {
      Opening_Balance_After_Sales[i] <- Ending_Balance[i-1] - Sales[i]
      Installment_Amount[i] <- Opening_Balance_After_Sales[i] * Installment[i]
      Ending_Balance[i] <- Opening_Balance_After_Sales[i] - Installment_Amount[i]
    }
    rm(i)
  }
) |> subset(
  select = c(
    Date, Amount, Installment, Sales, Opening_Balance_After_Sales,
    Installment_Amount, Ending_Balance)
)

output_df
#         Date  Amount Installment  Sales Opening_Balance_After_Sales Installment_Amount Ending_Balance
# 1 2022-01-31 1612234  0.00000000      0                   1612233.6               0.00      1612233.6
# 2 2022-02-28 1612234  0.00000000      0                   1612233.6               0.00      1612233.6
# 3 2022-03-31 1612234  0.06457226 444640                   1167593.6           75394.16      1092199.5
# 4 2022-04-30 1612234  0.06711277 444640                    647559.5           43459.51       604099.9
# 5 2022-05-31 1612234  0.00000000      0                    604099.9               0.00       604099.9
# 6 2022-06-30 1612234  0.00000000      0                    604099.9               0.00       604099.9
user_14224510
Related