Compound interest calculation when changing the balance for data.table

Question

I have a data table that has a balance. The balance is based on deposits / withdrawals of each period. In each period there is an interest rate that should be applied. However, I cannot draw up the interest rate on the balances, mainly applying the interest rate to the balance sheet, and then using the updated balance sheet in the next period to calculate the new value.

Balance_t1 = (0 + Deposit_t1)*(1+Interest_t1) Balance_t2 = (Balance_t1 + Deposit_t2)*(1+Interest_t2) Balance_t3 = (Balance_t2 + Deposit_t3)*(1+Interest_t3) 

I have the following data.table

 dtCash <- data.table( Deposit = c(100, 100, -300, 0), Balance = c(100, 200, -100, -100), Interest=c(0.1, 0.01, 0.2, 0.1) ) 

Result:

 dtCash <- data.table( Deposit = c(100, 100, -300, 0), Balance = c(100, 200, -100, -100), Interest=c(0.1, 0.01, 0.2, 0.1), BalanceWithInterest = c(110, 212.1, -105.48, -116.028) ) 

How to update and refer to the updated "Balance" column in each period?

Answer 1

It seems that you are looking for a “cumulative sum and product” that I don’t know about the way to do in R (except, for example, with the @dynamo loop for the loop).

However, this can be done efficiently with a relatively simple Rcpp solution:

 library(Rcpp) getBalance <- cppFunction( "NumericVector getBalance(NumericVector deposit, NumericVector interest) { NumericVector result(deposit.size()); double prevResult = 0.0; for (int i=0; i < deposit.size(); ++i) { result[i] = (prevResult + deposit[i]) * (1.0 + interest[i]); prevResult = result[i]; } return result; }") Deposit <- c(100, 100, -300, 0) Interest <- c(0.1, 0.01, 0.2, 0.1) getBalance(Deposit, Interest) # [1] 110.000 212.100 -105.480 -116.028 

To give an idea of ​​the improvement in Rcpp performance over the R base:

 # Base R solution f2 = function(Deposit, Interest) { Balance <- c(0, rep(NA, length(Deposit))) for (i in 2:length(Balance)) { Balance[i] = (Balance[i-1] + Deposit[i-1]) * (1+Interest[i-1]) } return(Balance[-1]) } set.seed(144) Deposit <- runif(1000000, -1, 2) Interest = runif(1000000, 0, 0.05) system.time(getBalance(Deposit, Interest)) # user system elapsed # 0.008 0.000 0.008 system.time(f2(Deposit, Interest)) # user system elapsed # 4.701 0.008 4.730 

Answer 2

Not enough comments for comments:

Can you indicate what data you have at each point / when you want to update? Do you want to calculate, say, balance_after_interest (t) based on interest, balance (t-1) and deposits (t)?

Answer 3

Somewhat dirty answer:

 library(data.table) dtCash <- data.table( Deposit = c(100, 100, -300, 0), Balance = c(100, 200, -100, -100), Interest=c(0.1, 0.01, 0.2, 0.1) ) # Add row for t = 0 dtCash <- rbind(rep(0, ncol(dtCash)), dtCash) # Add "dummy" column for interest-accrued balance dtCash$Balance.1 <- c(0, rep(NA, nrow(dtCash)-1)) for ( i in seq(nrow(dtCash))[-1] ) { dtCash$Balance.1[i] <- (dtCash$Balance.1[i - 1] + dtCash$Deposit[i]) * (1 + dtCash$Interest[i]) } dtCash # Deposit Balance Interest Balance.1 # 1: 0 0 0.00 0.000 # 2: 100 100 0.10 110.000 # 3: 100 200 0.01 212.100 # 4: -300 -100 0.20 -105.480 # 5: 0 -100 0.10 -116.028 

Is that what you mean? It is not very effective, but it gives you what you are looking for. With some clever re-parameterization, you can get around the explicit loop.

Also, if the size of your problem is small, you can use data.frame rather than data.table . In this case, the designation will be identical. (And in this case, there is no benefit in using data.table .)

Answer 4

It seems to me that you need to pull out the data, process it using lapply () and update it. I don't think there is any vector way to do this:

 interest<-dtCash[,Interest] balance<-dtCash[,Balance] lapply(1:(length(interest)-1), # leave the last entry - nothing to add it to function(x) {balance[x+1]<<-balance[x+1]+balance[x]*interest[x]} # remember the double arrow ) # because you're in a function dtCash[,rollBal:=balance] Deposit Balance Interest rollBal 1: 100 100 0.10 100.00 2: 100 200 0.01 220.00 3: -300 -100 0.20 -95.70 4: 0 -100 0.10 -138.72