W tym poście dowiemy się, jak stworzyć harmonogram spłat kredytu w Excelu. Będziemy korzystać z trzech różnych funkcji finansowych. W poprzednim poście dowiedzieliśmy się już, jak obliczyć wysokość raty przy użyciu funkcji PMT. Dziś nauczymy się jeszcze funkcji PPMT i IPMT.
Funkcja PPMT służy do obliczania części kapitałowej w racie dla określonego okresu, przy założeniu stałej wysokości raty i stałego oprocentowania. Funkcja PPMT wygląda następująco:
=PPMT(stopa;okres;liczba_rat,wa;[wp];[typ])
Poszczególne elementy można wyjaśnić jako:
- stopa to wysokość oprocentowania kredytu;
- okres to numer kolejnej raty;
- liczba_rat to ilość rat kredytu;
- wa to wartość bieżąca czyli wysokość kredytu (piszemy z minusem);
- [wp] to wartość przyszła czyli wartość, którą chcemy otrzymać po dokonaniu ostatniej płatności;
- [typ] to rodzaj płatności - 1 oznacza płatności na początku okresu (tzw. płatności z góry), a 0 lub pominięcie oznacza płatności na koniec okresu (tzw. płatności z dołu).
Funkcja IPMT natomiast pozwoli nam obliczyć część odsetkową raty kredytu przy założeniu stałej wysokości raty i stałego oprocentowania. Składnia funkcji IPMT wygląda w ten oto sposób:
=IPMT(stopa;okres;liczba_rat,wa;[wp];[typ])
Poszczególne elementy również można wyjaśnić jako:
- stopa to wysokość oprocentowania kredytu;
- okres to numer kolejnej raty;
- liczba_rat to ilość rat kredytu;
- wa to wartość bieżąca czyli wysokość kredytu (piszemy z minusem);
- [wp] to wartość przyszła czyli wartość, którą chcemy otrzymać po dokonaniu ostatniej płatności;
- [typ] to rodzaj płatności - 1 oznacza płatności na początku okresu (tzw. płatności z góry), a 0 lub pominięcie oznacza płatności na koniec okresu (tzw. płatności z dołu).
Niech będzie to 10 000 zł kredytu oprocentowanego na 6 % w skali roku udzielony na 12 rat. Zakładamy, że oprocentowanie i rata są stałe. Przygotowaliśmy sobie już podstawowe informacje w tabelce w Excelu.
Zacznijmy od końca. Najpierw obliczmy wysokość raty kredytu. Jak wiemy, do obliczania wysokości raty kredytu używamy funkcję PMT. W naszym przypadku funkcja PMT będzie miała postać:
=PMT($C$3/12;$D$3;-$A$3;0;0)
Opisowo wygląda to tak:
- $C$3/12 to wysokość oprocentowania podzielona na 12, używamy tu adresu bezwzględnego, ponieważ funkcja odnosi się cały czas do jednej i tej samej komórki;
- $D$3 to u nas ilość rat, znów używamy adresu bezwzględnego;
- -$A$3 to kwota kredytu z minusem, ponieważ tyle jesteśmy winni bankowi, kolejny raz używamy adresu bezwzględnego;
- 0 to wartość przyszła, co oznacza, że spłacamy kredyt do zera;
- 0 to typ spłaty, wybraliśmy spłatę od dołu czyli na koniec każdego kolejnego okresu spłaty.
Wysokość raty, którą uzyskaliśmy to 860,66 zł.
Teraz przejdźmy do raty kapitałowej. Do wyliczenia części kapitałowej w racie kredytu służy funkcja PPMT. W naszym przypadku funkcja PPMT dla pierwszej raty będzie wyglądać następująco:
=PPMT($C$3/12;$A5;$D$3;-$A$3;0;0)
A teraz opisowo:
- $C$3/12 to znów wysokość oprocentowania podzielona na 12, używamy tu adresu bezwzględnego, ponieważ funkcja odnosi się cały czas do jednej i tej samej komórki;
- $A5 to numer kolejnej raty z kolumny A, znak dolara tylko przy A, blokujemy tylko kolumnę, ponieważ numer raty będzie się zmieniał;
- $D$3 to u ilość rat, ponownie używamy adresu bezwzględnego;
- -$A$3 to kwota kredytu z minusem, ponieważ tyle jesteśmy winni bankowi, kolejny raz używamy adresu bezwzględnego;
- 0 to wartość przyszła, co oznacza, że spłacamy kredyt do zera;
- 0 to typ spłaty, wybraliśmy spłatę od dołu czyli na koniec każdego kolejnego okresu spłaty.
Excel wyliczył nam, że w pierwszej racie spłacamy 810,66 zł kapitału.
Przechodzimy na końcu do części odsetkowej w racie. Ratę odsetkową obliczamy za pomocą funkcji IPMT. Dla pierwszej raty funkcja będzie wyglądać w ten sposób:
=IPMT($C$3/12;$A5;$D$3;-$A$3;0;0)
Funkcja IPMT wygląda tu analogicznie jak PPMT. Excel wyliczył nam, że w pierwszej racie spłacamy równo 50 zł odsetek.
Możemy jeszcze podsumować. Potrzebna będzie poznana już nam funkcja SUMA. Dowiemy się, że spłacamy równo 327,97 zł odsetek.
Harmonogram spłat kredytu jest gotowy.
Dla ułatwienia pokażę jeszcze dokładnie formuły, które zostały użyte przy tworzeniu harmonogramu.
Uff. NA dzisiaj tyle. Wpis jest długi, ale efekt zadowalający. Stworzyliśmy gotowy harmonogram spłat kredytu dzięki zaledwie kilku funkcjom Excela. Możliwości Excela są oczywiście dużo większe. Będę je pokazywał w kolejnych wpisach bloga.
Jak zwykle zachęcam też każdego do subskrypcji bloga Abc Excel. Dzięki subskrypcji nie ominiesz żadnego wpisu.
=IPMT($C$3/12;$A5;$D$3;-$A$3;0;0)
Funkcja IPMT wygląda tu analogicznie jak PPMT. Excel wyliczył nam, że w pierwszej racie spłacamy równo 50 zł odsetek.
Możemy jeszcze podsumować. Potrzebna będzie poznana już nam funkcja SUMA. Dowiemy się, że spłacamy równo 327,97 zł odsetek.
Harmonogram spłat kredytu jest gotowy.
Dla ułatwienia pokażę jeszcze dokładnie formuły, które zostały użyte przy tworzeniu harmonogramu.
Uff. NA dzisiaj tyle. Wpis jest długi, ale efekt zadowalający. Stworzyliśmy gotowy harmonogram spłat kredytu dzięki zaledwie kilku funkcjom Excela. Możliwości Excela są oczywiście dużo większe. Będę je pokazywał w kolejnych wpisach bloga.
Jak zwykle zachęcam też każdego do subskrypcji bloga Abc Excel. Dzięki subskrypcji nie ominiesz żadnego wpisu.
25 komentarze:
Hej.
Może pomożesz, bo nigdzie nie mogę tego znaleźć.
Czy w excelu na wykresie da się zrobić skrócenie jednego słupka z danymi.
Np. mamy wyniki rzędu 1-10 na większości wartości, a jedna wartość ma wielkość 10k i powoduje że nie da się zobaczyć wielkości tych innych wartości. W takich sytuacjach można się spotkać z ścięciem tej kolumny z wynikami jak i skali, tylko czy da się to zrobić w excelu?
Hej Marek. W Excelu wszystko się da. W następnym poście opublikuję odpowiedź na Twoje pytanie.
Hej
Czy jest możliwość zrobienia w Harmonogramie spłaty kredytu tak aby wpisać 24 raty ( w przykładzie pokazane 12) i excel sam uzupełni pozostałe komórki?
Hej,
Jeśli zależy Ci na całym kompletnym harmonogramie składającym się z 24 rat, to niestety należy ręcznie uzupełnić wszystkie komórki. Ewentualnie można przeciągnąć tyle, ile się da. Nie znam sposobu na w pełni automatyczny sposób uzupełnienia całego harmonogramu formułami.
Cześć, jest sposób na automatycznie wypełniający się harmonogram, w zalezności od liczby rat. Podeślę Ci go na maila popołudniu.
Pozdrawiam
Wyszła mi inna wysokość raty niż tu podana 860,66 zł. Mi wyszło ponad 1300
-_-
Dany temat bardzo mi pomogł dziekuje.
Moze opiszesz sposob obliczenia rat kredytu przy oprocentowaniu zmiennym ? bardzo mi by sie przydał
A takze sposob obliczania rat kredytu jezeli ratf kapitalowa stała a rata odsetkowa zmienna?
Byłabym bardzo wdzięczna
Jeszcze bardzo bym prosila opis funkcji i interpretacje IRR, NPV
pozdrawiam Julia
do Marka.....
http://www.goldenline.pl/forum/623166/wykres-pomocy/s/1#12009836
to jest ciężki temat imasz 2 opcje albo skala logarytmiczna (http://4.bp.blogspot.com/_EkPbJe24NME/TDdZhY_ti7I/AAAAAAAAA5E/5qm8PLSolK8/s1600/wykres+kolumnowy+z+zastosowaniem+skali+logarytmicznej.JPG) albo BrokenYAxis (http://www.ozgrid.com/Excel/chartpics/brokencol.gif)
Witam, potrzebuje obliczenia rat kredytu w Excelu, ale sama nie moge sie polapac:( nie wiem tak naprawde od czego zacza. Czy moglbys mi przeslac na maila zalacznik z tymi obliczeniami w Excelu? Bylabym bardzooo wdzieczna!
adres piorun2000@yahoo.pl
Pozdrawiam
nie moge obliczyc nie wiem czemu chociaz formuły napisalam takie same
nieprawidlowy typ danych wartosci uzytej w formule.moze ktos mi powiedziec co robię zle?
@ih6res
Już tłumaczę :) Taka konstrukcja to tzw. rata annuitetowa. Ten mechanizm polega na tym, że wysokość odsetek spada wraz z upływem czasu. Tak więc w początkowych ratach przeważają odsetki, a kapitału jest bardzo mało. Takie rozwiązanie jest korzystne dla banku, a niekorzystne dla klienta, ale tak skonstruowana jest spłata wszystkich kredytów. Wyjątkiem są kredyty w ratą malejącą stosowana głównie w kredytach hipotecznych.
Witam, mam pytanie jeżeli w trakcie spłacania kredytu będę chciała nadpłacić kapitału, czyli zamiast zapłacić ratę 800 zł wpłacić 2000 zł, w jaki sposób wprowadzić to do arkusza harmonogramu aby na nowo przeliczył raty oraz koszty kredytu
witam,
przychylam się do prośby poprzednika, czy ktoś może napisać jak zrobić taki harmonogram uwzględniajacy nadpłaty kapitału ??
Z góry dziękuję za pomoc :)
pomóżcie
Potrzebuje pomocy mam do zrobienia taka funkcje w exelu i nie mam pojęcia jak się do tego zabrać y=ax^3+6x^2+cx+d x<-10,10> z góry dziekuje
Czy można zrobić taką tabelkę z ratami, jeśli po kilku miesiącach oprocentowanie się zmienia na wyższe a raty mają wynosić tyle samo? Nie bardzo wiem jak to zrobić..
udalo mi się w calc open office. trochę po swojemu zrobiłem ale ten wpis mi pomógł :D
robie to po to, żeby teraz dodać opcję "nadpłaty" miesięcznej i jak to wszystko będzie się zmieniać żebym mógł kontrolować opłacalność i redukcje kosztów kredytowania :P
zaproponowali mi kredyt na 7 lat :O
kombinuje ale nie wiem czy dobrze...
chce dodać opcję, że np dopłacam co miesiąc dodatkowo 200zl aby przyspieszyć spłatę kredytu
powinienem przy każdym miesiącu dodatkowo odejmować od pozostałości do spłacenia, czy odejmować tą kwotę od początkowej (przyznanej) kwoty kredytu?
Jeśli chcemy obliczyć na 24 raty to w tabeli ilości rat wstawiamy 24 a nie 12 i resztę robimy według przykładu. Tylko tu zmieniają się dane.
aha -,-
potrzebuję , pilnie .
jeżeli zmieni się ilość rat kredytu , to powstaną kwoty ujemne , chodzi mi jak się ich pozbyć raczej za pomocą funkcji ' jeżeli ' . a nie ręcznie . jakoś automatycznie .
Hej, a czy mogę jakoś zrobić ten harmonogram jeśli chcę uwzględnić WIBOR 3m ??? Bo tym sposobem który opisujecie jest tylko mozliowosc obliczenie jesli sie zna stopę stałą oprocentowania, a co ze zmienną ?
Prześlij komentarz
Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.