Static pool Analysis (pg. 7 of KPI)

NEW ONLY 1-29 $ check August at mo 1

library(tidyverse)
library(lubridate)
library(readr)
library(skimr)

## NEW ONLY Sanity Check August 2026

new_only <- read_csv("staticpool_ffn_new_august2026.csv")

new_only %>% 
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "August2026") %>% 
  filter(loanmonth == "2026-07-01 00:00:00") %>% 
  select(report_sheet, loanmonth, loans, `1-15 $`,`16-29 $`, `30+ $`, amountbooked)

# A tibble: 1 × 7
# Groups:   report_sheet, loanmonth [1]
  report_sheet loanmonth           loans `1-15 $` `16-29 $` `30+ $` amountbooked
  <chr>        <chr>               <dbl>    <dbl>     <dbl>   <dbl>        <dbl>
1 August2026   2026-07-01 00:00:00    13        0      5750   2333.        14700

The most recent loan month in the latest NEW ONLY file is July. At month 1, it shows the following for report month August 2026:

1–15 days past due: $0
16–29 days past due: $5,750
30+ days past due: $2,332.89
Amount booked: $14,700

Since no new loans were booked in August, Sandeep’s source files likely weren’t updated with an August 2026 column. This is consistent with the low volume we saw last month.

1-29 %$ at month 1

new_only %>% 
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "August2026") %>% 
  filter(loanmonth == "2026-07-01 00:00:00") %>% 
  select(report_sheet, loanmonth, `1-15 $`,`16-29 $`, `30+ $`, amountbooked) %>%
  summarise(`1-29 at mo 1` =  (`1-15 $` + `16-29 $` + `30+ $`) / amountbooked)

# A tibble: 1 × 3
# Groups:   report_sheet [1]
  report_sheet loanmonth           `1-29 at mo 1`
  <chr>        <chr>                        <dbl>
1 August2026   2026-07-01 00:00:00          0.550

1-29 at vintage month 1 = 55% (formula = (1-15 $ + 16-29$ +30+ $) / amountbooked)

NEW ONLY 1-29 $ Check July 2026 at mo 0

july_new_only <- read_csv("staticpool_ffn_new_july2026.csv")

july_new_only %>%
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "Jul-26") %>% 
  filter(loanmonth == "7/1/2026 0:00") %>% 
  select(report_sheet, loanmonth, loans, `1-15 $`, `16-29 $`, `30+ $`, amountbooked)

# A tibble: 1 × 7
# Groups:   report_sheet, loanmonth [1]
  report_sheet loanmonth     loans `1-15 $` `16-29 $` `30+ $` amountbooked
  <chr>        <chr>         <dbl>    <dbl>     <dbl>   <dbl>        <dbl>
1 Jul-26       7/1/2026 0:00    13    2833.      1500       0        14700

Same as above. This shows 1-15$, 16-29$, 30+$, amount booked FROM THE JULY 2026 staticpool files. This shows values at month 0 since this is last months staticpool file.

1–15 days past due: $2832.89
16–29 days past due: $1500
30+ days past due: $0
Amount booked: $14,700

Notice that both the August and July reports have the same amount booked and loans. Both these numbers are true as the August report reads as month 1 and the July report reads from month 0

NEW ONLY 1-29 %$ July 2026 at mo 0

july_new_only %>%
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "Jul-26") %>% 
  filter(loanmonth == "7/1/2026 0:00") %>% 
  select(report_sheet, loanmonth, `1-15 $`, `16-29 $`, `30+ $`, amountbooked) %>% 
  summarise(`1-29 delinq at month 0` = (`1-15 $` + `16-29 $`) / amountbooked)

# A tibble: 1 × 3
# Groups:   report_sheet [1]
  report_sheet loanmonth     `1-29 delinq at month 0`
  <chr>        <chr>                            <dbl>
1 Jul-26       7/1/2026 0:00                    0.295

1-29 %$ at vintage month 0 = 29.4% (formula = 1-15 $ + 16-29$ / amount booked)

REFI ALL 1-29 %$ check

refi_all_csv <- read_csv("staticpool_ffn_refi_all_august2026.csv")

refi_all_csv %>% 
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "August2026") %>% 
  filter(loanmonth == "2026-08-01 00:00:00") %>% 
  select(report_sheet, loanmonth, loans, `1-15 $`,`16-29 $`, `30+ $`, amountbooked)

# A tibble: 1 × 7
# Groups:   report_sheet, loanmonth [1]
  report_sheet loanmonth           loans `1-15 $` `16-29 $` `30+ $` amountbooked
  <chr>        <chr>               <dbl>    <dbl>     <dbl>   <dbl>        <dbl>
1 August2026   2026-08-01 00:00:00   103        0         0       0      178733.

REFI 1 ONLY 1-29 %$

refi_one_only_csv <- read_csv("staticpool_ffn_refi_1_only_august2026.csv")

refi_one_only_csv %>% 
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "August2026") %>% 
  filter(loanmonth == "2026-08-01 00:00:00") %>% 
  select(report_sheet, loanmonth, loans, `1-15 $`,`16-29 $`, `30+ $`, amountbooked)

# A tibble: 1 × 7
# Groups:   report_sheet, loanmonth [1]
  report_sheet loanmonth           loans `1-15 $` `16-29 $` `30+ $` amountbooked
  <chr>        <chr>               <dbl>    <dbl>     <dbl>   <dbl>        <dbl>
1 August2026   2026-08-01 00:00:00    39        0         0       0       59128.

REFI 2+ 1-29 %$

refi_two_plus <- read_csv("staticpool_ffn_refi_2plus_august2026.csv")

refi_two_plus %>% 
  group_by(report_sheet, loanmonth) %>% 
  filter(report_sheet == "August2026") %>% 
  filter(loanmonth == "2026-08-01 00:00:00") %>% 
  select(report_sheet, loanmonth, loans, `1-15 $`,`16-29 $`, `30+ $`, amountbooked)

# A tibble: 1 × 7
# Groups:   report_sheet, loanmonth [1]
  report_sheet loanmonth           loans `1-15 $` `16-29 $` `30+ $` amountbooked
  <chr>        <chr>               <dbl>    <dbl>     <dbl>   <dbl>        <dbl>
1 August2026   2026-08-01 00:00:00    64        0         0       0      119605.