=PMT(B2/12;B3*12;-B1) zwraca miesięczną ratę kredytu na kwotę z B1 przy rocznym oprocentowaniu z B2, spłacanego przez B3 lat. Stopę dzieli się przez 12, a lata mnoży przez 12, bo raty są miesięczne. Funkcja ma tę samą nazwę w polskim i angielskim Excelu; tabele pokazują formuły po angielsku, z przecinkami, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Monthly payment | $1,580.17 |
| 6 | Total paid | $568,861.22 |
| 7 | Total interest | $318,861.22 |
=PMT(B2/12;B3*12;-B1)Kredyt na 250 000 przy 6,5% na 30 lat kosztuje 1580,17 miesięcznie, a odsetki za cały okres wynoszą 318 861,22, więcej niż sam kredyt. Zmień B3 na 15: rata wzrośnie do 2177,77, ale łączne odsetki spadną poniżej połowy.
Składnia funkcji PMT
=PMT(rate, nper, pv, [fv], [type])
rate: oprocentowanie na okres. Dla rat miesięcznych przy stopie rocznej użyjstopa/12.nper: liczba rat. 30 lat rat miesięcznych to30*12, czyli 360.pv: wartość obecna, czyli pożyczona kwota.fv(opcjonalny): kwota, która ma zostać na końcu. 0 dla kredytu spłaconego w całości, co jest wartością domyślną; cel oszczędnościowy dla planu oszczędzania.type(opcjonalny): 0 (domyślnie) dla płatności na końcu każdego okresu, jak w większości kredytów; 1 dla płatności na początku okresu, jak przy czynszu albo leasingu.
PMT zakłada stałą stopę i równe raty. Nie uwzględnia podatków, ubezpieczeń ani opłat, które bank dolicza do raty kredytu hipotecznego.
Dlaczego PMT zwraca liczbę ujemną
Funkcje finansowe Excela (PMT, PV, FV, NPV, IRR) używają znaku liczby do oznaczenia kierunku pieniędzy. Pieniądze, które otrzymujesz, są dodatnie, a te, które wypłacasz, ujemne. Kredyt to pieniądze, które otrzymujesz, więc =PMT(B2/12;B3*12;B1) zwraca -1580,17: płatność wychodzącą.
Dlatego formuła na tej stronie ma -B1: kwota kredytu wchodzi jako ujemna, a rata wychodzi dodatnia. =-PMT(B2/12;B3*12;B1) robi to samo. Wybierz jeden sposób i stosuj go wszędzie, bo ta sama zasada znaku decyduje o fv: cel oszczędnościowy, który otrzymasz, wpisuje się jako ujemną wartość przyszłą z wartością obecną 0.
Porównanie okresów kredytowania
Wpisz okresy w kolumnę, ustaw kwotę i stopę jako bezwzględne i skopiuj formułę w dół, aby porównać raty i łączny koszt obok siebie.
| A | B | C | |
|---|---|---|---|
| 1 | Amount | $28,000 | |
| 2 | Rate | 7.9% | |
| 3 | Years | Monthly | Total interest |
| 4 | 3 | $876.13 | $3,540.58 |
| 5 | 4 | $682.25 | $4,747.92 |
| 6 | 5 | $566.40 | $5,984.00 |
| 7 | 6 | $489.56 | $7,248.66 |
=PMT($B$2/12;A4*12;-$B$1)Wydłużenie kredytu z 3 do 6 lat obniża ratę z 876,13 do 489,56, a odsetki rosną z 3540,58 do 7248,66. Więcej o znakach $ znajdziesz na stronie odwołania bezwzględne.
Ile oszczędzać co miesiąc
Gdy pv wynosi 0, a podana jest wartość przyszła, PMT odpowiada na odwrotne pytanie: ile odkładać co miesiąc, aby osiągnąć cel. Cel to pieniądze, które otrzymasz, więc wpisuje się go jako liczbę ujemną, żeby wpłata była dodatnia.
| A | B | |
|---|---|---|
| 1 | Goal | $20,000 |
| 2 | Annual rate | 4% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly deposit | $301.66 |
=PMT(B2/12;B3*12;0;-B1)Wpłacanie 301,66 miesięcznie przez 60 miesięcy przy 4% daje 20 000; wpłaty sumują się do około 18 100, a resztę dokładają odsetki. Zmień B2 na 0%, a wpłata wyniesie dokładnie 20 000 podzielone przez 60.
Odsetki i kapitał: IPMT i PPMT
Każda rata spłaca część odsetek i część kredytu. IPMT zwraca część odsetkową jednej raty, a PPMT część kapitałową; razem dają PMT. Obie przyjmują numer raty jako drugi argument, co po skopiowaniu w dół daje harmonogram spłat.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Payment | Interest | Principal | Balance |
| 2 | 1 | $1,580.17 | $1,354.17 | $226.00 | $249,774.00 |
| 3 | 2 | $1,580.17 | $1,352.94 | $227.23 | $249,546.77 |
| 4 | 3 | $1,580.17 | $1,351.71 | $228.46 | $249,318.31 |
| 5 | 4 | $1,580.17 | $1,350.47 | $229.70 | $249,088.61 |
| 6 | 5 | $1,580.17 | $1,349.23 | $230.94 | $248,857.67 |
=IPMT(6,5%/12;A2;360;-250000)W 1. miesiącu 1354,17 z raty 1580,17 to odsetki, a tylko 226,00 zmniejsza saldo. Część kapitałowa rośnie trochę z każdym miesiącem. W prawdziwym skoroszycie wpisz stopę, okres i kwotę w komórki i odwołuj się do nich ze znakami $, jak we wcześniejszych tabelach.
Spróbuj: rata kredytu samochodowego
| A | B | |
|---|---|---|
| 1 | Loan | $32,000 |
| 2 | Annual rate | 6.9% |
| 3 | Years | 5 |
| 4 | ||
| 5 | Monthly payment |
Twoja kolej: W B5 oblicz miesięczną ratę kredytu z B1 przy rocznej stopie z B2 na liczbę lat z B3. Pokaż ją jako liczbę dodatnią.
Wskazówka: i stopa, i liczba rat muszą być miesięczne.
Częsty błąd: stopa roczna przy ratach miesięcznych
Stopa i nper muszą dotyczyć tego samego okresu. Zostawienie stopy rocznej przy liczeniu miesięcy nalicza 6,5% co miesiąc, a rata wychodzi mniej więcej dziesięć razy za wysoka:
| A | B | |
|---|---|---|
| 1 | Loan amount | $250,000 |
| 2 | Annual rate | 6.5% |
| 3 | Years | 30 |
| 4 | ||
| 5 | Wrong | $16,250.00 |
| 6 | Right | $1,580.17 |
=PMT(B2;B3*12;-B1)Błędna formuła żąda 16 250,00 miesięcznie, czyli samych odsetek przy 6,5% na miesiąc. Druga wersja tego samego błędu to wpisanie stopy jako 6,5 zamiast 6,5% albo 0,065: to oznacza 650% rocznie. Jeśli rata wygląda absurdalnie, najpierw sprawdź jednostki stopy. Dla rat rocznych użyj stopy rocznej i liczby lat bez zmian.
Najczęściej zadawane pytania
Jak obliczyć miesięczną ratę kredytu w Excelu?
Użyj =PMT(stopa/12; lata*12; -kwota), na przykład =PMT(6,5%/12;30*12;-250000) dla kredytu na 250 000 na 30 lat przy 6,5%. Zwraca około 1580,17 miesięcznie.
Dlaczego PMT w Excelu zwraca liczbę ujemną?
Funkcje finansowe Excela używają znaku do oznaczenia kierunku: pieniądze, które otrzymujesz, są dodatnie, a te, które płacisz, ujemne. Kredyt trafia do ciebie, więc wychodząca rata jest ujemna. Postaw minus przed kwotą kredytu (-B1) albo przed PMT, aby pokazać ją jako liczbę dodatnią.
Jak obliczyć łączne odsetki od kredytu w Excelu?
Pomnóż ratę przez liczbę rat i odejmij kwotę kredytu: =PMT(B2/12;B3*12;-B1)*B3*12-B1. Aby dostać odsetki zapłacone w pierwszym roku, użyj =-SPŁAC.ODS(B2/12;B3*12;B1;1;12;0).
Czym różnią się PMT, IPMT i PPMT?
PMT to cała rata. IPMT to część odsetkowa jednej raty, a PPMT część kapitałowa, i razem dają PMT. Wczesne raty to głównie odsetki.
Jak obliczyć, ile oszczędzać co miesiąc w Excelu?
Podaj PMT wartość przyszłą zamiast obecnej: =PMT(4%/12;5*12;0;-20000) to miesięczna wpłata, która w 5 lat przy 4% rocznie urośnie do 20 000.
