Jak utworzyć własny kalkulator amortyzacji pożyczki

Większość ludzi uważa, że ​​istnieje magia voodoo, która idzie w kierunku utworzenia pożyczki harmonogram amortyzacji . Ale cieszę się, że mogę powiedzieć, że nie ma - i nawet lepiej - nawet początkujący może go łatwo stworzyć za pomocą Microsoft Excel.

Tabela amortyzacji pożyczki daje wiele dobrych informacji: całkowitą kwotę do zapłacenia pożyczkodawcy, część, która jest odsetkami, część, która jest główną płatnością i saldo pożyczki pozostające do spłaty.

Kiedy kredyt jest amortyzowany, oznacza to po prostu, że płatność odsetek i główna płatność są połączone w jedną okresową płatność, a kwoty płatności są równe dla każdego okresu płatności. Ale jedną z najciekawszych cech zamortyzowanej pożyczki jest to, że przy każdej płatności zmienia się wysokość odsetek i kwoty głównej.

Excel jest niezwykle przydatnym narzędziem do obliczania własnej tabeli amortyzacji. Możesz nawet eksperymentować z różnymi danymi wejściowymi, aby zobaczyć, w jaki sposób różne stopy procentowe, okresy spłaty i kwoty pożyczki głównej wpływają na spłatę kredytu.

Spójrzmy na przykład ilustrujący proces.

Załóżmy, że właśnie kupiłeś samochód z pożyczką w wysokości 20 000 $, która musi zostać spłacona w ciągu 4 lat. Roczna stopa oprocentowania pożyczki wynosi 3%. Ponieważ pożyczka jest amortyzowana, miesięczne płatności będą równe tej samej kwocie.

Pierwszym krokiem jest obliczenie miesięcznej kwoty płatności. Można to zrobić za pomocą funkcji PMT (co oznacza "płatność") w programie Excel.

Zacznij od utworzenia nowego arkusza kalkulacyjnego i wprowadzenia następujących danych potrzebnych do obliczenia kwoty płatności.

Kolumna A będzie zawierała następujące etykiety dla naszych danych:

A1 = pierwotna kwota pożyczki (Twoja kwota pożyczki)

A2 = okres pożyczki w latach

A3 = roczna stopa oprocentowania

A4 = liczba płatności rocznie

A5 = kwota płatności (co obliczamy)

Kolumna B będzie zawierają odpowiednie wartości dla każdej etykiety w kolumnie A:

B1 = 20 000 USD

B2 = 4

B3 = 3%

B4 = 12

B5 = wartość, której szukamy

Funkcja PMT programu Excel znajduje się w menu funkcji finansowych. Najpierw wybierz komórkę B5, następnie kliknij menu funkcji finansowych pod zakładką Formuły i wybierz funkcję PMT z listy. Zostanie wyświetlone okno, w którym należy wprowadzić dane z arkusza kalkulacyjnego, tak jak na tym obrazku:

Jest tu kilka ważnych rzeczy do zapamiętania. Najpierw musimy dokonać dwóch ważnych korekt, ponieważ obliczamy miesięcznie płatności. Musimy podzielić naszą roczną stopę na 12, aby uzyskać miesięczną stopę procentową (stawkę), a my musimy pomnożyć 4 przez 12, aby uzyskać całkowitą liczbę miesięcy w naszym okresie spłaty (Nper).

Po drugie, wartość obecna (PV) jest wprowadzana jako wartość ujemna, ponieważ kwota ta jest należna pożyczkodawcy.

Po wypełnieniu pól, naciśnij "OK".

Twój arkusz kalkulacyjny powinien teraz wyglądać następująco:

Teraz zaczniemy budować tabelę amortyzacyjną.

Pomiń wiersz poniżej arkusza kalkulacyjnego wykonanego w części 1, a zaczynając od wiersza 7, wykonaj następujące czynności etykiety zaczynające się od kolumny A i kończące się na kolumnie E:

A7 = miesiąc

B7 = Płatność

C7 = Odsetki

D7 = Zleceniodawca

E7 = Saldo

Są to pięć nagłówków tabeli amortyzacji.

Teraz czas zacząć wypełniać tabelę. Pierwszym krokiem jest wpisanie liczby miesięcy w okresie trwania pożyczki. Pod nagłówkiem "miesiąc" wpisz "0" w komórce A8 (czas wydania pożyczki). Wypełnij kolejne komórki w kolejności od liczby 48 (która przeniesie Cię do komórki A56). Jedna sztuczka: po wprowadzeniu 0, upewnij się, że komórka jest wybrana (A8), a następnie przeciągnij prawy dolny róg komórki do linii 56. Następnie wybierz "wypełnij serię" z małego pola po prawej stronie ostatnia komórka. Powinno to automatycznie wypełnić serię liczbową przez 48 miesięcy.

Następnie wszystkie płatności można wypełnić, ponieważ wartość każdej miesięcznej płatności została już ustalona. Począwszy od pierwszego miesiąca (nie ma płatności w miesiącu 0, ponieważ płatności odbywają się pod koniec każdego miesiąca), należy wpisać 442,69 USD za każdy miesiąc 1-48. Następnie kwoty odsetek i kwoty głównej można obliczyć za pomocą odpowiednich funkcji programu Excel.

Obliczanie odsetek i głównych składników płatności za pomocą funkcji Excel jest proste i można je wykorzystać do wypełnienia całej tabeli amortyzacji. Aby obliczyć kwotę odsetek, wybierz komórkę C9, a następnie kliknij menu funkcji finansowych w zakładce formuły. Wybierz z listy funkcję IPMT (dla "wypłaty odsetek"). Zostanie wyświetlone pole, w którym należy wprowadzić dane z arkusza kalkulacyjnego, tak jak na tym obrazku:

Uwaga: Znaki "$" między literami i numerami komórek są niezbędne, gdy kopiujemy formuły w dół do pozostałych komórek później. "$" Blokuje wskazaną komórkę, więc formuły pozostają nienaruszone, gdy skopiujemy je do pozostałych komórek. Możesz zablokować komórki (i zobaczyć "$"), naciskając F4.

Następnie możemy obliczyć kwotę kapitału w płatności. Podobnie jak w obliczeniach IPMT, wybierz komórkę D9, a następnie kliknij menu funkcji finansowych w zakładce formuły. Wybierz z listy PPMT (dla funkcji "główna płatność") Wyświetli się pole, w którym należy wprowadzić dane z arkusza kalkulacyjnego, tak jak na tym obrazku:

Uwaga: Główny oblicza się za pomocą tej funkcji lub po prostu odejmując kwotę odsetek od całkowitej kwoty płatności, podobnie, odsetki można obliczyć, jeśli najpierw obliczymy kwotę główną za pomocą funkcji PPMT, a następnie odejmiemy tę wartość od całkowitej kwoty płatności. wiedzieć, jak używać obu formuł, co pozwala również na bardziej efektywne wypełnianie reszty tabeli, jak widać poniżej.

Ostatnia kolumna (Kolumna E) jest kolumną bilansu, która może być łatwo obliczona dla w każdym okresie, w którym ustaliliśmy kwoty odsetek i kwoty głównej, dla miesiąca 0 nie dokonano jeszcze żadnych płatności, więc saldo wynosi 20 000 USD, jednak chcemy polecić programowi Excel automatyczne obliczanie salda w każdym okresie, więc zamiast manu sojusznik wpisując "20 000 $", wprowadź "= B1", odnosząc się do powyższej tabeli do obliczenia płatności. W przypadku miesiąca 1, ponieważ saldo jest obliczane poprzez odjęcie salda poprzedniego miesiąca przez główną kwotę bieżącego miesiąca, wpisz "= E8-D9" w komórce E9. Saldo zostanie automatycznie obliczone. Twoja tabela amortyzacji jest teraz ukończona dla miesiąca 1 i powinna wyglądać dokładnie tak:

Wszystkie dane dla pozostałych miesięcy można wypełnić, po prostu wybierając komórki B9-E9, kopiując zaznaczenie, a następnie wklejając zaznaczenie do komórek B10-E10. Ponieważ wprowadziliśmy formuły dla każdej komórki i zablokowaliśmy nasze komórki, które będą używane w każdym obliczeniu, wartości odsetek, kapitału i salda są obliczane automatycznie. Widząc tę ​​pracę za miesiąc 2, całą tabelę można ukończyć, po prostu wybierając kolumny B-E do 48 miesiąca i wklejając już skopiowane informacje. Program Excel obliczył teraz każdą wartość dla tabeli amortyzacji, korzystając z wprowadzonych przez nas formuł. Wypełniony formularz powinien wyglądać następująco:

Pierwszy rok pożyczki:

Ostatni rok pożyczki:

Uwaga: Te tabele zawierają tylko pierwsze i ostatnie lata tabela amortyzacji. Lata 2 i 3 byłyby pomiędzy tymi dwoma tabelami tworzącymi ciągły tabelę amortyzacji dla wszystkich 48 miesięcy.

Jeśli chcesz obliczyć spłatę hipoteki za hipotekę o stałym oprocentowaniu, możesz sam dokonać tabeli amortyzacji, wykonując te kroki, lub możesz skorzystać z naszego kalkulatora finansowego, aby zrobić to za Ciebie. Nasz kalkulator kredytu hipotecznego pokaże Ci miesięczną opłatę oraz pełny harmonogram amortyzacji.