Pożyczka PMT opłata 2 razy w miesiącu kilka sztuczek z finansami i datami

Jak obliczyć należną ratę za pożyczkę?

Zacznijmy od tego, że chcesz wyliczyć ile wyniesie każda rata spłaty pożyczki w sytuacji kiedy masz aż 2 płatności w 1 miesiącu.

Excel Pożyczka PMT opłata 2 razy w miesiącu kilka sztuczek z finansami i datami sztuczki #31

Do tego należy wykorzystać funkcję PMT, po polsku ciężko ją skojarzyć, ale po angielsku to jest Payment i poszczególne litery pasują.

Do funkcji PMT potrzebujesz stopy procentowej, tylko nie rocznej, a takiej jaka przypada na dokładnie 1 okres płatności. Jeśli masz stopę roczną i ilość płatności w roku, wystarczy je podzielić (B2/B4).

Następnie potrzebujesz liczbę płatności w roku, to jest proste (B4).

Do danych wymaganych należy jeszcze wartość początkowa pożyczki (argument wa funkcji PMT). Tutaj zwróć uwagę, z której strony patrzysz na pożyczkę, czy ze strony banku czy pożyczkobiorcy. Od tego będzie zależał znak przy pożyczce i później analogicznie znak przy jej spłacaniu (-B1)

Pozostałe argumenty są opcjonalne:

wp — czyli wartość końcowa pożyczki, domyślnie 0.
typ — 0 albo 1 która wskazuje, kiedy płatność jest należna. 0 (wartość domyślna) na koniec okresu, 1 na początek.

Teraz chcemy wypisać wszystkie okresy płatności (daty). Mamy 2 możliwości, albo napiszemy je ręcznie wykorzystując sztuczki excela, by było szybko (daty będą wpisane na stałe), albo połączymy formułami z wartością początkową i uzyskamy dynamiczne daty.

Jeśli skopiujesz datę początkową, a następnie przeciągniesz ją w dół, to będzie się zwiększać tylko o 1 dzień, ale jeśli rozwiniesz pomocne menu przy kopiowaniu w dół, znajdziesz tam opcję wypełnienia miesiącami. Dzięki temu uzyskasz np: 15 dzień każdego miesiąca.

Jeśli chcesz uzyskać ostatni dzień miesiąca postępujesz analogicznie. Najpierw wpisujesz pierwszy ostatni dzień, następnie kopiujesz i wybierasz Wypełnij miesiącami.

Jeśli chcemy skorzystać z formuł, to do pierwszej komórki wpisujemy, że równa się wartości początkowej (=B5), a następnie wykorzystujemy funkcję

=NR.SER.DATY(A9;1)

by każda następna data była większa o dokładnie 1 miesiąc (1 jako drugi argument).

Żeby uzyskać daty z końca miesiąca wykorzystamy odwołanie do dat z 15 dniem miesiąca i odpowiednią funkcję:

=NR.SER.OST.DN.MIES(A9;0)

Drugi argument to 0, co oznacza, że chcemy ostatni dzień tego samego miesiąca co data początkowa, jeśli wpiszesz np: 1 to będzie to ostatni dzień następnego miesiąca, a ‑1 da Ci ostatni dzień poprzedniego miesiąca.

Daty zapisane formułami będą się zmieniać odpowiednio do ustawionej przez Ciebie daty początkowej, a daty wpisane przez wypełnienie pozostaną stałe.

Nasze dane są w 2 kolumnach (by te same miesiące były w tych samych wierszach),
trzeba je wypełnić wynikiem funkcji PMT, żeby zrobić to w jednym podejściu należy zaznaczyć 2 niesąsiadujące ze sobą obszary, żeby to zrobić wystarczy przytrzymać klawisz Ctrl, po zaznaczenie 1 obszaru, a przed zaznaczeniem drugiego. Teraz wpisujesz, że =$B$6 i naciskasz Ctrl + Enter, dzięki temu ta formuła wpisywana jest we wszystkie zaznaczone komórki.

Aby zsumować wszystkie płatności wykorzystamy funkcję SUMA i tu też analogicznie zaznaczamy 2 obszary w odpowiednim momencie przytrzymując Ctrl. Zwróć uwagę, że w funkcji niesąsiadujące obszary zostały rozdzielone średnikiem (;). Powstały w ten sposób 2 argumenty dla funkcji z 2 obszarów.

=SUMA(D9:D20;B9:B20)

Żeby wyliczyć sumę odsetek wystarczy, od przed chwilom obliczonej wartości odjąć wartość początkową pożyczki.

Na koniec jeszcze chcemy rozpisać całą pożyczkę na poszczególne okresy, ile wpłaciliśmy, ile spłaciliśmy odsetek, o ile zmniejszyliśmy balans pożyczki i jaki on aktualnie jest.

Wpłacamy zawsze taką samą wartość (wynik PMT).
Następnie żeby obliczyć wartość spłaconych odsetek mnożymy wartość bilansu z końca wcześniejszego okresu razu stopę procentową 1 okresu, czyli stopa_roczna/ilość_okresów_w_roku:

=E27*$B$2/$B$4

Wartość o jaką zmniejszyliśmy bilans uzyskujemy odejmując od PMT wartość spłaconych odsetek:

=B28-C28

a aktualny bilans uzyskujemy odejmując powyższą wartość od bilansu z wcześniejszego okresu.

=E27-D28

Z tych obliczeń możemy sobie sprawdzić, czy wcześniej poprawnie obliczyliśmy sumę odsetek.

Jak wielkie jest nasze zdziwienie kiedy okazuje się, że dokonaliśmy poprawnych obliczeń 🙂

P.S.

Wpis na podstawie Excel Magic Trick 1007

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.