Fix RPM distribution amounts

[dfsnow]

PINs within the special RPM TIF district have slightly incorrect total tax amounts, especially compared to non-TIF PINS. The RPM TIF has the following disbursement:

1. CPS gets its normal revenue as if the RPM TIF doesn't exist
2. 80% of the remaining TIF funds go to the RPM project
3. 20% of the remaining TIF funds are redistributed back to the jurisdiction except CPS

The first two are correct, but calculating the correct proportional distribution for part 3 is challenging. Currently using the following formula: agency_tax_rate / sum(agency_tax_rate (excluding CPS))

The following script yields the example data below:

left_join(
  tax_bill(2019, "14081020190000", simplify = FALSE),
  ptaxsim::sample_tax_bills_detail %>%
    filter(pin == "14081020190000") %>%
    select(pin, tax, agency),
  by = c("pin", "agency")
) %>%
  select(
    agency_name, tax_amt_calced = tax_amt_final, tax_amt_real = tax
  )

# A tibble: 10 x 3
   agency_name                                tax_amt_calced tax_amt_real
   <chr>                                               <dbl>        <dbl>
 1 COUNTY OF COOK                                      606.         624. 
 2 FOREST PRESERVE DISTRICT OF COOK COUNTY              78.8         78.4
 3 CITY OF CHICAGO                                    2141.        2131. 
 4 CITY OF CHICAGO LIBRARY FUND                        162.         161. 
 5 CITY OF CHICAGO SCHOOL BLDG & IMP FUND              226.         225. 
 6 CHICAGO COMMUNITY COLLEGE DISTRICT 508              199.         198. 
 7 BOARD OF EDUCATION                                 4561.        4560. 
 8 CHICAGO PARK DISTRICT                               435.         433. 
 9 CHICAGO PARK DIST. AQUARIUM & MUSEUM BONDS            0            0  
10 METRO WATER RECLAMATION DIST OF GR CHGO             520.         517. 

[dfsnow]

RPM logic:

1. CPS receives their proportionate share of revenue (they effectively ignore the TIF)
2. 80% of the remaining revenue goes directly to the RPM TIF
3. The remaining 20% of revenue goes back to the taxing districts. The portion that would be sent back to CPS as part of this 20% is proportionally divvied up among the other jurisdictions by their tax rates. So, the percentage of CPS portion they get = district_tax_rate​ / sum of all district tax rates (except CPS)​. I think this last step is where I'm going wrong, but I can't figure out a way that results in numbers close to what is in the bills. Any help?

Mirella response, via email 2021-09-06:

Response: The eav should already have any applicable exemptions removed prior to applying any tax rate; that is the value that is subject to taxation. Try reconfiguring your spreadsheet to either not account or applying any exemption (for a straight parcel) or to calculate the billable amount once the final EAV has been calculated.

The TIF distribution comes after the tax has been calculated and should only be applied to the “incremental” eav of the value. In following the logic of the previously attached spreadsheet, I think the thing that is missing is the bifurcation of taxes based on the “frozen eav”, which requires that taxes be distributed as if there was no TIF, to the newly “captured” TIF funds, which are subject to the distribution referenced above.

[dfsnow]

Comment for my own future reference. Tried recalculating rates after distributing the capture TIF increment back to districts. Doesn't seem to matter much:

agencies <- DBI::dbGetQuery(ptaxsim_db_conn, "SELECT * FROM agencies")
tax_codes <- DBI::dbGetQuery(ptaxsim_db_conn, "SELECT * FROM tax_codes")
tifs <- DBI::dbGetQuery(ptaxsim_db_conn, "SELECT * FROM tif_distributions")
setDT(agencies, key = c("year", "agency_num"))
setDT(tax_codes, key = c("year", "agency_num", "tax_code_num"))
setDT(tifs, key = c("year", "tax_code_num"))

temp <- merge(agencies, tax_codes, all.x = TRUE)
temp2 <- agencies[
  tax_codes
][tifs, on = .(year, tax_code_num)
][, .(new_rate = total_ext / (total_eav + sum(tax_code_frozen_eav))), by = .(year, agency_num)]

# Get list of all tax codes within agencies that overlap the RPM TIF
rpm_agencies <- unique(tax_codes[tax_codes[agency_num == "030210900", c("year", "tax_code_num")
], on = .(year, tax_code_num)][, c("year", "agency_num")])
rpm_tax_codes <- tax_codes[rpm_agencies, c("year", "agency_num", "tax_code_num")]


temp3 <- rpm_tax_codes[
  tifs,
  on = .(year, tax_code_num),
  tax_code_frozen_eav := i.tax_code_frozen_eav
][, .(frozen_eav = sum(tax_code_frozen_eav, na.rm = TRUE)), by = .(year, agency_num)]

# filter TIFs
# calc new rates
# calc distributions

temp3 <- rpm_tax_codes[tifs, on = .(year, tax_code_num)]

temp4 <- agencies[
  rpm_agencies
][temp3, on = .(year, agency_num)
][, agency_rate_rpm := total_ext / (total_eav + frozen_eav)][!is.na(agency_name), c("year", "agency_num", "agency_rate_rpm")]

[dfsnow]

Per the Treasurer's team, it seems like this is in fact the correct way to do this calculation, but there may be some discrepancy between how it's done for the purpose of being shown on bills on how it's done to actually divvy up collections. So, without further info, I'm going to close this for now.