Zaplanuj spłaty pożyczki za pomocą formuł programu Excel
Spłata pożyczki jest czynnością polegającą na spłacie pieniędzy wcześniej pożyczonych od pożyczkodawcy, zazwyczaj w drodze serii okresowych płatności obejmujących kwotę główną i odsetki. Czy wiesz, że możesz użyć programu Excel do obliczenia spłaty kredytu?
Ten artykuł jest przewodnikiem krok po kroku, jak skonfigurować obliczenia pożyczki.
Kluczowe wnioski:
- Skorzystaj z programu Excel, aby poradzić sobie ze swoim kredytem hipotecznym, określając miesięczną płatność, stopę procentową i harmonogram pożyczki.
- Możesz dokładniej przyjrzeć się rozkładowi pożyczki w programie 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.
- Rozłożenie i zbadanie pożyczki krok po kroku może sprawić, że proces spłaty będzie mniej przytłaczający i łatwiejszy do opanowania.
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 kredytu na czas trwania pożyczki, dekompozycję pożyczki, amortyzację i miesięczną płatność.
Oblicz miesięczną płatność
Po pierwsze, oto jak obliczyć miesięczną ratę kredytu hipotecznego. Na podstawie rocznej stopy procentowej, kwoty głównej oraz czasu trwania możemy określić kwotę do spłaty miesięcznie.
Wzór, jak pokazano na powyższym zrzucie ekranu, jest zapisany w następujący sposób:
= -PMT (stopa; długość; aktualna_wartość; [przyszła_wartość]; [typ])
Znak minus przed PMT jest konieczny, ponieważ formuła zwraca liczbę ujemną. Pierwsze trzy argumenty to stopa pożyczki, długość pożyczki (liczba okresów) i pożyczony kapitał. Ostatnie dwa argumenty są opcjonalne, wartość rezydualna domyślnie wynosi zero; płatne z góry (za jedną) lub na koniec (za zero) jest również opcjonalne.
Formuła programu Excel używana do obliczania miesięcznej spłaty pożyczki to:
= PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Wyjaśnienie: Jako stawkę przyjmujemy stawkę miesięczną (okres stawki), następnie obliczamy liczbę okresów (120 dla 10 lat pomnożone przez 12 miesięcy) i na końcu wskazujemy pożyczony kapitał. Nasza miesięczna rata wyniesie 1161,88 USD w ciągu 10 lat.
Oblicz roczną stopę procentową
Widzieliśmy, jak ustawić obliczanie miesięcznej raty kredytu hipotecznego. Ale możemy chcieć ustalić maksymalną miesięczną płatność, na którą nas stać, która pokazuje również liczbę lat, przez które musielibyśmy spłacać pożyczkę. Z tego powodu chcielibyśmy poznać odpowiednią roczną stopę procentową.
Jak pokazano na powyższym zrzucie ekranu, najpierw obliczamy stawkę za okres (w naszym przypadku miesięczną), a następnie roczną. Użyta formuła to RATE, jak pokazano na powyższym zrzucie ekranu. 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 oraz pożyczony kapitał. Ostatnie trzy argumenty są opcjonalne, a wartość rezydualna domyślnie wynosi zero; termin argument za zarządzaniem terminem zapadalności z góry (dla jednego) lub na koniec (dla zera) jest również opcjonalny. Wreszcie argument szacunkowy jest opcjonalny, ale może podać wstępne oszacowanie stawki.
Formuła programu Excel użyta do obliczenia stopy pożyczki to:
= SZYBKOŚĆ (12 * B4; -B2; B3) = SZYBKOŚĆ (12 * 13; -960; 120000)
Uwaga: odpowiednie dane w płatności miesięcznej muszą mieć znak ujemny. Dlatego przed formułą znajduje się znak minus. Okres oprocentowania wynosi 0,294%.
Używamy wzoru = (1 + B5) to 12-1 ^ = (1 + 0,294%) ^ 12-1, aby otrzymać roczną stopę kredytu, która wynosi 3,58%. Innymi słowy, aby pożyczyć 120 000 dolarów w ciągu 13 lat i spłacać 960 dolarów miesięcznie, powinniśmy negocjować pożyczkę z roczną maksymalną stopą 3,58%.
Korzystanie z Excela to świetny sposób na śledzenie tego, co jesteś winien i wymyślanie harmonogramu spłaty, który minimalizuje wszelkie opłaty, które możesz być winien.
Określenie długości pożyczki
Zobaczymy teraz, jak określić długość pożyczki, znając roczną stopę procentową, pożyczony kapitał i miesięczną spłatę, która ma zostać spłacona. Innymi słowy, jak długo będziemy musieli spłacać kredyt hipoteczny w wysokości 120 000 USD przy oprocentowaniu 3,10% i miesięcznej spłacie w wysokości 1100 USD?
Formuła, której użyjemy, to NPER, jak pokazano na powyższym zrzucie ekranu, i jest zapisana w następujący sposób:
= NPER (stopa; rata; aktualna_wartość; [przyszła_wartość]; [typ])
Pierwsze trzy argumenty to roczna stopa pożyczki, miesięczna rata potrzebna do spłaty pożyczki oraz pożyczona kwota główna. Ostatnie dwa argumenty są opcjonalne, wartość rezydualna domyślnie wynosi zero. Termin argument płatny z góry (za jeden) lub na koniec (za zero) jest również opcjonalny.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Znak minus przed formułą
Odpowiednie dane w miesięcznej płatności muszą mieć znak ujemny. Dlatego przed formułą mamy znak minus. Długość refundacji wynosi 127,97 okresów (w naszym przypadku miesięcy).
Użyjemy wzoru = B5 / 12 = 127,97 / 12 przez liczbę lat do zakończenia spłaty kredytu. Innymi słowy, aby pożyczyć 120 000 USD z roczną stopą 3,10% i płacić 1 100 USD miesięcznie, powinniśmy spłacać okres spłaty na 128 miesięcy lub 10 lat i 8 miesięcy.
Rozkład pożyczki
Na spłatę pożyczki składa się kwota główna i odsetki. Odsetki naliczane są za każdy okres – na przykład miesięczne spłaty przez 10 lat dadzą nam 120 okresów.
Powyższa tabela przedstawia podział pożyczki (łączny okres równy 120) przy użyciu formuł PPMT i IPMT. Argumenty obu formuł są takie same i są podzielone w następujący sposób:
= -PPMT (stopa; liczba_okres; długość; kwota główna; [reszta]; [termin])
Argumenty są takie same, jak w przypadku przedstawionej już formuły PMT, z wyjątkiem „num_period”, który jest dodawany, aby pokazać okres, w którym należy rozbić pożyczkę, biorąc pod uwagę kapitał 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 jest pokazany na zrzucie ekranu powyżej „Dekompozycja kredytu” w analizowanym okresie, który wynosi „jeden”; to znaczy pierwszy okres lub pierwszy miesiąc. Płacimy 1161,88 $ w podziale na 856,20 $ kapitału i 305,68 $ odsetek.
Obliczanie pożyczki w programie Excel
Możliwe jest również obliczenie spłaty kapitału i odsetek za kilka okresów, np. Przez pierwsze 12 miesięcy lub pierwsze 15 miesięcy.
= -CUMPRINC (stopa; długość; jednostka główna; data_początkowa; data_końcowa; typ)
Znajdujemy argumenty, stopę, 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 okresu do analizy, a „data_końcowa” wskazuje koniec okresu do analizy.
Oto przykład:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Wynik jest pokazany na zrzucie ekranu „Cumul 1st year”, więc analizowane okresy mieszczą się w zakresie od jednego do 12 pierwszego okresu (pierwszy miesiąc) do dwunastego (12 miesiąc). W ciągu roku zapłacilibyśmy 10419,55 USD kapitału i 3522,99 USD odsetek.
Amortyzacja pożyczki
Wcześniejsze formuły pozwalają nam tworzyć harmonogram okres po okresie, wiedzieć, ile będziemy płacić co miesiąc kapitału i odsetek oraz wiedzieć, ile pozostało do spłaty.
Tworzenie harmonogramu pożyczek
Aby utworzyć harmonogram pożyczek, użyjemy różnych formuł omówionych powyżej i rozszerzymy je na liczbę okresów.
W pierwszej kolumnie okresu wprowadź „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 to 120.
Druga kolumna to miesięczna kwota, jaką musimy spłacać co miesiąc – która jest stała w całym harmonogramie pożyczki. Aby obliczyć kwotę, wstaw następującą formułę w komórce naszego pierwszego okresu:
= -PMT (TP; 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 za czterdziesty okres spłacimy 945,51 USD z tytułu kwoty głównej z naszej łącznej miesięcznej kwoty 1161,88 USD.
Aby obliczyć spłaconą kwotę główną, używamy następującego wzoru:
= -PPMT (TP; A18; B $ 4 * 12; B $ 3 $) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
Czwarta kolumna to odsetki, dla których używamy wzoru do obliczenia spłacanego kapitału od naszej miesięcznej kwoty, aby dowiedzieć się, ile odsetek do zapłacenia:
= -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 czterdziestej płatności będziemy musieli zapłacić 83 994,69 USD za 120 000 USD.
Wzór wygląda następująco:
= 3 $ B $ + CUMPRINC (TP; B $ 4 $ * 12; B $ 3 $; 1; A18; 0)
Formuła wykorzystuje kombinację kwoty głównej w okresie przed komórką zawierającą pożyczony kapitał. Ten okres zaczyna się zmieniać, gdy kopiujemy i przeciągamy komórkę w dół. Z poniższej tabeli wynika, że na koniec 120 okresów nasza pożyczka jest spłacana.