Tworzenie harmonogramu spłat kredytu w Excelu może być cenną umiejętnością, pozwalającą na wizualizację i efektywne zarządzanie spłatami kredytu. Harmonogram amortyzacji to tabela zawierająca szczegółowe informacje na temat każdej okresowej spłaty kredytu amortyzacyjnego (zwykle kredytu hipotecznego lub samochodowego). Rozbija każdą płatność na odsetki i główne składniki, pokazując pozostałe saldo po każdej płatności. Zanurzmy się w przewodniku krok po kroku, jak utworzyć taki harmonogram w Excelu.

Co to jest harmonogram amortyzacji?

Utwórz harmonogram amortyzacji w programie Excel

Utwórz harmonogram amortyzacji dla zmiennej liczby okresów

Utwórz harmonogram amortyzacji z dodatkowymi płatnościami

Utwórz harmonogram amortyzacji (z dopłatami) za pomocą szablonu Excel

Co to jest harmonogram amortyzacji?

Harmonogram amortyzacji to szczegółowa tabela używana do obliczeń kredytu, która przedstawia proces spłacania kredytu w czasie. Harmonogramy amortyzacji są powszechnie stosowane w przypadku kredytów o stałym oprocentowaniu, takich jak kredyty hipoteczne, kredyty samochodowe i pożyczki osobiste, w przypadku których kwota płatności pozostaje stała przez cały okres kredytowania, ale proporcja spłaty odsetek do kwoty głównej zmienia się w czasie.

Do stworzenia harmonogramu spłat kredytu w Excelu rzeczywiście kluczowe są wbudowane funkcje PMT, PPMT i IPMT. Rozumiemy, co robi każda funkcja:

• Funkcja PMT: Ta funkcja służy do obliczenia całkowitej spłaty pożyczki w danym okresie w oparciu o stałe płatności i stałą stopę procentową.
• Funkcja IPMT: Ta funkcja oblicza część odsetkową płatności za dany okres.
• Funkcja PPMT: Ta funkcja służy do obliczenia głównej części płatności za określony okres.

Korzystając z tych funkcji w programie Excel, można utworzyć szczegółowy harmonogram amortyzacji przedstawiający odsetki i główne składniki każdej płatności, a także saldo pozostałe do spłaty pożyczki po każdej spłacie.

Utwórz harmonogram amortyzacji w programie Excel

W tej sekcji przedstawimy dwie różne metody tworzenia harmonogramu amortyzacji w programie Excel. Metody te uwzględniają różne preferencje użytkowników i poziomy umiejętności, dzięki czemu każdy, niezależnie od znajomości programu Excel, może z powodzeniem skonstruować szczegółowy i dokładny harmonogram spłat swojej pożyczki.

Formuły umożliwiają głębsze zrozumienie podstawowych obliczeń i zapewniają elastyczność w dostosowywaniu harmonogramu zgodnie z konkretnymi wymaganiami. Takie podejście jest idealne dla tych, którzy chcą mieć praktyczne doświadczenie i jasny wgląd w podział każdej płatności na składniki główne i odsetkowe. Teraz przeanalizujmy krok po kroku proces tworzenia harmonogramu amortyzacji w programie Excel:

⭐️ Krok 1: Skonfiguruj informacje o pożyczce i tabelę amortyzacji

1. Wprowadź względne informacje o pożyczce, takie jak roczna stopa procentowa, okres pożyczki w latach, liczba płatności rocznie i kwota pożyczki do komórek, jak pokazano na poniższym zrzucie ekranu:
   
2. Następnie utwórz tabelę amortyzacji w programie Excel z określonymi etykietami, takimi jak Okres, Płatność, Odsetki, Kwota główna, Pozostałe saldo w komórkach A7:E7.
3. W kolumnie Okres wprowadź numery okresów. W tym przykładzie łączna liczba płatności wynosi 24 miesiące (2 lata), dlatego w kolumnie Okres należy wprowadzić liczby od 1 do 24. Zobacz zrzut ekranu:
   
4. Po skonfigurowaniu tabeli z etykietami i numerami okresów możesz przystąpić do wprowadzania formuł i wartości w kolumnach Płatność, Odsetki, Kwota główna i Saldo w oparciu o specyfikę pożyczki.

⭐️ Krok 2: Oblicz całkowitą kwotę płatności za pomocą funkcji PMT

Składnia PMT jest następująca:

Wprowadź następującą formułę do komórki B7, a następnie przeciągnij uchwyt wypełniania w dół, aby wypełnić tę formułę w innych komórkach, a zobaczysz stałą kwotę płatności za wszystkie okresy. Zobacz zrzut ekranu:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

⭐️ Krok 3: Oblicz odsetki za pomocą funkcji IPMT

Na tym etapie obliczysz odsetki za każdy okres płatności, korzystając z funkcji IPMT programu Excel.

Wprowadź następującą formułę do komórki C7, a następnie przeciągnij uchwyt wypełniania w dół kolumny, aby wypełnić tę formułę i uzyskać odsetki za każdy okres.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Krok 4: Oblicz kwotę główną za pomocą funkcji PPMT

Po obliczeniu odsetek za każdy okres kolejnym krokiem w tworzeniu harmonogramu amortyzacji jest obliczenie części głównej każdej płatności. Dokonuje się tego za pomocą funkcji PPMT, która ma na celu ustalenie części głównej płatności za dany okres, w oparciu o stałe płatności i stałą stopę procentową.

Składnia IPMT jest następująca:

Składnia i parametry wzoru PPMT są identyczne jak te stosowane we wcześniej omawianym wzorze IPMT.

Wprowadź następującą formułę do komórki D7, a następnie przeciągnij uchwyt wypełniania w dół kolumny, aby wypełnić kwotę główną dla każdego okresu. Zobacz zrzut ekranu:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

⭐️ Krok 5: Oblicz pozostałe saldo

Po obliczeniu zarówno odsetek, jak i kwoty głównej każdej płatności, kolejnym krokiem w harmonogramie amortyzacji jest obliczenie pozostałego salda pożyczki po każdej spłacie. Jest to kluczowa część harmonogramu, ponieważ pokazuje, jak saldo kredytu zmniejsza się w czasie.

1. W pierwszej komórce kolumny salda – E7, wprowadź poniższą formułę, co oznacza, że ​​pozostałe saldo będzie pierwotną kwotą pożyczki pomniejszoną o część główną pierwszej płatności:

   =B4-D7

   
2. Dla drugiego i wszystkich kolejnych okresów oblicz pozostałe saldo, odejmując kwotę główną za bieżący okres od salda poprzedniego okresu. Proszę zastosować następującą formułę do komórki E8:

   =E7-D8

    Note: Odniesienie do komórki bilansu powinno być względne, aby było aktualizowane w miarę przeciągania formuły w dół.
   
3. A następnie przeciągnij uchwyt wypełniania w dół do kolumny. Jak widać, każda komórka automatycznie dostosuje się do obliczenia pozostałego salda na podstawie zaktualizowanych płatności głównych.
   

⭐️ Krok 6: Zrób podsumowanie pożyczki

Po ustaleniu szczegółowego harmonogramu spłat utworzenie podsumowania pożyczki może zapewnić szybki przegląd kluczowych aspektów pożyczki. Podsumowanie to zazwyczaj obejmuje całkowity koszt pożyczki i całkowite zapłacone odsetki.

● Aby obliczyć sumę płatności:

=SUM(B7:B30)

● Aby obliczyć całkowite odsetki:

=SUM(C7:C30)

⭐️ Wynik:

Teraz udało się stworzyć prosty, ale kompleksowy harmonogram spłat kredytu. zobacz zrzut ekranu:

Utwórz harmonogram amortyzacji dla zmiennej liczby okresów

W poprzednim przykładzie tworzymy harmonogram spłaty kredytu na ustaloną liczbę rat. Takie podejście doskonale sprawdza się w przypadku konkretnej pożyczki lub kredytu hipotecznego, którego warunki się nie zmieniają.

Jeśli jednak chcesz stworzyć elastyczny harmonogram amortyzacji, który można wielokrotnie stosować w przypadku pożyczek o różnych okresach, umożliwiając modyfikację liczby płatności zgodnie z wymaganiami dla różnych scenariuszy pożyczek, musisz zastosować bardziej szczegółową metodę.

⭐️ Krok 1: Skonfiguruj informacje o pożyczce i tabelę amortyzacji

1. Wprowadź względne informacje o pożyczce, takie jak roczna stopa procentowa, okres pożyczki w latach, liczba płatności rocznie i kwota pożyczki do komórek, jak pokazano na poniższym zrzucie ekranu:
   
2. Następnie utwórz tabelę amortyzacji w programie Excel z określonymi etykietami, takimi jak Okres, Płatność, Odsetki, Kwota główna, Pozostałe saldo w komórkach A7:E7.
3. W kolumnie Okres wprowadź największą liczbę płatności, jaką możesz rozważyć w przypadku dowolnej pożyczki, na przykład wpisz liczby z zakresu od 1 do 360. Może to obejmować standardową pożyczkę na 30 lat, jeśli dokonujesz płatności miesięcznych.
   

⭐️ Krok 2: Zmodyfikuj formuły płatności, odsetek i zasady za pomocą funkcji JEŻELI

Wprowadź poniższe formuły w odpowiednich komórkach, a następnie przeciągnij uchwyt wypełniania, aby rozszerzyć te formuły do ​​maksymalnej ustawionej liczby okresów płatności.

● Formuła płatności:

Zwykle do obliczania płatności używasz funkcji PMT. Aby włączyć instrukcję JEŻELI, należy zastosować formułę składniową:

Zatem formuła jest następująca:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), "")

● Formuła oprocentowania:

Formuła składniowa to:

Zatem formuła jest następująca:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

● Zasada zasadnicza:

Formuła składniowa to:

Zatem formuła jest następująca:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), "")

⭐️ Krok 3: Dostosuj pozostałą formułę salda

W przypadku pozostałego salda zazwyczaj można odjąć kwotę główną od poprzedniego salda. Za pomocą instrukcji IF zmodyfikuj ją jako:

● Pierwsza komórka bilansowa:(E7)

=B4-D7

● Drugie ogniwo bilansowe:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, "") 

⭐️ Krok 4: Zrób podsumowanie pożyczki

Po ustawieniu harmonogramu amortyzacji za pomocą zmodyfikowanych formuł następnym krokiem jest utworzenie podsumowania pożyczki.

● Aby obliczyć sumę płatności:

=SUM(B7:B366)

● Aby obliczyć całkowite odsetki:

=SUM(C7:C366)

⭐️ Wynik:

Teraz masz kompleksowy i dynamiczny harmonogram amortyzacji w programie Excel wraz ze szczegółowym podsumowaniem pożyczki. Za każdym razem, gdy dostosujesz okres płatności, cały harmonogram amortyzacji zostanie automatycznie zaktualizowany, aby odzwierciedlić te zmiany. Zobacz demo poniżej:

Utwórz harmonogram amortyzacji z dodatkowymi płatnościami

Dokonując dodatkowych wpłat poza planowanymi, pożyczkę można spłacić szybciej. Harmonogram spłat obejmujący dodatkowe płatności, utworzony w programie Excel, pokazuje, w jaki sposób te dodatkowe płatności mogą przyspieszyć spłatę pożyczki i zmniejszyć całkowite zapłacone odsetki. Oto jak możesz to skonfigurować:

⭐️ Krok 1: Skonfiguruj informacje o pożyczce i tabelę amortyzacji

1. Wprowadź względne informacje o pożyczce, takie jak roczna stopa procentowa, okres pożyczki w latach, liczba płatności rocznie, kwota pożyczki i dodatkowa płatność do komórek, jak pokazano na poniższym zrzucie ekranu:
   
2. Następnie oblicz zaplanowaną płatność.
   Oprócz komórek wejściowych do naszych późniejszych obliczeń potrzebna jest jeszcze jedna predefiniowana komórka – zaplanowana kwota płatności. Jest to regularna kwota płatności pożyczki, przy założeniu, że nie zostaną dokonane żadne dodatkowe płatności. Proszę zastosować następującą formułę do komórki B6:

   =IFERROR(-PMT($B$1/$B$3, $B$2*$B$3, $B$4),"")

   
   
3. Następnie utwórz tabelę amortyzacji w programie Excel:
   • Ustaw określone etykiety, takie jak Okres, Harmonogram płatności, Dodatkowa płatność, Całkowita płatność, Odsetki, Kwota główna, Pozostałe saldo w komórkach A8:G8;
   • W kolumnie Okres wprowadź największą liczbę płatności, jakie możesz rozważyć w przypadku dowolnej pożyczki. Na przykład wpisz liczby z zakresu od 0 do 360. Może to obejmować standardową pożyczkę na 30 lat, jeśli dokonujesz płatności miesięcznych;
   • Dla okresu 0 (w naszym przypadku wiersz 9) pobierz wartość Saldo za pomocą tej formuły = B4, co odpowiada początkowej kwocie pożyczki. Wszystkie pozostałe komórki w tym wierszu należy pozostawić puste.
   • 

⭐️ Krok 2: Stwórz formuły harmonogramu amortyzacji z dopłatami

Wprowadź kolejno następujące formuły w odpowiednich komórkach. Aby usprawnić obsługę błędów, zamykamy tę i wszystkie przyszłe formuły w funkcji IFERROR. Takie podejście pomaga uniknąć wielu potencjalnych błędów, które mogą wystąpić, jeśli którakolwiek z komórek wejściowych pozostanie pusta lub będzie zawierać nieprawidłowe wartości.

● Oblicz zaplanowaną płatność:

Wprowadź następującą formułę do komórki B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Oblicz dodatkową opłatę:

Wprowadź następującą formułę do komórki C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Oblicz całkowitą kwotę płatności:

Wprowadź następującą formułę do komórki D10:

=IFERROR(B10+C10, "")

● Oblicz kwotę główną:

Wprowadź następującą formułę do komórki E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Oblicz odsetki:

Wprowadź następującą formułę do komórki F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Oblicz pozostałe saldo

Wprowadź następującą formułę do komórki G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Po ukończeniu każdej formuły wybierz zakres komórek B10:G10 i użyj uchwytu wypełniania, aby przeciągnąć i rozszerzyć te formuły na cały zestaw okresów płatności. Dla niewykorzystanych okresów komórki będą pokazywać 0. Zobacz zrzut ekranu:

⭐️ Krok 3: Zrób podsumowanie pożyczki

● Uzyskaj zaplanowaną liczbę płatności:

=B2:B3

● Uzyskaj rzeczywistą liczbę płatności:

=COUNTIF(D10:D369,">"&0)

● Uzyskaj całkowite dodatkowe płatności:

=SUM(C10:C369)

● Uzyskaj całkowite odsetki:

=SUM(F10:F369)

⭐️ Wynik:

Wykonując te kroki, utworzysz dynamiczny harmonogram amortyzacji w programie Excel, który uwzględnia dodatkowe płatności.

Utwórz harmonogram amortyzacji, korzystając z szablonu programu Excel

Utworzenie harmonogramu amortyzacji w programie Excel przy użyciu szablonu jest metodą prostą i oszczędzającą czas. Excel udostępnia wbudowane szablony, które automatycznie obliczają odsetki, kwotę główną i pozostałe saldo każdej płatności. Oto jak utworzyć harmonogram amortyzacji przy użyciu szablonu Excel:

1. Kliknij filet > Nowości, w polu wyszukiwania wpisz Harmonogram amortyzacjii naciśnij Wchodzę klucz. Następnie wybierz szablon, który najlepiej odpowiada Twoim potrzebom, klikając na niego. Na przykład tutaj wybiorę szablon prostego kalkulatora pożyczkowego. Zobacz zrzut ekranu:
   
2. Po wybraniu szablonu kliknij przycisk Stwórz aby otworzyć go jako nowy skoroszyt.
3. Następnie wprowadź dane dotyczące własnej pożyczki, szablon powinien automatycznie obliczyć i wypełnić harmonogram na podstawie wprowadzonych danych.
4. Na koniec zapisz nowy skoroszyt harmonogramu amortyzacji.