文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >R中宽限期月份的Emi计算

问R中宽限期月份的Emi计算
EN

Stack Overflow用户
提问于 2020-04-03 13:54:05
回答 1查看 150关注 0票数 6

从我之前的问题中，我已经尝试简化计算。我将以下变量保存在我的数据框中。

dataframe1:这是正常的场景。

代码语言：javascript
复制
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

在dataframe1中，我尝试提供下一个x月的宽限期(其中x=2个月，但也可以以天为单位，这样我希望保持它的可配置性)，从上一个状态=1开始(对于上面的数据帧，它是emi_date = 01/03/2020)

余额计算(row1) =113.67- 10.333 =113.67，row2及以上= balance row1(113.67)-emi(10.333)

输出所需的dataframe2：

代码语言：javascript
复制
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

余额计算(row1) =113.67- 10.333 =113.67，余额及结转状态(1) =余额row1(113.67)-emi(10.333)

为了增加宽限期，我们将下两个月的emi设置为0。这两个月的余额计算方法为：(amt(100)*interest(2%))+(amt(100)*interest(2%))*2%+ = 01/04/2020 (93.00333) *利息(2%)+前一余额(93.00333)，01/05/2020 =amt前一余额(95.00333)

剩余余额的计算将按原样进行(例如。以前的余额- emi)直到余额< emi，如果余额< emi，我们将结转该emi中的余额到下个月，并将该月的余额保持为0。

P.S - Interest方法使用的是扁平的，为了简化，我正在尝试建立逻辑，如果这样也可以帮助我。

出于示例目的，我实际为一个uid创建了数据帧，在数据帧中有大约10000个唯一的uid。

输入Dput：

代码语言：javascript
复制
    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.666666667

r
dataframe
ggplot2
dplyr
tidyverse
EN

回答 1

Stack Overflow用户

发布于 2020-04-03 16:07:07

这是一个完全依赖于tidyverse的解决方案。

代码语言：javascript
复制
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))
#       uid   emi_date amt interest tenure      emi Rep_seq status   balance
#1  KII-453 2020-01-01 100       2%     12 10.33333       1      1 113.66667
#2  KII-453 2020-02-01 100       2%     12 10.33333       2      1 103.33667
#3  KII-453 2020-03-01 100       2%     12 10.33333       3      1  93.00337
#4  KII-453 2020-04-01 100       2%     12  0.00000       4      0  95.00333
#5  KII-453 2020-05-01 100       2%     12  0.00000       5      0  97.00333
#6  KII-453 2020-06-01 100       2%     12 10.33333       6      0  86.67000
#7  KII-453 2020-07-01 100       2%     12 10.33333       7      0  76.33667
#8  KII-453 2020-08-01 100       2%     12 10.33333       8      0  66.00334
#9  KII-453 2020-09-01 100       2%     12 10.33333       9      0  55.67001
#10 KII-453 2020-10-01 100       2%     12 10.33333      10      0  45.33668
#11 KII-453 2020-11-01 100       2%     12 10.33333      11      0  35.00335
#12 KII-453 2020-12-01 100       2%     12 10.33333      12      0  24.67002
#13 KII-453 2021-01-01 100       2%     12 10.33333      13      0  14.33669
#14 KII-453 2021-02-01 100       2%     12 10.33333      14      0   4.00336
#15 KII-453 2021-03-01 100       2%     12  4.00336      15      0   0.00000

我不得不分解并使用一个自定义函数来重新计算余额，添加新行的过程非常混乱。如有任何改进建议，我们将不胜感激。

Data

代码语言：javascript
复制
dataframe1 <- structure(list(uid = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L), .Label = "KII-453", class = "factor"), emi_date = structure(1:12, .Label = c("01/01/2020", 
"01/02/2020", "01/03/2020", "01/04/2020", "01/05/2020", "01/06/2020", 
"01/07/2020", "01/08/2020", "01/09/2020", "01/10/2020", "01/11/2020", 
"01/12/2020"), class = "factor"), amt = c(100L, 100L, 100L, 100L, 
100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L), interest = structure(c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "2%", class = "factor"), 
    tenure = c(12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 
    12L, 12L), emi = c(10.33333, 10.33333, 10.33333, 10.33333, 
    10.33333, 10.33333, 10.33333, 10.33333, 10.33333, 10.33333, 
    10.33333, 10.33333), Rep_seq = 1:12, status = c(1L, 1L, 1L, 
    0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L), balance = c(113.67, 
    103.3367, 93.00333, 82.67, 72.33667, 62.00333, 51.67, 41.33667, 
    31.00333, 20.67, 10.33667, 0.003333)), class = "data.frame", row.names = c(NA, 
-12L))
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/61005836

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档