Is an amortization table posible without an for loop in R?

Question

I need to create several amortization tables. I did it, but it takes a lot because there are a lot of data. As you know R is not good with for loops, so im trying to optimize the time invested in that operation.

I have the for loop and it works

for (i in 1:nrow(df)) {
              if (df[i,"aux"]== 1) {
                     df[i,"Interest"] <-df[i,"Original_mount"] * df[i,"MonthlyRate"]
                     df[i,"Capital"] <- df[i,"instalement"]-df[i,"Interest"]
                     df[i,"final_balance"]<-df[i,"inicial_balance"] - df[i,"Capital"]
              } else {
                     df[i,"inicial_balance"]<-df[i-1,"final_balance"]
                     df[i,"Interest"] <-df[i,"inicial_balance"] * df[i,"MonthlyRate"]
                     df[i,"Capital"] <- df[i,"instalement"]-df[i,"Interest"]
                     df[i,"final_balance"]<-df[i,"inicial_balance"] - df[i,"Capital"]

and i try mutate, apply, purr and nothing works.

Mutate: because is an iterative process and mutate make all the column apply: because the output is a list Purrr: all the output is NA

Do you have any experience with this?

Answer 1

You can use known formulas for

installment: ( A * [( i * (1 + i)^n) / ((1 + i)^n - 1)]),

the first capital payment: (P_1= A * i /((1 + i)^n - 1)),

and the subsequent capital payments: (P_n = P_{n-1} * (1 + i))

This reduzes time and avoid the loop. Try this:

ii <- 0.12/12 #MonthlyRate
A <- 20000 # Original_mount
nm=60 # Months

df3 <- data.frame(n = 0:nm)
df3$final_balance  <-  df3$Capital <- df3$Interest <- df3$instalement  <- 0
df3$instalement[-1] = A*((ii*(1+ii)^nm)/((1+ii)^nm-1))
df3$Capital[2] <- ii*A/((1+ii)^nm-1)
df3$Capital[-c(1:2)] <- cumprod(1+rep(ii,nm-1))*df3$Capital[2]
df3$Interest=df3$instalement - df3$Capital
df3$final_balance = A - cumsum(df3$Capital)

Using library(microbenchmark). I call your procedure "proc1" and this alternative "proc2". The improvement is very clear!

> mbm
Unit: microseconds
  expr     min       lq      mean   median      uq     max neval cld
 proc1 19106.6 19443.70 20727.336 19893.35 21620.9 25979.4   100 a  
 proc2   168.2   199.50   217.734   211.90   233.7   422.2   100   b

> print(df3[c(1:5,(nrow(df3)-5):nrow(df3)),])
    n instalement   Interest  Capital final_balance
1   0       0.000   0.000000   0.0000  2.000000e+04
2   1     444.889 200.000000 244.8890  1.975511e+04
3   2     444.889 197.551110 247.3378  1.950777e+04
4   3     444.889 195.077732 249.8112  1.925796e+04
5   4     444.889 192.579620 252.3093  1.900565e+04
56 55     444.889  25.783435 419.1055  2.159238e+03
57 56     444.889  21.592379 423.2966  1.735941e+03
58 57     444.889  17.359414 427.5295  1.308412e+03
59 58     444.889  13.084118 431.8048  8.766070e+02
60 59     444.889   8.766070 436.1229  4.404841e+02
61 60     444.889   4.404841 440.4841  2.182787e-11