Versuchen Sie, eine gleichmäßige monatliche Prognose für mehrere Jahre bei sich ändernden Zinssätzen mit jährlichen Zinssatzeingaben zu erstellen.
Mit anderen Worten, ich habe einen Zinssatz für 2022 (10 %), 2023 (15 %), 2024 (12 %), aber ich möchte eine glatte monatliche Prognose erstellen, anstatt 0,8 % (=POWER((1+0,1),(1/12))-1) für Januar 2022 bis Dezember 2022 zu haben und dann für Januar 2023 auf 1,17 % zu ändern... Ich möchte die Werte glätten, um die sich ändernden Werte zu berücksichtigen (etwa 0,8 % Januar 2022, 0,85 % Februar 2022 usw...
Gibt es einen Mathematik-Guru, der dabei helfen kann, eine möglichst genaue Monatsprognose zu erstellen?
Danke!
Mikeee
Antwort1
Sie möchten also, dass die Zinsen im Januar einen bestimmten (korrekten) Betrag betragen und dann im Februar um einen bestimmten Betrag steigen und im März das Doppelte und so weiter, bis sie im Dezember das 11-fache erreichen, sodass die Zinsen jeden Monat gleichmäßig um diesen zusätzlichen Betrag steigen und trotzdem die korrekten Gesamtzinsen für das Jahr ergeben. Vielleicht betragen die Zinsen im Januar 4,50 $ und steigen jeden Monat um einen Dollar, bis sie im Dezember 15,50 $ betragen.
Natürlich wird dabei vorausgesetzt, dass der Zinssatz jährlich und nicht monatlich oder täglich berechnet wird. Wenn er nicht jährlich berechnet wird, ist er etwas komplexer. (Das nennt man übrigens „einfache Zinsen“ und da Computer verfügbar sind, die alle Berechnungen durchführen, wird es so gut wie verschwinden.)
Wie soll das denn nun als mathematisches Problem aussehen? Nun, Sie hätten im Januar den Betrag „x“, dann im Februar „x+$1“ und so weiter. Addieren Sie diese über das Jahr und Sie erhalten die 12 monatlichen x plus 1+2+3+...+9+10+11 = 66. Also 12x + 66 und das entspricht dem jährlichen Zinssatz. Sagen wir 120 $. Wenn Sie die Algebra anwenden, erhalten Sie x = 4,50 $. Monatlich hätten Sie also 4,50 $, 5,50 $, 6,50 $... 13,50 $, 14,50 $, 15,50 $.
Aber das ist nicht „perfekt“, oder? Ich meine, warum diese 4,50 $? Warum nicht 2,00 $ mit einem größeren Anstieg jeden Monat? Idealerweise sollten es 0 $ Anfangszinsen und nur „Bump“ sein – aber wenn man einfach 0 $ für x einzahlt, bekommt man im Januar keine Zinsen. Nehmen wir also an, der Januar hat einen mythischen „Bump“-Betrag, dann der Februar das Doppelte und bis Dezember das 12-fache. Dann wäre es glatt, wobei der Anfangsbetrag dem Betrag entspricht, der jeden Monat steigt. Dann PERFEKT glatt!
Rechnen Sie also noch einmal, aber jetzt 1+2+3+...+10+11+12 (12 Werte, nicht die 11 von oben). Das ergibt 78. Es müssen also 78 „Beulen“ verteilt werden, eine im Januar, zwei im Februar und so weiter bis 12 im Dezember. Ahh, jetzt ist es so offensichtlich, oder?
Im Beispiel mit 120 $ wären das 120 $/78 = 20 $/13 = ungefähr 1,54 $ pro „Stoß“.
Sie müssen dann nur noch die Gesamtzinsen des Jahres in diese Aufteilung einsetzen. Wenn es sich um 10.000 $ bei 10 % einfachen Zinsen für das Jahr handelt, wären das 1.000 $ Zinsen, die wie folgt verteilt werden: 1.000 $/78 = etwa 12,82 $ pro Monat. Im Jahr 2022 wären das also 12,82 $ für Januar, 25,64 $ für Februar, 38,46 $ für März und so weiter. Beginnend bei 12,82 $ und jeden Monat um 12,82 $ steigend. So ziemlich die Definition von „glatt“. Es wären 4¢ extra, die man einschieben könnte, aber ein Penny hier und da alle drei Monate ist einfach und spielt für die Glätte keine große Rolle.
Die „78“ ist für jedes Jahr gleich, also nehmen Sie einfach den Kapitalbetrag für das Jahr, den einfachen Zinssatz für das Jahr, multiplizieren Sie beides und dividieren Sie durch 78. Da es sich um Excel handelt, können Sie diese Berechnung einfach mit der „Monatszahl“ für den Monat multiplizieren.
Nehmen wir beispielsweise an, dass Spalte A den Kapitalbetrag des Jahres enthält (berechnet oder manuell eingegeben, wie Sie es für angebracht halten), Spalte B den Zinssatz (wie Kapitalbetrag, wie auch immer Sie ihn dort erscheinen lassen möchten) und die Spalten C bis N die Monate und jede Zeile ein neues Jahr. Sie haben entweder Überschriften für die Spalten der einzelnen Monate, die tatsächliche Daten sind, wie 1/1/„Jahr“, und formatiert als, sagen wir, „mmm“, sodass sie als „Jan“, „Feb“ usw. angezeigt werden, sodass Sie sie verwenden könnten =MONTH(C1)und eine 1 zurückgegeben würde, die Sie als Multiplikator für diese Spalte verwenden würden, ODER Sie könnten Textüberschriften haben, die Sie in einer Formel auf eine von zehn Arten in ein Datum umwandeln und MONTH()darauf anwenden. So oder so, oder auf eine andere Art, erhalten Sie Multiplikatoren von 1-12. Jede Zelle unter den Monaten würde also etwa Folgendes enthalten:
=((Principal * Interest Rate) / 78) * MONTH(Heading)
Da sich Kapital und Zinssatz in A1 und B1 befinden und die „Überschrift“ C1 bis N1 umfasst, sähe es genauer so aus:
=((A1 * B1) / 78) * Month(N1)
für Zelle C2, dann kopieren und in alle zwölf Monate und jede Zeile einfügen, die Sie verwenden. Einfach das, einmal geschrieben, dann einmal eingefügt.
Wenn Sie über Spilldie entsprechende Funktionalität verfügen, können Sie einfach eine Formel schreiben und diese wird in alle benötigten Zellen aufgeteilt: (sagen wir, Sie haben drei Jahre, also werden die Zeilen 2, 3 und 4 verwendet)
=((A2:A4 * B2:B4) / 78) * SEQUENCE(1,12)
denn wenn Sie haben Spill, haben Sie auch die SEQUENCE()Funktion und wissen, dass Sie alle Monate verwenden. Daher ist das Generieren einer Sequenz von 1-12 genauso gut wie die „glatte“ MONTH()Sache mit den Überschriften. Schreiben Sie es, drücken Sie die Eingabetaste und alle 36 Zellen werden ausgefüllt.
Fügen Sie ein hinzu, ROUND()um die zusätzlichen oder weniger Cents zu erfassen, und lassen Sie die letzte Zelle (Dezember) tatsächlich die Summe der anderen 11 Zellen in der Zeile von den von Ihnen berechneten Zinsen abziehen, und die Zinsen werden alle addiert. Machen Sie es nicht zu kompliziert ... das würde die Dinge nur näher an "richtig" bringen, und "richtig" bis auf den Cent genau ist nicht das Ziel: Das Ziel ist Glätte, daher ist das Runden des Endergebnisses für jeden Monat eher angebracht als der "richtige" Weg.
Lange Antwort: Klingt viel schlimmer, als es ist. Es ist nur die obige Formel oder die, die Sie in alle erforderlichen Zellen einfügen müssen. Passen Sie die Spalten an, die Sie wirklich verwenden, sowie die Position des Kapitals und Zinssatzes für das Jahr, und Sie sind fertig.