R语言 如何计算新的付款清单

axkjgtzd  于 2023-06-19  发布在  其他
关注(0)|答案(1)|浏览(78)

首先，很抱歉再次提出同样的问题。
我想将宽限期添加到固定利息计算方法的偿还表中，以便可以收到宽限期的利息金额。
Dataframe 1：这是正常的场景。

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

在dataframe 1中，我尝试使用status = 1提供从最后一个日期起的下一个x个月的宽限期（在dataframe 1中，这是emi_date = '01/03/2020'）
余额计算：

  • 行1 = 124 - 10.333 = 113.67，
  • 行2及以后=平衡行1- emi = 113.67 - 10.333

所需的结果应该如下所示：

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

平衡计算（第1行）= 124 - 10.333 =113.67，第2行，并向前直到状态（1）=平衡第1行（113.67）-emi（10.333）
为了添加宽限期，我们将下两个月emi设为0。这两个月的余额计算为01/04/2020 = amt（100）* 利息（2%）+以前的余额（93.00333），01/05/2020 =（amt（100）* 利息（2%）+（amt（100）* 利息（2%）*2%+以前的余额（95.00333）
剩余余额的计算将保持原样（例如：上一个余额- emi）直到余额< emi，如果余额< emi，我们将把emi中的余额结转到下个月，并将该月的余额保持为0。
P的。
为了示例的目的，我已经为一个uid创建了dataframe，真实的上我在dataframe中有大约10000个唯一的uid。
输入Dput：

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))

uid = KII-62的行1余额将是（amt * 利息 * 保有期）+amt，对于uid = KII-63的行1余额将重复相同的操作
第1行余额（KII-62）：
（4，70，000 *0.02）-28983.33（emi）= 450416.66666667

library(tidyverse)
startingbalance <- 124
period <- "1 month"
dataframe1 %>% 
  mutate(index = seq(1,nrow(.))) %>%
  mutate(emi_date = dmy(emi_date)) %>%
  mutate(emi = case_when(status - lag(status) < 0 ~ 0, status - lag(status,2L) < 0 ~ 0, TRUE ~ emi)) %>%
  mutate(balance = case_when(index == 1 ~ startingbalance - emi,
                                index > 1 & emi > 0 & status == 1 ~ lag(balance) - emi,
                                index > 1 & emi == 0 & lag(status) == 1 & lag(status,2L) == 1 ~ lag(balance) + (amt * (as.integer(gsub("%","",interest))) / 100),
                                index > 1 & emi == 0 & lag(status) == 0 & lag(status,2L) == 1 ~ lag(balance,2L) + 2 * (amt * (as.integer(gsub("%","",interest))) / 100),
                                TRUE ~ NaN)) %>%
  select(-index) %>%
  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)) %>% 
  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)) %>%
  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)) %>%
  mutate(balance =  {ind <- which(is.nan(balance)); for(i in ind){balance[i] <- balance[i-1] - emi[i]}; balance}) %>%
  mutate(emi = case_when(balance < 0 ~ lag(balance), TRUE ~ emi),
         balance = case_when(balance < 0 ~ 0, TRUE ~ balance))
r

1条答案

按热度按时间
a8jjtwal

a8jjtwal1#

library(tidyverse)

df <-  tibble( # the dataframe, as a tibble, with a few unnecessary columns removed for clarity
  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 =  as.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"), format = "%d/%m/%Y"),
  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 = rep(2, 36),
    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), 
    grace_period = 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))

# split the data into two dataframes by uid (keeping different user data together makes it very difficult to use)
df1 <- df %>% filter(uid == "KII-62")
df2 <- df %>% filter(uid == "KII-63")

# create a function to calculate the balance and clean the tibble:
clean_df <- function(df) {
  df <- df %>% 
    mutate(
      emi = ifelse(grace_period == 1, 0, emi), # if grace period, pay back nothing
      net_change = (amt * interest / 100) - emi,
      month = row_number()
    )
# create a row which is the initial payment at month 0
  first_row <- df %>%  
    slice(1) %>% 
    mutate(
      net_change = amt,
      month = 0
    )

  df <- df %>% 
    add_row(first_row, .before = 1) %>% # join the first row and the rest of the tibble together
    select(-amt, -interest, -grace_period, -emi) %>% # remove columns we've used and now have no need for
    mutate(balance = cumsum(net_change))

  return(df)
}

df1 <- clean_df(df1)
df2 <- clean_df(df2)
赞(0）分享  回复(0）举报  2023-06-19
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com