niedziela, 20 czerwca 2010

Jak stworzyć harmonogram spłat kredytu w Excelu?

Witajcie! Dziś wpis dla bardziej zaawansowanych użytkowników Excela. Mimo wszystko myślę, że początkujący też dadzą sobie radę. Musicie tylko widzieć, co to są względne i bezwzględne adresy komórek i jak działa funkcja PMT.

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])

funkcja PPMT

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])


funkcja IPMT

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).
Tyle teorii. Przejdźmy teraz do praktyki. Zobaczmy, jak stworzyć harmonogram spłat kredytu.


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.

tworzenie harmonogramu spłat kredytu

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.

jak stworzyć harmonogram spłat kredytu w Excelu


Dla ułatwienia pokażę jeszcze dokładnie formuły, które zostały użyte przy tworzeniu harmonogramu.

harmonogram kredytu w Excelu

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:

Marek pisze...

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?

Ynwestor pisze...

Hej Marek. W Excelu wszystko się da. W następnym poście opublikuję odpowiedź na Twoje pytanie.

Anonimowy pisze...

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?

Ynwestor pisze...

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.

Unknown pisze...

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

Anonimowy pisze...

Wyszła mi inna wysokość raty niż tu podana 860,66 zł. Mi wyszło ponad 1300

Anonimowy pisze...

-_-

Anonimowy pisze...

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

Anonimowy pisze...

Jeszcze bardzo bym prosila opis funkcji i interpretacje IRR, NPV
pozdrawiam Julia

reptar pisze...

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)

Anonimowy pisze...

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

Anonimowy pisze...

nie moge obliczyc nie wiem czemu chociaz formuły napisalam takie same

Anonimowy pisze...

nieprawidlowy typ danych wartosci uzytej w formule.moze ktos mi powiedziec co robię zle?

Ynwestor pisze...

@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.

Anonimowy pisze...

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

Anonimowy pisze...

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 :)

Anonimowy pisze...

pomóżcie

Anonimowy pisze...

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

Aneta pisze...

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ć..

Anonimowy pisze...

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

Anonimowy pisze...

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?

Anonimowy pisze...

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.

Anonimowy pisze...

aha -,-

Anonimowy pisze...

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 .

Unknown pisze...

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.