PDA

Vollständige Version anzeigen : Jährliche Dynamik Formel?


Can.Ay
08.01.2019, 02:40
Hallo alle miteinander,

ich versuche seit ein paar Tagen eine Formel oder ein Array hinzubekommen, um mir bei Excel bei einer Einzahlung mit Dynamik die Summe ausrechnen zu lassen.

Beispiel:

100€ einzahlung im Monat
10 Jahre Laufzeit
Jährliche Erhöhung der Einzahlung um 3% pro Jahr

Wie viel hätte man nach 10 Jahre gesamt eingezahlt?

Dann würde ich gerne in einer weiteren Zeile noch angeben, wie hoch die Summe bei zusätzlich 6% Rendite pro Jahr (immer auf das aktuelle Guthaben) nach 10 Jahren ist. Wie kann ich da am besten was zusammenbasteln?


Würde mich sehr über eure hilfe freuen! :p

MisterBurns
08.01.2019, 07:01
Ich glaube nicht, dass das mit einer einzigen Formel abbildbar ist. Es gibt die Formel
=ZW(...)
für die Berechnung mit einem konstant fixen Einzahlungsbetrag, dieser berücksichtigt aber nicht die jährliche Erhöhung. Dafür wirst du um eine Hilfstabelle nicht herumkommen. Aber das kann man doch problemlos so lösen, dass die Hilfstabelle gar nicht sichtbar ist und du einfach 50 Zeilen vorab ausfüllst. Länger wird wohl keine Sparform gehen.

Mc Santa
08.01.2019, 07:32
Das lässt sich mit der Formel des Endwertfaktor berechnen (https://de.wikipedia.org/wiki/Endwertmethode). Die Parallele ergibt sich, weil erwirtschaftete Zinsen und jährliche erhöhte Einzahlungen rechnerisch auf das gleiche Ergebnis kommen.

Manuelle Berechnung:
1200
1236
1273,08
1311,2724
1350,610572
1391,128889
1432,862756
1475,848639
1520,124098
1565,727821
== 13756,66

Über die Formel:
1200 * ((1 + 3%)^10 - 1) / 3%
== 1200 * 11,4638793
== 13756,66

Mc Santa
08.01.2019, 07:36
Hallo,

nach meiner Antwort habe ich leider erst gesehen, dass du auch eine Rendite berücksichtigen möchtest.*

Dabei stellt sich nun die Frage, wie diese Zinsen erwirtschaftet werden oder vielmehr, *wann* diese Zinsen gutgeschrieben werden. Legst du das Geld in Aktien bei einer Rendite von 6% p.a. an? Oder gibst du das Geld einer Bank mit jährlich 6% Zinsen? Der Teufel steckt hier im Detail und es kommen geringfügig andere Ergebnisse je nach Art der Berechnung heraus (https://de.wikipedia.org/wiki/Zinsberechnungsmethode)

Viele Grüße
Santa

*Hier habe ich vormals geschrieben, dass die Frage erst nach meiner Antwort verändert wurde, das stimmt auf Grund der offensichtlichen Zeitstempel nicht, ich entschuldige mich für diese Behauptung. (Danke für den entsprechenden Hinweis)

MisterBurns
08.01.2019, 07:39
@Santa: Schöne Lösung, dachte nicht, dass das so lösbar ist. Aber dass der Fragesteller die Frage nach deiner Antwort bearbeitet hat, stimmt nicht. Die Frage nach der Zinsberechnung war schon vor meiner Antwort da.

Can.Ay
08.01.2019, 16:03
Danke für die schnelle Antwort Santana! Ich hatte meine Frage ca. 2 min nach dem erstellen des Themas geändert, ist aber auch nicht weiter wichtig :grins:

Also deine Formel hilft mir trotzdem, da ich ja den Einzahlbetrag (mit eben der Dynamik) getrennt von dem gesamtkapital mit den Renditen darstellen möchte. Es ist so: Man zahlt monatlich ein und hat eine Dynamik von 3% jedes Jahr. Dann bekommt man immer am Ende des Jahres 6% Rendite auf die jeweils aktuelle Summe.

Beispiel für die ersten 2 Jahre mit 100€ pro Monat, 3% Dynamik, 6% Rendite:

Jahr 1
12*100*1,06 = 1272€ (Eigenkaptial+Rendite)

Jahr 2
(12*103+1272€)*1,06 = 2658,48€


Ich möchte dann einmal das Gesamtkapital, also in dem Fall 2658,48€ angezeigt haben und separat noch wie viel man selber eingezahlt hat (in dem Fall 1200+1236 = 2436€).

Hoffe ich konnte das soweit verständlich darstellen.

Mc Santa
09.01.2019, 09:54
Hallo,

Ich möchte kurz einleiten damit, dass diese Art der Zinszahlungen etwas unrealistisch sind. Denn wir gehen hier davon aus, dass die 100€ aus dem Januar und die 100€ aus dem Dezember eines Jahres jeweils 6% Rendite am Ende des Jahres bekommen.

Realistischer wäre der Fall, wenn wir zu Beginn jedes Jahres 1200€ einzahlen und dann am Ende des Jahres 6% dafür erhalten. Das führt dann im Ergebnis zu genau der Rechnung, die du unter #6 vorgerechnet hast. (Zu Beginn des nächsten Jahres zahlen wir dann 1200*1,03 = 1236€ ein und erhalten auf die bisherige Summe [2508€] erneut Zinsen von 6%).

Ungeachtet dieser Formulierungsprobleme, habe ich für das Problem der eigentlichen Berechnung eine Lösung gefunden:

Mir ist nicht bekannt, ob es für diese Problemstellung eine einfache Formel gibt, grundsätzlich versuchen wir folgenden Term zu lösen:

1200*1,06^10 + 1200*1,03*1,06^9 + ... + 1200*1,03^8*1,06^2 + 1200*1,03^9*1,06

Diese Formel konnte ich nur über eine Matrixformel abbilden:
=1200*SUMME(1,03^(10-ZEILE(1:10))*1,06^(ZEILE(1:10)))

Im Ergebnis führt das zu folgender Tabelle:
<style>.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .r{text-align:right;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.mcs .cond tr:first-child td{background-color:#aaaaee;}.mcs .valid tr:first-child td{background-color:#aaeeee;}.formats, .names, .formulas, .cond, .fonts, .valid, .notice{float: none;margin: 10pt 10pt 0 0;}</style><div class='mcs'><table class='sheet'><colgroup><col width='28pt'><col width='136pt'><col width='80pt'></colgroup><tr><td></td><td>A</td><td>B</td></tr><tr><td>1</td><td>Monatseinzahlung</td><td class='r'>100,00 €</td></tr><tr><td>2</td><td>Monate</td><td class='r'>12</td></tr><tr><td>3</td><td>Jahreseinzahlung</td><td class='r'>1.200,00 €</td></tr><tr><td>4</td><td>Dynamik jedes Jahr</td><td class='r'>3%</td></tr><tr><td>5</td><td>Zinsens jedes Jahr</td><td class='r'>6%</td></tr><tr><td>6</td><td>Laufzeit in Jahren</td><td class='r'>10</td></tr><tr><td>7</td><td class='r'></td><td class='r'></td></tr><tr><td>8</td><td>Eingezahltes Kapital</td><td class='r'>13.756,66 €</td></tr><tr><td>9</td><td>Zinsen</td><td class='r'>5.193,23 €</td></tr><tr><td>10</td><td>Gesamtes Kapital</td><td class='r'>18.949,89 €</td></tr></table><table class='formulas' style='word-break: break-all;'><colgroup><col width='40pt'><col></colgroup><tr><td>Zelle</td><td>Formel</td></tr><tr><td>B3</td><td><Span style='color:#222222'>=B1*B2</Span></td></tr><tr><td>B8</td><td><Span style='color:#222222'>=B3*</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>(1+B4)</Span><Span style='color:#0000DD'>^B6-1)</Span><Span style='color:#222222'>/B4</Span></td></tr><tr><td>B9</td><td><Span style='color:#222222'>=B10-B8</Span></td></tr><tr><td>B10</td><td><Span style='color:#222222'>{=SUMME</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>(1+B4)</Span><Span style='color:#0000DD'>^</Span><Span style='color:#222222'>(B6-ZEILE</Span><Span style='color:#0000DD'>(A1:INDEX</Span><Span style='color:#222222'>(A:A;B6)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>(1+B5)</Span><Span style='color:#0000DD'>^</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>(A1:INDEX</Span><Span style='color:#222222'>(A:A;B6)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>*B3}</Span></td></tr></table><table class='notice' tyle='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span style='text-decoration:underline'>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span style='font-weight:bold; color:#FF0000'>Strg + Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table></div>

Ich habe diese Zahlen mit einer manuell erstellten Tabelle überprüft und komme auf das gleiche Ergebnis. Fragen gerne.

VG, Santa

Mc Santa
09.01.2019, 13:36
Ich habe nun noch diese Formel gefunden:
https://www.onlinemathe.de/forum/Beweise-an-bn-

Darauf aufbauend lässt sich die Formel besser darstellen:
<style>.mcs{margin-top:5pt;}.mcs table{border-collapse:collapse;}.mcs table td{border:1px solid #999999;color:#000000;background-color:#FFFFFF;font:11pt Calibri, sans-serif;padding:1pt 2pt;text-align:left;vertical-align:middle;}.mcs .foot td{border:0;font-size:9pt;color:#888888;padding:0;}.mcs .foot{clear: both;}.mcs .foot td{background-color:transparent;}.mcs .sheet td:first-child{background-color:#ddddee;text-align:center;}.mcs .sheet tr:first-child td{background-color:#ddddee;text-align:center;}.mcs .bcFFFF00{background-color:#FFFF00;}.mcs .r{text-align:right;}.mcs .formulas tr:first-child td{background-color:#eeaaaa;}.mcs .cond tr:first-child td{background-color:#aaaaee;}.mcs .valid tr:first-child td{background-color:#aaeeee;}.formats, .names, .formulas, .cond, .fonts, .valid, .notice{float: none;margin: 10pt 10pt 0 0;}</style><div class='mcs'><table class='sheet'><colgroup><col width='28pt'><col width='136pt'><col width='80pt'><col width='80pt'></colgroup><tr><td></td><td>A</td><td>B</td><td>C</td></tr><tr><td>1</td><td>Monatseinzahlung</td><td class='r'>100,00 €</td><td class='r'></td></tr><tr><td>2</td><td>Monate</td><td class='r'>12</td><td class='r'></td></tr><tr><td>3</td><td>Jahreseinzahlung</td><td class='r'>1.200,00 €</td><td class='r'></td></tr><tr><td>4</td><td>Dynamik jedes Jahr</td><td class='r'>3%</td><td class='r'></td></tr><tr><td>5</td><td>Zinsens jedes Jahr</td><td class='r'>6%</td><td class='r'></td></tr><tr><td>6</td><td>Laufzeit in Jahren</td><td class='r'>10</td><td class='r'></td></tr><tr><td>7</td><td class='r'></td><td class='r'></td><td class='r'></td></tr><tr><td>8</td><td>Eingezahltes Kapital</td><td class='r'>13.756,66 €</td><td class='r'></td></tr><tr><td>9</td><td>Zinsen</td><td class='r'>5.193,23 €</td><td class='r'></td></tr><tr><td>10</td><td>Gesamtes Kapital</td><td class='r'>18.949,89 €</td><td class='r bcFFFF00'>18.949,89 €</td></tr></table><table class='formulas' style='word-break: break-all;'><colgroup><col width='40pt'><col></colgroup><tr><td>Zelle</td><td>Formel</td></tr><tr><td>B3</td><td><Span style='color:#222222'>=B1*B2</Span></td></tr><tr><td>B8</td><td><Span style='color:#222222'>=B3*</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>(1+B4)</Span><Span style='color:#0000DD'>^B6-1)</Span><Span style='color:#222222'>/B4</Span></td></tr><tr><td>B9</td><td><Span style='color:#222222'>=B10-B8</Span></td></tr><tr><td>B10</td><td><Span style='color:#222222'>{=SUMME</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>(1+B4)</Span><Span style='color:#0000DD'>^</Span><Span style='color:#222222'>(B6-ZEILE</Span><Span style='color:#0000DD'>(A1:INDEX</Span><Span style='color:#222222'>(A:A;B6)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>*</Span><Span style='color:#222222'>(1+B5)</Span><Span style='color:#0000DD'>^</Span><Span style='color:#222222'>(ZEILE</Span><Span style='color:#0000DD'>(A1:INDEX</Span><Span style='color:#222222'>(A:A;B6)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>*B3}</Span></td></tr><tr><td>C10</td><td><Span style='color:#222222'>=B3*</Span><Span style='color:#0000DD'>(</Span><Span style='color:#222222'>(1+B5)</Span><Span style='color:#0000DD'>^B6-</Span><Span style='color:#222222'>(1+B4)</Span><Span style='color:#0000DD'>^B6)</Span><Span style='color:#222222'>/</Span><Span style='color:#0000DD'>(B5-B4)</Span><Span style='color:#222222'>*</Span><Span style='color:#0000DD'>(1+B5)</Span><Span style='color:#222222'></Span></td></tr></table><table class='notice' tyle='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span style='text-decoration:underline'>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span style='font-weight:bold; color:#FF0000'>Strg + Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table></div>

Can.Ay
09.01.2019, 18:23
Hi Santana, danke dir vielmals! Wirklich sehr schön gelöst :)
Hab die Formel aus #7 schon probiert und funktioniert sehr gut! Was ist jetzt der Unterschied zu #8?
Außerdem komme ich irgendwie nicht auf die Gesamte Rendite wie sie in unserem alten Programm (brauche das ganze beruflich) dargestellt wurde. Muss mich da nochmal an die genaue Verzinsung ransetzen... Liegt auf jeden Fall nicht an der Formel.

Kann man ansonsten noch irgendwie Einmalzahlungen mit einrechnen? Zb. man zahlt einmalig 10.000€ und danach halt 100€ Monatlich mit 3% Dynamik.
Danke dir!

Mc Santa
10.01.2019, 08:14
Hallo,

kannst du an einem Beispiel zeigen, wie sich die Zahlen von mir und die Zahlen aus deinem alten Programm unterscheiden? Ich denke es liegt an der unterschiedlichen Berechnung, bzw der Definition. Bereits in #7 habe ich ja angedeutet, dass die vorgeschlagene Berechnung eher zu einer jährlichen Einzahlung und nicht zu einer monatlichen Einzahlung passt.

Einmalzahlungen zu ergänzen ist relativ einfach, sobald wir den vorherigen Punkt geschafft haben.

Zwischen meinen beiden Formelvorschlägen ist inhaltlich kein Unterschied, es ist nur eine andere Art der Berechnung.

VG, Santa