Incorrect disabled veteran exemption values in database

[AleaWM]

the pin table in the database appears to be missing values for some PINs that received disabled veteran exemptions. These values appear to be missing in multiple years. Values are recorded as 0 instead of the amount of EAV exempt.

tax_bill_total in pin table of database is correctly recorded as $0, homeowner exemptions, senior, exemptions, freeze exemptions, etc, appear to be correct.

tax_amt_post_exe, final_tax_to_dist, & final_tax_to_tif created with tax_bill() is also incorrect for some of these PINs.

PIN examples:

• 31274090020000 (missing 2016-2023, tax_bill() has total_billed value of $4,554, includes correct exe_homeowner amount but missing disabled veterans exemption amount in pin table)
• 06134050330000 (missing 2020, 2021, 2023 in pin table. creates incorrect final_tax_to_dist, total_billed, tax_amt_post_exe from tax_bill() for 2023, has correct exe_homeowner amount. COE in 2022 not reflected)
• 17344230080000 (missing 2018-2023)
• 02214040310000 (missing 2018-2023 and tax_bill() shows a tax bill of $24,478 for 2023 instead of $0.)
• 05204080120000 (missing 2019-2022, COE in 2023 not reflected)

[AleaWM]

1. A follow up comment explaining how this was identified:

Subtracting the exemptions from the eav in the pin table results in post-exemption EAV values that would not result in a tax bill of $0. Around 7,000 observations were identified with large amounts of "remaining" taxable EAV after exemptions that still had a $0 tax bill. Exemption values and taxbills were checked online to see if there was some nuance with the tax bills that was not included in the PTAXSIM database. While doing this, the only reoccurring pattern identified were PINs that appeared to be missing the amount of exempt EAV for exe_vet_dis_ge70 that would wipe the remaining taxable EAV to 0 and result in a $0 tax bill.

2. A list of example PINs that are slightly more thoroughly recorded and maybe easier to read:

	Source: https://www.cookcountypropertyinfo.com/	Source: https://www.cookcountyassessor.com/address-search Values match PIN table in PTAXSIM db
PIN	Treasurer Exemps.	CCAO Exemps.	Municipality	FMV
16063130270000	GHE; Dis. Vet	GHE	Oak Park	$940,000
19362220200000	GHE; Dis. Vet.	GHE	Chicago	$140,000
13231160160000	GHE; Dis. Vet	GHE	Chicago	$670,000
27181040410000	GHE; Dis. Vet	GHE	Orland Park	$700,000
05204080120000	GHE; Dis. Vet	GHE	Winnetka	$872,230
05332030150000	GHE; Senior; Dis. Vet	GHE; Senior	Wilmette	$875,000
02214040310000	GHE; Dis. Vet	GHE	Palatine	$860,000
31274090020000	GHE; Dis. Vet	GHE	Richton Park	$140,000
20302010100000	GHE; Senior; Dis. Vet	GHE; Senior	Chicago	$250,000
06134050330000	GHE; Dis. Vet	GHE	Streamwood	$220,000
18173030070000	GHE; Senior; Dis. Vet.	GHE	La Grange	$890,010
10244020370000	GHE; Dis. Vet.	GHE	Evanston	$800,000
22241070050000	GHE; Dis. Vet	GHE	Lemont	$810,000
27291170100000	GHE; Dis. Vet	GHE	Orland Park	$880,000
10113030300000	GHE; Dis. Vet	GHE	Evanston	$779,240
04063050210000	GHE; Dis. Vet	GHE	Northbrook	$730,010
22331180040000	GHE; Dis. Vet	GHE	Lemont	$700,000
09354170170000	GHE; Senior; Dis. Vet.	GHE; Senior	Park Ridge	$700,000
14302080030000	GHE; Dis. Vet.	GHE	Chicago	$670,010
01022060290000	GHE; Dis. Vet.	GHE	Barrington	$510,000
17202330810000	GHE; Dis. Vet.	GHE	Chicago	$500,000
15344030260000	GHE; Dis. Vet.	GHE	Brookfield	$500,310 (Treasurer); $486,830 (CCAO)
03082070270000	GHE; Senior; Dis. Vet.	GHE; Senior	Arlington Heights	$500,000
06202050020000	GHE; Dis. Vet.	GHE	Elgin	$410,000
02173070020000	GHE; Dis. Vet.	GHE	Inverness	$403,620
13053220250000	GHE; Senior; Dis. Vet.	GHE; Senior	Chicago	$430,000
04251080120000	GHE; Senior; Dis. Vet.	GHE; Senior	Glenview	$455,410 (Treasurer); $412,930 (CCAO)
18172080200000	GHE; Senior; Dis. Vet.	GHE; Senior	La Grange	$410,000

[kyrasturgill]

Hi Alea,
I'm sorry for not commenting on this issue until now! We've been running into this problem as well where there are not exemptions present in the PTAXSIM data to account for a taxable EAV to be reduced to $0. We are currently working on fixing this issue in the PTAXSIM TY2024 database update. Our first goal is to ensure we have complete exemption data for 2024, and then we'll look into correcting this issues for prior years. So while this issue has not been corrected yet, it's definitely on our radar and we hope to have address is with the upcoming data update. Let me know if you have any questions!

[AleaWM]

No worries, and thanks for the response!

I used a wonky workaround to adjust the EAV extracted from the data tables within PTAXSIM when calculating all my aggregate stats and it seemed to work out okay so far.

# other code not relevant to issue .... |> 
mutate( 
      eq_av = av_clerk*eq_factor,
      exe_total_old = rowSums(across(starts_with("exe_"))),
      exe_total_old = ifelse(exe_total_old > eq_av, eq_av, exe_total_old),
      
      # create variables that appear from taxbill() function
      taxed_eav_old = av_clerk*eq_factor - exe_total_old,      # but exe_total was missing some exemptions.
      
      flag_missingdata = ifelse(taxed_eav_old > 1000 & tax_bill_total == 0 & c2_prop == 1, 1, 0)) |>
    
    mutate(exe_missing_disvet = ifelse(taxed_eav_old > 1000 & tax_bill_total == 0 & c2_prop == 1, taxed_eav_old, 0 )) |>
    
    mutate(taxed_eav_adj = ifelse(taxed_eav_old > 1000 & flag_missingdata == 1, 0 , taxed_eav_old),
           total_taxed_eav_AWM = tax_bill_total / tax_code_rate,  # EAV that was taxed by TIFs and taxing districts
           taxed_eav_TIFincrement = total_taxed_eav_AWM*tax_code_distribution_pct,
           taxed_eav_nonTIF = total_taxed_eav_AWM*(1-tax_code_distribution_pct)) |>
    
    mutate(exe_total_adj = rowSums(across(starts_with("exe_"))) - exe_total_old)  |>  ..... # other code not relevant for issue 

Thanks for all the time and effort put into updating PTAXSIM!