Zaplanuj spłaty pożyczek za pomocą formuł Excel

Spłata pożyczki polega na spłacie wcześniej pożyczonych pieniędzy od pożyczkodawcy, zazwyczaj poprzez serię okresowych płatności obejmujących kwotę główną plus odsetki. Czy wiesz, że możesz użyć programu Excel do obliczenia spłat kredytu?

Ten artykuł jest szczegółowym przewodnikiem po ustawieniach obliczania kredytu.

Kluczowe dania na wynos

• Użyj programu Excel, aby uzyskać kontrolę nad hipoteką, określając miesięczną spłatę, stopę procentową i harmonogram pożyczek.
• Możesz przyjrzeć się dokładniej podziałowi pożyczki za pomocą programu Excel i stworzyć harmonogram spłat, który będzie dla Ciebie odpowiedni.
• Dla każdego kroku dostępne są obliczenia, które można dostosować do własnych potrzeb.
• Podział i badanie pożyczki krok po kroku może sprawić, że proces spłaty będzie mniej przytłaczający i łatwiejszy w zarządzaniu.

Zrozumieć swój kredyt hipoteczny

Korzystając z programu Excel, możesz lepiej zrozumieć swój kredyt hipoteczny w trzech prostych krokach. Pierwszy krok określa miesięczną płatność. Drugi krok oblicza stopę procentową, a trzeci krok określa harmonogram pożyczki.

Możesz zbudować tabelę w Excelu, która poda Ci stopę procentową, obliczenie pożyczki na czas trwania pożyczki, rozkład pożyczki, amortyzację i miesięczną płatność.

Oblicz miesięczną płatność

Po pierwsze, oto jak obliczyć miesięczną spłatę kredytu hipotecznego. Wykorzystując roczną stopę procentową, kwotę główną i czas trwania, możemy ustalić kwotę, która będzie spłacana co miesiąc.

Formuła pokazana na powyższym zrzucie ekranu jest zapisana w następujący sposób:

= -PMT (stopa; długość; wartość_ teraźniejsza; [wartość_przyszłości]; [typ])

Znak minus przed PMT jest konieczny, ponieważ formuła zwraca liczbę ujemną. Pierwsze trzy argumenty to stopa kredytu, długość kredytu (liczba okresów) oraz pożyczona kwota główna. Ostatnie dwa argumenty są opcjonalne, wartość resztkowa domyślnie wynosi zero; płatne z góry (za jeden) lub na końcu (za zero), jest również opcjonalne.

Formuła Excel zastosowana do obliczenia miesięcznej raty kredytu to:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Objaśnienie: W przypadku stopy stosujemy stopę miesięczną (okres stopy), a następnie obliczamy liczbę okresów (120 przez 10 lat pomnożoną przez 12 miesięcy), a na koniec wskazujemy pożyczoną kwotę główną. Nasza miesięczna płatność wyniesie 1 161, 88 USD w ciągu 10 lat.

Oblicz roczną stopę procentową

Widzieliśmy, jak skonfigurować obliczanie miesięcznej raty kredytu hipotecznego. Możemy jednak chcieć ustalić maksymalną miesięczną płatność, na jaką możemy sobie pozwolić, która pokazuje również liczbę lat, przez które musielibyśmy spłacić pożyczkę. Z tego powodu chcielibyśmy poznać odpowiednią roczną stopę procentową.

Jak pokazano na zrzucie ekranu powyżej, najpierw obliczamy stawkę okresową (w naszym przypadku miesięcznie), a następnie stawkę roczną. Zastosowana formuła to RATE, jak pokazano na zrzucie ekranu powyżej. Jest napisane w następujący sposób:

= RATE (Nper; pmt; present_value; [future_value]; [type])

Pierwsze trzy argumenty to długość pożyczki (liczba okresów), miesięczna rata spłaty pożyczki i pożyczona kwota główna. Ostatnie trzy argumenty są opcjonalne, a wartość rezydualna domyślnie wynosi zero; Argument dotyczący zarządzania terminem zapadalności z góry (dla jednego) lub na końcu (dla zera) jest również opcjonalny. Wreszcie argument oszacowania jest opcjonalny, ale może dać wstępne oszacowanie stawki.

Formuła Excel zastosowana do obliczenia stopy pożyczki to:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Uwaga: odpowiednie dane w płatności miesięcznej muszą mieć znak ujemny. Dlatego przed formułą jest znak minus. Okres ten wynosi 0, 294%.

Używamy wzoru = (1 + B5) wynosi 12-1 ^ = (1 + 0, 294%) ^ 12-1, aby uzyskać roczną stopę naszej pożyczki, która wynosi 3, 58%. Innymi słowy, aby pożyczyć 120 000 USD w ciągu 13 lat i spłacić 960 USD miesięcznie, powinniśmy wynegocjować pożyczkę według rocznej maksymalnej stopy 3, 58%.

Korzystanie z Excela to świetny sposób na śledzenie tego, co jesteś winien, i opracowanie harmonogramu spłat, który minimalizuje wszelkie ewentualne opłaty.

Określanie długości pożyczki

Zobaczymy teraz, jak określić długość pożyczki, kiedy znasz roczną stopę, pożyczoną kwotę główną i miesięczną spłatę, która ma zostać spłacona. Innymi słowy, jak długo będziemy musieli spłacić kredyt hipoteczny w wysokości 120 000 USD przy oprocentowaniu 3, 10% i miesięcznej spłacie 1 100 USD ">

Formuła, której użyjemy, to NPER, jak pokazano na zrzucie ekranu powyżej i jest napisana w następujący sposób:

= NPER (stopa; pmt; wartość_ teraźniejsza; [wartość_przyszłości]; [typ])

Pierwsze trzy argumenty to roczna stopa pożyczki, miesięczna płatność potrzebna do spłaty pożyczki i pożyczona kwota główna. Ostatnie dwa argumenty są opcjonalne, wartość resztkowa domyślnie wynosi zero. Pojęcie argumentu płatnego z góry (dla jednego) lub na końcu (dla zera) jest również opcjonalne.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Uwaga: odpowiednie dane w płatności miesięcznej muszą mieć znak ujemny. Dlatego przed formułą mamy znak minus. Długość zwrotu wynosi 127, 97 okresów (w naszym przypadku miesiące).

Zastosujemy formułę = B5 / 12 = 127, 97 / 12 dla liczby lat w celu zakończenia spłaty kredytu. Innymi słowy, aby pożyczyć 120 000 USD z roczną stopą 3, 10% i spłacać 1100 USD miesięcznie, powinniśmy spłacać terminy zapadalności za 128 miesięcy lub 10 lat i 8 miesięcy.

Dekompozycja pożyczki

Spłata pożyczki składa się z kwoty głównej i odsetek. Odsetki naliczane są za każdy okres - na przykład miesięczne spłaty w ciągu 10 lat dają nam 120 okresów.

Powyższa tabela pokazuje podział pożyczki (całkowity okres równy 120) przy użyciu formuł PPMT i IPMT. Argumenty dwóch formuł są takie same i są podzielone w następujący sposób:

= -PPMT (stopa; num_period; długość; główny; [rezydualny]; [term])

Argumenty są takie same, jak w przypadku już znanej formuły PMT, z wyjątkiem „num_period”, który jest dodawany w celu pokazania okresu, w którym pożyczka ma zostać podzielona z uwagi na kwotę główną i odsetki. Oto przykład:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)

Wynik pokazano na zrzucie ekranu powyżej „Rozkładu pożyczki” w analizowanym okresie, czyli „jeden”; to znaczy pierwszy okres lub pierwszy miesiąc. Płacimy 1 161, 88 USD w rozbiciu na 856, 20 USD kapitału i 305, 68 USD odsetek.

Obliczanie pożyczki w programie Excel

Możliwe jest również obliczenie spłaty kapitału i odsetek za kilka okresów, takich jak pierwsze 12 miesięcy lub pierwsze 15 miesięcy.

= -CUMPRINC (stopa; długość; nazwa główna; data_początkowa; data_końcowa; typ)

Znajdujemy argumenty, stawkę, długość, kwotę główną i termin (które są obowiązkowe), które widzieliśmy już w pierwszej części ze wzorem PMT. Ale tutaj potrzebujemy również argumentów „data_początkowa” i „data_końcowa”. „Data_początkowa” wskazuje początek analizowanego okresu, a „data_końcowa” wskazuje koniec analizowanego okresu.

Oto przykład:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

Wynik pokazano na zrzucie ekranu „Cumul 1st year”, więc analizowane okresy mieszczą się w przedziale od jednego do 12 pierwszego okresu (pierwszy miesiąc) do dwunastego (12. miesiąc). Przez rok zapłacilibyśmy 10 419, 55 USD kwoty głównej i 3 522, 99 USD kwoty odsetek.

Amortyzacja pożyczki

Wcześniejsze formuły pozwalają nam tworzyć harmonogram według okresu, wiedzieć, ile miesięcznie będziemy spłacać w kwocie głównej i odsetkach, oraz wiedzieć, ile pozostało do spłaty.

Tworzenie harmonogramu pożyczki

Aby utworzyć harmonogram pożyczek, użyjemy różnych formuł omówionych powyżej i rozszerzymy je o liczbę okresów.

W kolumnie pierwszego okresu wpisz „1” jako pierwszy okres, a następnie przeciągnij komórkę w dół. W naszym przypadku potrzebujemy 120 okresów, ponieważ 10-letnia spłata kredytu pomnożona przez 12 miesięcy wynosi 120.

Druga kolumna to miesięczna kwota, którą musimy płacić każdego miesiąca - która jest stała w całym harmonogramie pożyczek. Aby obliczyć kwotę, wstaw następującą formułę do komórki naszego pierwszego okresu:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

Trzecia kolumna to kwota główna, która będzie spłacana co miesiąc. Na przykład w 40. okresie spłacimy kwotę główną w wysokości 945, 51 USD, a naszą miesięczną łączną kwotę w wysokości 1 161, 88 USD.

Aby obliczyć główną kwotę umorzenia, stosujemy następujący wzór:

= -PPMT (TP; A18; 4 mld $ 4 * 12; 3 mld $) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Czwarta kolumna to odsetki, dla których wykorzystujemy wzór do obliczenia spłacanej kwoty głównej w naszej miesięcznej kwocie, aby dowiedzieć się, ile odsetek należy zapłacić:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

Piąta kolumna zawiera kwotę pozostałą do zapłaty. Na przykład po 40. płatności będziemy musieli zapłacić 83 994, 69 USD za 120 000 USD.

Wzór jest następujący:

= 3 mld USD + CUMPRINC (TP; 4 mld USD * 12; 3 mld USD; 1; A18; 0)

Formuła wykorzystuje kombinację kwoty głównej w okresie poprzedzającym komórkę zawierającą pożyczoną kwotę główną. Ten okres zaczyna się zmieniać, gdy kopiujemy i przeciągamy komórkę w dół. Poniższa tabela pokazuje, że na koniec 120 okresów spłacana jest nasza pożyczka.