Wie berechne ich den monatlichen Zinseszins in Google Sheets?

Ich habe eine Finanztabelle mit einer Spalte aller meiner Einzahlungen auf mein Sparkonto. Die linke Spalte listet die Daten der Einzahlungen auf. Nehmen wir an, mein Sparguthaben beträgt 100,00 $ und meine monatlichen Zinsen betragen 0,25 % (1/4 von 1 %). Dies ist der Zinseszins, der am letzten Tag eines jeden Monats gezahlt wird. Das heißt, am Ende eines Monats erhalte ich 100,00*0,0025 (also 100,02). Am Ende des nächsten Monats bekomme ich 100,02 * 0,0025. Usw. Gibt es eine Finanzformel, mit der ich dies in Google Sheets berechnen kann? Ich würde es vorziehen, alles in einer Zelle zu haben und so etwas wie "Zinsen: $-.--" zu sagen. Danke.

Sind Sie sicher, dass Ihre monatlichen Zinsen 0,25 % betragen, das sind 3 % jährlich?
Und sind Sie sicher, dass Ihre Zinsen nicht verzinst und monatlich ausgezahlt werden, sondern anhand der Tagessalden berechnet werden? Betrachten Sie die Frage Tägliche Zinsberechnung kombiniert mit monatlicher Aufzinsung: Warum machen Banken das und wie geht das in Excel?
Eigentlich hast du Recht. Ich rief die Kreditgenossenschaft an und sie sagten, dass die Zinsen auf Tagesguthaben berechnet und monatlich gezahlt werden.

Antworten (2)

Sie verwenden einfach die Zinseszinsformel:

Principle * (1 + Rate / Time) ^ Time

Geben Sie hier die Bildbeschreibung ein

Für Zelle C2 möchten Sie diese Formel:

=B2*(((1+(D$1/360))^(C$1-$A2))-1)
  • Spalte A ist das Hinterlegungsdatum
  • Spalte B ist der Einzahlungsbetrag
  • Zelle C1 ist das heutige Datum
  • Zelle D1 ist der jährliche Zinssatz

Die meisten Sparkonten, die ich kenne, haben tägliche Zinseszinsen und monatliche Gutschriftzinsen , so dass die obige Formel realistischerweise bis zum heutigen Datum korrekt ist, auch wenn Ihnen noch kein Teil der Zinsen gutgeschrieben wurde.

Sie können auch die eigentliche Formel für den Zinseszins überspringen und einfach die eingebaute Formel für den zukünftigen Wert verwenden:

=FV(D$1/360,C$1-$A2,0,-B2)-B2

Um weiter nach unten zu gehen und nur monatlich zu verzinsen, müssen Sie anfangen, mit der Anpassung der Daten zu spielen ...

Sie können damit beginnen, indem Sie die beiden Daten nehmen und die Anzahl der verstrichenen Monate mit der Funktion DATEDIF() wie folgt berechnen:

=DATEDIF(A2,C$1,"M")

Aber Sie müssen die beiden Daten anpassen, da ein einfaches DATEDIFF zwischen dem heutigen Datum und den Zellen A3 und A4 beide 2 zurückgibt, was nicht wirklich richtig ist. Sie können den ersten Tag des folgenden Monats der Einzahlung entnehmen, indem Sie Folgendes verwenden:

=EOMONTH(A2,0)+1

Und Sie können den Monatsersten des laufenden Monats mitnehmen

=DATE(YEAR(C1),MONTH(C1),1)

Was macht Ihre Formel:

=B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1)

Aber das ist nicht wirklich richtig, weil es erst am Ersten des Monats nach der Einzahlung beginnt, Zinsen anzusammeln. Sie können auch eine ungefähre Anzahl von Monaten erhalten, indem Sie die beiden Daten subtrahieren und durch 30 Tage teilen.

Sie können dies komplizierter machen, um eine Anzahl von Tagen für den ersten Monat + volle monatliche Zinsen darüber hinaus zu berechnen, aber es macht die Formel viel länger, weil Sie müssen

First month in days interest + monthly interest beyond that

Um die verbleibenden Tage in einem Monat zu erhalten, würden Sie Folgendes tun:

=EOMONTH($A2,0) - $A2

Um also den Anteil des Rests des Monats zu erhalten, den Sie tun (Tage im Monat geteilt durch die Anzahl der Tage im Monat):

=(EOMONTH($A2,0) - $A2) / DAY(EOMONTH($A2,0))

Dann multiplizieren Sie das obige mit dem monatlichen Zinssatz mal dem Prinzip, um den Teilmonat zu erhalten, und addieren Sie dann die obigen monatlichen Zinsen.

=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))

Aber denken Sie daran, Ihr Hauptbetrag auf die monatlichen Zinsen ist jetzt Ihr Hauptbetrag + die Zinsen, die im ersten Monat gutgeschrieben wurden, also sollte Ihre Formel eigentlich lauten:

=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(($B2*1+((((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12)))))*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))

An diesem Punkt spalten Sie wirklich Haare, denn es ist die Differenz von 1,74327 $ an Zinsen gegenüber 1,74331 $, wenn Sie die Zinsen der ersten Monate in den Kapitalbetrag für die verbleibenden Monate einbeziehen. Dies unterscheidet sich von den 1,85 $ in Zelle C2 oben, da Ihnen die ersten 10 Tage im August noch nicht gutgeschrieben wurden. In vielen Fällen spielen die winzigen Unterschiede bei der Aufzinsung nur bei großen Zahlen eine Rolle, und selbst dann ... Wenn Sie im Prinzip 10.000.000 $ hätten, würde sich die Aufzinsungsdifferenz von 0,00004 $ auf 4 $ ändern. Für die meisten Zwecke ist die erste Formel ganz oben mehr als ausreichend (und wahrscheinlich diejenige, die ich tatsächlich in allen Fällen verwenden würde, da der praktische Unterschied zwischen der täglichen und der monatlichen Aufzinsung einfach nicht signifikant ist).

Wow, danke für die sehr ausführliche Antwort! Wenn ich Zeit habe, werde ich das versuchen und sehen, ob es den Trick tut. Ich ziehe es vor, so genau wie möglich zu sein, aber ich verstehe auch, dass es normalerweise um ein paar Cent geht oder sogar noch weniger.
@grgoelyk, Wirklich, nach dieser Pause habe ich es für mich selbst getan, einfach die Bewegungen in Excel durchzugehen, weil das manchmal Spaß macht. Wirklich, die erste Formel verbindet sich täglich und das ist, was Sie wollen. Ich denke, die einzige Verbesserung, die in der ersten Formel vorgenommen werden muss, besteht darin, die tatsächlich erscheinende Anzahl von Tagen im angegebenen Jahr zu verwenden, anstatt nur 360 anzunehmen, aber auch hier spalten Sie nur Haare und es wird durch die Anzahl der Jahre zwischen den beiden variieren Termine.
Tolle Antwort, und ich hasse es, pingelig zu sein, aber es gibt 365 Tage in einem Jahr, nicht 360, also müssen Sie die obigen Formeln anpassen, um dies widerzuspiegeln.
Es ist nicht ungewöhnlich, dass Banken die Zinsen auf der Grundlage eines 360-Tage-Jahres berechnen. Und der Unterschied von 5 Tagen würde das Ergebnis nicht ändern, bis Sie mit sehr großen Zahlen handeln.
@quid Wirklich? Warum verschenken Banken damit freiwillig Geld?
Weil Zeiträume von 12, 30 Tagen einfacher zu verwalten sind.

Dazu dient die Funktion „Future Value“.

=FV(rate, number_of_periods, payment_amount, present_value, [end_or_beginning])

Zum Beispiel:

=FV(2%, 12, -100, -400, 0)

Beachten Sie, dass sowohl der Zahlungsbetrag als auch der Gegenwartswert als negative Zahlen eingegeben werden sollten, da sonst ein negativer Wert ausgegeben wird

Weitere Informationen und verwandte Funktionen finden Sie im Google-Support-Artikel .

Wie berechne ich den monatlichen Zinseszins in Google Sheets?
AntwortenHierDatenschutz-Bestimmungen1y, 3v