Hypothetical Taxbills: Remove General Homestead Exemptions

Bring in data

Bills were pulled from PTAXSIM amd summed to the pin level in files 1_...rmd and 2...rmd. Exemption data per PIN was pulled from PTAXSIM in file 3_.....rmd.

ptaxsim_db_conn <- DBI::dbConnect(RSQLite::SQLite(), "./ptaxsim.db/ptaxsim-2021.0.4.db")

eq_factor <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT*
  FROM eq_factor
  "
)

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_tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT DISTINCT agency_num, tax_code_num
  FROM tax_code
  WHERE agency_num IN ({muni_agency_names$agency_num*})
  AND year = 2021
  ",
  .con = ptaxsim_db_conn
  )
) %>% 
  mutate(tax_code_num = as.numeric(tax_code_num))

tax_codes <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  glue_sql("
  SELECT DISTINCT tax_code_num, tax_code_rate
  FROM tax_code
  WHERE year = 2021
  ",
  .con = ptaxsim_db_conn
  )
) %>% mutate(tax_code_num = as.numeric(tax_code_num))

taxbills_current <- read_csv("./Output/Dont_Upload/1_Get_All_Pins-CookPinTaxbills_2021_Actual.csv")
# 22,453,875 tax bills in 2021 in municipalities. 
# taxing agency-PIN is the unit of observation here (only partial property tax bills)
# Each PIN has multiple rows (because it is taxed by multiple taxing agencies)

sapply(taxbills_current, function(x) sum(is.na(x)))


# 1,825,816 billed properties with 14-digit PINs in incororated areas
# 1,864,594 in incorported and unincorporated areas.
pin14_bills_current <- taxbills_current %>%
  group_by(tax_code, class, pin) %>%
  
  mutate(total_bill = final_tax_to_dist + final_tax_to_tif) %>% # from each taxing agency
  
  summarize(
    total_billed = sum(total_bill, na.rm = TRUE), # total on someone's property tax bill
    av = first(av),
    eav = first(eav),
    # pin_count_in_parcel = n(),
    final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE),
    final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
    tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), # revenue lost due to exemptions
    tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), # total rev before all exemptions
    tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE), # total rev after all exemptions
    rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
    rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
    rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
    tif_share = mean(tif_share, na.rm=TRUE), # not used
  )  %>% 
  mutate(propclass_1dig = str_sub(class, 1, 1))

# head(pin14_bills_current)

sapply(pin14_bills_current, function(x) sum(is.na(x)))


# Now group again and sum values for tax code and each property class!

# There are 28,381 property class - tax code group combinations in incorporated areas.
# There are 28,534 property class - tax code group combos in incorporated + unincorporated areas

rm(taxbills_current)

Add exemption types to pin level tax bills

Need to merge PIN level data of tax bills and exemptions per PIN if the goal is to calculate new tax rates for different scenarios AND view individual level tax bill changes. (i.e. if we want to look at the “median taxpayer” then need to use this method).

Note: The EAV from the exemption table is the original EAV (AV * equalizer) without consider TIFs or exemptions.

31,000 properties have $0 tax bills based on exemption data table made from PTAXSIM’s pin table. (Using the tax bill data allows for more in depth look at tax bills and can create variables such as small_bill if bills were less than $100 after exemptions were applied).

pin_eav <- read_csv("./Output/Dont_Upload/3_Exemption_Details_output-all_cook_pin_exemptions_2021_actual.csv") %>%
  #filter(class_code !=0) %>%
  
  mutate(all_exemptions = exe_homeowner + exe_senior + exe_freeze + exe_longtime_homeowner + 
           exe_disabled + exe_vet_returning + exe_vet_dis_lt50 + exe_vet_dis_50_69 + exe_vet_dis_ge70 + exe_abate) %>%
  mutate(zero_bill = ifelse(eav <= all_exemptions, 1, 0),
         has_HO_exemp = ifelse(exe_homeowner > 0, 1, 0)) %>%
  rename(equalized_AV = eav,
         pin_AV = av)

# table(pin_eav$zero_bill)

table(pin_eav$major_class_code, pin_eav$zero_bill)

# table(pin_eav$major_class_code, pin_eav$has_HO_exemp)



## Add exemption types and values to the tax bill data at PIN level
pin_data <- left_join(pin14_bills_current, pin_eav, by = c("pin", "class" = "class_code" ))

## Add tax code tax rate to PIN level data
pin_data <- left_join(pin_data, tax_codes, by = c("tax_code" = "tax_code_num"))

Calculate new tax rates

# taxcodes_by_class_current <- pin_data %>% 
#   group_by(tax_code, class)  %>%
#   
#   summarize(
#     av = sum(av),
#     eav = sum(eav),
#     equalized_AV = sum(equalized_AV),
#     pins_in_class = n(),
#     current_exemptions = sum(all_exemptions),
#     HO_exemps = sum(exe_homeowner),
#     tax_code_rate = first(tax_code_rate),
#     final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE), # used as LEVY amount!! 
#     final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
#     tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), 
#     tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), 
#     tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE),
#     rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
#     rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
#     rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
#     tif_share = mean(tif_share, na.rm=TRUE), # not used
#   ) %>%
#   
#   mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%
#   rename(cur_comp_TC_rate = tax_code_rate) %>%
#   mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),
#          new_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + HO_exemps) %>%
#   mutate(new_comp_TC_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%
#   mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>%
#   select(tax_code, class, cur_comp_TC_rate, new_comp_TC_rate, current_taxable_eav, new_taxable_eav, everything())
# 
# 
# head(taxcodes_by_class_current) 


taxcodes_current <- pin_data %>% 
  group_by(tax_code)  %>%
  summarize(
    av = sum(av),
    eav = sum(eav),
    equalized_AV = sum(equalized_AV),
    pins_in_class = n(),
    current_exemptions = sum(all_exemptions),
    HO_exemps = sum(exe_homeowner),
    tax_code_rate = first(tax_code_rate), 
    final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE), # used as LEVY amount!! 
    final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
    tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), 
    tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), 
    tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE),
    rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
    rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
    rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
    tif_share = mean(tif_share, na.rm=TRUE), # not used
  ) %>%
  
  mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%
  rename(cur_comp_TC_rate = tax_code_rate) %>%
  mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),
         new_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + HO_exemps) %>%
  mutate(new_comp_TC_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%
  mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>%
  select(tax_code, cur_comp_TC_rate, new_comp_TC_rate, current_taxable_eav, new_taxable_eav, everything())


#head(taxcodes_current) 


taxcode_taxrates <- taxcodes_current %>% 
  select(tax_code, cur_comp_TC_rate, new_comp_TC_rate, current_exemptions, HO_exemps)

Finding the “Median Property” or “Average Taxpayer”

The median assessed value is used to determine the median property for various groups (e.g. the municipality, the 1-digit major property class, and 3-digit property class).

The assessed value and original equalized assessed values come from the pin data table within PTAXSIM. This table also has every type of exemption that the property received and the amount of EAV that was exempt due to the exemption.

Download 4C_joined_PINs from Box link here:. I would move the downloaded file into your R project and then the code below should work without changing the file path.

pin_data2 <- read_csv("./Output/4C_joined_PINs_bills_and_exemptions.csv")

# head(pin_data2)
# dim(pin_data2)

pin_data2 <- pin_data2 %>% 
  mutate(
    bill_current = (final_tax_to_dist + final_tax_to_tif),
    bill_noGHE = new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
    bill_noexemps = new_comp_TC_rate/100*(equalized_AV),
    bill_change = bill_noGHE - bill_current)

C2_munistats <- pin_data2 %>% 
  filter(class > 199 & class < 300) %>%
  group_by(agency_name)  %>%
  arrange(av) %>%
  summarize(
    median_eav = round(median(eav)), 
    median_av = round(median(av)), 
    avg_av = round(mean(av)),
    avg_eav = round(mean(eav)),
    C2_pins_in_muni = n(),
    C2_current_exemptions = sum(all_exemptions, na.rm = TRUE),
    C2_HO_exemps = sum(exe_homeowner, na.rm = TRUE),
  )

eq_2021 <- 3.0027


muni_taxrates <- pin_data2 %>% 
  group_by(agency_name)  %>%
  arrange(av) %>%
  
  summarize(
    muni_median_av = median(av, na.rm=TRUE),
    muni_median_eav = median(eav, na.rm=TRUE),
    muni_mean_av = round(mean(av, na.rm=TRUE)),
    muni_mean_eav = round(mean(eav, na.rm=TRUE)),
    av = sum(av, na.rm = TRUE),
    eav = sum(eav, na.rm = TRUE),
    equalized_AV = sum(equalized_AV, na.rm = TRUE),
    pins_in_muni = n(),
    current_exemptions = sum(all_exemptions, na.rm = TRUE),
    HO_exemps = sum(exe_homeowner, na.rm = TRUE),
    tax_code_rate = mean(tax_code_rate, na.rm = TRUE), # Changed from first() to mean() on Nov 1
    final_tax_to_dist = sum(final_tax_to_dist, na.rm = TRUE), # used as LEVY amount!! 
    final_tax_to_tif = sum(final_tax_to_tif, na.rm = TRUE),
    tax_amt_exe = sum(tax_amt_exe, na.rm = TRUE), 
    tax_amt_pre_exe = sum(tax_amt_pre_exe, na.rm = TRUE), 
    tax_amt_post_exe = sum(tax_amt_post_exe, na.rm = TRUE),
    rpm_tif_to_cps = sum(rpm_tif_to_cps, na.rm = TRUE), # not used
    rpm_tif_to_rpm = sum(rpm_tif_to_rpm, na.rm=TRUE), # not used
    rpm_tif_to_dist = sum(rpm_tif_to_dist, na.rm=TRUE), # not used
    tif_share = mean(tif_share, na.rm=TRUE), # not used
  ) %>%
  
  mutate(total_bill_current = final_tax_to_dist + final_tax_to_tif) %>%
  rename(cur_comp_TC_rate = tax_code_rate) %>%
  mutate(current_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100),
         new_taxable_eav = final_tax_to_dist/(cur_comp_TC_rate/100) + HO_exemps) %>%
  mutate(new_comp_TC_rate = (final_tax_to_dist / new_taxable_eav)*100) %>%
  
  mutate(new_comp_TC_rate = ifelse(is.nan(new_comp_TC_rate), cur_comp_TC_rate, new_comp_TC_rate)) %>% 
  
  left_join(C2_munistats) %>%
  
  mutate(rate_change = cur_comp_TC_rate - new_comp_TC_rate,
    nobillchange_propertyEAV = round(10000 * ((cur_comp_TC_rate/100) / (rate_change/100))),
    nochange_av = round(nobillchange_propertyEAV / eq_2021),
         nochange_ratio = nochange_av / median_av) %>%
  select(agency_name, median_av, nochange_av, nochange_ratio, cur_comp_TC_rate, new_comp_TC_rate, current_taxable_eav, new_taxable_eav, everything())

muni_taxrates 

muni_taxrates %>% write_csv("./Output/4C_muni_taxrates.csv")

Most Common Class 2 Property Type

203 is most common in Riverdale, Phoenix, and Park Forest. 206 is most common in Winnetka

pin_data2 %>% 
  filter(pin_AV > 0 & (class > 199 & class < 300) )%>%
         #major_class_code == "2") %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  group_by(agency_name, class) %>%
  summarize(PIN_count = n()) %>% 
  arrange(-PIN_count)

pin_data2 %>% 
  filter(pin_AV > 0 & (class > 199 & class < 300) ) %>% 
 # filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  group_by(agency_name, class) %>%
  summarize(PIN_count = n()) %>% 
  arrange(agency_name, -PIN_count) %>%
 # group_by(agency_name) %>%
  summarize(agency_name = first(agency_name),
            class = first(class),
            PIN_count = first(PIN_count)
            )

Assessed Value PIN distribution

pin_data2 %>%
  #  filter(major_class_code == 2 & agency_name == "VILLAGE OF PARK FOREST") %>% 
  filter((class > 199 & class < 300) & agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
#  geom_vline(aes(xintercept = median(av, na.rm=TRUE), color = "red")) +
  theme_classic()+
  theme(legend.position = "none") + 
 # geom_text(aes(x=(median(av) + 5000), y = 1500, label = median(av))) +
  scale_x_continuous(limits = c(-5, 50000)) +
  #   scale_y_continuous(limits = c(0, 1500))+
  labs(title = "PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs") +
  facet_wrap(~agency_name)

pin_data2 %>%
  #  filter(major_class_code == 2 & agency_name == "VILLAGE OF PARK FOREST") %>% 
  filter((class > 199 & class < 300) &  agency_name == "VILLAGE OF PHOENIX") %>%
         #  agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  geom_vline(aes(xintercept = median(av, na.rm=TRUE), color = "red")) +
  geom_vline(aes(xintercept = 7974), linetype = "dashed", label = "Breakeven AV") +
  theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=(median(av)), y = 450, label = median(av))) +
  geom_text(aes(x=14000, y = 350), label = "Breakeven AV = $7,974") +
  scale_x_continuous(limits = c(-5, 50000)) +
  #   scale_y_continuous(limits = c(0, 1500))+
  labs(title = "Phoneix PIN AV Distribution for All Class 2 Properties", x="AV", y="Number of PINs",
      caption =  "Median AV is $3,502, Breakeven AV is $7,974") 

pin_data2 %>%
  filter((class > 199 & class < 300) &  agency_name == "CITY OF CHICAGO") %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  geom_vline(aes(xintercept = median(av, na.rm=TRUE), color = "red")) +
  geom_vline(aes(xintercept = 84228), linetype = "dashed") +
  theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=median(av)+20000, y = 300000, label = median(av))) +
 geom_text(aes(x=100000, y = 200000), label = "Breakeven AV = $84,228") +

  scale_x_continuous(limits = c(-5, 500000), labels = scales::dollar) +
  scale_y_continuous(limits = c(0, 300000))+
  labs(title = "Chicago PIN AV Distribution for All Class 2 Properties", 
       x="AV", y="Number of PINs",
      caption =  "Median AV is ~$21,000, Breakeven AV is ~$84,000") 

pin_data2 %>%
  filter((class > 199 & class < 300)  & agency_name == "VILLAGE OF PARK FOREST") %>% 
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  theme_classic()+
  theme(legend.position = "none") + 
  scale_x_continuous(limits = c(-5, 10000)) +
  labs(title = "Park Forest PIN AV Distribution per Class", x="AV", y="Number of PINs") +
  facet_wrap(~class)

pin_data2 %>%
  filter((class > 199 & class < 300)  & agency_name == "VILLAGE OF PHOENIX") %>% 
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  theme_classic()+
  theme(legend.position = "none") + 
  scale_x_continuous(limits = c(-5, 10000)) +
  labs(title = "Phoenix PIN AV Distribution per Class", x="AV", y="Number of PINs") +
  facet_wrap(~class)

pin_data2 %>%
    filter((class > 199 & class < 300)  & agency_name == "VILLAGE OF RIVERDALE") %>% 
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  theme_classic()+
  theme(legend.position = "none") + 
  scale_x_continuous(limits = c(-5, 10000)) +
  labs(title = "Riverdale PIN AV Distribution per Class", x="AV", y="Number of PINs") +
  facet_wrap(~class)


pin_data2 %>%
    filter((class > 199 & class < 300)  & agency_name == "VILLAGE OF WINNETKA") %>% 
  group_by(agency_name) %>%
  ggplot() +
  geom_histogram(aes(x = av, bins = 100)) +
  theme_classic()+
  theme(legend.position = "none") + 
  scale_x_continuous(limits = c(-5, 200000)) +
  labs(title = "Winnetka PIN AV Distribution per Class", x="AV", y="Number of PINs") +
  facet_wrap(~class)

Property Value where Bill doesn’t change

nobillchange <- muni_taxrates

nobillchange %>%       
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA", "CITY OF CHICAGO")) %>%
  ggplot(aes(y=median_av, x = agency_name)) +
  geom_col()+
  #geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 1.5) +
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  scale_x_discrete(label = c("Chicago","Park Forest", "Phoenix", "Riverdale", "Winnetka")) + 
  labs(y = "Median Residential AV", x = "", title = "Median Residential Property Assessed Value - All Class 2 property Types")

nobillchange %>%       
  filter(agency_name %in% c("CITY OF CHICAGO", "VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%
  ggplot(aes(y=(nobillchange_propertyEAV/eq_2021), x = agency_name)) +
  geom_col() +
  geom_text(aes(y=(nobillchange_propertyEAV/eq_2021)+3000, label = round(nobillchange_propertyEAV/eq_2021)) ) +
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  scale_x_discrete(label = c("Chicago", "Park Forest", "Phoenix", "Riverdale", "Winnetka")) + 
  labs(y = "Breakeven Point - Assessed Value", x = "", title = "Residential Property Assessed Value Breakeven Point", caption = "Residential properties above these values would have their bills decrease if the GHE were eliminated.")

nochange <- C2_munistats %>% left_join(muni_taxrates)


muni_taxrates %>%
  ggplot(aes(x=nochange_av, 
             y = median_av, 
             label=agency_name
             )) +
  geom_point() +
 # geom_smooth(method = "lm" )+
  geom_text(aes(x = (nochange_av), y = (median_av+1000)), size = 2)+
  theme_classic() +
  scale_x_continuous(labels = scales::dollar) +
  scale_y_continuous(labels = scales::dollar) +
  labs(x = "AV of Class 2 Property w/ No Bill Change", y = "Median AV of Class 2 Properties in Municipality", title = "Highly valued homes would have lower tax bills if the GHE were eliminated")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
 # filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=agency_name)) +
  geom_point()+
  geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 1.5) +
      scale_x_continuous(labels = scales::dollar) +
      scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners with GHE benefit from removal of the GHE?",
       subtitle = "Yes, if from a muni with high tax rates and if they have a property valued way above other properties in their taxing district",
       caption = "Homeowners in municipalities with high tax rates and AV's much higher
       the median AV in the municipality properties see their bill decrease in GHE removal scenario. 
       These are extreme situations that should not be used for policy decisions or examples.")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE") ) %>%
  filter(is.finite(nobillchange_propertyEAV) )%>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
 # filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=agency_name)) +
  geom_point()+
  geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+500)), size = 2) +
      scale_x_continuous(labels = scales::dollar) +
      scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners have lower tax bills if the GHE exemption was ended?",
       subtitle = "Yes, in very, very rare cases. ")

muni_taxrates %>%
    #  filter(agency_name == "VILLAGE OF WINNETKA") %>%
        filter(median_eav > 150000) %>%
  filter(is.finite(nobillchange_propertyEAV) ) %>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  #filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=agency_name)) +
  geom_point()+
  geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+1000)), size = 2) +
      scale_x_continuous(labels = scales::dollar) +
      scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners with GHE benefit from removal of the GHE?",
       caption = "A $500K home in Winnetka would benefit if everyone else lost their General Homestead Exemption")

muni_taxrates  %>%
  filter(is.finite(nobillchange_propertyEAV) ) %>%
  select(agency_name,   nobillchange_propertyEAV, rate_change, everything()) %>%
#  filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(x=nobillchange_propertyEAV, y = median_eav, label=agency_name)) +
  # geom_smooth(method = "lm" )+
  geom_text(aes(x = (nobillchange_propertyEAV), y = (median_eav+1000)), size = 2)+
  theme_classic() +
  scale_x_continuous(labels = scales::dollar) +
  scale_y_continuous(labels = scales::dollar) +
  labs(x = "EAV of Class 2 Property w/ No Bill Change", y = "Median EAV of Municipality", title = "Can homeowners who claim GHE benefit from removal of the GHE?",
       subtitle = "In very, very rare instances: yes. ")

muni_taxrates %>%
  select(agency_name,   nobillchange_propertyEAV, rate_change, everything()) %>%
#  filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(y=rate_change, x = median_eav, label=agency_name)) +
  geom_point() +
  theme_classic() +
  scale_x_continuous(labels = scales::dollar) +
  labs(x= "EAV of Class 2 Property w/ No Bill Change", y = "Composite Tax Rate Change (Percentage Points)", title = "EAV of Residential Property 'Breakeven Point' & Composite Tax Rate Change 
       from Elimination of GHE")

muni_taxrates %>%
  select(agency_name,   nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=rate_change, x = median_av, label=agency_name)) +
  geom_point() +
  theme_classic() +
  scale_x_continuous(labels = scales::dollar) +
  labs(x= "AV of Class 2 Property w/ No Bill Change", y = "Composite Tax Rate Change (Percentage Points)", title = "AV of Residential Property 'Breakeven Point' & Composite Tax Rate Change 
       from Elimination of GHE")

muni_taxrates %>%
  select(agency_name,   nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=rate_change, x = median_av*10, label=agency_name)) +
  geom_point() +
  theme_classic() +
  scale_x_continuous(labels = scales::dollar) +
  labs(x= "Market Value of Class 2 Property 
       at 'Breakeven Point'", 
       y = "Composite Tax Rate Change 
       (Percentage Points)", 
       title = "Market Value of Residential Property 'Breakeven Point' & 
       Composite Tax Rate Change from Elimination of GHE")

muni_taxrates%>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE")) %>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
 # filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(y=median_av, x = agency_name)) +
  geom_col()+
  #geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 1.5) +
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Median Residential AV", x = "", title = "Median Residential Property Assessed Value - All Class 2 property Types")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE")) %>%

  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
 # filter(nobillchange_propertyEAV < 7000000) %>% 
  ggplot(aes(y=median_av*10, x = agency_name)) +
  geom_col()+
  #geom_text(aes(x =(nobillchange_propertyEAV), y = (median_eav+5000)), size = 1.5) +
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Median Residential Market Value", x = "", title = "Median Residential Market Value (AV*10) - All Class 2 property Types")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE")) %>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=median_eav, x = agency_name)) +
  geom_col()+
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Median Residential EAV", x = "", title = "Median Residential EAV - All Class 2 property Types")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF GLENCOE", "VILLAGE OF WINNETKA")) %>%

  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=median_av, x = agency_name)) +
  geom_col()+
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Median Residential AV", x = "", title = "Median Residential Property AV - All Class 2 property Types")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF GLENCOE", "VILLAGE OF WINNETKA")) %>%

  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=median_av*10, x = agency_name)) +
  geom_col()+
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Median Residential Market Value", x = "", title = "Median Residential Property Market Value - All Class 2 property Types")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%
  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=nobillchange_propertyEAV, x = agency_name)) +
  geom_col()+
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "EAV of Class 2 Property w/ No Bill Change", x = "", title = "Breakeven points - EAV where taxbill wouldn't change if GHE were eliminated",
       caption = "Homeowners in municipalities with high tax rates and AV's much higher
       the median AV in the municipality properties see their bill decrease in GHE removal scenario. 
       These are extreme situations that should not be used for policy decisions or examples.")

muni_taxrates %>%
      filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF RIVERDALE", "VILLAGE OF WINNETKA")) %>%

  select(agency_name, nobillchange_propertyEAV, rate_change, everything()) %>%
  ggplot(aes(y=nobillchange_propertyEAV/3*10, x = agency_name)) +
  geom_col()+
  scale_y_continuous(labels = scales::dollar) +
  theme_classic() +
  labs(y = "Approximate Market Value of Class 2 Property", x = "", title = "Breakeven points - Value of home where taxbill wouldn't change",
       subtitle = "If General Homestead Exemption were Eiminated",
       caption = "A home valued above the points in the graph would experience a lower tax bill if the GHE were eliminated from all other properties.
       These are not the type of properties we are trying to help. Their taxbill going down is NOT a benefit in the big picture.")

EAV PIN distribution

pin_data2 %>%
  filter((class > 199 & class < 300) & agency_name == "VILLAGE OF PARK FOREST") %>% 
  ggplot() +
  geom_histogram(aes(x= eav, bins = 100))+
    geom_vline(aes(xintercept=median(eav, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=(median(eav)+5000), y = 1500, label = median(eav))) +
   scale_x_continuous(limits = c(-5,100000)) +
#   scale_y_continuous(limits = c(0, 1500))+
  labs(title="Park Forest's PIN EAV distribution", x= "EAV", y = "Number of PINs")


pin_data2 %>%
  filter((class > 199 & class < 300) & agency_name == "VILLAGE OF RIVERDALE") %>% 
  ggplot() +
  geom_histogram(aes(x= eav, bins = 100))+
    geom_vline(aes(xintercept=median(eav, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=(median(eav)+5000), y = 1500, label = median(eav))) +
   scale_x_continuous(limits = c(-5,100000)) +
#   scale_y_continuous(limits = c(0, 1500))+
  labs(title = "Riverdale's PIN EAV distribution", x = "EAV", y = "Number of PINs")


pin_data2 %>%
  filter((class > 199 & class < 300) & agency_name == "VILLAGE OF PHOENIX") %>% 
  ggplot() +
  geom_histogram(aes(x= eav, bins = 100))+
    geom_vline(aes(xintercept=median(eav, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=(median(eav)+5000), y = 400, label = median(eav))) +
   scale_x_continuous(limits = c(-5,100000)) +
#   scale_y_continuous(limits = c(0, 1500))+
  labs(title="Phoenix's PIN EAV distribution", x = "EAV", y = "Number of PINs")


pin_data2 %>%
  filter((class > 199 & class < 300) & agency_name == "VILLAGE OF WINNETKA") %>% 
  ggplot() +
  geom_histogram(aes(x= eav, bins = 100))+
    geom_vline(aes(xintercept=median(eav, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
  geom_text(aes(x=(median(eav)+50000), y = 1500, label = median(eav))) +
   scale_x_continuous(limits = c(-5, 3000000), label = scales::dollar) +
#   scale_y_continuous(limits = c(0, 1500))+
  labs(title="Winnetka's PIN EAV distribution", x="EAV", y = "Number of PINs")

Bill Distributions

pin_data2 %>%
  filter(major_class_code == 2 & bill_current < 30000 & agency_name == "VILLAGE OF PARK FOREST") %>% ggplot() +
  geom_histogram(aes(x= bill_current, bins = 100))+
    geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

  scale_x_continuous(limits = c(-5,20000), label = scales::dollar) +
  scale_y_continuous(limits = c(0, 1500), label = scales::comma)+
  labs(x="PF's Actual Tax Bills With Current Exemptions", y = "Number of Tax Bills")


pin_data2 %>%
  group_by(has_HO_exemp) %>%
  filter(major_class_code == 2 & bill_current < 30000 & agency_name == "VILLAGE OF PARK FOREST") %>% ggplot() +
  geom_histogram(aes(x= bill_current, bins = 100))+
    theme_classic()+theme(legend.position = "none") + 
  scale_x_continuous(limits = c(-5,20000), label = scales::dollar) +
  scale_y_continuous(limits = c(0, 1500), label = scales::comma)+
  labs(x="PF's Actual Tax Bills With Current Exemptions", y = "Number of Tax Bills") +
  facet_wrap(~has_HO_exemp)

pin_data2 %>%
  filter(major_class_code == 2 & bill_noexemps < 30000 & agency_name == "VILLAGE OF PARK FOREST") %>% ggplot() +
  geom_histogram(aes(x= bill_noexemps, bins = 100))+
    geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

  scale_x_continuous(limits = c(-5,20000), label = scales::dollar) + 
    scale_y_continuous(limits = c(0, 1500), label = scales::comma)+

  labs(x="PF's Tax Bills Without General Homestead Exemptions", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF PHOENIX") %>% ggplot() +
  geom_histogram(aes(x= bill_current, bins = 100))+
    geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

  scale_x_continuous(limits = c(-5,20000)) + 
   scale_y_continuous(limits = c(0, 600))+

  labs(title="PHOENIX's Current Tax Bill Distribution", x = "Dollars", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF PHOENIX") %>% ggplot() +
  geom_histogram(aes(x= bill_noexemps, bins = 100))+
    geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

  scale_x_continuous(limits = c(-5,20000)) + 
    scale_y_continuous(limits = c(0, 600))+

  labs(title="PHOENIX's Tax Bills Without General Homestead Exemptions", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF WINNETKA") %>% ggplot() +
  geom_histogram(aes(x= bill_current, bins = 100))+
    geom_vline(aes(xintercept=median(bill_current, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

 # scale_x_continuous(limits = c(-5,20000)) +
 # scale_y_continuous(limits = c(0, 80000)) +
  labs(title="WINNETKA's Actual Tax Bills With Current Exemptions", x= "Dollars", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2  & agency_name == "VILLAGE OF WINNETKA") %>% ggplot() +
  geom_histogram(aes(x= bill_noexemps, bins = 100))+
    geom_vline(aes(xintercept=median(bill_noexemps, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 

  #scale_x_continuous(limits = c(-5,20000)) + 
  #scale_y_continuous(limits = c(0, 80000)) +

  labs(title="WINNETKA's Tax Bills Without General Homestead Exemptions", x="Dollars", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF PARK FOREST") %>% 
  ggplot() +
  geom_histogram(aes(x= bill_change, bins = 100))+
    geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
    scale_x_continuous(limits = c(-10000,10000)) +

#   scale_y_continuous(limits = c(0, 2000))+
  labs(title="Park Forest's Change in Tax Bills", subtitle =  "Hypothetical with no General Homestead Exemptions", x = "Change in Tax Bill ($)", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF PHOENIX") %>% ggplot() +
  geom_histogram(aes(x= bill_change, bins = 100))+
    geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color = "red")) +
    theme_classic()+ theme(legend.position = "none") + 
   scale_x_continuous(limits = c(-10000,10000)) +

#   scale_x_continuous(limits = c(-2800,2800)) + 
#   scale_y_continuous(limits = c(0, 2000))+

  labs(title ="PHOENIX's Change in Tax Bills", subtitle = "Hypothetical with no General Homestead Exemptions", x = "Change in Tax Bill ($)", y = "Number of Tax Bills")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF WINNETKA") %>% ggplot() +
  geom_histogram(aes(x= bill_change, bins = 100))+
    geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color = "red")) +
    theme_classic()+theme(legend.position = "none") + 
   scale_x_continuous(limits = c(-10000,10000)) +

#  scale_x_continuous(limits = c(-5,20000)) + 
#    scale_y_continuous(limits = c(0, 1500))+

  labs(title="WINNETKA's Change in Tax Bills Without General Homestead Exemptions", y = "Number of Tax Bills", x = "Dollars")

pin_data2 %>%
  filter(major_class_code == 2 & agency_name == "VILLAGE OF RIVERDALE") %>% ggplot() +
  geom_histogram(aes(x= bill_change, bins = 100))+
    geom_vline(aes(xintercept=median(bill_change, na.rm=TRUE), color = "red")) +
    theme_classic()+
  theme(legend.position = "none") + 
   scale_x_continuous(limits = c(-10000,10000)) +
labs(title="RIVERDALE's Change in Tax Bills Without General Homestead Exemptions", y = "Number of Tax Bills", x = "Dollars")

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA")) %>%
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, zero_bill, has_HO_exemp) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            mean_bill_cur = mean(bill_current, na.rm=TRUE),
            median_bill_cur = median(bill_current),
            mean_bill_new = mean(bill_noexemps, na.rm=TRUE),
            median_bill_new = median(bill_noexemps),
            mean_change = mean(bill_change, na.rm=TRUE),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            mean_bill_cur = mean(bill_current, na.rm=TRUE),
            median_bill_cur = median(bill_current),
            mean_bill_new = mean(bill_noexemps, na.rm=TRUE),
            median_bill_new = median(bill_noexemps),
            mean_change = mean(bill_change, na.rm=TRUE),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA","VILLAGE OF RIVERDALE")) %>%
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            mean_bill_cur = mean(bill_current, na.rm=TRUE),
            median_bill_cur = median(bill_current),
            mean_bill_new = mean(bill_noexemps, na.rm=TRUE),
            median_bill_new = median(bill_noexemps),
            mean_change = mean(bill_change, na.rm=TRUE),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

tax_bill_change_HO <- pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class == 203) %>% 
  arrange(av) %>%
    mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            median_bill_cur = round(median(bill_current)),
            median_bill_new = round(median(bill_noexemps)),
            median_change = round(median(bill_change)),
            pincount=n(),
            perceived_savings = median(tax_amt_exe))

tax_bill_change_HO

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
    filter(class == 203) %>% 
  arrange(av) %>%
  mutate(median_EAV = median(eav),
            median_bill_cur = round(median(bill_current)),
            median_bill_new = round(median(bill_noexemps)),
            median_change = round(median(bill_change)),
            pincount=n(),
            perceived_savings = median(tax_amt_exe))

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class == 203) %>% 
  arrange(av) %>%
    mutate(bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, zero_bill, has_HO_exemp) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            median_bill_cur = round(median(bill_current)),
            median_bill_new = round(median(bill_noexemps)),
            median_change = round(median(bill_change)),
            pincount=n(),
            perceived_savings = median(tax_amt_exe))

If only “removing”/“refunding” the general homestead exemption:

For Park Forest class 203 properties, the median tax bill would go down $2,088 for those who don’t currently have a general homestead exemption and would increase $2,314 for people who do currently claim the general homestead exemption. 779 people don’t claim the exemption and 1398 people do. A home with EAV above 88,298 EAV would experience a lower tax bill and homes below 88,298 EAV would experience an increase in their taxbill. The median EAV for all class 2 properties is 18,320 in Park Forest.

Phoenix class 203 median properties have a decrease of $1,481 if they didn’t claim homestead exemptions currently and increased $1,468 f they did already claim the general homestead exemption. 137 people do not claim the exemption and 209 people do claim the general homestead exemption. A home with EAV above 79,946 EAV would experience a lower tax bill and homes below 29,946 EAV would experience an increase in their taxbill. The median EAV for all class 2 properties is 10,515 in Phoenix.

Riverdale class 203 median properties have a decrease of $2095 if they didn’t claim homestead exemptions currently and increased $1,155 f they did already claim the general homestead exemption. 209 people do not claim the exemption and 443 people do claim the general homestead exemption. A home with EAV above 170,261 EAV would experience a lower tax bill and homes below 170,261 EAV would experience an increase in their taxbill.

The median EAV for all Class 2 properties is 17,163 in Riverdale.

Winnetka class 203 median properties have a decrease of $281 if they didn’t claim homestead exemptions currently and increased $608 f they did already claim the general homestead exemption. 37 people do not claim the exemption and 91 people do claim the general homestead exemption. A home with EAV above 5,159,327 EAV would experience a lower tax bill and homes below 5,159,327 EAV would experience an increase in their taxbill. The median EAV for all Class 2 properties is 249,018 EAV in Winnetka.

Muni Comparison of AVs and Tax Bills

Compare AV=10,000

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class > 199 & class < 300) %>% 
  arrange(av) %>%
    filter(av > 9500 & av < 10500) %>%

 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, zero_bill, has_HO_exemp) %>% 
  summarize(median_AV = median(av),
            median_EAV = median(eav),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
    filter(av > 9500 & av < 10500) %>%

 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>% 
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            median_EAV = median(eav),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
  filter(av > 9500 & av < 10500) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name) %>% 
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            median_EAV = median(eav),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

Class 203 only

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class ==203) %>% 
  arrange(av) %>%
  filter(av > 9500 & av < 10500) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class == 203) %>% 
  arrange(av) %>%
  filter(av > 9500 & av < 10500) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            median_EAV = median(eav),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
  filter(class == 203) %>% 
  arrange(av) %>%
  filter(av > 9500 & av < 10500) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            median_EAV = median(eav),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX",  "VILLAGE OF RIVERDALE")) %>%
 filter(class == 203) %>% 
# filter(class >199 & class < 300) %>% 

  arrange(av) %>%
  filter(av > 9000 & av < 11000) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)

Export Data for All Municipalities

Exported file uses an AV of 15000 for the PIN AV range

# 
# ## AV range around 10000, all Class 2
# 
# pin_data2 %>% 
# filter(pin_AV > 0) %>% 
#   filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX",  "VILLAGE OF RIVERDALE")) %>%
#  # filter(class == 203) %>% 
#  filter(class >199 & class < 300) %>% 
# 
#   arrange(av) %>%
#   filter(av > 9000 & av < 11000) %>%
#  # group_by(agency_name, has_HO_exemp) %>% 
#   mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
#     # current bill = current tax rate * portion of levy billed
#          bill_current = (final_tax_to_dist + final_tax_to_tif),
#          bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
#          bill_change = bill_noexemps - bill_current) %>%
#   group_by(agency_name, has_HO_exemp) %>%
#     summarize(median_AV = median(av),
#             median_equalizedAV =  median(equalized_AV),
#             taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
#             median_bill_cur = median(bill_current),
#             median_bill_new = median(bill_noexemps),
#             median_change = median(bill_change),
#             perceived_savings = median(tax_amt_exe),
#             cur_comp_TC_rate = mean(cur_comp_TC_rate),
#             new_comp_TC_rate = mean(new_comp_TC_rate),
#             class = first(class),
#             pincount=n()
# )
# #write_csv(medianEAVs_inMunis, "requests_medianEAV_inMunis.csv" )


## AV range around $15,000, all Class 2
pin_data2 %>% 
filter(pin_AV > 0) %>% 
 filter(class >199 & class < 300) %>% 

  arrange(av) %>%
  filter(av > 14000 & av < 16000) %>%
  mutate(
         # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            class = mode(class),
            pincount=n()) # %>% write_csv("requests_av15000_allMunis.csv")

# Calculations for bill change the median EAV in each muni
medianEAVs_inMunis <- pin_data2 %>% 
  filter(pin_AV > 0) %>% 
  filter(class >199 & class < 300) %>% 
  arrange(av) %>%
  mutate(
    # current bill = current tax rate * portion of levy billed
    bill_current = (final_tax_to_dist + final_tax_to_tif),
    bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
    bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>%
  summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n())

# Bill change calculations for properties with AVs of $150,000
pin_data2 %>% 
filter(pin_AV > 0) %>% 
 filter(class >199 & class < 300) %>% 
  arrange(av) %>%
  filter(av > 140000 & av < 160000) %>%
  mutate(
         # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name, has_HO_exemp) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            class = first(class),
            pincount=n()) # %>% write_csv("requests_av150000_allMunis.csv")

pin_data2 %>% 
filter(pin_AV > 0) %>% 
  filter(agency_name %in% c("VILLAGE OF PARK FOREST", "VILLAGE OF PHOENIX", "VILLAGE OF WINNETKA", "VILLAGE OF RIVERDALE")) %>%
#  filter(class == 203) %>% 
    filter(class >199 & class < 300) %>% 

  arrange(av) %>%
  filter(av > 14500 & av < 15500) %>%
 # group_by(agency_name, has_HO_exemp) %>% 
  mutate(#taxable_eav = final_tax_to_dist / tax_code_rate,
    # current bill = current tax rate * portion of levy billed
         bill_current = (final_tax_to_dist + final_tax_to_tif),
         bill_noexemps =  new_comp_TC_rate/100*(equalized_AV-all_exemptions+exe_homeowner),
         bill_change = bill_noexemps - bill_current) %>%
  group_by(agency_name) %>%
    summarize(median_AV = median(av),
            median_equalizedAV =  median(equalized_AV),
            taxable_eav = mean(equalized_AV-all_exemptions+exe_homeowner),
            median_bill_cur = median(bill_current),
            median_bill_new = median(bill_noexemps),
            median_change = median(bill_change),
            perceived_savings = median(tax_amt_exe),
            cur_comp_TC_rate = mean(cur_comp_TC_rate),
            new_comp_TC_rate = mean(new_comp_TC_rate),
            pincount=n()
)