Folgendes Problem: Ich möchte einen Annuitäten Zins- und Tilgungsplan erstellen, welcher anhand von Eck- bzw. Rahmendaten (Darlehenssumme, Darlehenslaufzeit, Restobligo, Zinssatz etc.) angepasst werden kann. Problem ist jedoch hier, dass ich über die gesamte Darlehenslaufzeit nicht nur einen Zinssatz habe, sondern für verschiedene Zeiträume innerhalb dieser (Gesamt-)Laufzeit verschiedene Zinssätze. Beispiel:
Darlehenslaufzeit: 20 Jahre (01.01.2010 – 31.12.2029) Zinssätze: 01.01.2010 bis 31.12.2015: 3,15 % 01.01.2016 bis 31.12.2021: 4,90 % 01.01.2022 bis 31.12.2025: 4,80 % 01.01.2026 bis 31.12.2029: 5,10 % (Start- und End-Datum, sowie der dazugehörige Zinssatz befindet sich alles jeweils in einer eigenen Zelle) Der Zins- und Tilgungsplan ist zeigt die Zahlungen pro Quartal an: 01.01.2010 – 31.03.2010: Zins; Tilgung; Gesamt-Rate; etc. 01.04.2010 – 30.06.2010: Zins; Tilgung; Gesamt-Rate;
etc. … 01.10.2029 – 31.12.2029: Zins; Tilgung; Gesamt-Rate, etc. Das Start- und End-Datum befindet sich jeweils in einer Zelle nebeneinander. In dieser Zeile befinden sich pro Quartal auch Zins, Tilgung und Gesamt-Rate jeweils in einer eigenen Zelle. Und nun mein Problem: Wie schaffe ich es, dass im Zins- und Tilgungsplan die Spalte Zins jeweils (jede Zelle) nachschaut welcher Zins (siehe oben) zur Anwendung kommt für das Quartal in dieser Zeile. Sprich, für das Quartal 01.04.2016
bis 30.06.2016, soll der dazugehörige Zins von 4,90 % angezeigt werden. Die Berechnung der Zinszahlung ist erstmal nicht wichtig. Die bekomm ich dann ganz schnell hin, sofern ich automatisch pro Zeile/Quartal den richtigen Zinssatz da stehen habe. Ich hoffe Ihr könnt die Fragestellung einigermaßen nachvollziehen. Besten Dank schon mal im Voraus für eure Hilfe.
Vom Fragesteller als hilfreich ausgezeichnet 2 Antworten
Community-Experte
Microsoft Excel
Eine Variante wäre: (ich kann das nicht so toll im Text darstellen) 1.) Die legst eine Tabelle an: Spalte A = Beginndatum, ab wann der Zinssatz gültig ist, Spalte B = Zinssatz. Endedatum nicht
notwendig, da unterstellt wird, dass der Zinssatz solange gültig ist, bis eben ein neuer Eintrag kommt. Als fiktives Beispiel mit drei Werten 01.01.2014 10% 01.01.2015 15 %
01.10.2015 20%
2.) Formel je Quartal
=WVERWEIS([Zelle wo das Quartal steht];A1:B3;VERGLEICH([Zelle wo das Quartal steht];A1:A3))
Damit das Abgleichen funktioniert muss der Beginn des Quartals auch als Datumswert hinterlegt sein
vielleicht im Bild besser zu sehen. Das ganze nur als grobe Idee, musst Du für eine Tabelle entsprechend anpassen
Beispiel WENN(Datum>1.1.2026;0,051;Wenn(Datum>1.1.2022;0,048;Wenn( usw.
Was möchtest Du wissen?
Excel für Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Mehr...Weniger
Verwenden Sie die Funktion ZÄHLENWENN, um Zahlen oder Datumsangaben zu zählen, die eine einzelne Bedingung erfüllen (z. B. gleich, größer als, kleiner als, größer oder gleich oder kleiner oder gleich). Zum Zählen von Zahlen oder Datumsangaben, die innerhalb eines Bereichs liegen (z. B. größer als 9000 und gleichzeitig kleiner als 22500), können Sie die Funktion ZÄHLENWENNS verwenden. Alternativ können Sie auch SUMMENPRODUKT verwenden.
Beispiel
Hinweis: Sie müssen diese hier beschriebenen Zellformelbezüge darauf anpassen, wo und wie Sie diese Beispiele in das Excel kopieren.
2 | Verkäufer | Rechnung |
3 | Buchanan | 15.000 |
4 | Buchanan | 9.000 |
5 | Suyama | 8.000 |
6 | Suyma | 20.000 |
7 | Buchanan | 5.000 |
8 | Dauersberg | 22.500 |
9 | Formel | Beschreibung (Ergebnis) |
10 | =ZÄHLENWENN(B2:B7;">9000") | Mit der Funktion ZÄHLENWENN wird ermittelt, wie viele Zellen im Bereich B2:B7 Zahlen enthalten, die größer als 9000 sind (4) |
11 | =ZÄHLENWENN(B2:B7;"<=9000") | Mit der Funktion ZÄHLENWENN wird ermittelt, wie viele Zellen im Bereich B2:B7 Zahlen enthalten, die kleiner als 9000 sind (4) |
12 | =ZÄHLENWENNS(B2:B7;">=9000";B2:B7;"<=22500") | Mit der Funktion ZÄHLENWENNS (verfügbar Excel 2007 und höher) wird die Anzahl der Zellen im Bereich B2:B7 größer oder gleich 9000 und kleiner oder gleich 22500 (4) ermittelt. |
13 | =SUMMENPRODUKT((B2:B7>=9000)*(B2:B7<=22500)) | Mit der Funktion SUMMENPRODUKT wird ermittelt, wie viele Zellen im Bereich B2:B7 Zahlen enthalten, die größer gleich 9000 und kleiner gleich 22500 (4) sind. Sie können diese Funktion in Excel 2003 und früheren Versionen verwenden, in denen ZÄHLENWENNS nicht verfügbar ist. |
14 | Datum | |
15 | 3/11/2011 | |
16 | 1/1/2010 | |
17 | 12/31/2010 | |
18 | 6/30/2010 | |
19 | Formel | Beschreibung (Ergebnis) |
20 | =ZÄHLENWENN(B14:B17;">01.03.2010") | Zählt die Anzahl der Zellen im Bereich B14:B17 mit Daten größer als 01.03.2010 (3) |
21 | =ZÄHLENWENN(B14:B17;"31.12.2010") | Ermittelt die Anzahl der Zellen im Bereich B14:B17 gleich 31.12.2010 (1). Das Gleichheitszeichen ist in den Kriterien nicht erforderlich, daher ist es hier nicht enthalten (die Formel funktioniert mit einem Gleichheitszeichen, wenn Sie es enthalten ("=31.12.2010"). |
22 | =ZÄHLENWENNS(B14:B17;">=01.01.2010";B14:B17;"<=31.12.2010") | Ermittelt die Anzahl der Zellen im Bereich B14:B17 zwischen (einschließlich) dem 01.01.2010 und dem 31.12.2010 (3). |
23 | =SUMMENPRODUKT((B14:B17>=DATUMWERT("1.1.2010"))*(B14:B17<=DATUMWERT("31.12.2010"))) | Ermittelt die Anzahl der Zellen im Bereich B14:B17 zwischen (einschließlich) dem 01.01.2010 und dem 31.12.2010 (3). Dieses Beispiel dient als Ersatz für die Funktion ZÄHLENWENNS, die in Excel 2007 eingeführt wurde. Die Funktion DATWERT konvertiert die Datumsangaben in einen numerischen Wert, mit dem die Funktion SUMMENPRODUKT arbeiten kann. |