Introduction to PTAXSIM

This vignette introduces the PTAXSIM package, including its: main function, primary inputs, basic abilities, and common uses. Advanced use cases are described in separate vignettes. Familiarity with the Cook County property tax system is useful, but not essential to follow this vignette.

In this vignette, we will:

Start with the basics - what is PTAXSIM, its main function, the main function’s inputs;
Then we will look at common scenarios - calculating a bill after a change in assessed value, getting the change in a bill’s proportions over time, etc.

1. Basics

What is PTAXSIM?

PTAXSIM is a software package to estimate Cook County property tax bills. It has two parts:

A database file containing the historical data (>= 2006) necessary to estimate individual property tax bills
A highly-optimized R package that utilizes the database to quickly and efficiently estimate tax bills

By default, PTAXSIM uses the data in the database to estimate historical tax bills. However, you can also provide counterfactual data in order to generate counterfactual bills.

Note that:

The R package cannot function without the separate database, as its functions query the database directly.
PTAXSIM’s output bills are estimates which may not be identical to real bills. This is due to differences in rounding, math, and data between PTAXSIM and the real tax calculation program.
All of PTAXSIM’s inputs and outputs are data.tables to facilitate efficient operation chaining and/or by-reference column updates. This enables fast and memory-efficient calculations, which are needed to perform large-scale counterfactual simulations.

You must instantiate a database connection object with the name ptaxsim_db_conn before using PTAXSIM. Here is an example connection:

library(data.table)
library(dplyr)
library(here)
library(ptaxsim)

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

The main function - `tax_bill()`

PTAXSIM has a single primary function - tax_bill() - with two required arguments:

year_vec - A numeric vector of tax years
pin_vec - A character vector of 14-digit Property Index Numbers (PINs), with no dashes or spaces

The output is a data.table containing the tax amount directed to each taxing district, by PIN and year. Let’s look at an example using just one property and year:

bill <- tax_bill(year_vec = 2024, pin_vec = "13264290020000")
print(bill)
#> Key: <year, pin, agency_num>
#>      year            pin  class tax_code    av    eav agency_num
#>     <int>         <char> <char>   <char> <int>  <int>     <char>
#>  1:  2024 13264290020000    211    71163 63875 193893  010010000
#>  2:  2024 13264290020000    211    71163 63875 193893  010020000
#>  3:  2024 13264290020000    211    71163 63875 193893  030210000
#>  4:  2024 13264290020000    211    71163 63875 193893  030210002
#>  5:  2024 13264290020000    211    71163 63875 193893  043030000
#>  6:  2024 13264290020000    211    71163 63875 193893  044060000
#>  7:  2024 13264290020000    211    71163 63875 193893  050200000
#>  8:  2024 13264290020000    211    71163 63875 193893  050200001
#>  9:  2024 13264290020000    211    71163 63875 193893  080180000
#> 10:  2024 13264290020000    211    71163 63875 193893  140030000
#>                      agency_name     agency_major_type agency_minor_type
#>                           <char>                <char>            <char>
#>  1:               COUNTY OF COOK           COOK COUNTY              COOK
#>  2: FOREST PRESERVE DISTRICT ...           COOK COUNTY              COOK
#>  3:              CITY OF CHICAGO MUNICIPALITY/TOWNSHIP              MUNI
#>  4: CITY OF CHICAGO SCHOOL BL... MUNICIPALITY/TOWNSHIP              MISC
#>  5: CHICAGO COMMUNITY COLLEGE...                SCHOOL         COMM COLL
#>  6:           BOARD OF EDUCATION                SCHOOL           UNIFIED
#>  7:        CHICAGO PARK DISTRICT         MISCELLANEOUS              PARK
#>  8: CHICAGO PARK DISTRICT AQU...         MISCELLANEOUS              BOND
#>  9: METRO WATER RECLAMATION D...         MISCELLANEOUS             WATER
#> 10: LOGAN AVONDALE HERMOSA EX...         MISCELLANEOUS            HEALTH
#>     agency_tax_rate final_tax
#>               <num>     <num>
#>  1:      0.00390469    718.04
#>  2:      0.00068588    126.13
#>  3:      0.01614882   2969.65
#>  4:      0.00129569    238.27
#>  5:      0.00149480    274.88
#>  6:      0.03630964   6677.09
#>  7:      0.00294209    541.03
#>  8:      0.00000000      0.00
#>  9:      0.00340445    626.06
#> 10:      0.00022293     40.99

Output columns

Column Name	Description	Possible Valus
`year`	Tax year of the bill. Note that bills are paid in arrears, so a 2021 bill is paid in 2022	Must be >= 2006
`pin`	Property Index Number of the property. This is the unique identifier of each parcel in Cook County
`class`	The 3-digit Assessor minor class code. This identifies the property type/structure	See class dictionary
`tax_code`	The 5-digit tax code. Each tax code identifies the unique combination of taxing districts that a PIN is within. Note that the first 2 digits of `tax_code` are the Assessor township code
`av`	Assessed value of the property. By default, this is the finalized AV after all appeals have been completed. See optional arguments below for more details	Must be >= 0
`eav`	The `av` multiplied by the state equalization factor	Must be >= 0
`agency_num`	Unique numeric identifier for each taxing body/district. Each `pin` is typically taxed by multiple taxing districts. `tax_bill()` will output 1 row for each district for each PIN and year. The PIN above is being taxed by 11 districts
`agency_name`	Name of the taxing district
`agency_major_type`	Type of taxing district. Similar to the grouping used on real tax bills	COOK COUNTY, MISCELLANEOUS, MUNICIPALITY/TOWNSHIP, SCHOOL
`agency_minor_type`	Sub-type of taxing district. Much more fine-grained	BOND, COOK, COMM COLL, ELEMENTARY, FIRE, GEN ASST, HEALTH, INFRA, LIBRARY, MISC, MOSQUITO, MUNI, PARK, POLICE, SANITARY, SECONDARY, SSA, TIF, TOWNSHIP, UNIFIED, WATER
`agency_tax_rate`	The tax rate for the PIN and district, which is calculated from each district’s levy and total tax base
`final_tax`	The final tax (in dollars) the PIN owes to the taxing district. Equal to `agency_tax_rate * eav` in cases without exemptions

Multiple PINs or years

The tax_bill() function can take multiple years in the year_vec argument:

bills <- tax_bill(2010:2024, "13264290020000")
print(bills, topn = 3)
#> Key: <year, pin, agency_num>
#>       year            pin  class tax_code    av    eav agency_num
#>      <int>         <char> <char>   <char> <int>  <int>     <char>
#>   1:  2010 13264290020000    211    71001 34882 115111  010010000
#>   2:  2010 13264290020000    211    71001 34882 115111  010020000
#>   3:  2010 13264290020000    211    71001 34882 115111  030210000
#>  ---                                                             
#> 153:  2024 13264290020000    211    71163 63875 193893  050200001
#> 154:  2024 13264290020000    211    71163 63875 193893  080180000
#> 155:  2024 13264290020000    211    71163 63875 193893  140030000
#>                       agency_name     agency_major_type agency_minor_type
#>                            <char>                <char>            <char>
#>   1:               COUNTY OF COOK           COOK COUNTY              COOK
#>   2: FOREST PRESERVE DISTRICT ...           COOK COUNTY              COOK
#>   3:              CITY OF CHICAGO MUNICIPALITY/TOWNSHIP              MUNI
#>  ---                                                                     
#> 153: CHICAGO PARK DISTRICT AQU...         MISCELLANEOUS              BOND
#> 154: METRO WATER RECLAMATION D...         MISCELLANEOUS             WATER
#> 155: LOGAN AVONDALE HERMOSA EX...         MISCELLANEOUS            HEALTH
#>      agency_tax_rate final_tax
#>                <num>     <num>
#>   1:      0.00423000    282.30
#>   2:      0.00051000     34.04
#>   3:      0.00914000    609.97
#>  ---                          
#> 153:      0.00000000      0.00
#> 154:      0.00340445    626.06
#> 155:      0.00022293     40.99

And multiple PINs in the pin_vec argument:

bills <- tax_bill(2024, c("13264290020000", "07101010391078", "10153080520000"))
print(bills, topn = 3)
#> Key: <year, pin, agency_num>
#>      year            pin  class tax_code    av    eav agency_num
#>     <int>         <char> <char>   <char> <int>  <int>     <char>
#>  1:  2024 07101010391078    299    35011 20725  62911  010010000
#>  2:  2024 07101010391078    299    35011 20725  62911  010010001
#>  3:  2024 07101010391078    299    35011 20725  62911  010020000
#> ---                                                             
#> 34:  2024 13264290020000    211    71163 63875 193893  050200001
#> 35:  2024 13264290020000    211    71163 63875 193893  080180000
#> 36:  2024 13264290020000    211    71163 63875 193893  140030000
#>                      agency_name agency_major_type agency_minor_type
#>                           <char>            <char>            <char>
#>  1:               COUNTY OF COOK       COOK COUNTY              COOK
#>  2:       CONSOLIDATED ELECTIONS       COOK COUNTY              COOK
#>  3: FOREST PRESERVE DISTRICT ...       COOK COUNTY              COOK
#> ---                                                                 
#> 34: CHICAGO PARK DISTRICT AQU...     MISCELLANEOUS              BOND
#> 35: METRO WATER RECLAMATION D...     MISCELLANEOUS             WATER
#> 36: LOGAN AVONDALE HERMOSA EX...     MISCELLANEOUS            HEALTH
#>     agency_tax_rate final_tax
#>               <num>     <num>
#>  1:      0.00390469    206.60
#>  2:      0.00000000      0.00
#>  3:      0.00068588     36.29
#> ---                          
#> 34:      0.00000000      0.00
#> 35:      0.00340445    626.06
#> 36:      0.00022293     40.99

Passing year_vec and pin_vec of different lengths will yield the Cartesian product of those vectors:

bills <- tax_bill(
  year_vec = 2006:2024,
  pin_vec = c("13264290020000", "07101010391078", "10153080520000")
)
print(bills, topn = 3)
#> Key: <year, pin, agency_num>
#>       year            pin  class tax_code    av    eav agency_num
#>      <int>         <char> <char>   <char> <int>  <int>     <char>
#>   1:  2006 10153080520000    211    24023 54989 148888  010010000
#>   2:  2006 10153080520000    211    24023 54989 148888  010010001
#>   3:  2006 10153080520000    211    24023 54989 148888  010020000
#>  ---                                                             
#> 699:  2024 13264290020000    211    71163 63875 193893  050200001
#> 700:  2024 13264290020000    211    71163 63875 193893  080180000
#> 701:  2024 13264290020000    211    71163 63875 193893  140030000
#>                       agency_name agency_major_type agency_minor_type
#>                            <char>            <char>            <char>
#>   1:               COUNTY OF COOK       COOK COUNTY              COOK
#>   2:       CONSOLIDATED ELECTIONS       COOK COUNTY              COOK
#>   3: FOREST PRESERVE DISTRICT ...       COOK COUNTY              COOK
#>  ---                                                                 
#> 699: CHICAGO PARK DISTRICT AQU...     MISCELLANEOUS              BOND
#> 700: METRO WATER RECLAMATION D...     MISCELLANEOUS             WATER
#> 701: LOGAN AVONDALE HERMOSA EX...     MISCELLANEOUS            HEALTH
#>      agency_tax_rate final_tax
#>                <num>     <num>
#>   1:      0.00500000    215.83
#>   2:      0.00000000      0.00
#>   3:      0.00057000     24.61
#>  ---                          
#> 699:      0.00000000      0.00
#> 700:      0.00340445    626.06
#> 701:      0.00022293     40.99

Passing year_vec and pin_vec of the same length will match the vectors element-wise:

bills <- tax_bill(
  year_vec = c(2012, 2021, 2024),
  pin_vec = c("13264290020000", "07101010391078", "10153080520000")
)
print(bills, topn = 3)
#> Key: <year, pin, agency_num>
#>      year            pin  class tax_code    av    eav agency_num
#>     <int>         <char> <char>   <char> <int>  <int>     <char>
#>  1:  2012 13264290020000    211    71001 37280 104593  010010000
#>  2:  2012 13264290020000    211    71001 37280 104593  010020000
#>  3:  2012 13264290020000    211    71001 37280 104593  030210000
#> ---                                                             
#> 35:  2024 10153080520000    211    24023 51260 155600  050970000
#> 36:  2024 10153080520000    211    24023 51260 155600  080180000
#> 37:  2024 10153080520000    211    24023 51260 155600  090020000
#>                      agency_name     agency_major_type agency_minor_type
#>                           <char>                <char>            <char>
#>  1:               COUNTY OF COOK           COOK COUNTY              COOK
#>  2: FOREST PRESERVE DISTRICT ...           COOK COUNTY              COOK
#>  3:              CITY OF CHICAGO MUNICIPALITY/TOWNSHIP              MUNI
#> ---                                                                     
#> 35:         SKOKIE PARK DISTRICT         MISCELLANEOUS              PARK
#> 36: METRO WATER RECLAMATION D...         MISCELLANEOUS             WATER
#> 37: NORTH SHORE MOSQUITO ABAT...         MISCELLANEOUS          MOSQUITO
#>     agency_tax_rate final_tax
#>               <num>     <num>
#>  1:      0.00531000    518.22
#>  2:      0.00063000     61.48
#>  3:      0.01151000   1123.30
#> ---                          
#> 35:      0.00434711    502.53
#> 36:      0.00340445    393.55
#> 37:      0.00007613      8.80

These basic arguments can even be used to calculate line-item bills for all PINs and years, assuming you have enough system memory:

# Query the PTAXSIM database directly to get all unique PINs
pins <- DBI::dbGetQuery(ptaxsim_db_conn, "SELECT DISTINCT pin FROM pin")

# Calculate all bills for all years (~350M rows, takes about 10 minutes)
bills <- tax_bill(2006:2024, pins$pin)
print(bills, topn = 3)
#> NOT RUN, takes too long on GitHub CI and requires ~90 GB of RAM

Optional arguments to `tax_bill()`

In addition to the two required arguments, tax_bill() also has a number of optional arguments that can be used for counterfactual or predictive analysis.

By default, these arguments are filled with historic data from the PTAXSIM database. Each argument has a corresponding function, prefixed with lookup_, that retrieves data from the database and puts it in the format expected by tax_bill(). These arguments and their corresponding lookup_ functions are:

`tax_code_vec`

Expects a character vector of Cook County tax codes. Must be the same length as the combination of year_vec and pin_vec, using the same recycling rules as tax_bill()
A tax code is a 5-digit number that identifies the unique overlap of taxing districts for a given PIN and year
Tax codes can vary from year to year for the same PIN as taxing districts are created or destroyed
Changing tax_code_vec “relocates” a PIN by changing the things that are taxing it. This can be useful for counterfactual analysis. For example, if you own property within a school district and want to know what your tax bill would be just outside the district, but otherwise within the same municipality, then you can find the tax code that represents that situation and plug it into tax_bill()

Filled by lookup_tax_code():

Takes PINs and years as inputs and outputs a character vector of tax codes. Here is an example:

tax_code <- lookup_tax_code(2018:2024, "13264290020000")
print(tax_code)
#> [1] "71001" "71163" "71163" "71163" "71163" "71163" "71163"

Follows the same recycling rules as tax_bill(): input vectors of the same length return an element-wise output, input vectors of different lengths return the Cartesian product.

tax_code <- lookup_tax_code(
  year = 2018:2024,
  pin = c("13264290020000", "07101010391078", "10153080520000")
)
print(tax_code)
#>  [1] "71001" "35011" "24023" "71163" "35011" "24023" "71163" "35011" "24023"
#> [10] "71163" "35011" "24023" "71163" "35011" "24023" "71163" "35011" "24023"
#> [19] "71163" "35011" "24023"

tax_code <- lookup_tax_code(
  year = 2006:2024,
  pin = c("13264290020000", "07101010391078", "10153080520000")
)
print(tax_code)
#>  [1] "71001" NA      "24023" "71001" NA      "24023" "71001" "35011" "24023"
#> [10] "71001" "35011" "24023" "71001" "35011" "24023" "71001" "35011" "24023"
#> [19] "71001" "35011" "24023" "71001" "35011" "24023" "71001" "35011" "24023"
#> [28] "71001" "35011" "24023" "71001" "35011" "24023" "71001" "35011" "24023"
#> [37] "71001" "35011" "24023" "71163" "35011" "24023" "71163" "35011" "24023"
#> [46] "71163" "35011" "24023" "71163" "35011" "24023" "71163" "35011" "24023"
#> [55] "71163" "35011" "24023"

`agency_dt`

Column Name	Type	Key	Note
`year`	int	1	See tax_bill() outputs
`tax_code`	varchar(5)	2	See tax_bill() outputs
`agency_num`	varchar(9)	3	See tax_bill() outputs
`agency_name`	varchar		See tax_bill() outputs
`agency_major_type`	varchar		See tax_bill() outputs
`agency_minor_type`	varchar		See tax_bill() outputs
`agency_total_eav`	bigint		The total amount of EAV within the taxing district, otherwise known as the “base”. This is the denominator when calculating tax rates
`agency_total_ext`	double		The total extension requested by the taxing district, otherwise known as the “levy”. This is the amount the district needs in tax revenue and is the numerator when calculating tax rates

Expects a data.table with the columns above:
Each row represents a taxing district (agency) that imposes a tax on the specified tax_code. Different tax_codes can have different numbers of districts taxing them
The input data.table must be keyed on the year, tax_code, and agency_num columns and must be distinct, e.i. it must not have repeat rows
When joined to the PIN level (via tax_code) the agency_dt table is used to determine which districts a PIN pays and how much. The basic calculation is: $pin\_eav * (agency\_levy / agency\_base)$

Filled by lookup_agency():

Takes years and tax codes as inputs and outputs a keyed data.table of taxing districts, including their identifying information, extension, and base. Here is an example:

tax_code <- lookup_tax_code(2024, "13264290020000")
agency <- lookup_agency(2024, tax_code)
print(agency, topn = 3)
#> Key: <year, tax_code, agency_num>
#>      year tax_code agency_num                  agency_name
#>     <int>   <char>     <char>                       <char>
#>  1:  2024    71163  010010000               COUNTY OF COOK
#>  2:  2024    71163  010020000 FOREST PRESERVE DISTRICT ...
#>  3:  2024    71163  030210000              CITY OF CHICAGO
#> ---                                                       
#>  8:  2024    71163  050200001 CHICAGO PARK DISTRICT AQU...
#>  9:  2024    71163  080180000 METRO WATER RECLAMATION D...
#> 10:  2024    71163  140030000 LOGAN AVONDALE HERMOSA EX...
#>         agency_major_type agency_minor_type agency_total_eav agency_total_ext
#>                    <char>            <char>            <i64>            <num>
#>  1:           COOK COUNTY              COOK     208865973594        815556878
#>  2:           COOK COUNTY              COOK     208865973594        143256994
#>  3: MUNICIPALITY/TOWNSHIP              MUNI     109814786307       1773379217
#> ---                                                                          
#>  8:         MISCELLANEOUS              BOND     109814786307                0
#>  9:         MISCELLANEOUS             WATER     205447558453        699435940
#> 10:         MISCELLANEOUS            HEALTH       5152095778          1148557

Note that it returns a distinct set of districts, even if year and tax code are repeated:

tax_codes <- lookup_tax_code(
  year = 2024,
  pin = c("13264290020000", "13264290020000", "13264290020000")
)
agency <- lookup_agency(c(2024, 2024, 2024), tax_codes)
print(agency, topn = 3)
#> Key: <year, tax_code, agency_num>
#>      year tax_code agency_num                  agency_name
#>     <int>   <char>     <char>                       <char>
#>  1:  2024    71163  010010000               COUNTY OF COOK
#>  2:  2024    71163  010020000 FOREST PRESERVE DISTRICT ...
#>  3:  2024    71163  030210000              CITY OF CHICAGO
#> ---                                                       
#>  8:  2024    71163  050200001 CHICAGO PARK DISTRICT AQU...
#>  9:  2024    71163  080180000 METRO WATER RECLAMATION D...
#> 10:  2024    71163  140030000 LOGAN AVONDALE HERMOSA EX...
#>         agency_major_type agency_minor_type agency_total_eav agency_total_ext
#>                    <char>            <char>            <i64>            <num>
#>  1:           COOK COUNTY              COOK     208865973594        815556878
#>  2:           COOK COUNTY              COOK     208865973594        143256994
#>  3: MUNICIPALITY/TOWNSHIP              MUNI     109814786307       1773379217
#> ---                                                                          
#>  8:         MISCELLANEOUS              BOND     109814786307                0
#>  9:         MISCELLANEOUS             WATER     205447558453        699435940
#> 10:         MISCELLANEOUS            HEALTH       5152095778          1148557

`pin_dt`

Column Name	Type	Key	Note
`year`	int	1	See tax_bill() outputs
`pin`	varchar(14)	2	See tax_bill() outputs
`class`	varchar(3)		See tax_bill() outputs
`av`	int		See tax_bill() outputs
`eav`	int		See tax_bill() outputs
`exe_homeowner`	int		Homeowner Exemption. All exemption amounts are in EAV, e.i. the Homeowner Exemption is typically an integer equal to 10000
`exe_senior`	int		Senior Exemption
`exe_freeze`	int		Senior Freeze Exemption. Subtract from `eav` to get the frozen amount
`exe_longtime_homeowner`	int		Longtime Homeowner Exemption
`exe_disabled`	int		Persons with Disabilities Exemption
`exe_vet_returning`	int		Returning Veterans Exemption
`exe_vet_dis_lt50`	int		Veterans with Disabilities Exemption. Level of disability < 50%
`exe_vet_dis_50_69`	int		Veterans with Disabilities Exemption. Level of disability >= 51% and <= 69%
`exe_vet_dis_ge70`	int		Veterans with Disabilities Exemption. Level of disability >= 70%
`exe_vet_dis_100`	int		Veterans with Disabilities Exemption. New data point available as of 2024. Value for this field will be zero for all years < 2024. Level of disability = 100%
`exe_wwii`	int		WWII Veterans Exemption. New data point available as of 2024. Value for this field will be zero for all years < 2024.
`exe_abate`	int		Other tax abatements, exemptions, etc.

Expects a data.table with the columns above:
Each row represents the assessed value and exemptions for a specific PIN and year
All exemptions are recorded in EAV. For example, per Illinois statute, the Homeowner Exemption is currently equal to $10,000 in EAV. This amount is multiplied by the local tax rate for the PIN to determine the final exemption amount. The higher the tax rate, the larger the exemption
The input data.table must be keyed on the year and pin columns and must be distinct, e.i. it must not have repeat rows

Filled by lookup_pin():

Takes years and PINs as inputs and outputs a keyed data.table of PINs, including their class, assessed value, and individual exemptions. Here is an example:

pin <- lookup_pin(2024, "13264290020000")
print(pin)
#> Key: <year, pin>
#>     year            pin  class    av    eav exe_homeowner exe_senior exe_freeze
#>    <num>         <char> <char> <int>  <int>         <int>      <int>      <int>
#> 1:  2024 13264290020000    211 63875 193893         10000          0          0
#>    exe_longtime_homeowner exe_disabled exe_vet_returning exe_vet_dis_lt50
#>                     <int>        <int>             <int>            <int>
#> 1:                      0            0                 0                0
#>    exe_vet_dis_50_69 exe_vet_dis_ge70 exe_vet_dis_100 exe_wwii exe_abate
#>                <int>            <int>           <int>    <int>     <int>
#> 1:                 0                0               0        0         0

Repeat inputs will yield distinct outputs:

pin <- lookup_pin(2024, c("13264290020000", "13264290020000"))
print(pin)
#> Key: <year, pin>
#>     year            pin  class    av    eav exe_homeowner exe_senior exe_freeze
#>    <num>         <char> <char> <int>  <int>         <int>      <int>      <int>
#> 1:  2024 13264290020000    211 63875 193893         10000          0          0
#>    exe_longtime_homeowner exe_disabled exe_vet_returning exe_vet_dis_lt50
#>                     <int>        <int>             <int>            <int>
#> 1:                      0            0                 0                0
#>    exe_vet_dis_50_69 exe_vet_dis_ge70 exe_vet_dis_100 exe_wwii exe_abate
#>                <int>            <int>           <int>    <int>     <int>
#> 1:                 0                0               0        0         0

By default, will return the final assessed value for the year after all appeals are processed. This can be changed using an additional argument: stage. The options for stage include:

"mailed" - Initial assessed values mailed to taxpayers, no appeals
"certified" - Assessed values after Assessor appeals are complete
"board" - Assessed values after Board of Review appeals are complete
"clerk" - Assessed values used by the Clerk to calculate the base and by the Treasurer to calculate bills. Identical to "board" in the huge majority of cases

pin_mailed <- lookup_pin(2024, "13264290020000", stage = "mailed")
pin_board <- lookup_pin(2024, "13264290020000", stage = "board")
print(rbind(pin_mailed, pin_board))
#>     year            pin  class    av    eav exe_homeowner exe_senior exe_freeze
#>    <num>         <char> <char> <int>  <int>         <int>      <int>      <int>
#> 1:  2024 13264290020000    211 77000 233734         10000          0          0
#> 2:  2024 13264290020000    211 63875 193893         10000          0          0
#>    exe_longtime_homeowner exe_disabled exe_vet_returning exe_vet_dis_lt50
#>                     <int>        <int>             <int>            <int>
#> 1:                      0            0                 0                0
#> 2:                      0            0                 0                0
#>    exe_vet_dis_50_69 exe_vet_dis_ge70 exe_vet_dis_100 exe_wwii exe_abate
#>                <int>            <int>           <int>    <int>     <int>
#> 1:                 0                0               0        0         0
#> 2:                 0                0               0        0         0

`tif_dt`

Column Name	Type	Key	Note
`year`	int	1	See tax_bill() outputs
`tax_code`	varchar(5)	2	See tax_bill() outputs. Unique identifier for the tax situation created by the TIF
`agency_num`	varchar(9)	3	See tax_bill() outputs. Unique identifier for the TIF
`agency_name`	varchar		See tax_bill() outputs
`agency_major_type`	varchar		See tax_bill() outputs
`agency_minor_type`	varchar		See tax_bill() outputs
`tif_share`	double		The percentage of this tax code/PIN’s revenue dedicated to the TIF. Increases as the EAV above the frozen amount increases

Expects a data.table with the columns above:
Each row represents a TIF that covers the specified tax_code. Each tax_code can only have one TIF, likewise for PINs
Returns TIF information by tax_code for all years from 2006-2023. For 2024 and after, the tax_bill() function relies on pin_tif_dt (see below)
The input data.table must be keyed on the year, tax_code, and agency_num columns and must be distinct, e.i. it must not have repeat rows

Filled by lookup_tif():

Takes years and tax codes as inputs and outputs a keyed data.table of TIF districts, including their identifying information and distribution percentage/TIF share. Here is an example:

tax_code <- lookup_tax_code(2023, "14172270080000")
tif <- lookup_tif(2023, tax_code)
print(tif, topn = 3)
#> Key: <year, tax_code, agency_num>
#>     year tax_code agency_num                  agency_name     agency_major_type
#>    <int>   <char>     <char>                       <char>                <char>
#> 1:  2023    73032  030210610 TIF - CHICAGO - WILSON YA... MUNICIPALITY/TOWNSHIP
#>    agency_minor_type tif_share
#>               <char>     <num>
#> 1:               TIF 0.7425499

Returns a data.table with zero rows if the specified tax_code is not within a TIF district:

tax_code <- lookup_tax_code(2023, "13264290020000")
tif <- lookup_tif(2023, tax_code)
print(tif, topn = 3)
#> Key: <year, tax_code, agency_num>
#> Empty data.table (0 rows and 7 cols): year,tax_code,agency_num,agency_name,agency_major_type,agency_minor_type...

`pin_tif_dt`

Column Name	Type	Key	Note
`year`	int	1	See tax_bill() outputs
`pin`	varchar(14)	2	See tax_bill() outputs.
`tax_code`	varchar(5)		See tax_bill() outputs. Unique identifier for the tax situation created by the TIF
`agency_num`	varchar(9)	3	See tax_bill() outputs. Unique identifier for the TIF
`agency_name`	varchar		See tax_bill() outputs
`agency_major_type`	varchar		See tax_bill() outputs
`agency_minor_type`	varchar		See tax_bill() outputs
`tif_share`	double		The percentage of this tax PIN’s revenue dedicated to the TIF. Increases as the PIN’s EAV above its frozen EAV increases

Expects a data.table with the columns above:
Each row represents a pin and its TIF. Each pin can only have one TIF.
Returns TIF information by pin for years 2024 and after. This accounts for the TIF share calculation methodology change that began in 2024 (more information about this change is described in the PTAXSIM changelog and the TIF vignette)
The input data.table must be keyed on the year, pin, and agency_num columns and must be distinct, i.e. it must not have repeat rows

Filled by lookup_pin_tif():

Takes years and PINs as inputs and outputs a keyed data.table of the PINs and their TIF districts, including their identifying information and distribution percentage/TIF share. Here is an example:

tif <- lookup_pin_tif(2024, "14172270080000")
print(tif, topn = 3)
#> Key: <year, pin, agency_num>
#>     year            pin tax_code agency_num                  agency_name
#>    <int>         <char>   <char>     <char>                       <char>
#> 1:  2024 14172270080000    73032  030210610 TIF - CHICAGO - WILSON YA...
#>        agency_major_type agency_minor_type tif_share
#>                   <char>            <char>     <num>
#> 1: MUNICIPALITY/TOWNSHIP               TIF    0.6651

Returns a data.table with zero rows if the input year is before 2024:

tif <- lookup_pin_tif(2023, "14172270080000")
print(tif, topn = 3)
#> Key: <year, pin, agency_num>
#> Empty data.table (0 rows and 8 cols): year,pin,tax_code,agency_num,agency_name,agency_major_type...

Looking up TIF information before and after the 2024 TIF methodology update requires using both lookup_tif() and lookup_pin_tif(); however, for tax bill calculations we have built in functionality in tax_bill() to enable correct bill calculation for TIF’d PINs across all years:

pin_tax_to_tif <- tax_bill(2023:2024, "14172270080000") %>%
  filter(agency_num == "030210610") %>%
  group_by(year, agency_name) %>%
  summarise(total_tax_to_tif = sum(final_tax))
print(pin_tax_to_tif, topn = 3)
#> # A tibble: 2 × 3
#> # Groups:   year [2]
#>    year agency_name                 total_tax_to_tif
#>   <int> <chr>                                  <dbl>
#> 1  2023 TIF - CHICAGO - WILSON YARD           20496.
#> 2  2024 TIF - CHICAGO - WILSON YARD           24186.

2. Common scenarios

In this section, we’ll look at how to use the tax_bill() function to model common property tax scenarios. These scenarios are simple enough that they do not require complicated secondary tax_bill() inputs (such as recalculating the tax base). Let’s see some examples.

Change in assessed value

To recalculate a tax bill with a counterfactual assessed value, simply change the pin_dt column eav. For example, PIN 13-26-429-002-0000 had a 2024 estimated market value of $638,750. What if it had a market value of $1,000,000?

To find out, we’ll first convert the new market value into the equalized assessed value (EAV).

mkt_value <- 1000000

# For residential properties, AV is 10% of market value
assmt_value <- mkt_value * 0.1

# Get the final equalization factor for 2024 to get the equalized assessed value
eq_factor <- DBI::dbGetQuery(
  ptaxsim_db_conn,
  "SELECT * FROM eq_factor WHERE year = 2024"
) %>%
  pull(eq_factor_final)

eq_value <- assmt_value * eq_factor

Then we’ll recalculate the bill using the new EAV and compare to the original. To do so, we’ll create a counterfactual pin_dt input by replacing the original 2024 eav column with our newly calculated EAV.

pin_dt_new <- lookup_pin(2024, "13264290020000") %>%
  mutate(av = assmt_value, eav = eq_value) %>%
  setDT(key = c("year", "pin")) # convert to data.table or R will complain

# Combine the original and updated bills into one output
rbind(
  tax_bill(2024, "13264290020000") %>% mutate(type = "original"),
  tax_bill(2024, "13264290020000", pin_dt = pin_dt_new) %>%
    mutate(type = "counterfactual")
) %>%
  group_by(year, pin, type, av) %>%
  summarize(bill_total = sum(final_tax)) %>%
  arrange(desc(type))
#> # A tibble: 2 × 5
#> # Groups:   year, pin, type [2]
#>    year pin            type               av bill_total
#>   <int> <chr>          <chr>           <dbl>      <dbl>
#> 1  2024 13264290020000 original        63875     12212.
#> 2  2024 13264290020000 counterfactual 100000     19494.

What if the market value went down to $400,000?

mkt_value <- 400000
assmt_value <- mkt_value * 0.1
eq_value <- assmt_value * eq_factor

pin_dt_new <- lookup_pin(2024, "13264290020000") %>%
  mutate(av = assmt_value, eav = eq_value) %>%
  setDT(key = c("year", "pin"))

rbind(
  tax_bill(2024, "13264290020000") %>% mutate(type = "original"),
  tax_bill(2024, "13264290020000", pin_dt = pin_dt_new) %>%
    mutate(type = "counterfactual")
) %>%
  group_by(year, pin, type, av) %>%
  summarize(bill_total = sum(final_tax)) %>%
  arrange(desc(type))
#> # A tibble: 2 × 5
#> # Groups:   year, pin, type [2]
#>    year pin            type              av bill_total
#>   <int> <chr>          <chr>          <dbl>      <dbl>
#> 1  2024 13264290020000 original       63875     12212.
#> 2  2024 13264290020000 counterfactual 40000      7399.

In this case, the percentage change in total tax roughly mirrors the percentage change in assessed value. However, this isn’t always true. During a reassessment, the base can increase significantly, resulting in bills going down even for properties with increased assessments.

Change in levy

Changes in levies are another common cause of increased tax bills. Calculating a levy change is not as straightforward as a change in AV, but a good rule-of-thumb is that levies typically can only increase by 5% or the rate of inflation, whichever is less.

NOTE: Correctly calculating a levy increase can be extremely complicated. Most levies are subject to numerous limiting laws (PTELL, rate limits, tax caps) that can vary by municipality/district

To change a levy, we need to alter the agency_total_ext column of the agency_dt input. Let’s see what happens if we increase Chicago’s levy by 5%.

tax_code <- lookup_tax_code(2024, "13264290020000")

# Add 5% to only Chicago's levy for this PIN
agency_dt_new <- lookup_agency(2024, tax_code) %>%
  mutate(agency_total_ext = ifelse(
    agency_num == "030210000",
    agency_total_ext + (agency_total_ext * 0.05),
    agency_total_ext
  )) %>%
  setDT(key = c("year", "tax_code", "agency_num"))

rbind(
  tax_bill(2024, "13264290020000") %>% mutate(type = "original"),
  tax_bill(2024, "13264290020000", agency_dt = agency_dt_new) %>%
    mutate(type = "counterfactual")
) %>%
  group_by(year, pin, type, av) %>%
  summarize(bill_total = sum(final_tax)) %>%
  arrange(desc(type))
#> # A tibble: 2 × 5
#> # Groups:   year, pin, type [2]
#>    year pin            type              av bill_total
#>   <int> <chr>          <chr>          <int>      <dbl>
#> 1  2024 13264290020000 original       63875     12212.
#> 2  2024 13264290020000 counterfactual 63875     12361.

A 5% increase in the Chicago levy leads to a roughly $150 increase in taxes for this PIN, holding all else constant.

Tax bills over time

We can also use PTAXSIM to look at how tax bills have changed over time. To do so, simply use tax_bill() to get multiple years’ worth of bills. The PTAXSIM database starts in 2006, so we can use that as our earliest year.

bills <- tax_bill(2006:2024, "13264290020000")

bills_summ <- bills %>%
  group_by(pin, year) %>%
  summarize(bill_total = sum(final_tax))

We can plot the total bill over time:

Click here to show plot code

And even plot the change of the proportion of tax dollars going to each district:

Click here to show plot code

This PIN’s bill has increased over 3x since 2006. A significant portion of that increase is due to increases in the Chicago Public Schools levy.

Advanced scenarios

PTAXSIM can also be used to perform much more advanced analysis, such as calculating the impact of appeals or changes to exemptions. These analyses involve creating multiple counterfacual inputs for the tax_bill() function and typically require recalculating the tax base.

If you’re comfortable with data.table or dplyr and want to dive further into PTAXSIM, check out the articles section for a list of advanced vignettes.

1. Basics

What is PTAXSIM?

The main function - tax_bill()