Używasz po prostu wzoru na procent składany:
Principle * (1 + Rate / Time) ^ Time
Dla komórki C2 chcesz mieć taką formułę:
=B2*(((1+(D$1/360))^(C$1-$A2))-1)
- Kolumna A to data wpłaty
- Kolumna B to kwota wpłaty
- Komórka C1 to dzisiejsza data
- Komórka D1 to roczna stopa procentowa
Większość znanych mi kont oszczędnościowych nalicza odsetki codziennie, a odsetki naliczane są co miesiąc , więc realistycznie rzecz biorąc, powyższy wzór będzie dokładny do dzisiejszej daty, nawet jeśli część odsetek nie została jeszcze naliczona.
Możesz również pominąć rzeczywistą formułę procentu składanego i po prostu użyć wbudowanej formuły wartości przyszłej:
=FV(D$1/360,C$1-$A2,0,-B2)-B2
Aby dalej drążyć temat tylko miesięcznego procentu składanego, musisz zacząć bawić się w dopasowywanie dat…
Możesz zacząć od wzięcia tych dwóch dat i obliczenia liczby miesięcy, które upłynęły używając funkcji DATEDIF() w ten sposób:
=DATEDIF(A2,C$1,"M")
Ale będziesz musiał dostosować te dwie daty, ponieważ zwykłe DATEDIFF pomiędzy dzisiejszą datą a komórkami A3 i A4 zwróci 2, co nie jest do końca poprawne. Możesz wziąć pierwszy dzień następnego miesiąca depozytu, używając:
=EOMONTH(A2,0)+1
A możesz wziąć pierwszy dzień miesiąca bieżącego miesiąca za pomocą
=DATE(YEAR(C1),MONTH(C1),1)
Co sprawia, że twoja formuła:
=B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1)
Ale to nie jest do końca poprawne, ponieważ odsetki zaczynają być naliczane dopiero od pierwszego dnia miesiąca następującego po wpłacie. Można również uzyskać przybliżoną liczbę miesięcy odejmując obie daty i dzieląc przez 30 dni.
Możesz to bardziej skomplikować, aby obliczyć liczbę dni dla pierwszego miesiąca + pełne miesięczne odsetki po nim, ale to znacznie wydłuża formułę, ponieważ będziesz miał
First month in days interest + monthly interest beyond that
Aby uzyskać dni pozostałe w miesiącu, zrobiłbyś coś takiego:
=EOMONTH($A2,0) - $A2
Więc aby uzyskać proporcję pozostałej części miesiąca robisz (dni występujące w miesiącu podzielone przez liczbę dni w miesiącu):
=(EOMONTH($A2,0) - $A2) / DAY(EOMONTH($A2,0))
Następnie pomnóż powyższe przez miesięczną stopę procentową razy zasadę, aby uzyskać częściowy miesiąc, a następnie dodaj miesięczne odsetki powyżej.
=($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))
Ale pamiętaj, że Twoja kwota bazowa na odsetkach miesięcznych to teraz Twoja kwota bazowa + odsetki, które zostały dopisane w pierwszym miesiącu, więc Twój wzór powinien wyglądać następująco:
=($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))
W tym momencie naprawdę dzielisz włos na czworo, ponieważ jest to różnica $1,74327 odsetek w porównaniu z $1,74331, gdy uwzględnimy odsetki z pierwszego miesiąca w kwocie głównej za pozostałe miesiące. Różni się to od $1,85 w komórce C2 powyżej, ponieważ nie zostały zaksięgowane za pierwsze 10 dni w sierpniu jeszcze. W wielu przypadkach te drobne różnice w składaniu będą miały znaczenie tylko przy dużych liczbach, a nawet wtedy…. Jeśli miałbyś $10 000 000 w zasadzie, różnica w składaniu zmieniłaby się z $0,00004 do $4. Dla większości celów pierwszy wzór sposób tam jest więcej niż wystarczający (i prawdopodobnie ten, który faktycznie użyłbym we wszystkich przypadkach, ponieważ praktyczna różnica w składaniu codziennie i miesięcznie po prostu nie jest znacząca).
