R/llmon_stats.R
In canarionyc/loanroll: Routines for Parsing and Doing Analysis on Loan Data Files from Fannie Mae
Defines functions
aggregate.llmon
make_agg1
make_agg0
if(getRversion() >= "2.15.1") {
utils::globalVariables(c( 'Acq.LCount', 'Acq_Amt.M', 'Active_UPB.M', 'Cum_Acq_Amt.M', 'D120_UPB.M',
'D150_UPB.M', 'D180plus_UPB.M', 'D30_UPB.M', 'D60_UPB.M', 'D90_UPB.M', 'D90plus_UPB.M',
'Default.LCount', 'Default.M', 'NPL.LCount', 'Prepaid.LCount', 'REO.LCount',
'RPL.LCount', 'Repaid.LCount', 'Repurchased.LCount', 'Short_Sale.LCount',
'Third_Party_Sale.LCount',
'D30.Rate',
'D60.Rate',
'D90.Rate','D90plus.Rate',
'D120.Rate',
'D150.Rate',
'D180plus.Rate',
"Cum_Prepaid.LCount","Cum_Repurchased.LCount","Cum_Short_Sale.LCount","Cum_Third_Party_Sale.LCount",
"Cum_REO.LCount","Cum_NPL.LCount","Cum_RPL.LCount"))
}
make_agg0 <- function(Data_P, by.vars){
out <- Data_P[,list(
'LCount'=.N
), keyby=by.vars, verbose=FALSE]
return(out)
}
make_agg1 <- function(Data_P, by.vars){
# browser()
out <- Data_P[,list(
"Active.LCount"=sum(is.na(ZB_DTE)),
"Active_UPB.M"=sum(ifelse(is.na(ZB_DTE), last_upb,0), na.rm = TRUE)/1e6,
'Prev_UPB.M'=sum(prev_upb, na.rm = TRUE)/1e6,
# 'Non_Int_UPB.M'=-1*sum(NON_INT_UPB, na.rm = TRUE)/1e6,
# 'Prin_Forg_UPB.M'=sum(PRIN_FORG_UPB, na.rm = TRUE)/1e6,
#
# 'Mod_IR_Cost.M'=sum(modir_cost, na.rm = TRUE)/1e6,
# 'Mod_Fb_Cost.M'=sum(modfb_cost, na.rm = TRUE)/1e6,
# 'Mod_Fg_Cost.M'=sum(modfg_cost, na.rm = TRUE)/1e6,
'D30.LCount'=sum(fifelse(Delq.Status==1, 1L,0L, na=0L)),
'D30_UPB.M'=sum(fifelse(Delq.Status==1, last_upb,0, na=0))/1e6,
# 'Active_Ever_D30_UPB.M'=sum( fifelse(max.Delq.Status>=1, last_upb,0,na=0))/1e6,
'D60.LCount'=sum(fifelse(Delq.Status==2, 1L,0L, na=0L)),
'D60_UPB.M'=sum(fifelse(Delq.Status==2, last_upb,0,0))/1e6,
'D90.LCount'=sum(fifelse(Delq.Status==3, 1L,0L, na=0L)),
'D90_UPB.M'=sum(fifelse(Delq.Status==3, last_upb,0,0))/1e6,
'D90plus.LCount'=sum(fifelse(Delq.Status>=3, 1L,0L, na=0L)),
'D90plus_UPB.M'=sum(fifelse(Delq.Status>=3, last_upb,0,0))/1e6,
# 'Active_Ever_D90_UPB.M'=sum( fifelse(max.Delq.Status=3, last_upb,0,0))/1e6,
'D120.LCount'=sum(fifelse(Delq.Status==4, 1L,0L, na=0L)),
'D120_UPB.M'=sum(fifelse(Delq.Status==4, last_upb,0,0))/1e6,
# 'Active_Ever_D120_UPB.M'=sum( fifelse(max.Delq.Status=4, last_upb,0,0))/1e6,
'D150.LCount'=sum(fifelse(Delq.Status==5, 1L,0L, na=0L)),
'D150_UPB.M'=sum(fifelse(Delq.Status==5, last_upb,0,0))/1e6,
# 'Active_Ever_D150_UPB.M'=sum( fifelse(max.Delq.Status=5, last_upb,0,0))/1e6,
'D180plus.LCount'=sum(fifelse(Delq.Status>=6, 1L,0L, na=0L)),
'D180plus_UPB.M'=sum(fifelse(Delq.Status>=6, last_upb,0, na=0))/1e6,
# 'Active_Ever_D180.LCount'=sum( fifelse(max.Delq.Status>=6, 1L,0L,0L)),
# 'Active_Ever_D180_Orig_Amt.M'=sum( fifelse(max.Delq.Status>=6, ORIG_AMT,0,0))/1e6,
# 'Active_Ever_D180_UPB.M'=sum( fifelse(max.Delq.Status>=6, last_upb,0,0))/1e6,
# 'Active_Ever_D180CE.M'=sum( ifelse(Monthly.Rpt.Prd>=D180CE_DTE, last_upb,0), na.rm = TRUE)/1e6,
# Modifications
'Active_Modified.LCount'=sum(fifelse(MOD_FLAG=="Y",1L,0L,0L)),
# 'New_Modifications.LCount'=sum(!is.na(FMOD_DTE)&Monthly.Rpt.Prd==FMOD_DTE),
# 'Cum_MOD.LCount'=sum( ifelse(Monthly.Rpt.Prd>=FMOD_DTE, 1,0), na.rm = TRUE),
# 'Cum_ModRT1.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT1_DTE,1,0), na.rm = TRUE),
# 'Cum_ModRT2.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT2_DTE,1,0), na.rm = TRUE),
# Voluntary Repaidments
'Prepaid.LCount'=sum(fifelse(Zero.Bal.Code =='01', 1L,0L,0L)),
'Prepaid_UPB.M'=sum(fifelse(Zero.Bal.Code =='01', last_upb,0,0))/1e6,
'Repurchased.LCount'=sum(fifelse(Zero.Bal.Code =='06', 1L,0L,0L)),
'Repurchased.M'=sum(fifelse(Zero.Bal.Code =='06', last_upb,0,0))/1e6,
'RPL.LCount'=sum(fifelse(Zero.Bal.Code == '16', 1L,0L,0L)),
'RPL_UPB.M'=sum(fifelse(Zero.Bal.Code == '16', last_upb,0,0))/1e6,
'Prev_Sched_Bal1.M'=sum(
fifelse(is.numeric(Prev_Sched_Bal1) & is.finite(Prev_Sched_Bal1),
Prev_Sched_Bal1, 0,0))/1e6,
'Curtailment.M'=sum(fifelse( Zero.Bal.Code == '' & is.finite(Prev_Sched_Bal1) &
Prev_Sched_Bal1>=last_upb,Prev_Sched_Bal1-last_upb,0,0))/1e6,
# Involuntary Terminations
# 'preD180CE_Orig_Amt.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT) & max.Delq.Status<6, ORIG_AMT,0,0))/1e6,
'preD180CE_UPB.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT) & max.Delq.Status<6, last_upb,0,0))/1e6
,
#
'Third_Party_Sale.LCount'=sum(fifelse(Zero.Bal.Code=='02' & !is.na(DISP_DT), 1L,0L,0L)),
'Third_Party_Sale_UPB.M'=sum(fifelse(Zero.Bal.Code=='02'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'Short_Sale.LCount' =sum(fifelse(Zero.Bal.Code=='03'& !is.na(DISP_DT), 1L,0L,0L)),
'Short_Sale_UPB.M' =sum(fifelse(Zero.Bal.Code=='03'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'REO.LCount'=sum(fifelse(Zero.Bal.Code=='09'& !is.na(DISP_DT), 1L,0L,0L)),
'REO_UPB.M'=sum(fifelse(Zero.Bal.Code=='09'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'NPL.LCount'=sum(fifelse(Zero.Bal.Code=='15'& !is.na(DISP_DT), 1L,0L,0L)),
'NPL_UPB.M'=sum(fifelse(Zero.Bal.Code=='15'& !is.na(DISP_DT), last_upb,0,0))/1e6
# ,
#
# 'CE_pipeline.LCount'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & is.na(DISP_DT), 1L,0L,0L)),
# 'CE_pipeline.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & is.na(DISP_DT), last_upb,0,0))/1e6
#
# ,
# # ,'SMM'=sum(
# # ifelse(Zero.Bal.Code %in% c('01','06','16'), last_upb,
# # ifelse(Zero.Bal.Code=='' & Prev_Sched_Bal1>=last_upb,Prev_Sched_Bal1-last_upb,0)),
# # na.rm = TRUE
# # ) / sum(
# # ifelse(Zero.Bal.Code %in% c('01','06','16'), last_upb,
# # ifelse(Zero.Bal.Code=='' ,ifelse(is.na(Prev_Sched_Bal1), ORIG_AMT,Prev_Sched_Bal1),0)),
# # na.rm = TRUE
# # )
# 'Net_Loss_Third_Party_Sale_UPB.M'=sum(ifelse(Zero.Bal.Code =='02', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_Short_Sale_UPB.M'=sum(ifelse(Zero.Bal.Code =='03', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_REO_UPB.M'=sum(ifelse(Zero.Bal.Code =='09', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_NPL_UPB.M'=sum(ifelse(Zero.Bal.Code =='15', NET_LOSS,0), na.rm=TRUE)/1e6
), keyby=by.vars, verbose=FALSE]
# str(out)
# out[, 'Cum_preD180CE_Orig_Amt.M':=cumsum(fcoalesce(preD180CE_Orig_Amt.M,0))]
# out[, 'D180CE_Orig_Amt.M':=rowSums( .SD, na.rm = TRUE), .SDcols=c('D180_Orig_Amt.M','Cum_preD180CE_Orig_Amt.M')]
out[, c('D30.Rate',
'D60.Rate',
'D90.Rate','D90plus.Rate',
'D120.Rate',
'D150.Rate',
'D180plus.Rate'):=list(
ifelse(Active_UPB.M>0,D30_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D60_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D90_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D90plus_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D120_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D150_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D180plus_UPB.M/Active_UPB.M,0)
)]
#
#
# # out[, 'Cum_Modifications.LCount':=cumsum(New_Modifications.LCount)]
#
out[,'Repaid.LCount':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Prepaid.LCount','Repurchased.LCount','RPL.LCount')][,
'Cum_Repaid.LCount':=cumsum(Repaid.LCount)]
out[,'Repaid_UPB.M':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Prepaid_UPB.M','Repurchased.M','RPL_UPB.M')]
#
# # Default ----
out[, 'Default.LCount':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Third_Party_Sale.LCount','Short_Sale.LCount','REO.LCount','NPL.LCount')][,
c('Cum_Default.LCount'):=list(cumsum(Default.LCount))]
out[, 'Default.M':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Third_Party_Sale_UPB.M','Short_Sale_UPB.M','REO_UPB.M','NPL_UPB.M')][,
"Cum_Default.M":=cumsum(Default.M)]
# out[, 'Net_Loss_UPB.M':=rowSums(.SD, na.rm = TRUE), .SDcols=c('Net_Loss_Third_Party_Sale_UPB.M','Net_Loss_Short_Sale_UPB.M','Net_Loss_REO_UPB.M','Net_Loss_NPL_UPB.M')][, c('Cum_Net_Loss_UPB.M'):=list(cumsum(Net_Loss_UPB.M))]
# out[,c('Severity'):=list( ifelse(Default.M>0,Net_Loss_UPB.M/Default.M,0))]
# out[, c('SMM_Prepaid','SMM_Repurchase','SMM_RPL','SMM_Curtailment'):=list(
# ifelse(Prev_Sched_Bal1.M>0, Prepaid_UPB.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, Repurchased.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, RPL_UPB.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, Curtailment.M/Prev_Sched_Bal1.M,0)
# )][, 'SMM' := rowSums(.SD, na.rm = TRUE),
# .SDcols=c('SMM_Prepaid','SMM_Repurchase','SMM_RPL','SMM_Curtailment')
# ]
#
# out[, c('MDR_Third_Party_Sale','MDR_Short_Sale','MDR_REO','MDR_NPL'):=list(
# ifelse(Prev_UPB.M>0,Third_Party_Sale_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,Short_Sale_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,REO_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,NPL_UPB.M/Prev_UPB.M,0)
# )][, 'MDR':= rowSums(.SD, na.rm = TRUE),
# .SDcols=c('MDR_Third_Party_Sale','MDR_Short_Sale','MDR_REO','MDR_NPL')
# ]
#
out[, c("Cum_Prepaid.LCount","Cum_Repurchased.LCount","Cum_Short_Sale.LCount","Cum_Third_Party_Sale.LCount",
"Cum_REO.LCount","Cum_NPL.LCount","Cum_RPL.LCount"):=list(
cumsum(Prepaid.LCount),
cumsum(Repurchased.LCount),
cumsum(Short_Sale.LCount),
cumsum(Third_Party_Sale.LCount),
cumsum(REO.LCount),
cumsum(NPL.LCount),
cumsum(RPL.LCount)
)]
# setattr(out$SMM, "Desc", "Single Month Mortality")
# setattr(out$MDR, "Desc", "Monthly Defaulted UPB as % of Current UPB")
# setattr(out$Active_Ever_Default.Rate, "Desc", "Cumulative Default Rate")
# # setattr(out$Cum_NET_LOSS.Rate, "Desc", "Cumulative Net LoShort_Sale Rate")
# # setattr(out$Severity, "Desc", "Severity")
#
# setattr(out$Cum_Repaid.Rate, "Desc", "Cumulative Repaid %")
# setattr(out$Cum_Repurchased.Rate, "Desc", "Cumulative Repurchase %")
return(out)
}
aggregate.llmon <- function(Data_P, Data_A, xvar='Monthly.Rpt.Prd', by.vars=NULL, verbose=FALSE)
{
# browser()
setDT(Data_P, key=c('LOAN_ID', xvar))
str(Data_P)
# Acq_Monthly ----
if(missing(Data_A)) {
Acq <- Data_P[, .SD[1], by='LOAN_ID'][, .SD, .SDcols=c(xvar, by.vars, 'last_upb')]
setnames(Acq, 'last_upb', 'ORIG_AMT')
} else {
Acq <- Data_A[Data_P[, .SD[1], by='LOAN_ID'], on='LOAN_ID'][, .SD, .SDcols=c(xvar, by.vars, 'ORIG_AMT')]
}
Acq_Monthly<- Acq[,
list('Acq.LCount'=.N, 'Acq_Amt.M'=sum(ORIG_AMT, na.rm = TRUE)/1e6),
keyby=c(xvar,by.vars)]
out <- make_agg1(Data_P, by.vars = c(xvar,by.vars))
out.1 <- merge(Acq_Monthly,out, on=c(xvar,by.vars), all.y=TRUE)
setnafill(out.1, fill=0)
# if(!is.null(by.vars)) {
# } else {
# out.1[,
# c('Acq.LCount','Acq_Amt.M'):=list(fcoalesce(Acq.LCount,0L), fcoalesce(Acq_Amt.M,0))][,
# c('Cum_Acq.LCount','Cum_Acq_Amt.M'):=list(
# cumsum(Acq.LCount),
# cumsum(Acq_Amt.M))]
# }
make_agg2 <- function(out) {
out[,
c('Acq.LCount','Acq_Amt.M'):=
list(fcoalesce(Acq.LCount,0L), fcoalesce(Acq_Amt.M,0))][,
c('Cum_Acq.LCount','Cum_Acq_Amt.M'):=list(
cumsum(Acq.LCount),
cumsum(Acq_Amt.M)), keyby=as.list(by.vars)]
out[, c('Factor'):=list( Active_UPB.M/Cum_Acq_Amt.M) ]
setattr(out$Factor, "Desc", "Factors")
# out[, c( 'D180CE_of_Orig_Amt.Rate','Cum_Repaid.Rate','Cum_Repurchased.Rate',
# 'Cum_Default.Rate'
# # ,'Cum_NET_LOSS.Rate'
# ) := list(
#
# D180CE_Orig_Amt.M/Cum_Acq_Amt.M,
# cumsum(Repaid_UPB.M)/Cum_Acq_Amt.M,
# cumsum(Repurchased.M)/Cum_Acq_Amt.M,
#
# Cum_Default.M/Cum_Acq_Amt.M
# # ,
# # Cum_Net_Loss_UPB.M/Cum_Acq_Amt.M
# )]
#
return(invisible(out))
}
make_agg2(out.1)
if( xvar=='Monthly.Rpt.Prd' ) {
out.1[, Monthly.Rpt.Prd:=as.Date(Monthly.Rpt.Prd)]
if(verbose) {
print(out.1, digits=2, nrows=1)
}
}
rm(Acq, Acq_Monthly,out)
setcolorder(out.1, c(xvar, by.vars,
'Acq.LCount', 'Cum_Acq.LCount',"Acq_Amt.M","Cum_Acq_Amt.M",
"Active.LCount", "Active_UPB.M", "Factor" ,
'Active_Modified.LCount',
"Prev_UPB.M" , 'Prev_Sched_Bal1.M'
# , "Prev_Sched_Bal1.M",
#
# "D30_UPB.M" , "D30.Rate" ,
# "D60_UPB.M" , "D60.Rate" ,
# "D90_UPB.M" , "D90.Rate",
# "D120_UPB.M" , "D120.Rate" ,
# "D150_UPB.M", "D150.Rate",
# "D180_UPB.M" , "D180.Rate"
# # ,
# #
# # "Prepaid_UPB.M", "SMM_Prepaid" ,
# # "Repurchased.M", "SMM_Repurchase" , "Cum_Repurchased.Rate",
# # "RPL_UPB.M", "SMM_RPL" ,
# # "Curtailment.M", "SMM_Curtailment" ,
# #
# # "Third_Party_Sale_UPB.M" ,"MDR_Third_Party_Sale",
# # "Short_Sale_UPB.M" , "MDR_Short_Sale" ,
# # "REO_UPB.M", "MDR_REO" ,
# # "NPL_UPB.M" , "MDR_NPL" ,
# #
# # "Repaid_UPB.M" , "SMM" ,"Cum_Repaid.Rate" ,
# # "Default.M" , "MDR" , "Active_Ever_Default.M", "Active_Ever_Default.Rate"
))
setkeyv(out.1, c(xvar, by.vars))
llmon_stats <- out.1
cols.1 <- grep(".M", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.1):=lapply(.SD, round, 1), .SDcols=cols.1]
cols.2 <- grep(".Rate", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.2):=lapply(.SD, round, 2), .SDcols=cols.2]
cols.6 <- grep("Factor", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.6):=lapply(.SD, signif, 6), .SDcols=cols.6]
return(out.1)
}
canarionyc/loanroll documentation built on Sept. 7, 2020, 4:50 a.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Defines functions aggregate.llmon make_agg1 make_agg0
if(getRversion() >= "2.15.1") {
utils::globalVariables(c( 'Acq.LCount', 'Acq_Amt.M', 'Active_UPB.M', 'Cum_Acq_Amt.M', 'D120_UPB.M',
'D150_UPB.M', 'D180plus_UPB.M', 'D30_UPB.M', 'D60_UPB.M', 'D90_UPB.M', 'D90plus_UPB.M',
'Default.LCount', 'Default.M', 'NPL.LCount', 'Prepaid.LCount', 'REO.LCount',
'RPL.LCount', 'Repaid.LCount', 'Repurchased.LCount', 'Short_Sale.LCount',
'Third_Party_Sale.LCount',
'D30.Rate',
'D60.Rate',
'D90.Rate','D90plus.Rate',
'D120.Rate',
'D150.Rate',
'D180plus.Rate',
"Cum_Prepaid.LCount","Cum_Repurchased.LCount","Cum_Short_Sale.LCount","Cum_Third_Party_Sale.LCount",
"Cum_REO.LCount","Cum_NPL.LCount","Cum_RPL.LCount"))
}
make_agg0 <- function(Data_P, by.vars){
out <- Data_P[,list(
'LCount'=.N
), keyby=by.vars, verbose=FALSE]
return(out)
}
make_agg1 <- function(Data_P, by.vars){
# browser()
out <- Data_P[,list(
"Active.LCount"=sum(is.na(ZB_DTE)),
"Active_UPB.M"=sum(ifelse(is.na(ZB_DTE), last_upb,0), na.rm = TRUE)/1e6,
'Prev_UPB.M'=sum(prev_upb, na.rm = TRUE)/1e6,
# 'Non_Int_UPB.M'=-1*sum(NON_INT_UPB, na.rm = TRUE)/1e6,
# 'Prin_Forg_UPB.M'=sum(PRIN_FORG_UPB, na.rm = TRUE)/1e6,
#
# 'Mod_IR_Cost.M'=sum(modir_cost, na.rm = TRUE)/1e6,
# 'Mod_Fb_Cost.M'=sum(modfb_cost, na.rm = TRUE)/1e6,
# 'Mod_Fg_Cost.M'=sum(modfg_cost, na.rm = TRUE)/1e6,
'D30.LCount'=sum(fifelse(Delq.Status==1, 1L,0L, na=0L)),
'D30_UPB.M'=sum(fifelse(Delq.Status==1, last_upb,0, na=0))/1e6,
# 'Active_Ever_D30_UPB.M'=sum( fifelse(max.Delq.Status>=1, last_upb,0,na=0))/1e6,
'D60.LCount'=sum(fifelse(Delq.Status==2, 1L,0L, na=0L)),
'D60_UPB.M'=sum(fifelse(Delq.Status==2, last_upb,0,0))/1e6,
'D90.LCount'=sum(fifelse(Delq.Status==3, 1L,0L, na=0L)),
'D90_UPB.M'=sum(fifelse(Delq.Status==3, last_upb,0,0))/1e6,
'D90plus.LCount'=sum(fifelse(Delq.Status>=3, 1L,0L, na=0L)),
'D90plus_UPB.M'=sum(fifelse(Delq.Status>=3, last_upb,0,0))/1e6,
# 'Active_Ever_D90_UPB.M'=sum( fifelse(max.Delq.Status=3, last_upb,0,0))/1e6,
'D120.LCount'=sum(fifelse(Delq.Status==4, 1L,0L, na=0L)),
'D120_UPB.M'=sum(fifelse(Delq.Status==4, last_upb,0,0))/1e6,
# 'Active_Ever_D120_UPB.M'=sum( fifelse(max.Delq.Status=4, last_upb,0,0))/1e6,
'D150.LCount'=sum(fifelse(Delq.Status==5, 1L,0L, na=0L)),
'D150_UPB.M'=sum(fifelse(Delq.Status==5, last_upb,0,0))/1e6,
# 'Active_Ever_D150_UPB.M'=sum( fifelse(max.Delq.Status=5, last_upb,0,0))/1e6,
'D180plus.LCount'=sum(fifelse(Delq.Status>=6, 1L,0L, na=0L)),
'D180plus_UPB.M'=sum(fifelse(Delq.Status>=6, last_upb,0, na=0))/1e6,
# 'Active_Ever_D180.LCount'=sum( fifelse(max.Delq.Status>=6, 1L,0L,0L)),
# 'Active_Ever_D180_Orig_Amt.M'=sum( fifelse(max.Delq.Status>=6, ORIG_AMT,0,0))/1e6,
# 'Active_Ever_D180_UPB.M'=sum( fifelse(max.Delq.Status>=6, last_upb,0,0))/1e6,
# 'Active_Ever_D180CE.M'=sum( ifelse(Monthly.Rpt.Prd>=D180CE_DTE, last_upb,0), na.rm = TRUE)/1e6,
# Modifications
'Active_Modified.LCount'=sum(fifelse(MOD_FLAG=="Y",1L,0L,0L)),
# 'New_Modifications.LCount'=sum(!is.na(FMOD_DTE)&Monthly.Rpt.Prd==FMOD_DTE),
# 'Cum_MOD.LCount'=sum( ifelse(Monthly.Rpt.Prd>=FMOD_DTE, 1,0), na.rm = TRUE),
# 'Cum_ModRT1.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT1_DTE,1,0), na.rm = TRUE),
# 'Cum_ModRT2.LCount'=sum(ifelse(Monthly.Rpt.Prd>=ModRT2_DTE,1,0), na.rm = TRUE),
# Voluntary Repaidments
'Prepaid.LCount'=sum(fifelse(Zero.Bal.Code =='01', 1L,0L,0L)),
'Prepaid_UPB.M'=sum(fifelse(Zero.Bal.Code =='01', last_upb,0,0))/1e6,
'Repurchased.LCount'=sum(fifelse(Zero.Bal.Code =='06', 1L,0L,0L)),
'Repurchased.M'=sum(fifelse(Zero.Bal.Code =='06', last_upb,0,0))/1e6,
'RPL.LCount'=sum(fifelse(Zero.Bal.Code == '16', 1L,0L,0L)),
'RPL_UPB.M'=sum(fifelse(Zero.Bal.Code == '16', last_upb,0,0))/1e6,
'Prev_Sched_Bal1.M'=sum(
fifelse(is.numeric(Prev_Sched_Bal1) & is.finite(Prev_Sched_Bal1),
Prev_Sched_Bal1, 0,0))/1e6,
'Curtailment.M'=sum(fifelse( Zero.Bal.Code == '' & is.finite(Prev_Sched_Bal1) &
Prev_Sched_Bal1>=last_upb,Prev_Sched_Bal1-last_upb,0,0))/1e6,
# Involuntary Terminations
# 'preD180CE_Orig_Amt.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT) & max.Delq.Status<6, ORIG_AMT,0,0))/1e6,
'preD180CE_UPB.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & !is.na(DISP_DT) & max.Delq.Status<6, last_upb,0,0))/1e6
,
#
'Third_Party_Sale.LCount'=sum(fifelse(Zero.Bal.Code=='02' & !is.na(DISP_DT), 1L,0L,0L)),
'Third_Party_Sale_UPB.M'=sum(fifelse(Zero.Bal.Code=='02'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'Short_Sale.LCount' =sum(fifelse(Zero.Bal.Code=='03'& !is.na(DISP_DT), 1L,0L,0L)),
'Short_Sale_UPB.M' =sum(fifelse(Zero.Bal.Code=='03'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'REO.LCount'=sum(fifelse(Zero.Bal.Code=='09'& !is.na(DISP_DT), 1L,0L,0L)),
'REO_UPB.M'=sum(fifelse(Zero.Bal.Code=='09'& !is.na(DISP_DT), last_upb,0,0))/1e6,
'NPL.LCount'=sum(fifelse(Zero.Bal.Code=='15'& !is.na(DISP_DT), 1L,0L,0L)),
'NPL_UPB.M'=sum(fifelse(Zero.Bal.Code=='15'& !is.na(DISP_DT), last_upb,0,0))/1e6
# ,
#
# 'CE_pipeline.LCount'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & is.na(DISP_DT), 1L,0L,0L)),
# 'CE_pipeline.M'=sum( fifelse( Zero.Bal.Code %in% CreditEvents & is.na(DISP_DT), last_upb,0,0))/1e6
#
# ,
# # ,'SMM'=sum(
# # ifelse(Zero.Bal.Code %in% c('01','06','16'), last_upb,
# # ifelse(Zero.Bal.Code=='' & Prev_Sched_Bal1>=last_upb,Prev_Sched_Bal1-last_upb,0)),
# # na.rm = TRUE
# # ) / sum(
# # ifelse(Zero.Bal.Code %in% c('01','06','16'), last_upb,
# # ifelse(Zero.Bal.Code=='' ,ifelse(is.na(Prev_Sched_Bal1), ORIG_AMT,Prev_Sched_Bal1),0)),
# # na.rm = TRUE
# # )
# 'Net_Loss_Third_Party_Sale_UPB.M'=sum(ifelse(Zero.Bal.Code =='02', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_Short_Sale_UPB.M'=sum(ifelse(Zero.Bal.Code =='03', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_REO_UPB.M'=sum(ifelse(Zero.Bal.Code =='09', NET_LOSS,0), na.rm=TRUE)/1e6,
# 'Net_Loss_NPL_UPB.M'=sum(ifelse(Zero.Bal.Code =='15', NET_LOSS,0), na.rm=TRUE)/1e6
), keyby=by.vars, verbose=FALSE]
# str(out)
# out[, 'Cum_preD180CE_Orig_Amt.M':=cumsum(fcoalesce(preD180CE_Orig_Amt.M,0))]
# out[, 'D180CE_Orig_Amt.M':=rowSums( .SD, na.rm = TRUE), .SDcols=c('D180_Orig_Amt.M','Cum_preD180CE_Orig_Amt.M')]
out[, c('D30.Rate',
'D60.Rate',
'D90.Rate','D90plus.Rate',
'D120.Rate',
'D150.Rate',
'D180plus.Rate'):=list(
ifelse(Active_UPB.M>0,D30_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D60_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D90_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D90plus_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D120_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D150_UPB.M/Active_UPB.M,0),
ifelse(Active_UPB.M>0,D180plus_UPB.M/Active_UPB.M,0)
)]
#
#
# # out[, 'Cum_Modifications.LCount':=cumsum(New_Modifications.LCount)]
#
out[,'Repaid.LCount':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Prepaid.LCount','Repurchased.LCount','RPL.LCount')][,
'Cum_Repaid.LCount':=cumsum(Repaid.LCount)]
out[,'Repaid_UPB.M':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Prepaid_UPB.M','Repurchased.M','RPL_UPB.M')]
#
# # Default ----
out[, 'Default.LCount':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Third_Party_Sale.LCount','Short_Sale.LCount','REO.LCount','NPL.LCount')][,
c('Cum_Default.LCount'):=list(cumsum(Default.LCount))]
out[, 'Default.M':=rowSums(.SD, na.rm = TRUE),
.SDcols=c('Third_Party_Sale_UPB.M','Short_Sale_UPB.M','REO_UPB.M','NPL_UPB.M')][,
"Cum_Default.M":=cumsum(Default.M)]
# out[, 'Net_Loss_UPB.M':=rowSums(.SD, na.rm = TRUE), .SDcols=c('Net_Loss_Third_Party_Sale_UPB.M','Net_Loss_Short_Sale_UPB.M','Net_Loss_REO_UPB.M','Net_Loss_NPL_UPB.M')][, c('Cum_Net_Loss_UPB.M'):=list(cumsum(Net_Loss_UPB.M))]
# out[,c('Severity'):=list( ifelse(Default.M>0,Net_Loss_UPB.M/Default.M,0))]
# out[, c('SMM_Prepaid','SMM_Repurchase','SMM_RPL','SMM_Curtailment'):=list(
# ifelse(Prev_Sched_Bal1.M>0, Prepaid_UPB.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, Repurchased.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, RPL_UPB.M/Prev_Sched_Bal1.M,0),
# ifelse(Prev_Sched_Bal1.M>0, Curtailment.M/Prev_Sched_Bal1.M,0)
# )][, 'SMM' := rowSums(.SD, na.rm = TRUE),
# .SDcols=c('SMM_Prepaid','SMM_Repurchase','SMM_RPL','SMM_Curtailment')
# ]
#
# out[, c('MDR_Third_Party_Sale','MDR_Short_Sale','MDR_REO','MDR_NPL'):=list(
# ifelse(Prev_UPB.M>0,Third_Party_Sale_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,Short_Sale_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,REO_UPB.M/Prev_UPB.M,0),
# ifelse(Prev_UPB.M>0,NPL_UPB.M/Prev_UPB.M,0)
# )][, 'MDR':= rowSums(.SD, na.rm = TRUE),
# .SDcols=c('MDR_Third_Party_Sale','MDR_Short_Sale','MDR_REO','MDR_NPL')
# ]
#
out[, c("Cum_Prepaid.LCount","Cum_Repurchased.LCount","Cum_Short_Sale.LCount","Cum_Third_Party_Sale.LCount",
"Cum_REO.LCount","Cum_NPL.LCount","Cum_RPL.LCount"):=list(
cumsum(Prepaid.LCount),
cumsum(Repurchased.LCount),
cumsum(Short_Sale.LCount),
cumsum(Third_Party_Sale.LCount),
cumsum(REO.LCount),
cumsum(NPL.LCount),
cumsum(RPL.LCount)
)]
# setattr(out$SMM, "Desc", "Single Month Mortality")
# setattr(out$MDR, "Desc", "Monthly Defaulted UPB as % of Current UPB")
# setattr(out$Active_Ever_Default.Rate, "Desc", "Cumulative Default Rate")
# # setattr(out$Cum_NET_LOSS.Rate, "Desc", "Cumulative Net LoShort_Sale Rate")
# # setattr(out$Severity, "Desc", "Severity")
#
# setattr(out$Cum_Repaid.Rate, "Desc", "Cumulative Repaid %")
# setattr(out$Cum_Repurchased.Rate, "Desc", "Cumulative Repurchase %")
return(out)
}
aggregate.llmon <- function(Data_P, Data_A, xvar='Monthly.Rpt.Prd', by.vars=NULL, verbose=FALSE)
{
# browser()
setDT(Data_P, key=c('LOAN_ID', xvar))
str(Data_P)
# Acq_Monthly ----
if(missing(Data_A)) {
Acq <- Data_P[, .SD[1], by='LOAN_ID'][, .SD, .SDcols=c(xvar, by.vars, 'last_upb')]
setnames(Acq, 'last_upb', 'ORIG_AMT')
} else {
Acq <- Data_A[Data_P[, .SD[1], by='LOAN_ID'], on='LOAN_ID'][, .SD, .SDcols=c(xvar, by.vars, 'ORIG_AMT')]
}
Acq_Monthly<- Acq[,
list('Acq.LCount'=.N, 'Acq_Amt.M'=sum(ORIG_AMT, na.rm = TRUE)/1e6),
keyby=c(xvar,by.vars)]
out <- make_agg1(Data_P, by.vars = c(xvar,by.vars))
out.1 <- merge(Acq_Monthly,out, on=c(xvar,by.vars), all.y=TRUE)
setnafill(out.1, fill=0)
# if(!is.null(by.vars)) {
# } else {
# out.1[,
# c('Acq.LCount','Acq_Amt.M'):=list(fcoalesce(Acq.LCount,0L), fcoalesce(Acq_Amt.M,0))][,
# c('Cum_Acq.LCount','Cum_Acq_Amt.M'):=list(
# cumsum(Acq.LCount),
# cumsum(Acq_Amt.M))]
# }
make_agg2 <- function(out) {
out[,
c('Acq.LCount','Acq_Amt.M'):=
list(fcoalesce(Acq.LCount,0L), fcoalesce(Acq_Amt.M,0))][,
c('Cum_Acq.LCount','Cum_Acq_Amt.M'):=list(
cumsum(Acq.LCount),
cumsum(Acq_Amt.M)), keyby=as.list(by.vars)]
out[, c('Factor'):=list( Active_UPB.M/Cum_Acq_Amt.M) ]
setattr(out$Factor, "Desc", "Factors")
# out[, c( 'D180CE_of_Orig_Amt.Rate','Cum_Repaid.Rate','Cum_Repurchased.Rate',
# 'Cum_Default.Rate'
# # ,'Cum_NET_LOSS.Rate'
# ) := list(
#
# D180CE_Orig_Amt.M/Cum_Acq_Amt.M,
# cumsum(Repaid_UPB.M)/Cum_Acq_Amt.M,
# cumsum(Repurchased.M)/Cum_Acq_Amt.M,
#
# Cum_Default.M/Cum_Acq_Amt.M
# # ,
# # Cum_Net_Loss_UPB.M/Cum_Acq_Amt.M
# )]
#
return(invisible(out))
}
make_agg2(out.1)
if( xvar=='Monthly.Rpt.Prd' ) {
out.1[, Monthly.Rpt.Prd:=as.Date(Monthly.Rpt.Prd)]
if(verbose) {
print(out.1, digits=2, nrows=1)
}
}
rm(Acq, Acq_Monthly,out)
setcolorder(out.1, c(xvar, by.vars,
'Acq.LCount', 'Cum_Acq.LCount',"Acq_Amt.M","Cum_Acq_Amt.M",
"Active.LCount", "Active_UPB.M", "Factor" ,
'Active_Modified.LCount',
"Prev_UPB.M" , 'Prev_Sched_Bal1.M'
# , "Prev_Sched_Bal1.M",
#
# "D30_UPB.M" , "D30.Rate" ,
# "D60_UPB.M" , "D60.Rate" ,
# "D90_UPB.M" , "D90.Rate",
# "D120_UPB.M" , "D120.Rate" ,
# "D150_UPB.M", "D150.Rate",
# "D180_UPB.M" , "D180.Rate"
# # ,
# #
# # "Prepaid_UPB.M", "SMM_Prepaid" ,
# # "Repurchased.M", "SMM_Repurchase" , "Cum_Repurchased.Rate",
# # "RPL_UPB.M", "SMM_RPL" ,
# # "Curtailment.M", "SMM_Curtailment" ,
# #
# # "Third_Party_Sale_UPB.M" ,"MDR_Third_Party_Sale",
# # "Short_Sale_UPB.M" , "MDR_Short_Sale" ,
# # "REO_UPB.M", "MDR_REO" ,
# # "NPL_UPB.M" , "MDR_NPL" ,
# #
# # "Repaid_UPB.M" , "SMM" ,"Cum_Repaid.Rate" ,
# # "Default.M" , "MDR" , "Active_Ever_Default.M", "Active_Ever_Default.Rate"
))
setkeyv(out.1, c(xvar, by.vars))
llmon_stats <- out.1
cols.1 <- grep(".M", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.1):=lapply(.SD, round, 1), .SDcols=cols.1]
cols.2 <- grep(".Rate", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.2):=lapply(.SD, round, 2), .SDcols=cols.2]
cols.6 <- grep("Factor", names(llmon_stats), fixed = TRUE, value=TRUE)
llmon_stats[, (cols.6):=lapply(.SD, signif, 6), .SDcols=cols.6]
return(out.1)
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.