www.lohn-info.de  -  Informationen zur Lohn und Gehaltsabrechnung

Grundlagen |  Bruttorechnung |  Lohnsteuerabzugsverfahren |  Sozialversicherung |  Arbeitsverhältnisse |  Abrechnung |  Testaufgaben |  Gesamtübersicht (Direktaufruf aller Seiten)

Startseite > Lehrgang > Sozialversicherung > EXCEL-Aufgaben zur Gleitzone

Lehrgang der Lohnabrechnung - EXCEL-Aufgaben zur Sozialversicherung

Gleitzone im Niedriglohnbereich - Berechnung für 2014

Erstellen Sie folgende Tabelle. Die grauen Zellen enthalten Formeln. Die gelben Zellen sind Eingabefelder.

Berechnung der SV-Beiträge in der Gleitzone für 2014

In den Zellen D3, D6, D7 und D8 werden mit der Datenüberprüfung (Gültigkeitsprüfung) mögliche Zelleinträge festgelegt. In D3 sollen nur Dezimalzahlen zwischen 450,01 und 850 zulässig sein. Abbildung für Zelle D3:

Gültigkeitsprüfung D3

Die Besonderheiten bei schwankendem Verdienst werden hier nicht behandelt. Trotz Schwankungen (mehr oder weniger Stunden im Monat oder Zahlung von Urlaubs- bzw. Weihnachtsgeld) kann der Verdienst im gesamten Jahr innerhalb der Gleitzone liegen. Entscheidend ist der Durchschnitt. Für die Berechnung der SV-Beiträge sind dann drei Fälle möglich:

  1. Entgelt liegt innerhalb der Gleitzone
    Normale Gleitzonenregelung
  2. Entgelt liegt unterhalb der Gleitzone
    In solchen Fällen errechnet sich die beitragspflichtige Einnahme durch Multiplikation des Faktors F mit dem erzielten Arbeitsentgelt.
    Wird hier nicht behandelt.
  3. Entgelt liegt oberhalb der Gleitzone
    Die Beitragsberechnung erfolgt nach den allgemeinen Regelungen zur Beitragsberechnung in der Sozialversicherung.
    Wird hier nicht behandelt.

Diese Aufgabe behandelt nur den ersten Fall.

In D6, D7 und D8 sind nur ja oder nein zulässig. Wählen Sie bei Zulassen Liste und trennen Sie die möglichen Eingaben mit Semikolon.

In D5 ist mit einer Formel die fiktive beitragspflichtige Einnahme zu berechnen. Hier gab es durch die Minijob-Reform eine große Veränderung.

Berechnung von April 2003 bis Dezember 2012 Berechnung ab Januar 2013

F * 400 + (2 - F) * (Arbeitsentgelt - 400)

F * 450 + ([850/(850-450)] - [450/(850-450)] * F) * (Arbeitsentgelt - 450)

Unser Rechner soll die Berechnung für die Jahre ab 2013 ermöglichen. Dazu vereinfachen wir die Formel:

F * 450 + ([850/(850-450)] - [450/(850-450)] * F) * (Arbeitsentgelt - 450)
vereinfacht:
F * 450 + ([850/400] - [450/400] * F) * (Arbeitsentgelt - 450)
vereinfacht:
F * 450 + (2,125 - 1,125 * F) * (Arbeitsentgelt - 450)

Diese Formel verwenden wir für die Zelle D5. Nehmen Sie Bezug auf D3 und D4. Die Formel in D5 sieht damit so aus:
=RUNDEN(D4*450+(2,125-1,125*D4)*(D3-450);2)

Übernehmen Sie mit einem Zellbezug den Wert aus D5 in C13 und den Wert aus D3 in D13.

Die Formeln in C14, C15, C16 und C17 nehmen Bezug auf C13 und den entsprechenden Prozentwert aus Spalte B. C14 verwendet also den Beitragssatz aus B14. Denken Sie an die Berechnungsvorschrift.

Von der fiktiven Einnahme berechnet man die Gesamtbeiträge zur Kranken-, Pflege-, Renten- und Arbeitslosenversicherung unter Anwendung des jeweils halben Beitragssatzes, rundet und verdoppelt anschließend diesen Betrag. Die Rundung erfolgt auf 2 Nachkommastellen.

Übersicht der Sozialversicherungsbeiträge 2014

Sie brauchen also die Funktion RUNDEN. Im ersten Schritt vernachlässigen wir die Besonderheiten für Sachsen, den Beitragszuschlag für Kinderlose und die Nichtanwendung der Gleitzone in der Rentenversicherung. Stellen Sie die Auswahl in den Zellen D6, D7 und D8 wie im Bildschirmbild ein (D6 nein; D7 und D8 ja). Die kopierfähige Formel in C14 sieht so aus:
=RUNDEN(B14/2*C$13;2)*2

Für die Berechnung der Beiträge zur Krankenversicherung ist wegen der Besonderheiten der Beitragsverteilung eine modifizierte Form anzuwenden. Danach wird der für den Arbeitnehmer insgesamt zu zahlende Krankenversicherungsbeitrag durch Addition der getrennt berechneten gerundeten Anteile des Arbeitgebers und des Arbeitnehmers auf die beitragspflichtige Einnahme ermittelt.
Wir rechnen mit 7,3% und 8,2%.
Damit die Berechnung auch bei einer Beitragssatzänderung in Zelle B14 funktioniert, muss die Formel in C14 so aussehen:
=RUNDEN((B14-0,9%)/2*C$13;2)+RUNDEN(((B14-0,9%)/2+0,9%)*C$13;2)

Die Insolvenzgeldumlage in C18 wird durch Multiplikation von C13 mit B18 und anschließender Rundung errechnet. Die Anwendung des halben Beitragssatzes ist hier nicht notwendig, da die Insolvenzgeldumlage nur der Arbeitgeber trägt.

Die Arbeitgeberanteile in Spalte D werden als nächstes berechnet. In der Krankenversicherung ist der Sonderbeitrag von 0,9% zu beachten. Die Formel in D14 sieht so aus:
=RUNDEN((B14-0,9%)/2*D$13;2)
Vom Beitragssatz in B14 wird der Sonderbeitrag von 0,9% abgezogen und der Rest durch zwei geteilt (Klammersetzung beachten!).

In den Formeln der Zellen D15, D16 und D17 wird nur der Beitragssatz in Spalte B durch zwei geteilt und mit D13 multipliziert. Die Rundung erfolgt ebenfalls auf 2 Nachkommastellen. In D18 übernehmen Sie mit einem Zellbezug den Wert aus C18 (Insolvenzgeldumlage trägt nur der Arbeitgeber).

In Spalte E werden von den Gesamtbeiträgen die Arbeitgeberanteile abgezogen. In E14 steht also
=C14-D14

Bilden Sie in Zeile 19 die Summen der Zellen aus den Zeilen 14 bis 18. In der verbundenen Zelle D20 ist zur Kontrolle die Summe der Arbeitgeberanteile und Arbeitnehmeranteile zu bilden (D19 + E19).

Jetzt setzen wir die drei Spezialfälle um. Als erstes soll die Elterneigenschaft betrachtet werden. Wenn in D7 ein nein steht, muss folgendes passieren:

Gleitzone und keine Elterneigenschaft

Der Gesamtbeitrag zur Pflegeversicherung erhöht sich um 1,33 € (0,25% von 532,64 €) auf 12,25 €. Der Arbeitgeberanteil bleibt unverändert. Die Erhöhung des Gesamtbeitrags schlägt voll beim Arbeitnehmer durch. Sie müssen also mit einer WENN-Funktion in C15 die Zelle D7 (Elterneigenschaft) abfragen. Die Formel in C15 sieht so aus:
=WENN(D7="ja";RUNDEN(B15/2*C$13;2)*2;RUNDEN(B15/2*C$13;2)*2+RUNDEN(0,25%*C$13;2))

Als nächstes betrachten wir das Land Sachsen. Wenn in D6 ein ja steht, muss folgendes passieren:

Gleitzone in Sachsen und Elterneigenschaft vorhanden

Bei diesem Beispiel wurde die Elterneigenschaft in D7 wieder auf ja gestellt.

Der Arbeitgeberanteil ist in Sachsen niedriger. Wenn wir diesen verändern, wird der Arbeitnehmeranteil automatisch größer. Das erste Prozent zahlt in Sachsen der Arbeitnehmer allein (ausführliche Informationen zur Beitragsberechnung in Sachsen).
Hintergrund: Der Beitragssatz wurde am 01.01.1995 auf 1% der beitragspflichtigen Einnahmen festgesetzt. Zum Ausgleich der Arbeitgeberbeiträge sollten die Bundesländer einen gesetzlichen landesweiten Feiertag, der stets auf einen Werktag fällt, aufheben. In Bundesländern die den Feiertag gestrichen haben (alle außer Sachsen) wurde der Beitrag von AG und AN je zur Hälfte (je 0,5%) getragen. In Sachsen hatten die AN den Beitrag allein zu tragen (AN 1% und AG 0%). Die späteren Beitragserhöhungen wurden ohne weitere Bedingungen je zur Hälfte auf AG und AN aufgeteilt. Damit ist die Beitragsverteilung in Sachsen 2014 so:
AG 0,525% und AN 1,525%.

Wir müssen also mit einer WENN-Funktion in D15 die Zelle D6 (Sachsen) abfragen. Die Formel in D15 sieht so aus:
=WENN(D6="ja";RUNDEN((B15-1%)/2*D$13;2);RUNDEN(B15/2*D$13;2))
Wenn in D6 ein ja für Sachsen steht, wird vom Beitragssatz in B15 1,0% abgezogen und der Rest durch zwei geteilt (Klammersetzung beachten!). Dieser Betrag wird mit D13 multipliziert und das Ergebnis muss gerundet werden. Der SONST-Wert der WENN-Funktion ist die Berechnung die vorher schon in der Zelle stand.

Wenn wir jetzt noch die Elterneigenschaft auf nein setzen, muss folgendes passieren:

Gleitzone in Sachsen und keine Elterneigenschaft

Das letzte Problem ist die Möglichkeit des Arbeitnehmers, auf die Anwendung der Gleitzone in der Rentenversicherung zu verzichten. Der AN muss das schriftlich gegenüber dem Arbeitgeber erklären. Die Erklärung kann nur mit Wirkung für die Zukunft und bei mehreren Beschäftigungen nur einheitlich abgegeben werden. Sie ist für die Dauer der Beschäftigungen bindend.
Hintergrund: Verminderte Rentenversicherungsbeiträge führen auch zu einer verminderten Rente.

Wir müssen die Formeln in C16 und C18 anpassen. Wenn in D8 ein nein steht, muss folgendes passieren:

Verzicht auf die Anwendung der Gleitzone in der Rentenversicherung

Bei diesem Beispiel wurde die Elterneigenschaft in D7 wieder auf ja gestellt und Sachsen in D6 auf nein.

Wenn in D8 ein nein steht, wird der Gesamtbeitrag zur Rentenversicherung nicht von der fiktiven beitragspflichtigen Einnahme in C13 sondern vom tatsächlichen Arbeitsentgelt in D13 berechnet. Die Formel in C16 sieht so aus:
=RUNDEN(WENN(D8="ja";B16/2*C$13;B16/2*D$13);2)*2
Wenn in D8 ein ja steht, erfolgt die normale Berechnung (halber Beitragssatz mal fiktive beitragspflichtige Einnahme) sonst wird der halbe Beitragssatz mit dem tatsächlichen Arbeitsentgelt multipliziert. Die WENN-Funktion ist in der Funktion RUNDEN enthalten. Das Ergebnis der Funktion RUNDEN wird mit zwei multipliziert.

Grundlage für die Ermittlung der Insolvenzgeldumlage ist das rentenversicherungspflichtige Entgelt bis zur Beitragsbemessungsgrenze in der Rentenversicherung. Die Formel in C18 muss also ebenfalls die Zelle D8 abfragen. Wenn in D8 ein ja steht, erfolgt die normale Berechnung (Beitragssatz in B18 mal fiktive beitragspflichtige Einnahme in C13) sonst wird der Beitragssatz in B18 mit dem tatsächlichen Arbeitsentgelt in D13 multipliziert.

Die Zellen C21 bis C26 wurden zu einer Zelle verbunden. Hier soll eine Meldung (siehe Abbildung) erscheinen, wenn in D8 ein nein steht. Sie brauchen eine einfache WENN-Funktion (der Meldungstext muss in Anführungszeichen stehen).

Bei Problemen: E-Mail an lohn@lohn-info.de
Es folgen weitere Hinweise.

nach oben


Bücher bei Amazon zum Thema Lohnabrechnung suchen.

© 2007-2014 A.Liebig - Impressum - Kontakt - Datenschutz - Inhaltsverzeichnis (Sitemap) - Lohnlexikon