r - 用R中的宽限日和月偿还计算
问题描述
首先,很抱歉再次发布相同的问题。
我想将宽限期添加到固定利息计算方法的还款表中,以便可以在宽限期内收到利息金额。
dataframe1:这是正常情况。
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
从最后一个日期开始的接下来几个月的宽限期status = 1
(在dataframe 1中,这是emi_date = '01/03/2020'
)
余额计算:
- 第 1 行 = 124 - 10.333 = 113.67,
- 第 2 行及以后 = 余额 row1 - 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
余额计算 (row1) = 124 - 10.333 =113.67, row2 及以后直到状态 (1) = 余额 row1(113.67)-emi(10.333)
要添加宽限期,我们将接下来的两个月emi
设为 0。这两个月的余额计算将是01/04/2020
= amt(100)*interest(2%) + 之前的余额 (93.00333) 和01/05/2020
= (amt(100)*interest (2%))+(amt(100)*利息(2%))*2%+ 之前的余额 (95.00333)
剩余余额计算将保持原样(例如,以前的余额 - emi)直到余额 < emi,如果余额 < emi,我们会将 emi 中的余额结转到下个月,并在该月保持余额 0。
PS - 使用的兴趣方法是平坦的,为了减少我正在尝试构建的逻辑,如果 SO 也可以帮助我,这将是有帮助的。
出于示例目的,我已经为一个uid
真实的数据框创建了数据框,我在数据框中有约 10000 个唯一的 uid。
输入输出:
dataframe1<- 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 * interest * tenure)+amt,对于 uid = KII-63 的第 1 行余额将重复同样的操作
第 1 行余额 (KII-62):(4,70,000*0.02*24)+4,70,000 = 6,96,500
感谢 SO,下面是我用于计算的代码,但在 2-3 个月结束时得到一个负值,请帮助解决问题。
library(tidyverse)
library (lubridate) # for dmy()
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))
解决方案
推荐阅读
- java - 多个Fragment中的Activity调用接口
- extjs - 如何翻译 ExtJS 中的项目?
- redis - 从 Redis 数据库中提取一些信息
- ios - 我在 swift 中的多行标签不起作用,该短语在一行中超出标签的大小
- asp.net - 修改 Azure DevOps Web 配置
- python - 如何使用 pvlib 的 pvwatts 模型计算有效辐照度?
- python - 我想在我的数据框中将 1-May-19 和 5/1/2019 转换为 1/5/2019
- python - 如何使用 AdaBoostClassifier 中的概率获得预测类别?
- javascript - 在jquery post请求中传递数组不起作用
- r - 如何仅捕获R中字符串的一部分?