Google Sheets IRPF & Seguridad Social Spain Formula

Google Sheets formulas for calculating Spanish social security contributions for self-employed workers (autónomos) and income tax (IRPF) deductions for employees. The code includes specific contribution rates, minimum and maximum bases for 2022, and progressive tax brackets used to compute net income after social security and tax withholdings.

cotizar seguridad social autonomo.gs This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters // last changed 01.01.2022 // Source: http://www.seg-social.es/wps/portal/wss/internet/Trabajadores/CotizacionRecaudacionTrabajadores/10721/10724/1320/1322 // https://www.grupo2000.es/que-seguros-sociales-debo-aplicar-en-2020-bases-y-tipos-de-cotizacion/ var MINIMO = 960.6; var MAXIMO = 4139.4; var CONTINGENCIAS COMUNES = 28.3; var CONTINGENCIAS PROFESIONALES = 1.3; var CESE DE ACTIVIDAD = 0.9; var FORMACION = 0.1; function test { var a = cotizar autonomo mensual ; var b = cotizar autonomo mensual 11200 ; return a + ' ' + b; } function cotizar autonomo annual value annual { return cotizar autonomo mensual value annual/12 12; } function cotizar autonomo mensual value monthly { if value monthly == null || value monthly <= MINIMO { value monthly = MINIMO; } if value monthly = MAXIMO { value monthly = MAXIMO; } var cotizacion total = CONTINGENCIAS COMUNES + CONTINGENCIAS PROFESIONALES + CESE DE ACTIVIDAD + FORMACION; var result = value monthly cotizacion total / 100 ; return result; } es income tax.gs This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters // LAST CHANGED: 01.01.2022 var MAX SOCIAL SECURITY EMPLOYED MONTHLY = 4139.40; // engineer var MIN SOCIAL SECURITY MONTHLY INGENIERO = 1572.3; var COTIZACION SEGURIDAD SOCIAL CUOTA CONTINGENCIAS = 4.7; var COTIZACION SEGURIDAD SOCIAL CUOTA DESEMPLEO = 1.55; var COTIZACION SEGURIDAD SOCIAL CUOTA FORMACION PROFESIONAL = 0.1; var COTIZACION SEGURIDAD SOCIAL TOTAL = COTIZACION SEGURIDAD SOCIAL CUOTA CONTINGENCIAS + COTIZACION SEGURIDAD SOCIAL CUOTA DESEMPLEO + COTIZACION SEGURIDAD SOCIAL CUOTA FORMACION PROFESIONAL; var OTROS GASTOS CUANTÍA FIJA CON CARÁCTER GENERAL = 2000; var MÍNIMO PERSONAL Y FAMILIAR = 5550; // https://www2.agenciatributaria.gob.es/wlpl/PRET-R200/index.zul function es substract income tax income yearly, overwrite social security { var social security = MAX SOCIAL SECURITY EMPLOYED MONTHLY 12; if income yearly COTIZACION SEGURIDAD SOCIAL TOTAL <= MAX SOCIAL SECURITY EMPLOYED MONTHLY 12 { social security = income yearly COTIZACION SEGURIDAD SOCIAL TOTAL; } if overwrite social security = null { social security = overwrite social security; } var relevant income = income yearly - social security - OTROS GASTOS CUANTÍA FIJA CON CARÁCTER GENERAL; // - MÍNIMO PERSONAL Y FAMILIAR var tax of relevant income = es calculate income tax relevant income - es calculate income tax MÍNIMO PERSONAL Y FAMILIAR ; var result = Math.round income yearly - tax of relevant income - social security 100 /100; return result; } function es substract income tax monthly income monthly, overwrite social security { var yearly = es substract income tax income monthly 12, overwrite social security 12 ; var monthly = yearly / 12; return monthly; } function es calculate income tax value { var sum = 0; var tax1 = calculate tax by slice value, 0, 12450, 19 ; var tax2 = calculate tax by slice value, 12450, 20200, 24 ; var tax3 = calculate tax by slice value, 20200, 35200, 30 ; var tax4 = calculate tax by slice value, 35200, 60000, 37 ; var tax5 = calculate tax by slice value, 60000, 300000, 45 ; var tax6 = calculate tax by slice value, 300000, 100000000, 47 ; var all = tax1 + tax2 + tax3 + tax4 + tax5 + tax6; return all; } function calculate tax by slice total, from currency, to currency, percentage { if total < from currency { return 0; } if total to currency { var to subtract = Math.abs to currency - total ; total = total - to subtract; } var taxable amount = total - from currency; var percentage factor = percentage/100; var tax amount = taxable amount percentage factor; return tax amount; } function test irpf { var salario annual = 28000; var cotizar = cotizar autonomo mensual salario annual/12 ; var result = es substract income tax monthly salario annual/cotizar ; return result; }