Kalkulator Kredytowy Excel, Harmonogram Rat Kredytu
Czy wiesz, że w Excelu możesz stworzyć własny kalkulator kredytowy zawierający harmonogram rat kredytowych? W tym poradniku pokażę Ci jak krok po kroku wykonać kalkulator kredytowy w Excelu, który zapewni Ci lepszą kontrolę nad Twoim zobowiązaniem.
W dalszej części poradnika:
- Jak stworzyć kalkulator kredytowy Excel?
- Automatyczna ilość rat kalkulatora kredytowego Excel
- Obliczenie raty kredytu w Excelu
- Jak określić wartość raty kapitałowej w kalkulatorze kredytowym w Excelu?
- Jak określić wartość raty odsetkowej w kalkulatorze kredytowym w Excelu?
- Formatowanie warunkowe w kalkulatorze kredytowym w Excelu
- Jak wyliczyć sumę rat kapitałowych i odsetkowych, czyli całkowitą wartość kredytu?
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 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.
Tutaj pobierz darmowy kalkulator kredytowy w Excelu
Inne poradniki
SUMA.WARUNKÓW() w Excelu – sumowanie z wieloma warunkami
SUMA.WARUNKÓW() to istotna funkcja analityczna w Excelu. Pozwala sumować wartości na podstawie dwóch lub więcej warunków jednocześnie, dzięki czemu idealnie sprawdza się w raportach sprzedaży, analizach finansowych i zestawieniach okresowych.
SUMA.JEŻELI() w Excelu, czyli jak sumować na podstawie warunku
W tym artykule pokażę Ci krok po kroku, jak działa SUMA.JEŻELI(), jak poprawnie zapisywać warunki liczbowe i tekstowe, jak korzystać z symboli wieloznacznych oraz na co uważać, gdy formuła nie daje oczekiwanych wyników. Na konkretnych przykładach zobaczysz, jak szybko wyciągać z danych dokładnie te liczby, których naprawdę potrzebujesz – bez ręcznego filtrowania i zbędnych kolumn pomocniczych.
Jak zrobić wykres w Excelu – instrukcja krok po kroku
Wykresy w Excelu służą do szybkiego i czytelnego przedstawiania danych. Zamiast analizować kolumny liczb, możesz w kilka sekund zobaczyć zależności, porównać wartości albo sprawdzić, jak dane zmieniają się w czasie. To szczególnie przydatne przy raportach, zestawieniach sprzedaży czy analizie wyników.
Jeśli zastanawiasz się, jak zrobić wykres w Excelu, dobra wiadomość jest taka, że nie wymaga to zaawansowanej wiedzy ani skomplikowanych ustawień. Wystarczy poprawnie przygotować dane, wybrać odpowiedni typ wykresu oraz dostosować jego wygląd.
W tym artykule pokażę krok po kroku, jak utworzyć prosty wykres w Excelu: od zaznaczenia danych, przez wstawienie wykresu, aż po zmianę jego typu i dostosowanie wyglądu.
Data w Excelu – jak wstawiać, formatować i aktualizować daty
Z pozoru prosta rzecz, jak wpisanie daty w Excelu, potrafi przysporzyć sporo kłopotów, w szczególności dla osób mniej doświadczonych. Program nie rozpoznaje daty, sortowanie się psuje albo liczby same zmieniają swój format na datę, mimo iż nimi nie są. Excel traktuje daty w specyficzny sposób, ponieważ widzi je nie jako daty, ale jako liczby, które reprezentują kolejne dni od 1 stycznia 1900 roku nam ta liczba wyświetlana jest jako data (np. dzień 27.11.2025 zapisany jest w Excelu jako liczba 45988). To z jednej strony ogromna zaleta (bo można na nich wykonywać przeróżne obliczenia), a z drugiej jest źródłem wielu nieporozumień i błędów.
W tym poradniku pokażę Ci, jak poprawnie wprowadzać, edytować, formatować i wyświetlać daty w Excelu. Dzięki kilku prostym trikom unikniesz błędów i sprawisz, że Twoje arkusze będą działały dokładnie tak, jak chcesz.
Power Query w Excelu – Twój praktyczny poradnik krok po kroku
Power Query to potężne narzędzie w Excelu do importowania, przekształcania i łączenia danych z różnych źródeł. Dzięki niemu możesz automatyzować żmudne zadania związane z przygotowaniem danych do analiz. W tym przewodniku pokażę Ci krok po kroku, jak wykorzystać najważniejsze funkcje Power Query w praktyce.
Potęga tabel przestawnych w Excelu – jak szybko analizować dane jak ekspert?
Masz dość przekopywania się przez setki wierszy danych? Tabele przestawne to narzędzie, które w kilka kliknięć pozwala zamienić surowe dane w czytelne raporty, wykresy i zestawienia. Niezależnie od tego, czy analizujesz sprzedaż, budżet czy wyniki ankiet – z tabelami przestawnymi zrobisz to szybciej, prościej i skuteczniej. Przekonaj się, jak dużo możesz zyskać, korzystając z tej funkcji Excela!
- Bezpośrednie porównanie różnych banków i wariantów kredytu, raty stałe i malejące.
- Symulacja nadpłat jednorazowych i cyklicznych dla wszystkich wariantów pozwala na wybór najkorzystniejszej oferty.
- Obliczenie rzeczywistego kosztu (RRSO) poprzez uwzględnienie ukrytych opłat i prowizji
- Umożliwia symulację zmiennych stóp procentowych w trakcie trwania kredytu.