Kalkulator Kredytowy w Excelu

Mało kto z osób dorosłych nie miał w życiu jakiegoś kredytu. Albo był to kredyt hipoteczny, chwilowe zadłużenie na karcie lub też spłata telewizora na raty. Założę się jednak, że tylko nieliczni z Was zastanawiali się dlaczego właściwie muszę spłacać taką a nie inną kwotę. I właśnie ten temat spróbujemy dzisiaj rozgryźć i wspólnie przygotujemy taki prosty kalkulator w Excelu, który będzie za nas liczył każdą cześć składową kredytu.

Chcąc obliczyć ratę kredytu potrzebne nam są następujące parametry:

– Kwota kredytu,

– Długość trwania kredytu, najczęściej wyrażona w latach,

– oprocentowanie,

– rodzaj rat.

Kwotę kredytu sami określamy w zależności od tego ile potrzebujemy środków oraz naszych możliwość późniejszej spłaty ( to jednak weryfikuje też bank czy się przypadkiem nie przeliczyliśmy w ocenie naszych możliwości).

Wysokość oprocentowania jest już od nas niezależna. Zależy natomiast od aktualnego rynkowego oprocentowania, czyli tzw. stopy WIBOR dla kredytów w PLN oraz marży banku. Wpływ możemy mieć więc jedynie na marże bankową, a ta może się znacząco różnić w zależności od miejsca w którym kredyt zaciągamy.

Długości trwania kredytu zależy tak od nas jak i od banku. W przypadku kredytów hipotecznych okres jest z reguły dłuższy, a w przypadku pożyczek lub kredytów konsumpcyjnych najczęściej krótszy.

I ostatni najtrudniejszy element to wybór pomiędzy ratami stałymi, a ratami malejącymi. Ten element omówimy sobie dalej i zatrzymamy się na nim dłużej, gdyż wbrew pozorom jest to jedna z kluczowych decyzji jaką musimy podjąć.

W tym artykule nie będę dogłębnie wnikał w budowę każdego składnika kredytu. Nie będę też omawiał technik negocjacyjnych brania kredytów oraz ogólnego sensu zadłużania się. To jest temat na przynajmniej kolejne 10 artykułów.

To co natomiast zrobię to pokażę skąd się poszczególne liczby biorą oraz jak taki kalkulator kredytowy w skonstruować w Excelu. Kalkulator możecie pobrać z poniższego linku. Jest on  w wersji otwartej, abyście dalej mogli go poprawiać już według własnych potrzeb jeżeli coś będziecie chcieli zmienić. Wybaczcie brak pięknej szaty graficznej. Skupiłem się na merytoryce. Kalkulator ten możecie pobrać bezpośrednio tutaj:

POBIERZ KALKULATOR KREDYTOWY W EXCELU

W kolejnym linku zamieszczam też eksperymentalnie kalkulator w excelowskiej wersji online. Piszę, że jest to wersja eksperymentalna, ponieważ udostępniając otwartą wersję online każdy może wejść i ją po prostu skasować. Zobaczymy czy tak też się stanie. Jeżeli nie będzie działać to znaczyć będzie, że już się to stało i po prostu ściągnijcie go w klasycznej formie.

KALKUTOR W EXCELU WERSJA ONLINE.

Treść tego artkułu jest jedynie uzupełnieniem filmu, który zamieściłem na początku artykułu, nie mniej jednak przeczytanie w pierwszej kolejności artykułu na pewno nie zaszkodzi Wam w lepszym zrozumieniu wideo.

Budowa kredytu

Kredyt składa się zawsze z dwóch części. Raty kapitałowej oraz raty odsetkowej.  Rata kapitałowa jest to ta część kredytu o którą zmniejsza się nasze zadłużenie w stosunku do banku po każdej racie. Jeżeli na przykład nasze zadłużenie wyniosło 500 000 PLN i w pierwszym miesiącu zapłaciliśmy bankowi 5 000 PLN jako część kapitałową to nasze zadłużenie spadnie do 495 000 PLN. Nie oznacza to, że jest to całość, jaką musimy w danym miesiącu zapłacić bankowi. Jest to jedynie zwrot kredytu. Pozostała część to odsetki, czyli przychód banku.

Rata odsetkowa jest to opłata za to, że bank udzielił nam kredytu i uzależniona jest od oprocentowania naszego kredytu. Im większa kwota kredytu tym większa rata odsetkowa. Rata odsetkowa nie  zależy od długości trwania kredytu ani od rodzaju rat,  a jedynie od kwoty, która pozostała nam do spłaty oraz wcześniej wspomnianego oprocentowania. Jeżeli więc nasza kwota do spłaty w danym miesiącu wynosi 500 000 PLN, a oprocentowanie 5% to rata w tym miesiącu wyniesie 500 000 x 5% / 12 = 2 083,33 PLN.

Rata kapitałowa z kolei zależy już od długości trwania kredytu, kwoty kredytu a nawet i od oprocentowania w przypadku rat stałych. Za chwilę do tego dojdziemy.

Raty malejące

Jeżeli zdecydowaliśmy się na raty malejące oznacza to, że co miesiąc spłacamy dokładnie taką samą kwotę kredytu. Czyli część kapitałowa jest zawsze równa. Po każdej spłaconej racie pozostał kwota do spłaty jest coraz mniejsza, a co za tym idzie część odsetkowa maleje. Tak więc jeżeli jedna składowa jest zawsze równa, a druga maleje z miesiąca na miesiąc, to łączna rata kredytu siłą rzeczy musi także maleć.

Przykład.

Mamy 500 000 PLN kredytu na 10 lat i oprocentowaniem 5%. Jak będzie wyglądać nasza rata w pierwszym, w drugim i w ostatnim miesiącu?

Kwota kapitałowa zawsze będzie stała i wynosić będzie 500 000 podzielone na 10 lat, podzielone na 12 miesięcy, czyli 500 000 / 10 / 12. Wychodzi 4 166,67 PLN. W pierwszym miesiącu odsetki z liczymy od pełnej kwoty kredytu ponieważ nic jeszcze nie spłaciliśmy czyli będzie to 500 000 PLN pomnożone przez oprocentowanie roczne 5%  i podzielone przez ilość miesięcy czyli 12 co daje 2 083,33 PLN. Razem rata kapitałowa i odsetkowa wynosić będzie 6 250 PLN.

W drugim miesiącu część kapitałowa będzie dokładnie taka sama, natomiast już część odsetkowa zostanie liczona od kwoty, która pozostała do spłaty czyli od 500 000 PLN minus 4 166,67 PLN czyli od 495 833,33 PLN. Odsetki będą więc mniejsze i wyniosą 2 065,97 PLN, a łączna rata 6 232,64 PLN.

Dlatego więc ten sposób spłat rat nazywa się ratami malejącymi. Dzięki utrzymaniu raty kapitałowej na stałym poziomie rata łączna co miesiąc jest coraz mniejsza.

Nr raty Rata Cześć kapitałowa Część Odsetkowa Kwota do spłaty
1 6 250,00 zł 4 166,67 zł 2 083,33 zł 495 833,33 zł
2 6 232,64 zł 4 166,67 zł 2 065,97 zł 491 666,67 zł

W ostatnim miesiącu spłaty czyli w 120 racie odsetki będą już bardzo malutki ponieważ kredytobiorca zapłaci je jedynie od pozostałej do spłaty kwoty 4 166,67 PLN.

119 4 201,39 zł 4 166,67 zł 34,72 zł 4 166,67 zł
120 4 184,03 zł 4 166,67 zł 17,36 zł 0,00 zł

Raty stałe

Tutaj już gimnastyka jest trochę większa. Aby policzyć raty stałe należy skorzystać ze specjalnego wzoru na raty stałe, który wygląda w następujący sposób:

, gdzie :

S – Kwota kredytu do spłaty

q – 1 + r/12

r – roczna stopa oprocentowania

n – łączna ilość rat podczas całego kredytu

Tak więc w ratach stałych najpierw liczymy łączną ratę, a dopiero potem rozbijamy ją na część odsetkową i kapitałową.

Przykład

Mamy kredyt o wartości 500 000 PLN na 10 lat i oprocentowany na 5%. Jaka będzie rata kredytu, część odsetkowa i część kapitałowa w pierwszych dwóch i ostatniej racie?

Nr raty Rata Cześć kapitałowa Część Odsetkowa Kwota do spłaty
1 5 303,28 zł 3 219,94 zł 2 083,33 zł 496 780,06 zł
2 5 303,28 zł 3 233,36 zł 2 069,92 zł 493 546,70 zł

………

119 5 303,28 zł 5 259,36 zł 43,92 zł 5 281,27 zł
120 5 303,28 zł 5 281,27 zł 22,01 zł 0,00 zł

1 miesiąc. –  Liczymy najpierw łączną ratę, która zgodnie ze wzorem wyniosła 5 303,28 PLN. Możemy więc już ją przekopiować i do pozostałych 119 rat. Następnie liczymy odsetki od kwoty 500 000 PLN, które podobnie jak przy ratach malejących wyniosą 2 083,33 PLN.  Pozostaje więc od kwoty 5 303,28 PLN odjąć 2 083,33 PLN aby uzyskać 3 219,94 PLN czyli ratę kapitałową.

2 miesiąc – Łączna rata się nie zmienia i wyniesie 5 303,28 PLN. Odsetki jednak liczymy już od pozostałej kwoty do spłaty czyli 496 780,06 PLN ( 500 000 – 3 219,94). Siłą rzeczy muszą więc wyjść mniejsze niż miesiąc wcześniej. Dokładnie wyniosą 2 069,92 PLN. Skoro odsetki  wyszły mniejsze a łączna rata ma być bez zmian to oznacza, że rata kapitałowa musi wzrosnąć i tak też się dzieje. Rośnie do 3 233,36.

120 miesiąc – Łączna rata się nie zmienia i wynosi 5 303,28 PLN. Po drodze udało nam się spłacić większość kredytu i do spłaty zostało jedynie 5 281,27. Odsetki w skali miesięcznej są więc już bardzo małe i wyniosą jedynie 22,01 PLN. Rośnie z kolei rata kapitałowa i wynosi 5 281,27 PLN.

Raty malejące vs raty stałe

Podstawową zaletą rat malejących jest to, że dużo szybciej spłacamy kredyt niż w przypadku rat stałych, a co za tym idzie tym mniejsze płacimy odsetki i nasza łączna suma spłat też jest mniejsza. Im dłuższy kredyt tym większa jest dana różnica.

Przyjrzyjmy się jak to wygląda w przypadku kredytu na 500 000 PLN na 30 lat z oprocentowaniem 5%.

  Raty malejące Raty stałe
1 rata 3 472,22 zł 2 684,11 zł
180 – środkowa rata 2 436,34 zł 2 684,11 zł
Kwota do spłaty po 15 latach (180 ratach) 250 000,00 zł 339 419,54 zł
360 – ostatnia rata 1 394,68 zł 2 684,11 zł
Łączna suma spłat 876 041,67 zł 966 278,92 zł

Jeżeli więc zdecydujemy się na raty malejące na początku musimy bardziej zacisnąć pasa i płacimy aż o prawie 800 PLN więcej. Jednak już w połowie kredytu po 15 latach rata malejąca robi się mniejsza o ponad 200 PLN. Dodatkowo w przypadku rat malejących spłaciliśmy dokładnie połowę kredytu podczas gdy w ratach stałych mniej niż 30%.

Jeżeli więc zdecydowalibyśmy się nadpłacić kredyt i zakończyć go wcześniej to w przypadku rat malejących będzie to dla nas dużo łatwiejsze, gdyż mniejsza kwota nam zostanie do spłaty.

I to co robi chyba największe wrażenie przy podanych warunkach kredytu to fakt, że raty malejące oznaczają łącznie o 90 000 PLN mniejszą sumę, którą wpłaciliśmy do banku na przestrzeni całych 30 lat.

Kalkulator kredytowy w Excelu

Abyście najlepiej zrozumieli jak skonstruować kalkulator w Excelu zachęcam Was do obejrzenia filmu. Powyższe wprowadzenie było potrzebne, jednak to właśnie patrząc na wykonywane przez mnie operacje, dużo łatwiej będzie Wam zrozumieć poszczególne elementy i kroki. Tak wiec proponuje skończyć czytać właśnie w tym miejscu i przenieść się do wideo.

Przyznam się, że podjąłem próbę opisania każdego z kroków w tekście, jednak wychodzi z tego mało zrozumiały artykuł i chyba nie mam wystarczających umiejętności aby go napisać w sposób bardziej zrozumiały. Tak więc skasowałem wszystko i upewniłem się, że wideo jest właściwszą drogą aby wszystko wyjaśnić.

Jakbyście nie chcieli jednak oglądać wideo, to ściągnijcie arkusz i prześledźcie każdy krok w, a ewentualne pytania zadawajcie w komentarzach. Nie twierdzę też, że jest to najszybsza forma liczenia i pewnych kroków nie dałoby się zrobić łatwiej. Excel ma to do siebie, że do tych samych rozwiązań można dojść na wiele sposobów. Jeżeli więc jesteście te kroki w stanie uprościć to znaczy, że Wasz poziom zaawansowania jest wysoki, a ja nie jestem już Wam do niczego potrzebny. Nie mniej jednak cieszę się, że dotarliście do samego końca.😊

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *