Google Sheets IRPF & Seguridad Social Spain Formula

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;

}