R中的滚动计算 [英] Rolling calculations in R

问题描述

我试图使用数据表在R中建模一个分期偿还贷款帐户。每行代表一个月。

例如：

 > loan<  -  data.table（loan.age = seq（0：9），payment = c（5000，-rep（100,9）））
  

我看不到添加利息和余额列的简单方法。对于每月1％的利率，利息应为上一个月的余额的0.01倍，新的余额应为前一个月的余额加上当前月份的利息加上当前月份的付款（通常为负数）。在伪代码中：

  this_balance = last_balance + last_balance * 0.01 + this_payment 
  

这是我要查找的结果：

  ;贷款
贷款利息余额
 1：0 5000 0.00 5000.00 
 2：1 -100 50.00 4950.00 
 3：2 -100 49.50 4899.50 
 4：3 -100 48.99 4848.49 
 5：4 -100 48.49 4796.98 
 6：5 -100 47.97 4744.95 
 7：6 -100 47.45 4692.40 
 8：7 -100 46.92 4639.32 
 9：8 -100 46.39 4585.71 
 10：9 -100 45.87 4531.58 
  

可以用for循环来解决问题，但是对于一个大的组合，它是不可行的。对于上下文，300个月的贷款使用年金公式比一个for循环（每贷款2s vs 20ms）快100倍。

我试过加入表本身，我也尝试了shift（）函数，这是新的数据表1.9.5，但我不能制定出一种强制计算从顶部到底部执行的方式，使新的平衡波动下来表。

我的代码目前使用从年金公式派生的一些财务数学计算每个期间的余额，但如果利率变化部分，这种方法将不起作用。通过贷款。

感谢。

编辑：我计算这个问题解决了。解决方案2b）从
G. Grothendieck下面给出一个通用的解决方案，这两个都适用于这个简单的情况，可以扩展到更复杂的场景。

解决方案

1）尝试过滤：

  loan [，c（interest，balance）：= 0] [，
 balance：= c（filter（payment，1.01，method =recursive）） 
 interest：= c（0，diff（balance） -  payment [-1]）] 
  

给出：

 >贷款
 loan.age付款利息余额
 1：1 5000 0.00000 5000.000 
 2：2 -100 50.00000 4950.000 
 3：3 -100 49.50000 4899.500 
 4：4 -100 48.99500 4848.495 
 5：5 -100 48.48495 4796.980 
 6：6 -100 47.96980 4744.950 
 7：7 -100 47.44950 4692.399 
 8：8 -100 46.92399 4639.323 
 9：9 -100 46.39323 4585.716 
 10：10 -100 45.85716 4531.574 
  

strong>注意：最后一行可以写成：

  interest：= .01 * c余额[ - 。N]）] 
  

或在开发版本的data.table：

  interest：= .01 * shift（balance，fill = 0）[[1]]] 
  
 
 
 
  2）减少：另一种可能性是：
  f < - 功能（余额，付款）付款+ 1.01 *余额
 
贷款[，c（interest，balance）： 
 balance：= Reduce（f，payment，accumulate = TRUE）] [，
 interest：= c（0，diff（balance） -  payment [-1]）] 
   
 
 
  2a）列，并且已知除了第一个值（如问题）， payment 是常数，则以下工作。注意，我们已经使 rate 列为常数，但是下面的表达式仍然有效，即使 rate 常数付款持有：
  loan $ rate<  -  .01 
g < - 功能（余额，利率）贷款$ payment [2] +（1 +利率）*余额
 
贷款[，c 0] [，
 balance：= Reduce（g，rate [-1]，init = payment [1]，accumulate = TRUE） -  payment [-1]）] 
  
  2b） code>付款和费率变化：
 code> loan $ rate<  -  .01 
h<  -  function（balance，i）loan $ payment [i] +（1 + loan $ rate [i]）* balance 
 
 loan [，c（interest，balance）：= 0] [，
 balance：= Reduce（h，.I [-1]，init = payment [1]，accumulate = TRUE ）] [，
 interest：= c（0，diff（balance） -  payment [-1]）] 
  
 
I'm trying to model an amortising loan account in R using data table. Each row represents one month. Each month there is a repayment or an advance of funds, and an interest charge based on the previous month's balance.

For example:
> loan <- data.table(loan.age = seq(0:9), payment = c(5000, -rep(100,9)))
I can't see an easy way of adding the interest and balance columns. For an interest rate of 1% per month, the interest should be 0.01 times the previous month's balance, and the new balance should be the previous month's balance plus the current month's interest plus the current month's payment (which is normally negative). In pseudocode:
this_balance = last_balance + last_balance * 0.01 + this_payment
This is the result I'm looking for:
> loan
    loan.age payment interest balance
 1:        0    5000     0.00 5000.00
 2:        1    -100    50.00 4950.00
 3:        2    -100    49.50 4899.50
 4:        3    -100    48.99 4848.49
 5:        4    -100    48.49 4796.98
 6:        5    -100    47.97 4744.95
 7:        6    -100    47.45 4692.40
 8:        7    -100    46.92 4639.32
 9:        8    -100    46.39 4585.71
10:        9    -100    45.87 4531.58
I can solve the problem with a for loop, but it's unworkably slow over a large portfolio. For context, a 300 month loan is 100 times faster using the annuity formula than  a for loop (2s per loan vs 20ms).

I've tried joining the table to itself and I've also tried the shift() function that's new in Data Table 1.9.5, but I can't work out a way of forcing the calculations to be carried out from top to bottom so that the new balance ripples down the table.

My code currently works out the balance in each period using some financial maths derived from the annuity formula, but that approach won't work if interest rates change part way through the loan.

Thanks.

Edit: I count this problem as solved. Solution 2b) from 
G. Grothendieck below gives a generic solution that both works for this simple case and can be expanded to more complex scenarios.
解决方案
1) Try filter:
loan[, c("interest", "balance") := 0][, 
     balance := c(filter(payment, 1.01, method = "recursive"))][,
     interest := c(0, diff(balance) - payment[-1])]
giving:
> loan
    loan.age payment interest  balance
 1:        1    5000  0.00000 5000.000
 2:        2    -100 50.00000 4950.000
 3:        3    -100 49.50000 4899.500
 4:        4    -100 48.99500 4848.495
 5:        5    -100 48.48495 4796.980
 6:        6    -100 47.96980 4744.950
 7:        7    -100 47.44950 4692.399
 8:        8    -100 46.92399 4639.323
 9:        9    -100 46.39323 4585.716
10:       10    -100 45.85716 4531.574
Note: The last line could alternately be written:
         interest := .01 * c(0, balance[-.N])]
or in the development version of data.table:
         interest := .01 * shift(balance, fill = 0)[[1]]]
2) Reduce  Another possibility is:
f <- function(balance, payment) payment + 1.01 * balance

loan[, c("interest", "balance") := 0][, 
     balance := Reduce(f, payment, accumulate = TRUE) ][,
     interest := c(0, diff(balance) - payment[-1]) ]
2a) or if there is a rate column and it is known that payment is constant except for the first value (as in the question) then the following works. Note that we have made the rate column constant but the following still works even if rate varies as long as the assumption of a constant payment holds:
loan$rate <- .01
g <- function(balance, rate) loan$payment[2] + (1 + rate) * balance

loan[, c("interest", "balance") := 0][, 
     balance := Reduce(g, rate[-1], init = payment[1], accumulate = TRUE) ][,
     interest := c(0, diff(balance) - payment[-1]) ]
2b) This allows both payment and rate to vary:
loan$rate <- .01
h <- function(balance, i) loan$payment[i] + (1 + loan$rate[i]) * balance

loan[, c("interest", "balance") := 0][, 
     balance := Reduce(h, .I[-1], init = payment[1], accumulate = TRUE) ][,
     interest := c(0, diff(balance) - payment[-1]) ]


                        
这篇关于R中的滚动计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！