Not really helpful I guess and has some terrible Spaghetti code too, but this is the function I ended up with for my convoluted “Jupyter notebook of life, the universe and everything”. Speaking of rabbit holes… But maybe somebody gets a kick out of it.
Note that the DA-1 code is not validated, while it gets close to the actual figure it is still somehow a bit off in a way I cannot explain. The rest gets quite close to the actual tax bill figures.
# calculate tax, based a) on previous year's income & assets (provisional) or b) on final income & assets.
def Tax(Year, TaxType='provisional'):
import pandas as pd
import datetime
if TaxType == 'provisional':
Offset = 1
elif TaxType == 'final':
Offset = 0
ChildAge = (datetime.date(Year, 1, 1) - DoBChild).days / 365.2425
# import relevant variables from DataFrame
TotalTaxableIncomeSpouse1 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Type'] == 'Income Taxed') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum() + df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Type'] == 'Income Deductions') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum() + df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == 'Child Benefit'),
'Value'].sum()
NetDividendIncomeSpouse1 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == 'Dividends') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum()
PensionIncomeSpouse1 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '1st Pillar Pension') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum()
VoluntaryDeductionsSpouse1 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '2nd Pillar Voluntary Deduction') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum() + df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '3rd Pillar Deduction') &
(df_portfolio['Person'] == 'Spouse1'),
'Value'].sum()
TotalTaxableIncomeSpouse2 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Type'] == 'Income Taxed') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum() + df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Type'] == 'Income Deductions') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum()
NetDividendIncomeSpouse2 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == 'Dividends') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum()
PensionIncomeSpouse2 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '1st Pillar Pension') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum()
VoluntaryDeductionsSpouse2 = df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '2nd Pillar Voluntary Deduction') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum() + df_portfolio.loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Name'] == '3rd Pillar Deduction') &
(df_portfolio['Person'] == 'Spouse2'),
'Value'].sum()
TotalTaxableAssets = df_portfolio['Value'].loc[(df_portfolio['Year'] == Year - Offset) &
(df_portfolio['Type'] == 'Capital Taxable Assets')].sum()
# remove dividends, voluntary 2nd & 3rd pillar deductions for tax assessment (item 100 / 101 in tax return)
NetEmploymentIncomeSpouse1 = TotalTaxableIncomeSpouse1 - (NetDividendIncomeSpouse1 +
VoluntaryDeductionsSpouse1 +
PensionIncomeSpouse1)
NetEmploymentIncomeSpouse2 = TotalTaxableIncomeSpouse2 - (NetDividendIncomeSpouse2 +
VoluntaryDeductionsSpouse2 +
PensionIncomeSpouse2)
# total dividends with US withholding tax removed for CH tax assessment
TotalDividendIncome = ((NetDividendIncomeSpouse1 + NetDividendIncomeSpouse2) / 0.85)
# calculate and remove flat rate portfolio administration allowance, resulting in item 150 in tax return
if TotalTaxableAssets < 3000000:
PortfolioAdminFlatRateDeduction = TotalTaxableAssets * 0.003
else:
PortfolioAdminFlatRateDeduction = (TotalTaxableAssets - 3000000) * 0.001 + 9000
TaxableIncomeFromPortfolio = TotalDividendIncome - PortfolioAdminFlatRateDeduction
TotalNetTaxableIncome = (NetEmploymentIncomeSpouse1 +
NetEmploymentIncomeSpouse2 +
PensionIncomeSpouse1 +
PensionIncomeSpouse2 +
TaxableIncomeFromPortfolio) # item 199 in tax return
# professional deductable expenditures (item 238 / 239 in tax return)
if NetEmploymentIncomeSpouse1 > 0:
Spouse1ProfessionalDeductions = 1490 + 3200 + min(max(2000, NetEmploymentIncomeSpouse1 * 0.03), 4000)
else:
Spouse1ProfessionalDeductions = 0
if NetEmploymentIncomeSpouse2 > 0:
Spouse2ProfessionalDeductions = 1490 + 3200 + min(max(2000, NetEmploymentIncomeSpouse2 * 0.03), 4000)
else:
Spouse2ProfessionalDeductions = 0
# dual income deductions (assumes that spouse1 is having a higher income than spouse2, needs additional logic if not always the case)
if NetEmploymentIncomeSpouse2 > 0:
CantonalDualIncomeDeduction = 4700
FederalDualIncomeDeduction = max(8100, min(13400, NetEmploymentIncomeSpouse2 * 0.5))
else:
CantonalDualIncomeDeduction = 0
FederalDualIncomeDeduction = 0
# intermediate sums of deductions to check against individual deduction cap
IncomeRelatedDeductionsSpouse1 = Spouse1ProfessionalDeductions
if (NetEmploymentIncomeSpouse1 + VoluntaryDeductionsSpouse1) < IncomeRelatedDeductionsSpouse1:
IncomeRelatedDeductionsSpouse1 = max(0, (NetEmploymentIncomeSpouse1 + VoluntaryDeductionsSpouse1))
CantonalIncomeRelatedDeductionsSpouse2 = Spouse2ProfessionalDeductions + CantonalDualIncomeDeduction
if (NetEmploymentIncomeSpouse2 + VoluntaryDeductionsSpouse2) < CantonalIncomeRelatedDeductionsSpouse2:
CantonalIncomeRelatedDeductionsSpouse2 = max(0, (NetEmploymentIncomeSpouse2 + VoluntaryDeductionsSpouse2))
FederalIncomeRelatedDeductionsSpouse2 = Spouse2ProfessionalDeductions + FederalDualIncomeDeduction
if (NetEmploymentIncomeSpouse2 + VoluntaryDeductionsSpouse2) < FederalIncomeRelatedDeductionsSpouse2:
FederalIncomeRelatedDeductionsSpouse2 = max(0, (NetEmploymentIncomeSpouse2 + VoluntaryDeductionsSpouse2))
# deduction for insurance premiums (item 270 in tax return)
CantonalInsurancePremiumDeduction = 4900
FederalInsurancePremiumDeduction = 3500
if ChildAge < 21:
CantonalInsurancePremiumDeduction = CantonalInsurancePremiumDeduction + 700
FederalInsurancePremiumDeduction = FederalInsurancePremiumDeduction + 700
# child deductions
if ChildAge < 14:
CantonalChildDeductions = 8200
FederalChildDeductions = 6500
elif ChildAge < 21:
CantonalChildDeductions = 7200
FederalChildDeductions = 6500
else:
CantonalChildDeductions = 0
FederalChildDeductions = 0
# federal only deduction for married people
FederalDeductionMarriedStatus = 2600
# cantonal taxable income calculation
CantonalNetIncome = (TotalNetTaxableIncome +
VoluntaryDeductionsSpouse1 +
VoluntaryDeductionsSpouse2) - (IncomeRelatedDeductionsSpouse1 +
CantonalIncomeRelatedDeductionsSpouse2 +
CantonalInsurancePremiumDeduction)
CantonalNetIncome = max(0, CantonalNetIncome)
CantonalTaxableIncome = CantonalNetIncome - CantonalChildDeductions
CantonalTaxableIncome = max(0, CantonalTaxableIncome)
# federal taxable income calculation
FederalNetIncome = (TotalNetTaxableIncome +
VoluntaryDeductionsSpouse1 +
VoluntaryDeductionsSpouse2) - (IncomeRelatedDeductionsSpouse1 +
FederalIncomeRelatedDeductionsSpouse2 +
FederalInsurancePremiumDeduction)
FederalNetIncome = max(0, FederalNetIncome)
FederalTaxableIncome = FederalNetIncome - (FederalChildDeductions + FederalDeductionMarriedStatus)
FederalTaxableIncome = max(0, FederalTaxableIncome)
# asset deductions
if ChildAge < 21:
AssetDeductions = 137000
else:
AssetDeductions = 125000
NetTaxableAssets = TotalTaxableAssets - AssetDeductions
# cantonal tax (married/with children, from https://swisstaxcalculator.estv.admin.ch/#/taxdata/tax-scales)
if CantonalTaxableIncome < 18800:
CantonalIncomeTax = 0
elif CantonalTaxableIncome < 22700:
CantonalIncomeTax = (CantonalTaxableIncome - 18800) * 0.005
elif CantonalTaxableIncome < 23700:
CantonalIncomeTax = (CantonalTaxableIncome - 22700) * 0.015 + 20
elif CantonalTaxableIncome < 24800:
CantonalIncomeTax = (CantonalTaxableIncome - 23700) * 0.025 + 35
elif CantonalTaxableIncome < 26800:
CantonalIncomeTax = (CantonalTaxableIncome - 24800) * 0.03 + 62
elif CantonalTaxableIncome < 30800:
CantonalIncomeTax = (CantonalTaxableIncome - 26800) * 0.035 + 122
elif CantonalTaxableIncome < 93800:
CantonalIncomeTax = (CantonalTaxableIncome - 30800) * 0.045 + 262
elif CantonalTaxableIncome < 130600:
CantonalIncomeTax = (CantonalTaxableIncome - 93800) * 0.05 + 3097
elif CantonalTaxableIncome < 150600:
CantonalIncomeTax = (CantonalTaxableIncome - 130600) * 0.055 + 4937
else:
CantonalIncomeTax = (CantonalTaxableIncome - 150600) * 0.058 + 6037
# cantonal wealth tax
CantonalWealthTax = NetTaxableAssets * 0.000875
CantonalWealthTax = max(0, CantonalWealthTax)
# apply combined cantonal + communal tax multipliers
CantonalIncomeTax = round(CantonalIncomeTax * 3.45)
CantonalWealthTax = round(CantonalWealthTax * 3.45)
# add fire service duty to cantonal tax
Spouse1Age = (datetime.date(Year, 1, 1) - DoBSpouse1).days / 365.2425
Spouse2Age = (datetime.date(Year, 1, 1) - DoBSpouse2).days / 365.2425
if (Spouse1Age < 51) and (Spouse2Age < 51):
FireServiceDuty = round(CantonalTaxableIncome * 0.0045)
FireServiceDuty = max(60, FireServiceDuty)
FireServiceDuty = min(800, FireServiceDuty)
elif (Spouse1Age < 51) or (Spouse2Age < 51):
FireServiceDuty = round(CantonalTaxableIncome * 0.0015)
FireServiceDuty = max(10, FireServiceDuty)
FireServiceDuty = min(133, FireServiceDuty)
else:
FireServiceDuty = 0
# 'personal' flat rate tax to cantonal tax
PersonalFlatRateTax = 50
# add up cantonal (& communal) level tax
CantonalTax = CantonalIncomeTax + CantonalWealthTax + FireServiceDuty + PersonalFlatRateTax
# federal tax (married/with children, from https://swisstaxcalculator.estv.admin.ch/#/taxdata/tax-scales)
if FederalTaxableIncome < 28300:
FederalTax = 0
elif FederalTaxableIncome < 50900:
FederalTax = (FederalTaxableIncome - 28300) * 0.01
elif FederalTaxableIncome < 58400:
FederalTax = (FederalTaxableIncome - 50900) * 0.02 + 226
elif FederalTaxableIncome < 75300:
FederalTax = (FederalTaxableIncome - 58400) * 0.03 + 376
elif FederalTaxableIncome < 90300:
FederalTax = (FederalTaxableIncome - 75300) * 0.04 + 883
elif FederalTaxableIncome < 103400:
FederalTax = (FederalTaxableIncome - 90300) * 0.05 + 1483
elif FederalTaxableIncome < 114700:
FederalTax = (FederalTaxableIncome - 103400) * 0.06 + 2138
elif FederalTaxableIncome < 124200:
FederalTax = (FederalTaxableIncome - 114700) * 0.07 + 2816
elif FederalTaxableIncome < 131700:
FederalTax = (FederalTaxableIncome - 124200) * 0.08 + 3481
elif FederalTaxableIncome < 137300:
FederalTax = (FederalTaxableIncome - 131700) * 0.09 + 4081
elif FederalTaxableIncome < 141200:
FederalTax = (FederalTaxableIncome - 137300) * 0.1 + 4585
elif FederalTaxableIncome < 143100:
FederalTax = (FederalTaxableIncome - 141200) * 0.11 + 4975
elif FederalTaxableIncome < 145000:
FederalTax = (FederalTaxableIncome - 143100) * 0.12 + 5184
else:
FederalTax = (FederalTaxableIncome - 145000) * 0.13 + 5412
# child deduction from federal tax, if applicable
if ChildAge < 21:
FederalChildDeduction = 251
else:
FederalChildDeduction = 0
FederalTax = FederalTax - FederalChildDeduction
FederalTax = max(0, FederalTax)
# DA-1 double taxation claim:
DA1Claim = TotalDividendIncome * 0.15
# Max. DA-1 claim, capped on maximum of CH tax on this income (max tax rate to be confirmed / calculated, TBD)
# Also assumes that all dividends result in a DA-1 claim, e.g. no CH stocks
if CantonalNetIncome > 0:
EffectiveCantonalTaxRate = CantonalIncomeTax / CantonalNetIncome
else:
EffectiveCantonalTaxRate = 0
if FederalNetIncome > 0:
EffectiveFederalTaxRate = FederalTax / FederalNetIncome
else:
EffectiveFederalTaxRate = 0
DA1Max = (TotalDividendIncome - PortfolioAdminFlatRateDeduction) * (EffectiveCantonalTaxRate + EffectiveFederalTaxRate)
DA1Credit = round(min(DA1Claim, DA1Max, (CantonalIncomeTax + FederalTax)))
# invert tax since it is an outflow
CantonalTax = round(CantonalTax * -1)
FederalTax = round(FederalTax * -1)
data = {
'Year': [Year, Year, Year],
'Type': ['Tax', 'Tax','Tax'],
'Name': ['Cantonal Income and Capital Tax', 'Federal Tax', 'DA-1 Tax Credit'],
'Person' : ['All', 'All', 'All'],
'Value': [CantonalTax, FederalTax, DA1Credit]
}
df_Tax = pd.DataFrame(data)
return df_Tax
… maybe I copy&pasted from PDF and massaged the table in Sheets or so.
