Kalkulator kredytowy Excel - harmonogram rat kredytu

Kalkulator kredytowy w Excelu stoi na czterech funkcjach: PMT liczy całą ratę, PPMT jej część kapitałową, IPMT odsetkową, a NR.SER.DATY rozpisuje daty spłat. Poniżej robimy to krok po kroku: harmonogram rat na 30 lat, rozbicie każdej raty na kapitał i odsetki, paski danych pokazujące, jak te dwie części zamieniają się miejscami, i na końcu nadpłata. Plik z gotowym kalkulatorem jest do pobrania.

W dalszej części poradnika:

1. Jak stworzyć kalkulator kredytowy Excel?
2. Automatyczna ilość rat kalkulatora kredytowego Excel
3. Obliczenie raty kredytu w Excelu
4. Jak określić wartość raty kapitałowej w kalkulatorze kredytowym w Excelu?
5. Jak określić wartość raty odsetkowej w kalkulatorze kredytowym w Excelu?
6. Formatowanie warunkowe w kalkulatorze kredytowym w Excelu
7. Jak wyliczyć sumę rat kapitałowych i odsetkowych, czyli całkowitą wartość kredytu?
8. Dlaczego PMT zwraca ratę z minusem?
9. Jak policzyć nadpłatę kredytu w Excelu?
10. Najczęstsze błędy w kalkulatorze kredytowym
11. Wakacje kredytowe i zmienne oprocentowanie w kalkulatorze
12. Kalkulator kredytowy Excel do pobrania

Jak stworzyć kalkulator kredytowy Excel?

W tym poradniku utworzymy kalkulator kredytowy w Excelu przy założeniu stałej raty kredytu i stałej stopie procentowej. Aby stworzyć kalkulator kredytowy w Excelu potrzebne będą takie dane jak:

• kwota kredytu,
• data najbliższej spłaty raty kredytowej,
• data zakończenia kredytu,
• wysokość oprocentowania kredytu,
• ilość rat pozostałych do spłaty, którą wyliczymy na podstawie daty najbliższej spłaty kredytu oraz daty zakończenia kredytu.

Pierwszym krokiem podczas tworzenia kalkulatora kredytowego w Excelu jest wprowadzenie kwoty kredytu, daty najbliższej spłaty raty kredytu oraz daty zakończenia kredytu.

Następnie wyliczamy ilość rat pozostałych do spłaty kredytu poprzez zastosowanie funkcji DATA.RÓŻNICA, która oblicza liczbę dni, miesięcy lub lat między dwiema datami.

DATA.RÓŻNICA(data_początkowa ; data_końcowa; okres_obliczeń)

W tym celu w komórce B6 pod datą zakończenia kredytu wpisz funkcje =data.różnica, która przyjmuje trzy argumenty:

• data początkowa, czyli data najbliższej spłaty kredytu (B4)
• data końcowa, czyli data zakończenia kredytu (B5)
• okres_obliczeń - my chcemy obliczyć ilość miesięcy pomiędzy dwoma datami więc wprowadzamy "M"

Cała formuła funkcji powinna wyglądać następująco (1 na końcu pozwala uwzględnić w wyliczeniach również najbliższą ratę do spłaty):

Ilość rat pozostałych do spłaty w naszym przykładzie to 241, wprowadzamy również oprocentowanie w skali rocznej równe 8,54%

Teraz stworzymy harmonogram spłaty rat kredytu. Będziemy chcieli wykonać liczby porządkowe od 1 do 360 (max 30 lat) W komórce B14 wpisuję Lp a pod nią wprowadzam liczbę 1,

Następnie przejdź do karty Narzędzia główne > Wypełnij > Seria danych

Pojawi się okno Serie, w którym zaznacz Serie - Kolumny (1), Typ - Liniowy(2), a w polu wartość końcowa wpisz 360 (3).

Kliknij OK, a Excel utworzy automatycznie 360 pozycji.

Po wprowadzeniu liczby porządkowej, wstawmy konkretne daty wpłaty rat kredytowych

W tym celu obok 1 wpisz najbliższą datę spłaty raty kredytu, którą w przykładzie jest 28.08.2022

Uzupełnienie pozostałych dat spłaty rat kredytowych wykonaj znów za pomocą wypełniania serią danych, przechodząc do karty Narzędzia główne > Wypełnij > Seria danych.

Tym razem w okno Serie, zaznacz Serie - Kolumny (1), Typ - Liniowy (2), Jednostka daty - Miesiąc (3), a w polu wartość końcowa wpisz datę zakończenia kredytu, w przykładzie jest to 28.08.2042 (4) i kliknij OK.

Dzięki temu utworzyliśmy statyczne wartości liczby porządkowej od 1 do 360 i przypisaliśmy do nich dokładną datę spłaty. Poniżej pokazuję 12 z 360 pozycji

Automatyczna ilość rat kalkulatora kredytowego Excel

Teraz zajmiemy się tym aby ilość rat i daty pojawiały się automatycznie na podstawie ilości rat kredytu znajdującej się w komórce B6 (dla naszego przykładu ilość rat wynosi 241)

W tym celu skorzystamy z funkcji WIERSZ, która zwraca numer wiersza oraz funkcji JEŻELI (IF).

W komórce określającej ilość rat wpisz funkcję =WIERSZ(1:1) ta funkcja po prostu funkcja zwróci 1, po przeciągnięciu jej w dół będzie zmieniała się na WIERSZ(2:2) zwracając 2; WIERSZ(3:3) zwracając 3 itp.

Następnie zmodyfikujemy formułę poprzez dodanie funkcji JEŻELI, aby sprawdzić, czy numer wiersza jest mniejszy od naszej ilości rat 241 znajdującej się w komórce B6. Jeśli wiersz będzie mniejszy niż ilość rat, będziemy zwracać liczbę porządkową, w przeciwnym wypadku zwrócimy pustą komórkę „”. Kompletna formuła będzie wyglądać następująco:

Po wprowadzeniu formuły przeciągnij ją dla pozostałych wartości w kolumnie. Dzięki temu w kalkulatorze będzie się pojawiała wyłącznie rzeczywista liczba rat kredytu wraz z przypisanymi do nich datami spłaty. Pozostała numeracja rat kredytu będzie pusta.

Więcej o funkcji JEŻELI dowiesz się z artykułu: Funkcja JEŻELI Excel - JEŻELI z funkcjami ORAZ i LUB

W celu przedstawienia daty co miesiąc od jakiejś daty zastosujemy funkcję NR.SER.DATY. Funkcja składająca się z dwóch argumentów zwraca liczbę kolejną, odpowiadającą dacie przypadającej określoną liczbę miesięcy przed lub po wskazanej dacie:

NR.SER.DATY(data_początkowa ; miesiące)

• data_początkowa - argument wymagany. Data reprezentująca datę początkową.
• Miesiące - argument wymagany. Liczba miesięcy przed datą określoną argumentem data_początkowa lub po tej dacie. Dodatnia wartość argumentu „miesiące” oznacza datę przyszłą, ujemna oznacza datę przeszłą.

Wracając do przykładu w pierwszej komórce z datą wprowadzamy =NR.SER.DATY($B$4;B14-1) gdzie pierwszym argumentem jest najbliższa data spłaty, a w drugim argumencie wskazujemy ilość miesięcy późniejszych od wskazanej daty. Tak więc dla pierwszej pozycji będzie to 0 miesięcy, dla drugiej 1 miesiąc, dla trzeciej 2 miesiące i tak dalej. Dlatego od liczby porządkowej odejmujemy 1.

Teraz powstałą funkcję zmodyfikujemy dodając funkcję JEŻELI.BŁĄD. Funkcja składa się z dwóch argumentów. Pierwszy argument zawiera wartość a drugi argument wskazuje co ma się wyświetlać gdy w pierwszym argumencie będzie błąd.

Funkcja NR.SER.DATY będzie pierwszym argumentem funkcji JEŻELI.BŁĄD (IFERROR) i gdy ta funkcja zwróci błąd, wyświetlimy pustą komórkę „”.

Dzięki takiemu zabiegowi nie będzie się wyświetlał błąd dla kolejnych dat gdy nie będziemy mieli liczby kolejnej w kolumnie Lp.

Obliczenie raty kredytu w Excelu

Do obliczenia raty kredytu w Excelu wykorzystamy często stosowaną w środowisku finansowym funkcję PMT, która oblicza kwotę spłaty pożyczki przy założeniu stałych spłat i stałej stopy procentowej.

Funkcja PMT składa się trzech obowiązkowych argumentów:

PMT(stopa;liczba_okresów;wb)

• stopa - to stopa procentowa pożyczki.
• liczba_okresów - to całkowita liczba spłat w ramach pożyczki.
• wb - to wartość bieżąca, czyli całkowita kwota będąca wartością serii przyszłych płatności (nazywana także kapitałem).

W komórce obok pierwszej daty spłaty kredytu wpisz:

=PMT($B$7/12;$B$6;$B$3)

blokując poszczególne komórki, za pomocą klawisza funkcyjnego F4. Funkcję przekopiuj dla wszystkich rat.

Zwróć uwagę na to, aby stopa procentowa jak i liczba okresów była przedstawiona w takich samych okresach. W naszym przypadku raty kredytowe spłacane będą co miesiąc więc oprocentowanie (liczone w skali rocznej) również musimy przedstawić w formie miesięcznej, dlatego stopę procentową dzielimy przez 12 miesięcy.

Jeśli rata kredytu spłacana by była raz na kwartał stopę procentową dzielilibyśmy przez 3, a gdy rata spłacana by była raz na pół roku to dzielimy przez 6.  

UWAGA: Podobnie jak w przypadku liczby porządkowej oraz daty, aby raty nie pojawiały się w komórkach, niezaliczających się już do okresu spłaty kredytu musimy również zastosować funkcję JEŻELI. Pozwoli to uniknąć sytuacji jak poniżej:

W tym celu w pierwszej komórce z ratą, w formule przed PMT zastosuj funkcję JEŻELI, która powinna mieć postać:

Funkcja PMT automatycznie obliczy wartość raty, którą następnie przeciągnij w dół, aby pojawiła się obok pozostałych dat ich spłaty.

Jak określić wartość raty kapitałowej w kalkulatorze kredytowym w Excelu?

Raty kredytu składają się z dwóch elementów - raty kapitałowej oraz raty odsetkowej. W początkowym okresie spłaty kredytu spłacamy głównie część odsetkową, a tylko mała część raty kredytu jest faktycznie przeznaczana na spłatę zaciągniętej kwoty kredytu. Co przedstawię w dalszej części artykułu.

Aby wyliczyć wartość raty kapitałowej, zastosujemy funkcję PPMT, która zwraca spłaty kapitału w podanym okresie dla inwestycji w oparciu o stałe, okresowe płatności i stałą stopę procentową.

Funkcja PPMT składa się z czterech obowiązkowych argumentów:

PPMT(stopa; okres; liczba_rat; wb)

• stopa - to stopa procentowa dla okresu.
• okres - określa okres i musi znajdować się w zakresie od 1 do liczba_okresów.
• liczba_okresów - to całkowita liczba okresów płatności w okresie spłaty.
• wb - to obecna wartość, czyli całkowita suma bieżącej wartości szeregu przyszłych płatności.

Funkcja jest bardzo podobna do funkcji PMT z tą różnicą, że dodatkowo dochodzi argument okres, który mówi, w którym miesiącu spłaty obecnie jesteśmy. Im ten okres jest późniejszy, tym większą część kapitału spłacamy.

W komórce obok pierwszej raty wpisujemy funkcję:

=PPMT($B$7/12;B14;$B$6;$B$3)

Jak zapewne zauważyłeś wszystkie argumenty są takie same jak w funkcji PMT a argumentem okres jest liczba porządkowa (dla pierwszej raty to 1)

Następnie kliknij Enter, a Excel zwróci wartość raty kapitałowej, którą przeciągnij w dół, aby uzyskać jej wartość dla pozostałych okresów spłaty.

Po przeciągnięciu formuły w dół w komórkach, do których nie jest przypisana żadna data spłaty raty kredytu, pojawi się błąd #ARG!.

Aby się nie pojawiał, należy zastosować funkcję JEŻELI.BŁĄD. W tym celu w pierwszej komórce kolumny Rata wpisz formułę:

I przeciągnij ją w dół kolumny, a błąd #ARG! zostanie zlikwidowany.

Jak określić wartość raty odsetkowej w kalkulatorze kredytowym w Excelu?

Wysokość raty odsetkowej możemy natomiast wyliczyć na dwa sposoby.

Pierwszym z nich jest zastosowanie funkcji IPMT, która zwraca wysokość spłaty odsetek dla danego okresu dla kredytu opartego na regularnych, stałych spłatach i stałej stopie procentowej. W funkcji IPMT występują cztery obowiązkowe argumenty, takie same jak w przypadku funkcji PPMT.

IPMT(stopa;okres;liczba_okresów;wb).

• stopa - to stopa procentowa dla okresu.
• okres  - określa okres i musi znajdować się w zakresie od 1 do liczba_okresów.
• liczba_okresów - to całkowita liczba okresów płatności w okresie spłaty.
• wb - to obecna wartość, czyli całkowita suma bieżącej wartości szeregu przyszłych płatności.

Aby zastosować funkcję IPMT w komórce obok raty kapitałowej wpisujemy funkcję raty odsetkowej:

=IPMT($B$7/12;B14;$B$6;$B$3)

Kliknij Enter, a Excel zwróci wartość raty odsetkowej, której suma wraz z ratą kapitałową będzie równa wartości całej raty.

Drugim sposobem na wyliczenie wysokości raty odsetkowej jest natomiast odjęcie od całkowitej raty wartości raty kapitałowej. W tym celu w komórce wpisz formułę:

=D14-E14

Ten kalkulator stoi na czterech rzeczach z kursu podstawowego

✓Odwołania bezwzględne i klawisz F4 - bez tego formuła rozsypie się po przeciągnięciu

✓JEŻELI.BŁĄD - puste komórki zamiast błędu pod ostatnią ratą

✓Funkcje daty i paski danych formatowania warunkowego

✓40 lekcji i 52 zadań z rozwiązaniami, gwarancja zwrotu 30 dni

Zobacz kurs

Formatowanie warunkowe w kalkulatorze kredytowym w Excelu

W kredycie hipotecznym początkowe raty niemal w całości składają się z rat odsetkowych, wraz z liczbą spłaconych rat tendencja ta odwraca się na rzecz rat kapitałowych. Zobrazowanie tego ułatwia proste formatowanie komórek.

Aby uzyskać efekt taki jak poniżej,

zaznacz wszystkie komórki w kolumnie Spłata kapitału bez komórki nagłówka.

Przejdź do karty Narzędzia główne > Formatowanie warunkowe > Paski danych > Wypełnienie gradientowe

Następnie, aby nie były pokazywane minusowe wartości ponownie przejdź do Narzędzia główne > Formatowanie warunkowe > Zarządzanie regułami

Otworzy się okno Menadżer reguł formatowania warunkowego, w którym kliknij najpierw Pasek danych (1) a następnie Edytuj regułę (2)

W pojawiającym się oknie Edytowanie reguły formatowania wybierz Wartości ujemne i oś

Pojawi się kolejne okno - Ustawienia wartości ujemnych i osi, w którym zarówno w przypadku Koloru wypełnienia paska ujemnego, jak i Koloru obramowania słupka ujemnego zaznacz Zastosuj ten sam kolor obramowania jak w przypadku słupków dodatnich i naciśnij OK.

Powrócisz do okna Edytowanie reguły formatowania, w którym także naciśnij OK, a w oknie Menadżer reguł formatowania warunkowego, kliknij Zastosuj i OK. Takie same formatowanie zastosuj dla kolumny Spłata odsetek, dzięki temu uzyskasz efekt jak poniżej, w której ilość podświetlonego kolorem obszaru będzie zależna od wartości zawartej w komórce.

Jak wyliczyć sumę rat kapitałowych i odsetkowych, czyli całkowitą wartość kredytu?

Kalkulator kredytowy w Excelu pozwala nie tylko kontrolować to jak wygląda rozkład rat kapitałowych i odsetkowych w całkowitych ratach, ale także wyliczyć ich wartość całkowitą w całym okresie kredytowania. Aby określić całkowitą wartość rat kapitałowych i odsetkowych należy skorzystać o funkcji autosumowania.

Aby wyliczyć sumę spłaty kapitału kliknij w komórkę, w której ma znaleźć się wynik (w naszym przypadku podsumowanie utworzymy bezpośrednio nad kolumnami), a następnie przejdź do karty Narzędzia główne > Autosumowanie

W komórce pojawi się formuła =SUMA, dla której argumentem będą dane znajdujące się w kolumnie Spłata kapitału. Wprowadź je do formuły poprzez zaznaczenie tej kolumny.

Wartość spłaty kapitału w przykładzie wynosi 350 000,00 zł. Ta wartość musi równać się kwocie zaciągniętego kredytu.

Przeciągnięcie formuły na komórkę po lewo i po prawo, pozwoli uzyskać sumę spłaty odsetek oraz ogółem wszystkich rat.

Z tych danych wyliczymy jeszcze, jaka część całkowitego kosztu kredytu to kapitał, a jaka odsetki.

Dlaczego PMT zwraca ratę z minusem?

Pierwsza rzecz, która zwykle zaskakuje przy funkcji PMT: wynik wychodzi ujemny. Tak ma być. Excel liczy przepływy pieniężne, a rata jest wypływem z Twojej kieszeni, więc dostaje znak minus. Kwota kredytu wpływa do Ciebie, więc jest dodatnia.

Masz dwa sposoby, żeby w harmonogramie zobaczyć liczby dodatnie. Pierwszy to minus przed nazwą funkcji:

Drugi to wpisanie kwoty kredytu ze znakiem minus w komórce B3. Wtedy wszystkie trzy funkcje finansowe (PMT, PPMT i IPMT) same zwrócą wartości dodatnie i nie trzeba pamiętać o minusie w każdej formule osobno.

Jak policzyć nadpłatę kredytu w Excelu?

Tu jest pułapka, o której warto wiedzieć, zanim zaczniesz liczyć własne nadpłaty. Funkcje PMT, PPMT i IPMT liczą harmonogram teoretyczny: zakładają, że przez cały okres płacisz dokładnie tę samą ratę. Nadpłata łamie to założenie, a funkcje nic o niej nie wiedzą. Dopłacisz 20 000 zł, a PPMT w kolejnym wierszu dalej policzy kapitał tak, jakby nic się nie stało.

Harmonogram z nadpłatami trzeba więc liczyć na saldzie, a nie samymi funkcjami finansowymi. Potrzebne są dwie dodatkowe kolumny: Nadpłata i Kapitał pozostały.

1. Saldo startowe to kwota kredytu, czyli odwołanie do komórki B3.
2. Odsetki w każdym miesiącu liczysz od salda z poprzedniego wiersza: =H13*$B$7/12, gdzie H13 to kapitał pozostały po poprzedniej racie.
3. Część kapitałowa to rata minus odsetki tego miesiąca.
4. Nowe saldo to saldo poprzednie minus część kapitałowa minus nadpłata: =H13-F14-G14.

Przy takim układzie każda nadpłata od razu zmniejsza odsetki w następnym miesiącu, bo są liczone od mniejszego salda. Dlatego nadpłata zrobiona w trzecim roku kredytu oszczędza wielokrotnie więcej niż ta sama kwota wpłacona w roku dwudziestym: odsetki siedzą na początku harmonogramu.

Gotowe arkusze z nadpłatami i zmiennym oprocentowaniem są w plikach do pobrania na końcu tego poradnika. Jak działają, pokazuję w sekcji o wakacjach kredytowych i zmiennym oprocentowaniu.

Najczęstsze błędy w kalkulatorze kredytowym

Cztery pomyłki, które najczęściej psują harmonogram, a żadna z nich nie daje komunikatu o błędzie:

• Oprocentowanie roczne wpisane bez dzielenia przez 12. Rata wyjdzie absurdalnie wysoka, ale Excel policzy ją bez mrugnięcia. Stopa i liczba okresów muszą być w tej samej jednostce czasu.
• Brak blokady odwołań. Po przeciągnięciu formuły w dół odwołanie do kwoty kredytu zjeżdża na pustą komórkę, a rata zmienia się w każdym wierszu. Klawisz F4 stawia znaki dolara.
• Oprocentowanie wpisane jako 8,54 zamiast 8,54%. Excel przyjmie 854%, a rata urośnie sto razy. Komórka musi mieć format procentowy albo wartość 0,0854.
• Suma kapitału, która nie równa się kwocie kredytu. To najlepszy test poprawności całego arkusza. Jeśli suma kolumny Spłata kapitału różni się od kwoty kredytu, gdzieś jest błąd w adresach, nie w zaokrągleniach.

Ostatni punkt warto wpisać do arkusza jako kontrolę: =SUMA(E15:E374)-$B$3. Zero oznacza, że harmonogram się zgadza.

Wakacje kredytowe i zmienne oprocentowanie w kalkulatorze

Kalkulator z poprzednich sekcji zakłada dwie rzeczy: stałą ratę i stałe oprocentowanie przez cały okres kredytu. W prawdziwym kredycie żadne z tych założeń nie musi być prawdą. Oprocentowanie zmienne idzie w górę i w dół razem ze stawką WIBOR, a spłatę raty można czasem zawiesić.

Film pokazuje, jak rozbudować ten sam arkusz o nadpłaty, zmianę oprocentowania w trakcie kredytu i zawieszenie rat:

Mechanizm jest ten sam co przy nadpłacie: wszystko trzeba liczyć na saldzie. Funkcje PMT, PPMT i IPMT przyjmują jedną stopę procentową dla całego kredytu, więc przy oprocentowaniu zmiennym przestają się zgadzać od pierwszego miesiąca po zmianie stawki. W arkuszu dodaje się wtedy kolumnę z oprocentowaniem obowiązującym w danym miesiącu i odsetki liczy się jako saldo razy stopa z tego wiersza podzielona przez 12.

Zawieszenie raty działa podobnie: w miesiącu bez wpłaty część kapitałowa jest zerowa, a saldo zostaje takie samo jak w poprzednim wierszu. Okres kredytowania wydłuża się wtedy o tyle miesięcy, ile rat zawieszono.

Do pobrania niżej są trzy gotowe arkusze: ze stałym oprocentowaniem, ze zmiennym oprocentowaniem oraz ze zmiennym oprocentowaniem i dopłatami. Każdy liczy pełny harmonogram, więc widać w nich od razu, ile zmienia jedna nadpłata.

🏦 Kalkulator Kredytu Hipotecznego

Policz nadpłatę, zanim zaniesiesz wniosek do banku

✓Porównanie 2 banków obok siebie - raty stałe i malejące

✓Symulacja nadpłat: ile miesięcy i ile odsetek zdejmuje każda wpłata

✓Prowizje, ubezpieczenia i pełny koszt kredytu w jednym miejscu

✓Jednorazowa płatność, gwarancja zwrotu 30 dni

Otwórz kalkulator

⚡ Dostęp natychmiastowy · 🛡 Gwarancja zwrotu 30 dni