Someone once posted on here a website where all the tax rates had been collected and IIRC, there were expressed formulas which showed how they were calculated rather than long tables.
I can’t for the life of me find this, so wondered if anyone had the link?
This one from ESTV under basic data?
I think we had a chat on the use of ln formular for a single place?
Most places do have long tables there, and I’m not sure whether these actually do follow or could be expressed as formula. At least I haven’t found any when I looked for it, and came to terms with the table for my calculations.
It wasn’t that one. It was a site where you could choose which canton and it gave you the formula for the tax calculation.
Like this?
But that’s only for BL. Others I’ve checked had tables or missing data.
Please share if you find something else for others.
Ah, yes. That’s the one. I see it was in the basic data of the link you posted! Thanks!
Argh. I just got bitten by the ln/log issue again and only now remember the conversation! Fool me twice, shame on me!
Also, the vollsplitting is calculated by calculating tax at half the income, then using the tax rate from this applied to the full income.
I found my original thread: Do you know your marginal tax rate?
It was way too tedious to type out all the tax calculations, so I used AI to help me create the formulas (note taxable income in negative thousands in cell AP3):
Federal tax 2023
=IF( - AP3 > 912.6, ( - AP3 * 1000 - 912600) * 0.115 + 104949,
IF( - AP3 > 147.7, ( - AP3 * 1000 - 147700) * 0.13 + 5512,
IF( - AP3 > 145.8, ( - AP3 * 1000 - 145800) * 0.12 + 5284,
IF( - AP3 > 143.8, ( - AP3 * 1000 - 143800) * 0.11 + 5064,
IF( - AP3 > 139.9, ( - AP3 * 1000 - 139900) * 0.10 + 4674,
IF( - AP3 > 134.2, ( - AP3 * 1000 - 134200) * 0.09 + 4161,
IF( - AP3 > 126.5, ( - AP3 * 1000 - 126500) * 0.08 + 3545,
IF( - AP3 > 116.9, ( - AP3 * 1000 - 116900) * 0.07 + 2873,
IF( - AP3 > 105.4, ( - AP3 * 1000 - 105400) * 0.06 + 2183,
IF( - AP3 > 92.0, ( - AP3 * 1000 - 92000) * 0.05 + 1513,
IF( - AP3 > 76.7, ( - AP3 * 1000 - 76700) * 0.04 + 901,
IF( - AP3 > 59.4, ( - AP3 * 1000 - 59400) * 0.03 + 382,
IF( - AP3 > 51.8, ( - AP3 * 1000 - 51800) * 0.02 + 230,
IF( - AP3 > 28.8, ( - AP3 * 1000 - 28800) * 0.01, 0)))))))))))
Federal tax 2024
=IF( - AP3 > 928.7, ( - AP3 * 1000 - 928700) * 0.115 + 106801,
IF( - AP3 > 928.6, ( - AP3 * 1000 - 928600) * 0.13 + 106788,
IF( - AP3 > 150.3, ( - AP3 * 1000 - 150300) * 0.13 + 5609,
IF( - AP3 > 148.3, ( - AP3 * 1000 - 148300) * 0.12 + 5369,
IF( - AP3 > 146.3, ( - AP3 * 1000 - 146300) * 0.11 + 5149,
IF( - AP3 > 142.3, ( - AP3 * 1000 - 142300) * 0.10 + 4749,
IF( - AP3 > 136.6, ( - AP3 * 1000 - 136600) * 0.09 + 4236,
IF( - AP3 > 128.8, ( - AP3 * 1000 - 128800) * 0.08 + 3612,
IF( - AP3 > 119.0, ( - AP3 * 1000 - 119000) * 0.07 + 2926,
IF( - AP3 > 107.2, ( - AP3 * 1000 - 107200) * 0.06 + 2218,
IF( - AP3 > 93.6, ( - AP3 * 1000 - 93600) * 0.05 + 1538,
IF( - AP3 > 78.1, ( - AP3 * 1000 - 78100) * 0.04 + 918,
IF( - AP3 > 60.5, ( - AP3 * 1000 - 60500) * 0.03 + 390,
IF( - AP3 > 52.7, ( - AP3 * 1100 - 52700) * 0.02 + 234,
IF( - AP3 > 29.3, ( - AP3 * 1000 - 29300) * 0.01, 0)))))))))))
Basel-land 2024
=IF(BP4 > 1265000, 232436.77 + 0.1862 * (BP4 - 1265000),
IF(BP4 > 110000, 0.051298 * BP4 + 0.010441 * BP4 * (LN(BP4) - 1) + (-4825.535525),
IF(BP4 > 44000, -0.327919 * BP4 + 0.043109 * BP4 * (LN(BP4) - 1) + (-1232.14815),
IF(BP4 > 16500, -0.826281 * BP4 + 0.08972 * BP4 * (LN(BP4) - 1) + 818.725099,
IF(BP4 > 8250, 0.49 * BP4 / 100,
0)))))
All are for married w/kids rates.
Here is the formula for federal tax 2024 I use. For unmarried. It’s basically a summation as defined here. No need for nested if queries 
E42 references the taxable income
SUM(IF(E42>=$I29;$J28;0);
ROUNDDOWN((MAX(MIN(E42;$I30)-$I29;0))/100;0)*$J29;
ROUNDDOWN((MAX(MIN(E42;$I31)-$I30;0))/100;0)*$J30;
ROUNDDOWN((MAX(MIN(E42;$I32)-$I31;0))/100;0)*$J31;
ROUNDDOWN((MAX(MIN(E42;$I33)-$I32;0))/100;0)*$J32;
ROUNDDOWN((MAX(MIN(E42;$I34)-$I33;0))/100;0)*$J33;
ROUNDDOWN((MAX(MIN(E42;$I35)-$I34;0))/100;0)*$J34;
ROUNDDOWN((MAX(MIN(E42;$I36)-$I35;0))/100;0)*$J35;
ROUNDDOWN((MAX(MIN(E42;$I37)-$I36;0))/100;0)*$J36)
I reference the specific numbers as they change between years. Also has the advantage of keeping track of less numbers.
First column is “I”, second is “J”. Starting with row 28. Hope that helps.
| 25.41 | |
|---|---|
| 18’300 | 0.77 |
| 32’800 | 0.88 |
| 42’900 | 2.64 |
| 57’200 | 2.97 |
| 75’200 | 5.94 |
| 81’000 | 6.6 |
| 107’400 | 8.8 |
| 139’600 | 11 |
| 182’600 | 13.2 |
| 783’200 | 11.5 |
| 950’000 |
Thanks. I’m not sure if there’s an advantage to the SUM vs IF statements. But for updating, perhaps it is easier to copy the table than the complex PDF file?
It’s easier in the sense to maintain if the tax tarifs change, which they did from 2023 to 2024. No need to change the actual formulas in the cells. Just update the range values. With state and communal taxes the “Steuerfuss” can also change on a yearly basis.
I think there are 2 orthogonal decisions:
I put into formulas as it is more compact and all is in a single cell and doesn’t require referencing an external table.
This does require extra work, but with AI, there is no extra work: I past the new formula and ask AI to update the formula and it is done for me.