All Muni Calculations

knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)


library(tidyverse)
library(DBI)
library(data.table)
library(ggspatial)
library(gstat)
library(here)
library(httr)
library(jsonlite)
library(ptaxsim)
library(sf)
library(stars)
library(glue)

# Create the DB connection with the default name expected by PTAXSIM functions
ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "./ptaxsim.db/ptaxsim-2021.0.4.db")

# has all potential property classes for pins
# downloaded from CCAO gitlab website
## I used this to merge additional information to the pins and class data later on.
class_dict <- read_csv("class_dict.csv")

options(digits=4, scipen = 999)

Example from gitlab site

Cook County Assessors Data description page and other datasets.

Data Prep

Pull all agency names that exist, then use agency numbers associated with MUNI types to pull only the muni_agency_names object.

There are 946 unique taxing agencies that existed in 2021.

# has EAV values, extensions by agency_num
agency_dt <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT *
  FROM agency
  WHERE year = 2021
  "
)

# show variables that exist in the data table
agency_dt

This table is used for the municipality levy in later calculations.

agency_dt has all taxing agencies (BUT NOT TIFS!) that existed in 2021 and includes their total taxable base (cty_cook_eav), their levy, taxing rate, binary variables for if a municipality is home rule or not, as well as many other variables. Also currently doesn’t have agency_names to go with the agency numbers. That will be merged in shortly.

The table has waaaay too many columns to display all at once.Click the arrow on the right-hand side of the table to see additional columns that are hidden initially in the display.

# grabs all unique muni names. Would be needed if creating a loop for calculating all munis
# municipality names and their agency number
muni_agency_names <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT DISTINCT agency_num, agency_name, minor_type
  FROM agency_info
  WHERE minor_type = 'MUNI'
  OR agency_num = '020060000'  

  "
)

muni_agency_names <- muni_agency_names %>% 
  mutate(first6 = str_sub(agency_num,1,6),
         first5 = str_sub(agency_num,1,5)) %>% 
  select(-minor_type)

Makes a list of ALL taxing agencies, including TIFs, SSAs, etc.

First 5 and 6 digit codes variables are created to nest TIFs and other taxing agencies within the municipality. Still experimental, variables are not used in the code below.

There are 1,878 taxing agencies. When grouped by minor_type, there are 133 muni agencies, 639 tif agencies, 30 townships, etc.

# all agency names, numbers, and types
# includes TIF and non-TIF agencies
all_taxing_agencies <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT agency_num, agency_name, major_type, minor_type
  FROM agency_info
  "
)

# create same first 5 and 6 digit codes 
all_taxing_agencies <- all_taxing_agencies %>%
  mutate(first6 = str_sub(agency_num,1,6),
         first5 = str_sub(agency_num,1,5))

all_taxing_agencies

# 133 muni agencies, 639 tif agencies, 30 townships, etc.
# we use the 133 muni numbers and Cicero's agency number to get the 134 municipalities. 
table(all_taxing_agencies$minor_type)

## 
##       BOND  COMM COLL       COOK ELEMENTARY       FIRE   GEN ASST     HEALTH 
##         16         11          3        118         39         31         13 
##      INFRA    LIBRARY       MISC   MOSQUITO       MUNI       PARK     POLICE 
##         25        111         14          4        133        102          4 
##   SANITARY  SECONDARY        SSA        TIF   TOWNSHIP    UNIFIED      WATER 
##         18         29        529        639         30          6          3

table(all_taxing_agencies$major_type)

## 
##           COOK COUNTY         MISCELLANEOUS MUNICIPALITY/TOWNSHIP 
##                     3                   257                  1451 
##                SCHOOL 
##                   167

All agencies have an agency name, but there isn’t an indicator for which municipality each taxing agency is within. Merging the muni_agency_names to all_taxing_agencies hopefully adds a variable that allows for grouping and summing at the municipality level that includes SSAs, (if we want to do that). Does not work for School districts or other taxing districts which begin with completely different values (e.g. school districts begin with 4).

Everything depends on the tax codes.

# none of these are included in their geographic municipality areas automatically. have different agency number code beginnings too. 
all_taxing_agencies %>% 
  filter(major_type == "MISCELLANEOUS") %>% 
  group_by(minor_type) %>%
  summarize(count = n()) 

#all_taxing_agencies %>% filter(minor_type == "FIRE") 

Add agency names since those weren’t included originally in the table:

all_taxing_agencies <- all_taxing_agencies %>% 
  left_join(muni_agency_names, by = c("first5", "first6")) %>% 
  rename(muni_name =  agency_name.y,
        muni_num = agency_num.y,
        agency_name = agency_name.x,
        agency_num = agency_num.x)



# combine taxing agency names and agency type to data table that has eav and extension values
agency_data <- right_join(agency_dt, all_taxing_agencies) %>% 
  # get rid of unneeded columns to make table outputs smaller
  select(-c(cty_dupage_eav:cty_livingston_eav, lim_numerator, lim_denominator)) %>% # drop some of the unused variables
  arrange(first5, agency_num)

Using the agency numbers for each municipality, I pull all tax codes that have an agency_num included in the muni_agency_names object. By narrowing the agencies down to just Municipality types, this prevents duplicate tax_codes from being pulled.

There are 3774 tax codes within Cook County that are taxed by Municipalities. There are 4228 unique tax codes in Cook County in 2021.

# muni_agency_nums<- all_taxing_agencies %>% 
#   filter(minor_type %in% c("MUNI")) %>%
#   select(agency_num)

muni_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT*
  FROM tax_code
  WHERE agency_num IN ({muni_agency_names$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)

# There are 4228 unique taxcodes in Cook County in 2021
cook_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT *
  FROM tax_code
  WHERE year = 2021
  AND agency_num = '010010000'
  ",
  .con = ptaxsim_db_conn
  )
)

Levies

Levy data from agency_data is broken down by each taxing agency. Calculating a municipal levy can’t be done from this table because you can’t get a composite tax rate or the amount of eav within a region that is being taxed by taxing agency. But this does have the total levy and total eav of all taxing districts.

In later steps, the levy is actually calculated from the final_tax_to_dist variable from the tax_bill() output for all pins.

muni_levies <- agency_data %>% 
  filter(minor_type == "MUNI" | 
           agency_num == "020060000"  # Cicero is an oddball and considered Township

  )

# variables that exist for the 134 municipalities for levies and extensions values 
# originally from agency dt
muni_levies

TIF Dummy variable for tax codes

I find all TIF taxing agencies and tax codes that are taxed by TIF agencies.

TFI_distrib table total EAV within each tax code, the frozen EAV amount, tax code rate, and tax code revenue variables. Super helpful table, however, I don’t directly use the table at this point in time.

# Agency number and agency name for all TIFs
TIF_agencies <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT DISTINCT agency_num, agency_name, major_type, minor_type
  FROM agency_info
  WHERE minor_type = 'TIF'
  "
)

unique_tif_taxcodes <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT DISTINCT tax_code_num
  FROM tax_code
  WHERE agency_num IN ({TIF_agencies$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)



tif_distrib <- DBI::dbGetQuery(
  ptaxsim_db_conn, 
  glue_sql("
  SELECT *
  FROM tif_distribution
  WHERE tax_code_num IN ({muni_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
)

tif_distrib

#frozen_eav <- tif_distrib %>% select(year, tax_code_num, tax_code_eav, tax_code_frozen_eav)

Using the TIF distribution tables and tax_code tables included in ptaxsim, I calculate the total revenue collected by each tax code (and the amount that went to TIFs or the “district”.)

tif_distrib has the frozen eav within each tax_code, eav of the tax code, and the total revenue collected. This table is incorporated into the ptaxsim tax_bill() output when using ptaxsim correctly. I do not directly use this table in steps below.

tif_distrib <- tif_distrib %>% 
  mutate(#first6= str_sub(agency_num,1,6),
        # first5 = str_sub(agency_num, 1,5),
         tif_increment = tax_code_eav - tax_code_frozen_eav,
         total_taxcode_revenue = tax_code_eav*tax_code_rate/100,
         district_revenue_perTC = tax_code_frozen_eav*tax_code_rate/100,
         # checking to see if equal to tax_code_revenue
         # same but has the negative revenue values instead of zeros.
         tif_revenue_perTC = total_taxcode_revenue - district_revenue_perTC)

# add 
#tif_distrib <- tif_distrib %>% left_join(muni_tax_codes) 
tif_distrib

Pins

There are 1,825,816 pins in 2021 that are taxed by Municipalities.

There are 1,864,594 pins taxed by Cook County in 2021.

Difference is 38,779 pins. Initial hypothesis: these are in unincorporated areas?

muni_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql(
  "SELECT DISTINCT pin, class, tax_code_num
  FROM pin
  WHERE tax_code_num IN ({muni_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
))

# There are 1,864,594 pins taxed by Cook County in 2021.
cook_pins <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql(
  "SELECT DISTINCT pin, class, tax_code_num
  FROM pin
  WHERE tax_code_num IN ({cook_tax_codes$tax_code_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
))


# change variable type to character so the join works.
class_dict$class_code <- as.character(class_dict$class_code)

# finds all pins within a municipality
pin_data <- lookup_pin(2021, cook_pins$pin) %>%
  left_join(cook_pins, by = c("pin", "class"))


# use the property class to make the major property types
# joins the class_dict file to the pin_data classes
pin_data <- class_dict %>%
  select(-c(assessment_level:reporting_group, class_desc:max_size)) %>%
  right_join(pin_data, by = c("class_code" = "class"))

write_csv(pin_data, "all_cook_pins_2021.csv")


exemptions_inCook_perTC <- pin_data %>%
  group_by(tax_code_num, major_class_code, major_class_type) %>%
  summarize(eav=sum(eav, na.rm=TRUE),
            exe_homeowner = sum(exe_homeowner, na.rm=TRUE),
            exe_senior = sum(exe_senior, na.rm=TRUE),
            exe_freeze = sum(exe_freeze, na.rm=TRUE),
            exe_longtime_homeowner = sum(exe_longtime_homeowner, na.rm=TRUE),
            exe_disabled = sum(exe_disabled, na.rm=TRUE),
            exe_vet_returning = sum(exe_vet_returning, na.rm=TRUE),
            exe_vet_dis = sum(exe_vet_dis_lt50 + exe_vet_dis_50_69 + exe_vet_dis_ge70, na.rm=TRUE),
            exe_abate = sum(exe_abate, na.rm=TRUE),
            pin_count = n() # number of pins within each tax code and property combo

)
write_csv(exemptions_inCook_perTC, "all_exemptions_inCook_byTaxcode.csv")


# change variable type to character so the join works.
class_dict$class_code <- as.character(class_dict$class_code)

# finds all pins within a municipality
pin_data <- lookup_pin(2021, muni_pins$pin) %>%
  left_join(muni_pins, by = c("pin", "class"))


# use the property class to make the major property types
# joins the class_dict file to the pin_data classes
pin_data <- class_dict %>%
  select(-c(assessment_level:reporting_group, class_desc:max_size)) %>%
  right_join(pin_data, by = c("class_code" = "class"))

write_csv(pin_data, "all_muni_pins_2021.csv")

exemptions_inMunis_perTC <- pin_data %>%
  group_by(tax_code_num, major_class_code, major_class_type) %>%
  summarize(eav=sum(eav, na.rm=TRUE),
            exe_homeowner = sum(exe_homeowner, na.rm=TRUE),
            exe_senior = sum(exe_senior, na.rm=TRUE),
            exe_freeze = sum(exe_freeze, na.rm=TRUE),
            exe_longtime_homeowner = sum(exe_longtime_homeowner, na.rm=TRUE),
            exe_disabled = sum(exe_disabled, na.rm=TRUE),
            exe_vet_returning = sum(exe_vet_returning, na.rm=TRUE),
            exe_vet_dis = sum(exe_vet_dis_lt50 + exe_vet_dis_50_69 + exe_vet_dis_ge70, na.rm=TRUE),
            exe_abate = sum(exe_abate, na.rm=TRUE),
            pin_count = n() # number of pins within each tax code and property combo

)
write_csv(exemptions_inMunis_perTC, "all_exemptions_inMunis_byTaxcode.csv")

I have not calculated separate taxbill summaries using the Cook pins instead of the Muni pins. Sticking with muni pins until time to explore the differences - AWM 6.29.2023

Tax Bills

The tax_bill() command returns information for each taxing agency that taxed a pin. EACH pin can be taxed by 15+ taxing agencies and each taxing agency has its own taxing rate.

Each tax code has a unique combination of taxing agencies taxing properties within in. So, each tax code has a summed EAV value from each pin within it and a “composite tax rate” made from the taxing agencies and their taxable base. In the code below, its called the weighted tax rate but that isn’t the best name for it… It is more like the amount of money that each taxing agency gets from each property.

revenue collected(previously named weighted_tax_rate in earlier code versions) was calculated for every tax bill observation for every pin and is equal to the eav of a property X agency_tax_rate. Then all pins within tax codes were summed together to get a tax code level EAV and tax code level tax rate. Note: There is a tax_code_rate variable included in the muni_tax_codes object made from the tax_code dt included in ptaxsim

Found code issue: had mutate(frozen_eav = ifelse(in_TIF =1, final_tax_to_dist)) which was returning the revenue collected from the frozen eav, not the frozen eav. AAGGHHH. Glad I caught it. Doesn’t change large picture of EAV or burden.

# creates the 4.5 GB file of all taxbills for 2021. 
bills <- tax_bill(2021, muni_pins$pin, simplify = FALSE)

bills_byClass_pertaxcode <- bills %>%
  left_join(class_dict, by = c("class" = "class_code")) %>% # add major property types to pin data
  
  mutate(revenue_collected_pre_exemp = agency_tax_rate*eav) %>% # $ that each taxing agency gets from each pin
  mutate(revenue_collected_post_exemp = agency_tax_rate*(eav-exe_total) )%>% # $ that each taxing agency gets from each pin

  group_by(major_class_code, major_class_type, tax_code) %>%
  

  summarize(#tax_code_rate = first(tax_code_rate),
            final_tax_to_dist = sum(final_tax_to_dist),
            final_tax_to_tif = sum(final_tax_to_tif),
            tax_amt_exe = sum(tax_amt_exe), # revenue lost due to exemptions
            tax_amt_pre_exe = sum(tax_amt_pre_exe), # total rev before exemptions
            tax_amt_post_exe = sum(tax_amt_post_exe), # total rev after exemptions
        rpm_tif_to_cps = sum(rpm_tif_to_cps), # not used
        rpm_tif_to_rpm = sum(rpm_tif_to_rpm), # not used
        rpm_tif_to_dist = sum(rpm_tif_to_dist), # not used
           tif_share = mean(tif_share), # not used
        rev_perTC_pre_exemp = sum(revenue_collected_pre_exemp),
        rev_perTC_post_exemp = sum(revenue_collected_post_exemp)) %>%
    left_join(all_tax_codes, by = c("tax_code" = "tax_code_num")) 


 # na.rm = TRUE option is probably only needed for calculating the mean values of variables. I just didn't delete it yet. Avoids getting an error if there is a zero when calculating mean.



#write_csv(bills, "all_taxbills_inMunis.csv")


## adds additional variables for frozen eav from tif_distrib table! 
# if the tax code is not a TIF, then valus will all be NA
bills_byClass_pertaxcode2 <- bills_byClass_pertaxcode %>% 
  select(-c(agency_num, agency_rate)) %>%
  left_join(tif_distrib, by = c("tax_code" = "tax_code_num", "tax_code_rate", "year")) %>%
  select(-c(agency_num, agency_num))

write_csv(bills_byClass_pertaxcode2, "taxbills_inMunis_perTC.csv")

# Looking at properties taxbills that have NA as a property type:

bills_NA_propertytypes <- bills %>% 
  left_join(class_dict, by = c("class" = "class_code")) %>% 
  filter(major_class_code == NA)
write_csv(bills_NA_propertytypes, "taxbills_with_no_PropertyClass.csv")



bills_propertyclass_counts <- bills %>% 
 # left_join(class_dict, by = c("class" = "class_code")) %>% 
  group_by(class) %>% 
  summarize(property_count = n())

write_csv(bills_propertyclass_counts, "bills_propertyclass_counts.csv")

Combining Data

Tax Bills and Levies for Taxcodes and Municipalities

Tax bill data was pulled from ptaxsim using the tax_bill() function for all pins taxed by the 134 Municipalities within Cook County in 2021. This was a 4.5 GB file. In order to make the file manageable and combine it with exemption data for all municipalities, I summarized values to the TaxCode-PropertyClass level. This is later summarized by municipalities by aggregating the taxcodes that are taxed by each municipality.

Tax bills grouped and summarized for each tax code’s property class types make 9716 observations. (3774 tax codes X multiple property types in every tax code).

taxbills_by_Class_per_TC <- read_csv("taxbills_inMunis_perTC.csv") %>%
  mutate(tax_code = as.character(tax_code)) %>%
  
 # left_join(muni_tax_codes, by = c("tax_code" = "tax_code_num")) %>%
  
  mutate(#final_tax_to_dist = ifelse(is.na(final_tax_to_dist), 0, final_tax_to_dist),
    TaxableEAV = (final_tax_to_dist/(tax_code_rate/100)),
         TaxableEAV_b4_exemps = ((final_tax_to_dist+tax_amt_exe)/(tax_code_rate/100)),
         TIF_increment = (final_tax_to_tif/(tax_code_rate/100)),
         TotalEAV_post_exe = (tax_amt_post_exe/(tax_code_rate/100)),
         TotalEAV_pre_exe = (tax_amt_pre_exe/(tax_code_rate/100))

         
  )



#taxbills_by_Class_per_TC <- left_join(taxbills_by_Class_per_TC , muni_agency_names)

taxbills_by_Class_per_TC 

taxbills_by_Class_per_TC %>% mutate(
                            "Current Tax Revenue(District+TIF)" = scales::dollar(tax_amt_post_exe),
                            "District Revenue" = scales::dollar(final_tax_to_dist),
                            "TIF Revenue" = scales::dollar(final_tax_to_tif),
                            "Lost Revenue from Exempt." = scales::dollar(tax_amt_exe), # same as tax_amt_pre_exe-tax_amt_post_exe
                           # "Revenue Collected" = rev_perTC, # within each Taxcode
                   ) %>% 
 # mutate(`EAV in and out of TIFs` = `EAV in TIF (Increment)`+`EAV outside TIF`,
 #            EAVbeforeExemptions = `EAV in and out of TIFs`+Exemptions) %>%
  select(-c(tax_amt_post_exe, final_tax_to_dist:tax_amt_pre_exe, 
            #first5, first6, rev_perTC, agency_tax_rate,
            year, rpm_tif_to_cps:rpm_tif_to_dist,  tif_share, major_class_code)) 

#class_exemptions <- full_join(exemptions_by_class,  class_eav, by = "major_class_code") %>%
#  select(major_class_code, major_class_type, eav, final_tax_to_dist, tax_share)
#class_exemptions

Exemptions summarized by Tax Code

Uses exemption data from the lookup_pin() command for all pins in Cook County in 2021. Values are then summarized by Tax codes and property types (so each Municipality has a lot of rows at this point BUT this stage of the data is the most flexible for calculations and future research questions.)

exemptions_by_class_per_TC <- read_csv("all_exemptions_by_TC.csv") %>% 
  mutate(tax_code_num = as.character(tax_code_num))

Combine the tax bill data with the exemption data for every tax code and property class combination:

taxcode_data <- full_join(exemptions_by_class_per_TC, taxbills_by_Class_per_TC, 
          by = c("tax_code_num" = "tax_code", "major_class_code", "major_class_type")) %>%
  left_join(muni_tax_codes) %>% select(-agency_rate)

MuniLevy <- taxcode_data %>% 
  group_by(agency_num)%>%
  summarize(MuniLevy = round(sum(final_tax_to_dist)),
            TIF_rev_perMuni = sum(final_tax_to_tif),
            Lostrev_fromExemps_perMuni = sum(tax_amt_exe),
            Levy_plus_TIFrev_perMuni =round(sum(final_tax_to_dist + final_tax_to_tif)),
            #taxableEAV includes EAV outside of TIFs and frozen EAV in TIFs
            TaxableEAV_perMuni = sum(final_tax_to_dist/(tax_code_rate/100)), # MuniLevy / tax rate
            TIF_increment_perMuni = sum(final_tax_to_tif/(tax_code_rate/100)), # Tif revenue / tax rate
            TotalEAV_pre_exe_perMuni = sum(tax_amt_pre_exe/(tax_code_rate/100)), # includes TIF and district revenue!

            TotalEAV_post_exe_perMuni = sum(tax_amt_post_exe/(tax_code_rate/100)), # includes TIF and district revenue!
           # TotalEAV_post_exe2 = sum((final_tax_to_dist + final_tax_to_tif)/(tax_code_rate/100)) # same as above
  ) %>% 
  mutate(Exempted_EAV_perMuni = TotalEAV_pre_exe_perMuni - TotalEAV_post_exe_perMuni,
         TaxableEAV_b4_exemps_perMuni = TaxableEAV_perMuni + Exempted_EAV_perMuni,
         TotalRevenueCollected_perMuni = Lostrev_fromExemps_perMuni+Levy_plus_TIFrev_perMuni,
         taxrate_current = MuniLevy/TaxableEAV_perMuni,
         taxrate_noexemps = MuniLevy/TaxableEAV_b4_exemps_perMuni,
         inbetweenstep_rev_perMuni = taxrate_noexemps*TaxableEAV_perMuni,
         taxrate_change = taxrate_current - taxrate_noexemps) %>% 
  left_join(muni_agency_names)


taxcode_data <- left_join(taxcode_data, MuniLevy) 

#, "TaxableEAV", "TIF_increment","TotalEAV_post_exe","TotalEAV_pre_exe"))

write.csv(taxcode_data, "tax_codes_all_merged_data.csv")

# # all munis + Cicero
# MuniLevy <- taxbills_by_Class_per_TC %>% 
#   group_by(agency_name)%>%
#   summarize(MuniLevy = round(sum(final_tax_to_dist)),
#             TIF_rev_perMuni = sum(final_tax_to_tif),
#             Lostrev_fromExemps_perMuni = sum(tax_amt_exe),
#             Levy_plus_TIFrev_perMuni =round(sum(final_tax_to_dist + final_tax_to_tif)),
#             #taxableEAV includes EAV outside of TIFs and frozen EAV in TIFs
#             TaxableEAV_perMuni = sum(final_tax_to_dist/(tax_code_rate/100)), # MuniLevy / tax rate
#             TIF_increment_perMuni = sum(final_tax_to_tif/(tax_code_rate/100)), # Tif revenue / tax rate
#             TotalEAV_pre_exe_perMuni = sum(tax_amt_pre_exe/(tax_code_rate/100)), # includes TIF and district revenue!
# 
#             TotalEAV_post_exe_perMuni = sum(tax_amt_post_exe/(tax_code_rate/100)), # includes TIF and district revenue!
#            # TotalEAV_post_exe2 = sum((final_tax_to_dist + final_tax_to_tif)/(tax_code_rate/100)) # same as above
#   ) %>% 
#   mutate(Exemptions_perMuni = TotalEAV_pre_exe_perMuni - TotalEAV_post_exe_perMuni,
#          TaxableEAV_b4_exemps_perMuni = TaxableEAV_perMuni + Exemptions_perMuni,
#          TotalRevenueCollected_perMuni = Lostrev_fromExemps_perMuni+Levy_plus_TIFrev_perMuni,
#          taxrate_current = MuniLevy/TaxableEAV_perMuni,
#          taxrate_noexemps = MuniLevy/TaxableEAV_b4_exemps_perMuni,
#          inbetweenstep_rev_perMuni = taxrate_noexemps*TaxableEAV_perMuni,
#          taxrate_change = taxrate_current - taxrate_noexemps)
# 
# MuniLevy

## checking work:

# MuniLevy %>% mutate(eavcheck = TIF_increment+TaxableEAV+Exemptions,
#                     eavcheck2 = TotalEAV_pre_exe,
#                     eavcheck3 = TaxableEAV_b4_exemps+TIF_increment,
#                     
#                     revcheck = Lostrev_fromExemps+Levy_plus_TIFrev,
#                     revcheck2 = TotalRevenueCollected,
# 
#                     rate_check1 = MuniLevy/TaxableEAV,
#                     rate_check2 = MuniLevy/TaxableEAV_b4_exemps,
#                     
#                     incrementcheck = TotalEAV_pre_exe - TaxableEAV_b4_exemps
#                     )

MuniLevy %>% select(agency_name,MuniLevy, everything() )

Comparison of ptaxsim/Alea calculations and the numbers for the Levy value in Josh Drucker’s Excel files. Pretty close but not perfect matches (which is fine, I’m not surprised by that).

Chicago: 6,536,952,735 vs. 6,490,629,279 JD Bridgeview: 65,534,363 vs. 65,133,190 JD Dolton: 54,957,926 vs. 56,755,459 JD Hoffman Estates: 177,316,159 vs. 182,363,626 JD Midlothian: 30,988,554 vs 30,877,423 JD Oak Park: 234,665,831 vs 233,132,867 JD.

Scenarios

Burden Shift: If All Exemptions Ended

burden_shift <- taxcode_data %>% 
  group_by(agency_name, major_class_code, MuniLevy, major_class_type,taxrate_current, TaxableEAV_perMuni, taxrate_noexemps, Exempted_EAV_perMuni, agency_num) %>%
  summarize(
  #  TaxableEAV = sum((final_tax_to_dist + tax_amt_exe)/taxrate_current),
 #   taxrate_current = mean(taxrate_current),
  #  taxrate_noexemps = mean(taxrate_noexemps),
            
       # percent of levy paid by each property class based on current exemptions
     tax_burden_current = round(sum(TaxableEAV * taxrate_current/ MuniLevy), digits = 9),
     
     # for indirect effect
      tax_burden_inbetween = round(sum(TaxableEAV_b4_exemps* taxrate_current / MuniLevy), digits = 9),
     
     # percent of levy paid by each property class if there were no exemptions
     tax_burden_noexemps = round(sum(TaxableEAV_b4_exemps*taxrate_noexemps / MuniLevy), digits = 9)) %>% 
  mutate(
     

     # difference in percent of the levy being paid by each property class

    overall_burden_shift = tax_burden_noexemps - tax_burden_current,
    direct_effect = tax_burden_inbetween - tax_burden_current,
    indirect_effect = tax_burden_noexemps - tax_burden_inbetween ) 

burden_shift

write.csv(burden_shift, "burden_shift_allMunis_June29.csv")

burden_shift %>%  filter(agency_num %in% c("030120000", "030210000", "030800000", "030920000","030580000","030310000") & major_class_code == 2)

Current burden is the % of the levy paid by each property class.

Assumption: Levy does not change - so the money the municipality needs divided by the pre_exe_eav would be the new tax rate if exemptions didn’t exist.

Note: Burden shift is a percentage point change!

Comparing the original 6 municipalities in the JD excel files:

Interpretation:

3% of the total levy for Chicago that would be paid by class 2 properties is being shifted to other property types due to current exemptions.

8.5% of the total levy in Dolton is currently shifted from class 2 residential properties to other types of properties due to current exemptions in place.

If there were no Homeowners Exemptions

Not checked for accuarcy yet. Not complete in general. Don’t look here. - AWM 6.27.2023

Main difference for this chunk is that all exemptions except homeowners exemptions are subtracted from the EAV value to calculate the post exemption value.

# eval = FALSE, doesn't run 

burdenshift<- taxcode_data %>% 
  # mutate(taxrate_nohome_exemps = ....)
  group_by(agency_name, major_class_code, MuniLevy, major_class_type, taxrate_current, taxrate_nohome_exemps, agency_num) %>%
  summarize(
  #  TaxableEAV = sum((final_tax_to_dist + tax_amt_exe)/taxrate_current),
 #   taxrate_current = mean(taxrate_current),
   taxrate_no_home_exemps = mean(taxrate_noexemps),
            
       # percent of levy paid by each property class based on current exemptions
     tax_burden_current = round(sum(TaxableEAV * taxrate_current/ MuniLevy), digits = 9),
     
     # for indirect effect
      tax_burden_inbetween = round(sum(TaxableEAV_b4_exemps* taxrate_current / MuniLevy), digits = 9),
     
     # percent of levy paid by each property class if there were no exemptions
     tax_burden_no_homeexemps = round(sum((TaxableEAV_b4_exemps-exe_homeowner)*taxrate_noexemps / MuniLevy), digits = 9)) %>% 
  mutate(
     

     # difference in percent of the levy being paid by each property class

    overall_burden_shift = tax_burden_no_homeexemps - tax_burden_current,
    direct_effect = tax_burden_inbetween - tax_burden_current,
    indirect_effect = tax_burden_no_homeexemps - tax_burden_inbetween ) 

burden_shift %>%
  filter(agency_num %in% c("030120000", "030210000", "030800000", "030920000","030580000","030310000") & major_class_code == 2)

If there were no homeowner exemptions, Calumet Park, Broadview, Maywood, Thornton, etc. would experience the largest increase in revenue collected from the elimination of homeowners exemptions. This also means that homeowners in these municipalities are being subsidized the most (as a percentage of the levy) by other types of properties.