Spis treści
- Zrozumieć swój kredyt hipoteczny
- Oblicz miesięczną płatność
- Oblicz roczną stopę procentową
- Określanie długości pożyczki
- Dekompozycja pożyczki
- Obliczanie pożyczki w programie Excel
- Amortyzacja pożyczki
- Tworzenie harmonogramu pożyczki
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
- Skorzystaj z programu Excel, aby uzyskać kontrolę nad hipoteką, określając miesięczną spłatę, stopę procentową i harmonogram pożyczek. Możesz dokładniej przyjrzeć się podziałowi pożyczki z programem Excel i stworzyć harmonogram spłat, który będzie dla Ciebie odpowiedni Dostępne są obliczenia dla każdego kroku, które możesz dostosować do swoich konkretnych potrzeb. Rozbicie i zbadanie pożyczki krok po kroku może sprawić, że proces spłaty będzie mniej przytłaczający i łatwiejszy do zarządzania.
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 powie 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;;)
Znak minus przed PMT jest konieczny, ponieważ formuła zwraca liczbę ujemną. Pierwsze trzy argumenty to stopa kredytu, długość kredytu (liczba okresów) i 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 koniec (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)
Wyjaś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;;)
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ść resztkowa 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 jest następująca:
= 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 programu Excel to świetny sposób na śledzenie tego, co jesteś winien, i opracowanie harmonogramu spłaty, który minimalizuje wszelkie opłaty, które możesz być winien.
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 według stawki 3, 10% i miesięczną spłatę 1100 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;;)
Pierwsze trzy argumenty to roczna stopa pożyczki, miesięczna rata 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 przez liczbę lat w celu zakończenia spłaty kredytu. Innymi słowy, aby pożyczyć 120 000 USD przy rocznej stopie 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;;)
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ę 1 161, 88 USD.
Aby obliczyć główną kwotę umorzenia, stosujemy następujący wzór:
= -PPMT (TP; A18; 4 mld USD * 12; 3 mld USD) = -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.
