All investments linked to loans with loan hooks and loan bridges if merge_type == invest2loan
Format
Invest-loan roster with hooks and bridges:
- thres_inv_mgap
Month gap allowed to merge investment jump together as one investment
- thres_inv_dfds
Standard deviation threshold for counting investment jump
- hhid_Num
household id
- ivars
Capital-asset variable for finding investment jumps
- hh_inv_asset_ctr
Unique household + capital-asset investment counter
- hh_inv_ctr
Unique household investment span counter across
ivars- mth_inv_start
Start time of investment span
- mth_inv_end
End time of investment span
- capital_prior
Capital-asset level prior to investment
- capital_end
Capital-asset level after investment
- capital_invest
Level of investment, difference of end and prior
- hh_loan_id_nd
Non-duplicate loan id, sequential for all loans
- loan_start
Loan start month
- loan_end
Loan end month
- number_indi_loan
Number of individual loans combined to generate a non-duplicate loan
- forinfm4
Name of categorical variable defining loan group types
- loan_principal_interest
Loan principal and interests aggregating over individual loans with the same dates and lender type
- loan_principal
loan_principal_interestbut without interests- loan_interest_monthly
monthly interest rate given aggregated interests and principals
- merge_type
string equalling to
invest2loanorloan2invest.invest2loanbased on invest file left-joined by loan file.loan2investis non-duplicate loan file left-joined by investment file. The file stacks results from both types of files. Stacking is possible due to the datasets sharing subsets of variable names.- hh_loan_id_nd_paired_1t2
non-duplicate loan id, sequential for all loans, middle loan of bridge, loan set B
- hh_loan_id_nd_paired_2t3
non-duplicate loan id, sequential for all loans, bottom loan of bridge, loan set C
- loan_start_paired_1t2
loan start month, middle loan of bridge
- loan_end_paired_1t2
loan end month, middle loan of bridge
- loan_start_paired_2t3
loan start month, bottom loan of bridge
- loan_end_paired_2t3
loan end month, bottom loan of bridge
- forinfm4_paired_1t2
name of categorical variable defining loan group types, middle loan of bridge
- forinfm4_paired_2t3
name of categorical variable defining loan group types, bottom loan of bridge
- loan_principal_paired_1t2
loan principal aggregating over individual loans with the same dates and lender type, middle loan of bridge
- loan_principal_paired_2t3
loan principal aggregating over individual loans with the same dates and lender type, bottom loan of bridge
- loan_interest_monthly_paired_1t2
monthly interest rate given aggregated interests and principals, middle loan of bridge
- loan_interest_monthly_paired_2t3
monthly interest rate given aggregated interests and principals, bottom loan of bridge
- ll_gw1
GW1: loan A gap
- ll_gw2
GW2: loan B gap
- ll_gw3
GW3: loan C gap
- bl_lender_type
Lender type requirement, to be a bridge loan, by definition loan A and loan B can not be from the same lender, and loan B and loan C can not be from the same lender. Filter out loans that can not possibly be bridge, because it is from the same lender type in connected pieces.
if_else((forinfm4_1t2 != forinfm4_paired_1t2) & (forinfm4_paired_1t2 != forinfm4_paired_2t3), TRUE, FALSE))- bl_bridge_informal
Lender type informal as bridge.
bl_bridge_informal = if_else(forinfm4_1t2 %in% c("BAAC-Commercial", "Village-Fund") & forinfm4_paired_1t2 %in% c("Informal", "Quasi-formal") & forinfm4_paired_2t3 %in% c("BAAC-Commercial", "Village-Fund"), TRUE, FALSE))- bl_loan_size
Loan size based filter, relative loan size filter, bridge loan size is smaller than the loans it is connecting together.
bl_loan_size = if_else((loan_principal_1t2 >= loan_principal_paired_1t2) & (loan_principal_paired_2t3 >= loan_principal_paired_1t2), TRUE, FALSE))- bl_loan_dura_a
Duration a requirement.
if_else((ll_gw1 > ll_gw2) & (ll_gw3 > ll_gw2), TRUE, FALSE))- bl_loan_dura_b
Duration b requirement.
bl_loan_dura_b = if_else((ll_gw1 >= 11) & (ll_gw3 >= 11), TRUE, FALSE)- bl_loan_dura_c
Duration c requirement.
if_else(ll_gap > (ll_gab + ll_grv)*fl_approach_rela_bridge, TRUE, FALSE))
Examples
library(dplyr)
library(tidyr)
data(tstm_roster_invest_loan_linked)
summary(tstm_roster_invest_loan_linked)
#> thres_inv_mgap thres_inv_dfsd hhid_Num ivars
#> Min. :2 Min. :2.33 Min. : 70201 Length:248043
#> 1st Qu.:2 1st Qu.:2.33 1st Qu.:271339 Class :character
#> Median :2 Median :2.33 Median :490407 Mode :character
#> Mean :2 Mean :2.33 Mean :394996
#> 3rd Qu.:2 3rd Qu.:2.33 3rd Qu.:490635
#> Max. :2 Max. :2.33 Max. :531026
#> NA's :88080 NA's :88080
#> hh_inv_asset_ctr hh_inv_ctr mth_inv_start mth_inv_end
#> Min. :1.00 Min. : 1.00 Min. : 1.0 Min. : 1.00
#> 1st Qu.:1.00 1st Qu.: 2.00 1st Qu.: 56.0 1st Qu.: 56.00
#> Median :2.00 Median : 3.00 Median : 79.0 Median : 79.00
#> Mean :2.06 Mean : 3.74 Mean : 78.7 Mean : 78.75
#> 3rd Qu.:3.00 3rd Qu.: 5.00 3rd Qu.:103.0 3rd Qu.:103.00
#> Max. :9.00 Max. :16.00 Max. :160.0 Max. :160.00
#> NA's :88080 NA's :88080 NA's :88080 NA's :88080
#> capital_prior capital_end capital_invest hh_loan_id_nd
#> Min. : 0 Min. : 67 Min. : 40 Min. : 1
#> 1st Qu.: 18686 1st Qu.: 56620 1st Qu.: 14697 1st Qu.: 6617
#> Median : 97535 Median : 166532 Median : 38641 Median :11275
#> Mean : 690183 Mean : 793447 Mean : 103265 Mean :10462
#> 3rd Qu.: 462487 3rd Qu.: 611324 3rd Qu.: 79045 3rd Qu.:14240
#> Max. :141177726 Max. :141635206 Max. :11577376 Max. :19587
#> NA's :88080 NA's :88080 NA's :88080 NA's :2859
#> loan_start loan_end number_indi_loan forinfm4
#> Min. : 0.00 Min. : 0.00 Min. : 1.000 Length:248043
#> 1st Qu.: 52.00 1st Qu.: 65.00 1st Qu.: 1.000 Class :character
#> Median : 77.00 Median : 89.00 Median : 1.000 Mode :character
#> Mean : 77.31 Mean : 90.52 Mean : 1.079
#> 3rd Qu.:103.00 3rd Qu.:116.00 3rd Qu.: 1.000
#> Max. :160.00 Max. :160.00 Max. :11.000
#> NA's :2859 NA's :2859 NA's :2859
#> loan_principal_interest loan_principal loan_interest_monthly
#> Min. : 0 Min. : 0 Min. :0.000
#> 1st Qu.: 4200 1st Qu.: 4000 1st Qu.:0.002
#> Median : 14040 Median : 13000 Median :0.004
#> Mean : 25549 Mean : 23943 Mean : Inf
#> 3rd Qu.: 21800 3rd Qu.: 20000 3rd Qu.:0.006
#> Max. :2309000 Max. :2000000 Max. : Inf
#> NA's :9505 NA's :11927 NA's :12183
#> merge_type hh_loan_id_nd_paired_1t2 hh_loan_id_nd_paired_2t3
#> Length:248043 Min. : 4 Min. : 5
#> Class :character 1st Qu.: 6737 1st Qu.: 6750
#> Mode :character Median :11297 Median :11330
#> Mean :10507 Mean :10537
#> 3rd Qu.:14241 3rd Qu.:14279
#> Max. :19587 Max. :19587
#> NA's :9534 NA's :14589
#> loan_start_paired_1t2 loan_end_paired_1t2 loan_start_paired_2t3
#> Min. : 0.00 Min. : 0.00 Min. : 0.00
#> 1st Qu.: 62.00 1st Qu.: 75.00 1st Qu.: 72.00
#> Median : 86.00 Median : 99.00 Median : 95.00
#> Mean : 86.38 Mean : 99.98 Mean : 96.09
#> 3rd Qu.:113.00 3rd Qu.:126.00 3rd Qu.:122.00
#> Max. :160.00 Max. :160.00 Max. :160.00
#> NA's :9534 NA's :9534 NA's :14589
#> loan_end_paired_2t3 forinfm4_paired_1t2 forinfm4_paired_2t3
#> Min. : 0.0 Length:248043 Length:248043
#> 1st Qu.: 83.0 Class :character Class :character
#> Median :107.0 Mode :character Mode :character
#> Mean :107.6
#> 3rd Qu.:134.0
#> Max. :160.0
#> NA's :14589
#> loan_principal_paired_1t2 loan_principal_paired_2t3
#> Min. : 0 Min. : 0
#> 1st Qu.: 5000 1st Qu.: 4688
#> Median : 15000 Median : 15000
#> Mean : 24210 Mean : 23967
#> 3rd Qu.: 20000 3rd Qu.: 20000
#> Max. :2000000 Max. :2000000
#> NA's :25069 NA's :39292
#> loan_interest_monthly_paired_1t2 loan_interest_monthly_paired_2t3
#> Min. :0.000 Min. :0.00
#> 1st Qu.:0.002 1st Qu.:0.00
#> Median :0.004 Median :0.00
#> Mean : Inf Mean : Inf
#> 3rd Qu.:0.006 3rd Qu.:0.01
#> Max. : Inf Max. : Inf
#> NA's :25280 NA's :39347
#> ll_gw1 ll_gw2 ll_gw3 bl_lender_type
#> Min. : 0.00 Min. : 0.0 Min. : 0.0 Mode :logical
#> 1st Qu.:10.00 1st Qu.:11.0 1st Qu.: 9.0 FALSE:133490
#> Median :12.00 Median :12.0 Median :12.0 TRUE :105019
#> Mean :12.63 Mean :13.6 Mean :11.5 NA's :9534
#> 3rd Qu.:12.00 3rd Qu.:13.0 3rd Qu.:12.0
#> Max. :48.00 Max. :48.0 Max. :48.0
#> NA's :9534 NA's :9534 NA's :14589
#> bl_bridge_informal bl_loan_size bl_loan_dura_a bl_loan_dura_b
#> Mode :logical Mode :logical Mode :logical Mode :logical
#> FALSE:206360 FALSE:123999 FALSE:194371 FALSE:111982
#> TRUE :32149 TRUE :86262 TRUE :44138 TRUE :126527
#> NA's :9534 NA's :37782 NA's :9534 NA's :9534
#>
#>
#>
#> bl_loan_dura_c
#> Mode :logical
#> FALSE:148428
#> TRUE :85026
#> NA's :14589
#>
#>
#>
print(tstm_roster_invest_loan_linked)
#> # A tibble: 248,043 × 41
#> thres_inv_mgap thres_inv_dfsd hhid_Num ivars hh_inv_asset_ctr hh_inv_ctr
#> <dbl> <dbl> <dbl> <chr> <dbl> <dbl>
#> 1 2 2.33 70203 agg_BS_10… 2 2
#> 2 2 2.33 70203 agg_BS_10… 2 2
#> 3 2 2.33 70203 agg_BS_10… 2 2
#> 4 2 2.33 70203 agg_BS_10… 2 2
#> 5 2 2.33 70203 agg_BS_10… 2 2
#> 6 2 2.33 70203 agg_BS_10… 2 2
#> 7 2 2.33 70203 agg_BS_10… 2 2
#> 8 2 2.33 70203 agg_BS_10… 2 2
#> 9 2 2.33 70203 agg_BS_10… 2 2
#> 10 2 2.33 70203 agg_BS_10… 2 2
#> # ℹ 248,033 more rows
#> # ℹ 35 more variables: mth_inv_start <dbl>, mth_inv_end <dbl>,
#> # capital_prior <dbl>, capital_end <dbl>, capital_invest <dbl>,
#> # hh_loan_id_nd <dbl>, loan_start <dbl>, loan_end <dbl>,
#> # number_indi_loan <dbl>, forinfm4 <chr>, loan_principal_interest <dbl>,
#> # loan_principal <dbl>, loan_interest_monthly <dbl>, merge_type <chr>,
#> # hh_loan_id_nd_paired_1t2 <dbl>, hh_loan_id_nd_paired_2t3 <dbl>, …
tstm_roster_invest_loan_linked %>% group_by(merge_type) %>% tally()
#> # A tibble: 2 × 2
#> merge_type n
#> <chr> <int>
#> 1 invest2loan 81411
#> 2 loan2invest 166632