Nebenkostenabrechnung Excel Vorlage: Formeln und Schlüssel

Eine Nebenkostenabrechnung Excel Vorlage ist im Kern eine Tabelle mit drei Teilen: die Wohnungen, die Kosten des Hauses und eine Formel, die beides für jeden Mieter zusammenbringt. Wer versteht, wie diese Formeln arbeiten, kann jede Vorlage prüfen, eine eigene Tabelle bauen oder Fehler finden, wenn am Ende ein Cent fehlt. Hier steht, wie so eine Datei aufgebaut ist, welche Formel zu welchem Umlageschlüssel gehört, wie Sie mehrere Mieter und einen Mieterwechsel abbilden und woran Sie sehen, dass alle Kosten verteilt sind.

Die Rechenwege gelten für Excel und für LibreOffice Calc gleichermaßen. Die Beispielformeln sind in der deutschen Schreibweise mit Semikolon geschrieben.

Welche Kosten überhaupt in die Tabelle gehören, steht in der kostenlosen Liste der umlagefähigen Nebenkosten (PDF).

Warum eine Tabelle und kein Formular

Ein Word- oder PDF-Formular sieht ordentlich aus, rechnet aber nicht. In einer Tabelle tragen Sie jeden Betrag einmal ein, und alle Anteile passen sich an, wenn sich eine Rechnung ändert, etwa nach einer Korrektur des Wasserversorgers. Schon bei drei Wohnungen und einem Dutzend Kostenarten kommen so mehrere Dutzend Einzelrechnungen zusammen, die Sie nicht mehr von Hand machen.

Ob eine fertige Datei, kostenlos oder gekauft, die richtigen Kostenarten und Schlüssel mitbringt, prüfen Sie mit der Checkliste in Nebenkostenabrechnung Vorlage kostenlos: worauf Sie achten sollten. In diesem Artikel geht es um das Innenleben der Tabelle.

Der Aufbau: drei Blätter und eine Kontrolle

Eine Excel-Tabelle für die Nebenkostenabrechnung braucht nicht mehr als diese Blätter:

Unter der Tabelle „Einheiten“ stehen die Summen, die jede Formel braucht: Gesamtfläche, Personen insgesamt, Gesamtverbrauch Heizung und Warmwasser. Diese Zellen sind die Umlagebasis. Fehlt dort eine Wohnung, stimmt jede einzelne Abrechnung nicht.

Beispiel für diesen Artikel (frei erfunden, dieselben Zahlen wie im ausgefüllten Muster einer Betriebskostenabrechnung): ein Haus mit drei Wohnungen.

Die umlagefähigen Kosten des Hauses betragen 7.015 € im Jahr.

Die Formeln je Umlageschlüssel

Jeder Anteil folgt demselben Muster: Gesamtkosten × Wert der Wohnung ÷ Summe aller Wohnungen. Nur der Wert ändert sich je nach Schlüssel.

Fläche

Das ist der gesetzliche Normalfall: Ist im Mietvertrag nichts anderes vereinbart, wird nach dem Anteil der Wohnfläche umgelegt (§ 556a Abs. 1 BGB).

=Kosten!B5*Einheiten!C5/Einheiten!$C$18

B5 ist der Betrag der Kostenart, C5 die Fläche der Wohnung, C18 die Gesamtfläche. Das Dollarzeichen hält den Bezug auf die Summe fest, wenn Sie die Formel nach unten oder zur Seite kopieren. Ohne $ rutscht der Bezug mit, und die Formel teilt plötzlich durch eine leere Zelle.

Im Beispiel liegen Grundsteuer, Gebäudereinigung, Gartenpflege, Allgemeinstrom, Schornsteinreinigung und Versicherung auf Fläche, zusammen 2.205 €. Anteil EG links: 2.205 € × 62 ÷ 201 = 680,15 €.

Personen

=Kosten!B6*Einheiten!D5/Einheiten!$D$18

Wasser, Abwasser sowie Straßenreinigung und Müll (zusammen 1.730 €) werden im Beispiel nach Personen verteilt, so wie es im Mietvertrag vereinbart ist. Anteil EG links: 1.730 € × 2 ÷ 6 = 576,67 €.

Wohnt eine Person nur einen Teil des Jahres in der Wohnung, rechnen Sie mit Personentagen statt mit Köpfen: Personen × Tage im Zeitraum ÷ Tage des Jahres. Dann bleibt die Summe richtig, auch wenn jemand im Sommer auszieht.

Einheiten

=Kosten!B18/ANZAHL(Einheiten!C5:C16)

Jede Wohnung trägt denselben Teil. Im Beispiel läuft der Betriebsstrom der Gemeinschaftsantenne (120 €) über Einheiten: 120 € ÷ 3 = 40,00 €. ANZAHL zählt nur Zellen mit Zahlen. Bei einem Mieterwechsel zählt sie die Wohnung aber doppelt, weil sie dann zwei Zeilen hat. Dafür braucht die Tabelle eine Hilfsspalte, die jede Wohnung nur einmal zählt.

Verbrauch: Heizung und Warmwasser

Bei einer zentralen Heizung müssen Sie in der Regel mindestens 50 % und höchstens 70 % der Kosten nach Verbrauch verteilen, den Rest nach Fläche (§ 7 Abs. 1 HeizkostenV, für Warmwasser § 8 Abs. 1 HeizkostenV). In der Tabelle sind das zwei Teile in einer Formel, mit dem Verbrauchsanteil in einer eigenen Zelle (hier F8 = 70 %):

=Kosten!B8*Kosten!F8*Einheiten!H5/Einheiten!$H$18 + Kosten!B8*(1-Kosten!F8)*Einheiten!C5/Einheiten!$C$18

Heizung im Beispiel: 2.350 €. Verbrauchsteil 2.350 € × 70 % × 5.800 ÷ 18.000 = 530,06 €, Flächenteil 2.350 € × 30 % × 62 ÷ 201 = 217,46 €. Anteil EG links: 747,52 €. Warmwasser (610 €) nach demselben Muster: 144,52 € + 56,45 € = 200,97 €.

Warum der Prozentsatz in eine eigene Zelle gehört: Wenn Sie ihn ändern, etwa von 70 auf 50 %, rechnen alle Wohnungen sofort neu, und Sie müssen keine Formel anfassen. Wann 70 % Pflicht sind und wann Sie wählen dürfen, steht in Heizkosten nach 70/30 abrechnen.

Zusammen

Den Schritt von der Summe zum Ergebnis erklärt Nebenkostenabrechnung berechnen noch einmal ohne Tabellenformeln.

Den Schlüssel per Auswahlliste wählen

Eine Formel muss wissen, welcher Schlüssel für eine Kostenart gilt. Am sichersten steht er als Text in einer Spalte neben dem Betrag, und eine WENN-Kette wählt danach die passende Rechnung:

=WENN(C5="Fläche";…;WENN(C5="Personen";…;WENN(C5="Einheiten";…;…)))

Das Risiko dabei: Tippt jemand „Flache“ oder „Personen “ mit Leerzeichen, greift keine Bedingung, und die Kostenart wird still mit 0 € verteilt. Darum gehört in die Schlüsselspalte eine Auswahlliste. In Excel heißt das Datenüberprüfung, in LibreOffice Calc Daten → Gültigkeit. Dann lassen sich nur die erlaubten Schlüssel eintragen.

Mehrere Mieter und Mieterwechsel

Für die Tabelle ist ein Mieterwechsel eine zusätzliche Zeile: dieselbe Wohnung mit derselben Fläche, einmal für den alten Mieter mit Auszugsdatum und einmal für den neuen mit Einzugsdatum. Eine Spalte „Zeitanteil“ rechnet die Tage im Abrechnungszeitraum durch die Tage des Jahres, und jede Formel nach Fläche oder Einheiten wird mit diesem Zeitanteil multipliziert.

Zwei Dinge sind dabei leicht zu übersehen:

• Die Gesamtfläche darf die Wohnung nur einmal zählen. Sonst teilen Sie durch 263 m² statt 201 m², und am Ende fehlt Geld.
• Tage ohne Mieter gehören dem Vermieter. Steht die Wohnung zwei Wochen leer, verteilt die Tabelle diesen Anteil nicht auf die anderen Mieter. Er erscheint als eigene Zeile „Anteil Vermieter“.

Verbrauchswerte lassen sich nicht nach Tagen teilen. Beim Wechsel brauchen Sie eine Zwischenablesung je Mieter. Ausführlich mit Rechenbeispiel steht das in Nebenkostenabrechnung bei Mieterwechsel.

Die Kontrollsumme: ist alles verteilt?

Die wichtigste Zelle der ganzen Datei steht in der Übersicht: Summe aller Anteile der Mieter plus Anteil Vermieter (Leerstand) minus umlagefähige Gesamtkosten. In der Übersicht der VermieterKlar-Vorlage stehen diese drei Werte in E18, E19 und E20:

=E18+E19-E20

Das Ergebnis muss 0,00 € sein. Steht dort etwas anderes, ist eine Wohnung nicht in der Umlagebasis, ein Schlüssel falsch geschrieben oder eine Kostenart außerhalb des Summenbereichs eingefügt worden. Weil jeder Anteil auf Cent gerundet wird, kann die Summe um ein oder zwei Cent abweichen. Das ist normal. Größere Abweichungen sind ein Fehler.

Häufige Fehler in selbst gebauten Tabellen

1. Zeile eingefügt, Summe nicht erweitert. Die neue Kostenart steht in der Tabelle, aber nicht in SUMME(B5:B21). Die Kontrollsumme zeigt das sofort.
2. Zahlen als Text. Ein Betrag, der aus einer PDF-Rechnung kopiert wurde, steht manchmal linksbündig als Text in der Zelle und zählt nicht mit.
3. Bezug ohne Dollarzeichen. Siehe oben: Die Gesamtfläche muss fest verankert sein.
4. Alte Kostenarten. Kabelgebühren gehören seit dem 01.07.2024 nicht mehr in die Abrechnung. Bei der Gemeinschaftsantenne sind nur noch Betriebsstrom und Prüfung umlagefähig (§ 2 Nr. 15 BetrKV).
5. Rechnen statt abrechnen. Die Tabelle liefert die Zahlen. Was auf dem Papier für den Mieter stehen muss (Gesamtkosten, Schlüssel, Abrechnungszeitraum), zeigt das ausgefüllte Muster mit allen Angaben.

Und unabhängig von der Datei: Die Abrechnung für 2025 muss spätestens zwölf Monate nach Ende des Abrechnungszeitraums beim Mieter sein, bei Abrechnung nach Kalenderjahr also bis zum 31.12.2026 (§ 556 Abs. 3 BGB). Mehr dazu in Nebenkostenabrechnung: Frist für Vermieter.

Die VermieterKlar-Vorlage: so funktioniert die Datei

Wenn Sie die Tabelle nicht selbst bauen möchten: Die Nebenkostenabrechnung Vorlage für 9,99 € ist genau nach dem Aufbau oben gebaut:

• Blätter Anleitung, Einheiten, Kosten, Abrechnung, Anschreiben und Übersicht
• 1 bis 12 Wohnungen, 17 Kostenarten mit der Nummer aus § 2 BetrKV
• 5 Umlageschlüssel per Auswahlliste: Fläche, Personen, Einheiten, Verbrauch Heizung, Verbrauch Warmwasser, mit Verbrauchsanteil in einer eigenen Zelle
• Mieterwechsel tagesgenau, Leerstand als eigene Zeile für den Vermieter
• Übersicht mit Kontrollsumme, die „passt“ oder „bitte prüfen“ anzeigt
• Anschreiben an den Mieter, das sich aus der Abrechnung füllt
• Beispieldaten: genau das Haus aus diesem Artikel, EG links mit 2.245,31 € Anteil und 85,31 € Nachzahlung
• Format .xlsx, ohne Makros und ohne Blattschutz, sodass Sie jede Formel ansehen können, getestet mit LibreOffice Calc

Fragen zur Datei: vermieterklar@mail.grokbot.com

Häufige Fragen

Funktioniert die Vorlage in Microsoft Excel und Google Sheets?

Die Datei ist im .xlsx-Format und nutzt Standardfunktionen wie WENN, INDEX und SUMMENPRODUKT, ohne Makros. Getestet ist sie mit LibreOffice Calc. In Microsoft Excel und Google Sheets haben wir sie nicht selbst getestet.

Reicht nicht eine kostenlose Excel-Tabelle?

Für eine Wohnung mit wenigen Kostenarten oft ja. Sobald mehrere Mieter, Heizkosten nach Verbrauch oder ein Mieterwechsel dazukommen, lohnt ein Blick auf die Punkte in Nebenkostenabrechnung Vorlage kostenlos. Mit den Formeln oben können Sie jede Tabelle selbst nachprüfen.

Wie runde ich richtig?

Runden Sie jeden Anteil auf zwei Nachkommastellen (=RUNDEN(…;2)) und rechnen Sie mit den gerundeten Werten weiter. Eine Abweichung von ein oder zwei Cent in der Kontrollsumme ist dann normal.

Rechnet die Tabelle die CO₂-Kosten?

Die VermieterKlar-Vorlage nicht. Wenn Sie mit Gas oder Öl heizen, ermitteln Sie den Vermieteranteil an den CO₂-Kosten vorher und tragen bei „Heizung“ nur den Betrag ein, den die Mieter tragen.