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_principal_last
Last month of principal repayment in loan panel
- loan_interest_monthly
monthly interest rate given aggregated interests and principals, monthly compounding rate (see Issue-24)
- 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_principal_last_paired_1t2
Last month of principal repayment in loan panel, middle loan of bridge
- loan_principal_last_paired_2t3
Last month of principal repayment in loan panel, bottom loan of bridge
- loan_interest_monthly_paired_1t2
monthly interest rate given aggregated interests and principals, middle loan of bridge, monthly compounding rate (see Issue-24)
- loan_interest_monthly_paired_2t3
monthly interest rate given aggregated interests and principals, bottom loan of bridge, monthly compounding rate (see Issue-24)
- ll_gw1
GW1: loan A gap between end and start
- ll_gw2
GW2: loan B gap between end and start
- ll_gw3
GW3: loan C gap between end and start
- 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 middle (component B) bridge component, with formal lender as component A and C of 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))
Source
Regenerated by
vignettes/ffv_invest_loan_bridge.qmd
via ffp_hfid_invest_loan_linked_abc_investloan_char_gateway()
(PrjThaiHFID-#32).
Set bl_replace_data_output <- TRUE in the vignette to overwrite data/*.rda.
MBF filters: investment months 14–144, fl_min_invest_size = 10000,
ar_st_vars_to_keep = c("agg_BS_1021", "agg_BS_1012", "agg_BS_1011").
Packaged inputs: tstm_loans_panel, tstm_asset_loan (see ?tstm_loans_panel).
Household IDs are anonymized tmid_hh (id or hhid_Num), aligned with tstm_asset_loan.
Three paper asset ivars only; hh_inv_ctr spans kept ivars (gateway early filter).
See also issue-22, step 3.
Examples
data(tstm_roster_invest_loan_linked)
ffp_preview_dataset(tstm_roster_invest_loan_linked)
#>
#> ── tstm_roster_invest_loan_linked ──────────────────────────────────────────────
#> Dimensions: 145746 rows × 44 columns(41.7 Mb)
#>
#> ── Column names (44) ──
#>
#> • 1. thres_inv_mgap
#> • 2. thres_inv_dfsd
#> • 3. hhid_Num
#> • 4. ivars
#> • 5. hh_inv_asset_ctr
#> • 6. hh_inv_ctr
#> • 7. mth_inv_start
#> • 8. mth_inv_end
#> • 9. capital_prior
#> • 10. capital_end
#> • 11. capital_invest
#> • 12. hh_loan_id_nd
#> • 13. loan_start
#> • 14. loan_end
#> • 15. number_indi_loan
#> • 16. forinfm4
#> • 17. loan_principal_interest
#> • 18. loan_principal
#> • 19. loan_principal_last
#> • 20. loan_interest_monthly
#> • 21. merge_type
#> • 22. hh_loan_id_nd_paired_1t2
#> • 23. hh_loan_id_nd_paired_2t3
#> • 24. loan_start_paired_1t2
#> • 25. loan_end_paired_1t2
#> • 26. loan_start_paired_2t3
#> • 27. loan_end_paired_2t3
#> • 28. forinfm4_paired_1t2
#> • 29. forinfm4_paired_2t3
#> • 30. loan_principal_paired_1t2
#> • 31. loan_principal_paired_2t3
#> • 32. loan_principal_last_paired_1t2
#> • 33. loan_principal_last_paired_2t3
#> • 34. loan_interest_monthly_paired_1t2
#> • 35. loan_interest_monthly_paired_2t3
#> • 36. ll_gw1
#> • 37. ll_gw2
#> • 38. ll_gw3
#> • 39. bl_lender_type
#> • 40. bl_bridge_informal
#> • 41. bl_loan_size
#> • 42. bl_loan_dura_a
#> • 43. bl_loan_dura_b
#> • 44. bl_loan_dura_c
#>
#> ── Summary statistics (all variables) ──
#>
#> thres_inv_mgap thres_inv_dfsd hhid_Num ivars
#> Min. :2 Min. :2.326 Min. :1003 Length :145746
#> 1st Qu.:2 1st Qu.:2.326 1st Qu.:3155 N.unique : 3
#> Median :2 Median :2.326 Median :5385 N.blank : 0
#> Mean :2 Mean :2.326 Mean :5467 Min.nchar: 11
#> 3rd Qu.:2 3rd Qu.:2.326 3rd Qu.:7673 Max.nchar: 11
#> Max. :2 Max. :2.326 Max. :9996 NAs : 73271
#> NAs :73271 NAs :73271
#> hh_inv_asset_ctr hh_inv_ctr mth_inv_start mth_inv_end
#> Min. :1.000 Min. : 1.000 Min. : 1.00 Min. : 1.00
#> 1st Qu.:1.000 1st Qu.: 2.000 1st Qu.: 53.00 1st Qu.: 53.00
#> Median :2.000 Median : 3.000 Median : 77.00 Median : 77.00
#> Mean :2.152 Mean : 3.073 Mean : 76.86 Mean : 76.92
#> 3rd Qu.:3.000 3rd Qu.: 4.000 3rd Qu.:101.00 3rd Qu.:101.00
#> Max. :8.000 Max. :13.000 Max. :160.00 Max. :160.00
#> NAs :73271 NAs :73271 NAs :73271 NAs :73271
#> capital_prior capital_end capital_invest hh_loan_id_nd
#> Min. : 0 Min. : 40 Min. :3.750e+01 Min. : 1
#> 1st Qu.: 28885 1st Qu.: 70912 1st Qu.:1.590e+04 1st Qu.: 5154
#> Median : 156086 Median : 238026 Median :3.945e+04 Median : 9961
#> Mean : 830329 Mean : 939589 Mean :1.093e+05 Mean : 9946
#> 3rd Qu.: 640935 3rd Qu.: 754437 3rd Qu.:9.558e+04 3rd Qu.:14800
#> Max. :141177726 Max. :141635206 Max. :1.156e+07 Max. :19587
#> NAs :73271 NAs :73271 NAs :73271 NAs :1615
#> loan_start loan_end number_indi_loan forinfm4
#> Min. : 0.00 Min. : 0.00 Min. : 1.00 Length :145746
#> 1st Qu.: 51.00 1st Qu.: 65.00 1st Qu.: 1.00 N.unique : 4
#> Median : 76.00 Median : 89.00 Median : 1.00 N.blank : 0
#> Mean : 76.54 Mean : 90.17 Mean : 1.07 Min.nchar: 8
#> 3rd Qu.:102.00 3rd Qu.:115.00 3rd Qu.: 1.00 Max.nchar: 15
#> Max. :160.00 Max. :160.00 Max. :11.00 NAs : 1615
#> NAs :1615 NAs :1615 NAs :1615
#> loan_principal_interest loan_principal loan_principal_last
#> Min. : 0 Min. : 63 Min. : 0
#> 1st Qu.: 5150 1st Qu.: 5000 1st Qu.: 2900
#> Median : 15900 Median : 15000 Median : 11000
#> Mean : 29571 Mean : 29279 Mean : 22103
#> 3rd Qu.: 27440 3rd Qu.: 27500 3rd Qu.: 20000
#> Max. :13160000 Max. :7000000 Max. :2000000
#> NAs :1615 NAs :1622 NAs :1615
#> loan_interest_monthly merge_type hh_loan_id_nd_paired_1t2
#> Min. :-1.000000 Length :145746 Min. : 2
#> 1st Qu.: 0.003221 N.unique : 2 1st Qu.: 5164
#> Median : 0.004844 N.blank : 0 Median : 9969
#> Mean :-0.011800 Min.nchar: 11 Mean : 9953
#> 3rd Qu.: 0.006776 Max.nchar: 11 3rd Qu.:14803
#> Max. : 1.040000 Max. :19587
#> NAs :1622 NAs :6630
#> hh_loan_id_nd_paired_2t3 loan_start_paired_1t2 loan_end_paired_1t2
#> Min. : 7 Min. : 0.0 Min. : 0.00
#> 1st Qu.: 5168 1st Qu.: 62.0 1st Qu.: 75.00
#> Median : 9965 Median : 86.0 Median : 99.00
#> Mean : 9960 Mean : 86.3 Mean : 99.37
#> 3rd Qu.:14779 3rd Qu.:112.0 3rd Qu.:125.00
#> Max. :19587 Max. :160.0 Max. :160.00
#> NAs :16149 NAs :6630 NAs :6630
#> loan_start_paired_2t3 loan_end_paired_2t3 forinfm4_paired_1t2
#> Min. : 2.00 Min. : 4.0 Length :145746
#> 1st Qu.: 73.00 1st Qu.: 85.0 N.unique : 4
#> Median : 94.00 Median :107.0 N.blank : 0
#> Mean : 95.23 Mean :107.8 Min.nchar: 8
#> 3rd Qu.:119.00 3rd Qu.:133.0 Max.nchar: 15
#> Max. :159.00 Max. :160.0 NAs : 6630
#> NAs :16149 NAs :16149
#> forinfm4_paired_2t3 loan_principal_paired_1t2 loan_principal_paired_2t3
#> Length :145746 Min. : 100 Min. : 200
#> N.unique : 4 1st Qu.: 5000 1st Qu.: 5000
#> N.blank : 0 Median : 15000 Median : 15000
#> Min.nchar: 8 Mean : 27381 Mean : 28242
#> Max.nchar: 15 3rd Qu.: 26000 3rd Qu.: 26500
#> NAs : 16149 Max. :2000000 Max. :4100000
#> NAs :6634 NAs :16150
#> loan_principal_last_paired_1t2 loan_principal_last_paired_2t3
#> Min. : 0 Min. : 0
#> 1st Qu.: 3000 1st Qu.: 2700
#> Median : 13000 Median : 12000
#> Mean : 22526 Mean : 22201
#> 3rd Qu.: 20000 3rd Qu.: 20000
#> Max. :2000000 Max. :2000000
#> NAs :6630 NAs :16149
#> loan_interest_monthly_paired_1t2 loan_interest_monthly_paired_2t3
#> Min. :-1.000000 Min. :-1.000000
#> 1st Qu.: 0.003258 1st Qu.: 0.003022
#> Median : 0.004749 Median : 0.004492
#> Mean :-0.021171 Mean :-0.047229
#> 3rd Qu.: 0.006432 3rd Qu.: 0.005938
#> Max. : 0.745614 Max. : 0.311488
#> NAs :6634 NAs :16150
#> ll_gw1 ll_gw2 ll_gw3 bl_lender_type
#> Min. : 0.00 Min. : 0.00 Min. : 1.00 Mode :logical
#> 1st Qu.:11.00 1st Qu.:11.00 1st Qu.:11.00 FALSE:77994
#> Median :12.00 Median :12.00 Median :12.00 TRUE :61122
#> Mean :12.97 Mean :13.07 Mean :12.59 NAs :6630
#> 3rd Qu.:13.00 3rd Qu.:13.00 3rd Qu.:12.00
#> Max. :48.00 Max. :48.00 Max. :48.00
#> NAs :6630 NAs :6630 NAs :16149
#> bl_bridge_informal bl_loan_size bl_loan_dura_a bl_loan_dura_b
#> Mode :logical Mode :logical Mode :logical Mode :logical
#> FALSE:119293 FALSE:77138 FALSE:105917 FALSE:52857
#> TRUE :19823 TRUE :61969 TRUE :33199 TRUE :86259
#> NAs :6630 NAs :6639 NAs :6630 NAs :6630
#>
#>
#>
#> bl_loan_dura_c
#> Mode :logical
#> FALSE:73800
#> TRUE :55797
#> NAs :16149
#>
#>
#>
#> ── Sample rows (first 6) ──
#>
#> # A tibble: 6 × 44
#> thres_inv_mgap thres_inv_dfsd hhid_Num ivars hh_inv_asset_ctr hh_inv_ctr
#> <dbl> <dbl> <int> <chr> <int> <int>
#> 1 2 2.33 1003 agg_BS_1011 1 1
#> 2 2 2.33 1003 agg_BS_1011 1 1
#> 3 2 2.33 1003 agg_BS_1011 1 1
#> 4 2 2.33 1003 agg_BS_1011 1 1
#> 5 2 2.33 1003 agg_BS_1011 1 1
#> 6 2 2.33 1003 agg_BS_1011 1 1
#> # ℹ 38 more variables: mth_inv_start <dbl>, mth_inv_end <dbl>,
#> # capital_prior <dbl>, capital_end <dbl>, capital_invest <dbl>,
#> # hh_loan_id_nd <int>, loan_start <dbl>, loan_end <dbl>,
#> # number_indi_loan <int>, forinfm4 <chr>, loan_principal_interest <dbl>,
#> # loan_principal <dbl>, loan_principal_last <dbl>,
#> # loan_interest_monthly <dbl>, merge_type <chr>,
#> # hh_loan_id_nd_paired_1t2 <int>, hh_loan_id_nd_paired_2t3 <int>, …
if (requireNamespace("dplyr", quietly = TRUE)) {
print(tstm_roster_invest_loan_linked |>
dplyr::count(merge_type, sort = TRUE))
}
#> # A tibble: 2 × 2
#> merge_type n
#> <chr> <int>
#> 1 loan2invest 108701
#> 2 invest2loan 37045