title: repay calculation with grace days and month in R
body: <p>First of all, apologies for posting the same question again.</p>
  
  <p>I want to add the grace period to a repay table for the flat interest calculation method such that the interest amount can be received for the grace period.</p>
  
  <p>dataframe1: This is the normal scenario.</p>
  
  <pre><code>uid     emi_date    amt interest    tenure  emi      Rep_seq    status  balance
  KII-453 01/01/2020  100 2%          12      10.33333    1          1    113.67
  KII-453 01/02/2020  100 2%          12      10.33333    2          1    103.3367
  KII-453 01/03/2020  100 2%          12      10.33333    3          1    93.00333
  KII-453 01/04/2020  100 2%          12      10.33333    4          0    82.67
  KII-453 01/05/2020  100 2%          12      10.33333    5          0    72.33667
  KII-453 01/06/2020  100 2%          12      10.33333    6          0    62.00333
  KII-453 01/07/2020  100 2%          12      10.33333    7          0    51.67
  KII-453 01/08/2020  100 2%          12      10.33333    8          0    41.33667
  KII-453 01/09/2020  100 2%          12      10.33333    9          0    31.00333
  KII-453 01/10/2020  100 2%          12      10.33333    10         0    20.67
  KII-453 01/11/2020  100 2%          12      10.33333    11         0    10.33667
  KII-453 01/12/2020  100 2%          12      10.33333    12         0    0.003333
  </code></pre>
  
  <p>From the dataframe1, I'm trying to provide the grace period for the next <code>x</code> months  from the last date with <code>status = 1</code> (In dataframe 1 this is <code>emi_date = '01/03/2020'</code>)</p>
  
  <p>balance calculation:</p>
  
  <ul>
  <li>row 1 = 124 - 10.333 = 113.67, </li>
  <li>row 2 and onwards = balance row1 - emi = 113.67 - 10.333</li>
  </ul>
  
  <p>The desired result should look like this:</p>
  
  <pre><code>uid     emi_date    amt interest    tenure  emi       rep_seq   status  balance
  KII-453 01/01/2020  100   2%          12    10.33333    1         1     113.67
  KII-453 01/02/2020  100   2%          12    10.33333    2         2     103.3367
  KII-453 01/03/2020  100   2%          12    10.33333    3         3     93.00333
  KII-453 01/04/2020  100   2%          12    0           4         0     95.00333
  KII-453 01/05/2020  100   2%          12    0           5         0     97.04333
  KII-453 01/06/2020  100   2%          12    10.33333    6         0     86.71
  KII-453 01/07/2020  100   2%          12    10.33333    7         0     76.37667
  KII-453 01/08/2020  100   2%          12    10.33333    8         0     66.04333
  KII-453 01/09/2020  100   2%          12    10.33333    9         0     55.71
  KII-453 01/10/2020  100   2%          12    10.33333    10        0     45.37667
  KII-453 01/11/2020  100   2%          12    10.33333    11        0     35.04333
  KII-453 01/12/2020  100   2%          12    10.33333    12        0     24.71
  KII-453 01/01/2021  100   2%          12    10.33333    13        0     14.37667
  KII-453 01/02/2021  100   2%          12    10.33333    14        0     4.043333
  KII-453 01/03/2021  100   2%          12    4.043333    15        0     0
  </code></pre>
  
  <p>balance calculation (row1) = 124 - 10.333 =113.67, row2 and onward till status (1) = balance row1(113.67)-emi(10.333)</p>
  
  <p>To add grace period, we are next two months <code>emi</code> as 0. and balance calculation for those two months will be for <code>01/04/2020</code> = amt(100)*interest(2%) + previous balance (93.00333) and for <code>01/05/2020</code> = (amt(100)*interest(2%))+(amt(100)*interest(2%))*2%+ previous balance (95.00333)</p>
  
  <p>The rest balance calculation will be as it is (For Eg. Previous balance - emi) till the balance is &lt; emi, if the balance &lt; emi we will carry forward that balance in emi to next month and keep balance 0 for that month. </p>
  
  <p>P.S - Interest method used is flat, for reducing i'm trying to build the logic it would be helpful if SO can help me for that as well. </p>
  
  <p>For the example purpose i have created the dataframe for one <code>uid</code> in real i have ~10000 unique uid in dataframe.</p>
  
  <p>Input Dput:</p>
  
  <pre><code>   dataframe1&lt;- structure(list(uid = c("KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-62", "KII-62", "KII-62", 
  "KII-62", "KII-63", "KII-63", "KII-63", 
  "KII-63", "KII-63", "KII-63", "KII-63", 
  "KII-63", "KII-63", "KII-63", "KII-63", 
  "KII-63"), emi_date = c("05/12/2019", "05/01/2020", "05/02/2020", 
  "05/03/2020", "05/04/2020", "05/05/2020", "05/06/2020", "05/07/2020", 
  "05/08/2020", "05/09/2020", "05/10/2020", "05/11/2020", "05/12/2020", 
  "05/01/2021", "05/02/2021", "05/03/2021", "05/04/2021", "05/05/2021", 
  "05/06/2021", "05/07/2021", "05/08/2021", "05/09/2021", "05/10/2021", 
  "05/11/2021", "05/12/2019", "05/01/2020", "05/02/2020", "05/03/2020", 
  "05/04/2020", "05/05/2020", "05/06/2020", "05/07/2020", "05/08/2020", 
  "05/09/2020", "05/10/2020", "05/11/2020"), amt = c(470000, 470000, 
  470000, 470000, 470000, 470000, 470000, 470000, 470000, 470000, 
  470000, 470000, 470000, 470000, 470000, 470000, 470000, 470000, 
  470000, 470000, 470000, 470000, 470000, 470000, 220000, 220000, 
  220000, 220000, 220000, 220000, 220000, 220000, 220000, 220000, 
  220000, 220000), interest = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
  2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 
  2, 2, 2, 2), tenure = c(24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 
  24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 24, 12, 12, 
  12, 12, 12, 12, 12, 12, 12, 12, 12, 12), emi = c(28983.33, 28983.33, 
  28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 
  28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 
  28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 28983.33, 
  28983.33, 22733.33, 22733.33, 22733.33, 22733.33, 22733.33, 22733.33, 
  22733.33, 22733.33, 22733.33, 22733.33, 22733.33, 22733.33), 
      Rep_seq = c("1", "2", "3", "4", "5", "6", "7", "8", "9", 
      "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", 
      "20", "21", "22", "23", "24", "1", "2", "3", "4", "5", "6", 
      "7", "8", "9", "10", "11", "12"), status = c(1L, 1L, 1L, 
      1L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
      0L, 0L, 0L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L, 0L, 
      0L, 0L, 0L), balance = c(450416.666666667, 430833.333333333, 
      411250, 391666.666666667, 372083.333333333, 352500, 332916.666666667, 
      313333.333333333, 293750, 274166.666666667, 254583.333333333, 
      235000, 215416.666666667, 195833.333333333, 176250, 156666.666666667, 
      137083.333333333, 117500, 97916.6666666667, 78333.3333333333, 
      58750, 39166.6666666667, 19583.3333333333, 8e-28, 201666.666666667, 
      183333.333333333, 165000, 146666.666666667, 128333.333333333, 
      110000, 91666.6666666667, 73333.3333333333, 55000, 36666.6666666667, 
      18333.3333333333, 4e-28)), .Names = c("uid", "emi_date", 
  "amt", "interest", "tenure", "emi", "Rep_seq", "status", "balance"
  ), class = "data.frame", row.names = c(NA, 36L))
  </code></pre>
  
  <p>Row 1 balance for uid = KII-62 would be (amt * interest * tenure)+amt, the same will repeat for Row 1 balance for uid = KII-63</p>
  
  <p>Row 1 Balance (KII-62):
  (4,70,000*0.02*24)+4,70,000 = 6,96,500</p>
  
  <p>Thanks to SO, below is the code that I'm using for the calculation but getting a negative value at the end of the 2-3 months, please help in resolve the issue.</p>
  
  <pre><code>library(tidyverse)
  library (lubridate) # for dmy()
  startingbalance &lt;- 124
  period &lt;- "1 month"
  dataframe1 %&gt;% 
    mutate(index = seq(1,nrow(.))) %&gt;%
    mutate(emi_date = dmy(emi_date)) %&gt;%
    mutate(emi = case_when(status - lag(status) &lt; 0 ~ 0, status - lag(status,2L) &lt; 0 ~ 0, TRUE ~ emi)) %&gt;%
    mutate(balance = case_when(index == 1 ~ startingbalance - emi,
                                  index &gt; 1 &amp; emi &gt; 0 &amp; status == 1 ~ lag(balance) - emi,
                                  index &gt; 1 &amp; emi == 0 &amp; lag(status) == 1 &amp; lag(status,2L) == 1 ~ lag(balance) + (amt * (as.integer(gsub("%","",interest))) / 100),
                                  index &gt; 1 &amp; emi == 0 &amp; lag(status) == 0 &amp; lag(status,2L) == 1 ~ lag(balance,2L) + 2 * (amt * (as.integer(gsub("%","",interest))) / 100),
                                  TRUE ~ NaN)) %&gt;%
    select(-index) %&gt;%
    do(add_row(., uid = .$uid[nrow(.)],emi_date = .$emi_date[nrow(.)] + period(period), amt = .$amt[nrow(.)],interest = .$interest[nrow(.)],tenure = .$tenure[nrow(.)],emi = .$emi[nrow(.)],status = .$status[nrow(.)],Rep_seq = .$Rep_seq[nrow(.)] + 1,balance = NaN)) %&gt;% 
    do(add_row(., uid = .$uid[nrow(.)],emi_date = .$emi_date[nrow(.)] + period(period), amt = .$amt[nrow(.)],interest = .$interest[nrow(.)],tenure = .$tenure[nrow(.)],emi = .$emi[nrow(.)],status = .$status[nrow(.)],Rep_seq = .$Rep_seq[nrow(.)] + 1,balance = NaN)) %&gt;%
    do(add_row(., uid = .$uid[nrow(.)],emi_date = .$emi_date[nrow(.)] + period(period), amt = .$amt[nrow(.)],interest = .$interest[nrow(.)],tenure = .$tenure[nrow(.)],emi = .$emi[nrow(.)],status = .$status[nrow(.)],Rep_seq = .$Rep_seq[nrow(.)] + 1,balance = NaN)) %&gt;%
    mutate(balance =  {ind &lt;- which(is.nan(balance)); for(i in ind){balance[i] &lt;- balance[i-1] - emi[i]}; balance}) %&gt;%
    mutate(emi = case_when(balance &lt; 0 ~ lag(balance), TRUE ~ emi),
           balance = case_when(balance &lt; 0 ~ 0, TRUE ~ balance))
  </code></pre>