Compound interest calculation on changing balance for data.table

Question

I have a data.table which has a balance. The balance is based on deposits/withdrawals each period. Each period there is an interest rate that should be applied. However I am not able to compound the interest rate to the balances, basically applying the interest rate to the balance and then using the updated balance 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)
)

The result would be:

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 do I update and reference the updated Balance column in every period?

Answer 1

It seems like you're looking for a "cumulative sum and product," which I don't know of a way to do in R (other than, for instance, with @dynamo's for loop).

That being said, 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 a sense of the efficiency improvements of Rcpp vs. base R:

# 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