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().

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

Więcej o tworzeniu list porządkowych w zakresach i tabelach dowiesz się z artykułu Excel Automatyczne Numerowanie, Numeracja Porządkowa

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 a dla pozostałych kolejno -1, -2, -3 …

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() 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 argumentu 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, on nie występował 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 IMPT(), 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 IMPT 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ę IMPT() w komórce obok raty kapitałowej wpisujemy funkcję raty odsetkowej:

=IMPT($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łę:

=D4-E14

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ły 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.

Mając powyższe dane możemy także wyliczyć, ile procent całkowitej wartości kredytu stanowi spłata kapitału i spłata odsetek.

Widzimy, że w naszym przypadku spłata wartości kredytu stanowi tylko 47,7% pozostałe 52,3% wartości kredytu to są odsetki!!! Co zrobić zatem aby zmniejszyć odsetki? Nadpłacaj kredyt. W kolejnym artykule Kalkulator Wakacji Kredytowych. Nadpłata Kredytu w Excelu zmodyfikujemy ten kalkulator i dodamy możliwość nadpłaty w tym – wakacje kredytowe, dodamy też możliwość zmiany oprocentowania kredytu.

Tutaj pobierz kalkulator kredytowy w Excelu:

Kurs Excel

Chcesz dobrze nauczyć się Excela ? Nie trać więcej czasu na rzeczy które możesz wykonać jednym kliknięciem. Prawie 10 godzin praktycznej wiedzy, w której przedstawiam najprzydatniejsze możliwości Excela. Wszystkie 72 lekcje wideo oparte są na przykładach podsumowanych w ebooku. Dodatkowo 62 zadania do samodzielnego wykonania.

Zobacz mój kurs Excel