Utwórz harmonogram amortyzacji pożyczki w Excelu (z dodatkowymi płatnościami w razie potrzeby)

samouczek pokazuje, jak zbudować harmonogram amortyzacji w Excelu, aby szczegółowo określić okresowe płatności na amortyzującą pożyczkę lub hipotekę.

pożyczka amortyzacyjna to tylko wymyślny sposób na określenie kredytu, który jest spłacany w ratach przez cały okres kredytowania.

zasadniczo wszystkie kredyty są amortyzowane w taki czy inny sposób. Na przykład w pełni amortyzujący kredyt na 24 miesiące będzie miał 24 równe miesięczne raty., Każda płatność stosuje pewną kwotę do kwoty głównej, a część do odsetek. Aby szczegółowo określić każdą płatność pożyczki, możesz zbudować harmonogram amortyzacji pożyczki.

harmonogram amortyzacji to tabela, która wymienia okresowe płatności na kredyt lub kredyt hipoteczny w czasie, rozbija każdą płatność na kwotę główną i odsetki oraz pokazuje pozostałe saldo po każdej płatności.,

Jak utworzyć harmonogram amortyzacji pożyczki w Excelu
harmonogram amortyzacji dla zmiennej liczby okresów
harmonogram amortyzacji pożyczki z dodatkowymi płatnościami
szablon amortyzacji Excela

jak utworzyć harmonogram amortyzacji pożyczki w Excelu

aby zbudować harmonogram amortyzacji pożyczki lub kredytu hipotecznego w Excelu, będziemy musieli użyć następujących funkcji:

PMT – oblicza całkowitą kwotę płatności okresowej. Kwota ta pozostaje stała przez cały czas trwania pożyczki.,

funkcja PPMT-pobiera główną część każdej płatności, która trafia na główną pożyczkę, czyli kwotę, którą pożyczyłeś. Kwota ta wzrasta w przypadku kolejnych płatności.

funkcja IPMT-wyszukuje część odsetkową każdej płatności, która trafia na odsetki. Kwota ta maleje wraz z każdą płatnością.

teraz przejdźmy przez proces krok po kroku.,

Skonfiguruj tabelę amortyzacji

na początek zdefiniuj komórki wejściowe, w których będziesz wprowadzał znane składniki kredytu:

C2 – roczna stopa procentowa
C3 – okres kredytowania w latach
C4 – liczba płatności w roku
C5 – kwota kredytu

następną rzeczą, którą zrobisz, jest utworzenie tabeli amortyzacji z etykietami okres, płatność, odsetki, kapitał, saldo) w A7:E7., W kolumnie Period wprowadź szereg liczb równych całkowitej liczbie płatności (1 – 24 w tym przykładzie):

mając wszystkie znane komponenty na miejscu, przejdźmy do najciekawszych form amortyzacji części kredytu.

Oblicz całkowitą kwotę płatności (wzór PMT)

kwota płatności jest obliczana za pomocą funkcji PMT(rate, nper, pv,,).

aby poprawnie obsługiwać różne częstotliwości płatności (np. tygodniowe, miesięczne, kwartalne itp.,), powinieneś być zgodny z wartościami podanymi dla argumentów rate i nper:

Rate-podziel roczną stopę procentową przez liczbę okresów płatności w roku ($C$2/$C$4).
Nper-pomnóż liczbę lat przez liczbę okresów płatności w ciągu roku ($C$3*$C$4).
w argumencie pv wpisz kwotę kredytu ($C$5).
argumenty fv I type można pominąć, ponieważ ich domyślne wartości działają dla nas dobrze (saldo po ostatniej płatności ma wynosić 0; płatności są dokonywane na koniec każdego okresu).,

składając powyższe argumenty razem, otrzymujemy formułę:

=PMT($C$2/$C$4, $C$3*$C$4, $C$5)

proszę zwrócić uwagę, że używamy bezwzględnych odniesień do komórek, ponieważ ta formuła powinna kopiować do poniższych komórek bez żadnych zmian.,

wprowadź formułę PMT w B8, przeciągnij ją w dół kolumny, a zobaczysz stałą kwotę płatności dla wszystkich okresów:

Oblicz odsetki (formuła IPMT)

aby znaleźć część odsetek każdej okresowej płatności, użyj funkcji IPMT(rate, per, nper, pv,,):

=IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

wszystkie argumenty są takie same jak w formule PMT, z wyjątkiem per argument, który określa okres płatności., Argument ten jest dostarczany jako względne odniesienie do komórki (A8), ponieważ ma się zmieniać na podstawie względnej pozycji wiersza, do którego kopiowana jest formuła.,

ta formuła przechodzi do C8, a następnie kopiujesz ją do tylu komórek, ile potrzebujesz:

Znajdź główną (formułę PPMT)

aby obliczyć główną część każdej okresowej płatności, użyj tej formuły PPMT:

=PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5)

składnia i argumenty są dokładnie takie same jak w formule IPMT omówionej powyżej:

ta formuła przechodzi do kolumny d, zaczynając od D8:

tip. Aby sprawdzić, czy twoje obliczenia są poprawne w tym momencie, Dodaj liczby w kolumnach kapitał i odsetki., Suma powinna być równa wartości w kolumnie płatności w tym samym wierszu.

Pobierz pozostałe saldo

aby obliczyć pozostałe saldo dla każdego okresu, będziemy używać dwóch różnych wzorów.

aby znaleźć saldo po pierwszej płatności w E8, dodaj kwotę pożyczki (C5) i kapitał pierwszego okresu (D8):

=C5+D8

ponieważ kwota pożyczki jest liczbą dodatnią, a kapitał jest liczbą ujemną, ta ostatnia jest faktycznie odejmowana od pierwszej.,

dla drugiego i wszystkich kolejnych okresów dodaj poprzednie saldo i główny okres tego okresu:

=E8+D9

powyższa formuła przechodzi do E9, a następnie kopiujesz ją w dół kolumny. Ze względu na zastosowanie względnych odniesień do komórek, formuła dostosowuje się poprawnie dla każdego wiersza.

To jest to! Nasz miesięczny harmonogram amortyzacji kredytu odbywa się:

Wskazówka: Zwrot płatności jako liczby dodatnie

ponieważ pożyczka jest wypłacana z konta bankowego, funkcje Excela zwracają płatność, odsetki i kwotę główną jako liczby ujemne., Domyślnie wartości te są podświetlone na czerwono i umieszczone w nawiasach, jak widać na powyższym obrazku.

Jeśli wolisz mieć wszystkie wyniki jako liczby dodatnie, umieść znak minus przed funkcjami PMT, IPMT i PPMT.

w przypadku formuł salda użyj odejmowania zamiast dodawania, jak pokazano na poniższym zrzucie ekranu:

harmonogram amortyzacji dla zmiennej liczby okresów

w powyższym przykładzie zbudowaliśmy harmonogram amortyzacji pożyczki dla określonej liczby okresów płatności., To szybkie jednorazowe rozwiązanie działa dobrze na konkretny kredyt lub kredyt hipoteczny.

Jeśli chcesz stworzyć harmonogram amortyzacji wielokrotnego użytku ze zmienną liczbą okresów, musisz przyjąć bardziej kompleksowe podejście opisane poniżej.

wprowadź maksymalną liczbę okresów

w kolumnie okres wpisz maksymalną liczbę płatności, które zamierzasz zezwolić na dowolny kredyt, powiedzmy, od 1 do 360. Możesz skorzystać z funkcji automatycznego wypełniania programu Excel, aby szybciej wprowadzać serię liczb.,

użyj IF w formułach amortyzacyjnych

ponieważ masz teraz wiele nadmiernych liczb okresowych, musisz jakoś ograniczyć obliczenia do rzeczywistej liczby płatności dla danego kredytu. Można to zrobić, owijając każdą formułę W instrukcję IF. Test logiczny wyciągu IF sprawdza, czy numer okresu w bieżącym wierszu jest mniejszy lub równy całkowitej liczbie płatności. Jeśli test logiczny jest TRUE, odpowiednia funkcja jest obliczana; jeśli FALSE, zwracany jest pusty łańcuch.,

zakładając, że okres 1 jest w wierszu 8, wprowadź następujące formuły w odpowiednich komórkach, a następnie skopiuj je w całej tabeli.,

Płatność (B8):

=IF(A8<=$C$3*$C$4, PMT($C$2/$C$4, $C$3*$C$4, $C$5), "")

odsetki (C8):

=IF(A8<=$C$3*$C$4, IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

główny (D8):

=IF(A8<=$C$3*$C$4,PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5), "")

Balance:

dla okresu 1 (E8) formuła jest taka sama jak w poprzednim przykładzie:

=C5+D8

dla okresu 2 (E9) i wszystkich kolejnych okresów formuła przyjmuje ten kształt:

=IF(A9<=$C$3*$C$4, E8+D9, "")

w rezultacie masz prawidłowo obliczony harmonogram amortyzacji i kilka pustych wierszy z numerami okresu po spłacie kredytu.,

Ukryj dodatkowe numery okresów

Jeśli możesz żyć z mnóstwem zbędnych numerów okresów wyświetlanych po ostatniej płatności, możesz wziąć pod uwagę wykonaną pracę i pominąć ten krok. Jeśli dążysz do perfekcji, Ukryj wszystkie niewykorzystane okresy, wprowadzając warunkową regułę formatowania, która ustawia kolor czcionki na biały dla dowolnych wierszy po dokonaniu ostatniej płatności.,

w tym celu wybierz wszystkie wiersze danych, jeśli tabela amortyzacji (A8:E367 w naszym przypadku) i kliknij zakładkę Strona główna > Formatowanie warunkowe > Nowa reguła… > użyj formuły, aby określić, które komórki sformatować.

w odpowiednim polu wprowadź poniższą formułę, która sprawdza, czy numer okresu w kolumnie A jest większy niż całkowita liczba płatności:

=$A8>$C$3*$C$4

Ważna uwaga!, Aby formuła formatowania warunkowego działała poprawnie, należy użyć bezwzględnych odniesień do komórek dla okresu pożyczki i płatności rocznie komórek, które mnożysz ($C$3*$C$4). Produkt jest porównywany z okresem 1 komórki, dla którego używa się mieszanego odniesienia komórki-bezwzględnej kolumny i względnego wiersza ($A8).

następnie kliknij przycisk Format… i wybierz biały kolor czcionki. Zrobione!,

dokonaj podsumowania kredytu

aby wyświetlić podsumowanie informacji o pożyczce w skrócie, dodaj kilka innych formuł u góry harmonogramu amortyzacji.

płatności ogółem (F2):

=-SUM(B8:B367)

odsetki ogółem (F3):

=-SUM(C8:C367)

Jeśli masz płatności jako liczby dodatnie, Usuń znak minus z powyższych formuł.

To jest to! Nasz harmonogram amortyzacji kredytu jest zakończony i gotowy do pracy!,

Pobierz harmonogram amortyzacji pożyczki dla programu Excel

jak zrobić harmonogram amortyzacji pożyczki z dodatkowymi płatnościami w programie Excel

omówione w poprzednich przykładach harmonogramy amortyzacji są łatwe do utworzenia i naśladowania (miejmy nadzieję :). Pomijają jednak przydatną funkcję, którą interesuje wielu płatników kredytów – dodatkowe płatności, aby szybciej spłacić kredyt. W tym przykładzie przyjrzymy się, jak stworzyć harmonogram amortyzacji kredytu z dodatkowymi płatnościami.

Zdefiniuj komórki wejściowe

jak zwykle zacznij od ustawienia komórek wejściowych., W tym przypadku, nazwijmy te komórki jak napisane poniżej, aby nasze formuły były łatwiejsze do odczytania:

Interesrate – C2 (roczna stopa procentowa)
LoanTerm – C3 (okres kredytowania w latach)
PaymentsPerYear – C4 (liczba płatności rocznie)
LoanAmount – C5 (całkowita kwota kredytu)
ExtraPayment – C6 (dodatkowa płatność za okres)

Oblicz zaplanowaną płatność

oprócz komórek wejściowych do naszych dalszych obliczeń wymagana jest jeszcze jedna predefiniowana komórka – planowana kwota płatności, tj., kwota do zapłaty od pożyczki, jeśli nie są dokonywane żadne dodatkowe płatności. Kwota ta jest obliczana według następującego wzoru:

=IFERROR(-PMT(InterestRate/PaymentsPerYear, LoanTerm*PaymentsPerYear, LoanAmount), "")

proszę zwrócić uwagę, że przed funkcją PMT umieszczamy znak minus, aby wynik był liczbą dodatnią. Aby zapobiec błędom w przypadku, gdy niektóre komórki wejściowe są puste, załączamy formułę PMT do funkcji IFERROR.

wprowadź tę formułę w jakiejś komórce (w naszym przypadku G2) i nazwij tę komórkę zaplanowaną płatnością.,

Ustaw tabelę amortyzacji

Utwórz tabelę amortyzacji pożyczki z nagłówkami pokazanymi na poniższym zrzucie ekranu. W kolumnie Period wprowadź serię liczb rozpoczynających się od zera (w razie potrzeby możesz ukryć wiersz kropki 0 później).

Jeśli chcesz utworzyć harmonogram amortyzacji wielokrotnego użytku, wprowadź maksymalną możliwą liczbę okresów płatności (w tym przykładzie od 0 do 360).

dla okresu 0 (w naszym przypadku wiersz 9), pociągnij wartość salda, która jest równa pierwotnej kwocie pożyczki., Wszystkie inne komórki w tym wierszu pozostaną puste:

formuła W G9:

=LoanAmount

4. Tworzenie formuł harmonogramu amortyzacji z dodatkowymi płatnościami

jest to kluczowa część naszej pracy. Ponieważ wbudowane funkcje Excela nie zapewniają dodatkowych płatności, będziemy musieli samodzielnie wykonać wszystkie obliczenia.

Uwaga. W tym przykładzie kropka 0 jest w wierszu 9, A kropka 1 jest w wierszu 10. Jeśli tabela amortyzacji zaczyna się w innym wierszu, należy odpowiednio dostosować odwołania do komórek.,

wprowadź następujące formuły w wierszu 10( okres 1), a następnie skopiuj je dla wszystkich pozostałych okresów.

zaplanowana Płatność (B10):

Jeśli zaplanowana kwota płatności (o nazwie komórka G2) jest mniejsza lub równa pozostałemu saldowi (G9), użyj zaplanowanej płatności. W przeciwnym razie należy dodać pozostałe saldo i odsetki za poprzedni miesiąc.

=IFERROR(IF(ScheduledPayment<=G9, ScheduledPayment, G9+G9*InterestRate/PaymentsPerYear), "")

jako dodatkowy środek ostrożności zawijamy tę i wszystkie kolejne formuły w funkcji IFERROR., Zapobiegnie to wielu różnym błędom, jeśli niektóre komórki wejściowe są puste lub zawierają nieprawidłowe wartości.

dodatkowa płatność (C10):

użyj formuły IF z następującą logiką:

Jeśli kwota dopłaty (nazwana komórką C6) jest mniejsza niż różnica między pozostałym Saldem a kwotą główną tego okresu (G9-E10), zwróć dopłatę; w przeciwnym razie użyj różnicy.

=IFERROR(IF(ExtraPayment<G9-E10, ExtraPayment, G9-E10), "")

Tip. Jeśli masz zmienne dodatkowe płatności, po prostu wpisz poszczególne kwoty bezpośrednio w kolumnie dodatkowa płatność.,

całkowita płatność (D10)

Po prostu dodaj zaplanowaną płatność (B10) i dodatkową płatność (C10) za bieżący okres:

=IFERROR(B10+C10, "")

Główna (E10)

jeśli planowana płatność za dany okres jest większa niż zero, zwróć mniejszą z dwóch wartości: zaplanowana płatność minus odsetki (B10-F10) lub pozostałego salda (G9); w przeciwnym razie zwraca zero.

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

należy pamiętać, że kwota główna obejmuje tylko część zaplanowanej płatności (a nie dodatkową płatność!), która idzie w kierunku kapitału pożyczkowego.,

odsetki (F10)

Jeżeli płatność według harmonogramu za dany okres jest większa niż zero, należy podzielić roczną stopę procentową (o nazwie komórka C2) przez liczbę płatności rocznie (o nazwie komórka C4) i pomnożyć wynik przez saldo pozostałe po poprzednim okresie; w przeciwnym razie należy zwrócić 0.

=IFERROR(IF(B10>0, InterestRate/PaymentsPerYear*G9, 0), "")

saldo (G10)

Jeśli pozostałe saldo (G9) jest większe niż zero, należy odjąć główną część płatności (E10) i dodatkową płatność (C10) od salda pozostałego po poprzednim okresie (G9); w przeciwnym razie należy zwrócić 0.,

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

Uwaga. Ponieważ niektóre z formuł odwołują się do siebie (nie do kołowego odniesienia!), mogą wyświetlać błędne wyniki w procesie. Więc nie zaczynaj rozwiązywania problemów, dopóki nie wprowadzisz ostatniej formuły w tabeli amortyzacji.

Jeśli wszystko jest zrobione poprawnie, twój harmonogram amortyzacji pożyczki w tym momencie powinien wyglądać mniej więcej tak:

Ukryj dodatkowe okresy

Ustaw regułę formatowania warunkowego, aby ukryć wartości w niewykorzystanych okresach, jak wyjaśniono w tej poradzie.,płatności na rok:

=LoanTerm*PaymentsPerYear

rzeczywista liczba płatności:

Policz komórki w kolumnie płatności ogółem, które są większe niż zero, zaczynając od okresu 1:

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

suma dodatkowych płatności:

Dodaj komórki w kolumnie płatności dodatkowych, począwszy od okresu 1:

=SUM(C10:C369)

odsetki ogółem:

dodaj komórki w kolumnie odsetek, zaczynając od okresu 1:

=SUM(F10:F369)

Opcjonalnie Ukryj wiersz okresu 0, a harmonogram amortyzacji kredytu z dodatkowymi płatnościami zostanie zakończony!, Poniższy zrzut ekranu pokazuje ostateczny wynik:

Pobierz harmonogram amortyzacji pożyczki z dodatkowymi płatnościami

harmonogram amortyzacji Szablon programu Excel

aby błyskawicznie sporządzić najwyższej klasy harmonogram amortyzacji pożyczki, skorzystaj z wbudowanych szablonów programu Excel. Po prostu przejdź do pliku >Nowy, wpisz” harmonogram amortyzacji”w polu wyszukiwania i wybierz szablon, który Ci się podoba, na przykład ten z dodatkowymi płatnościami:

w ten sposób tworzysz harmonogram amortyzacji pożyczki lub kredytu hipotecznego w programie Excel., Dziękuję za przeczytanie i mam nadzieję, że do zobaczenia na naszym blogu w przyszłym tygodniu!

dostępne pliki do pobrania

harmonogram amortyzacji przykładowy skoroszyt

Jak obliczyć odsetki złożone w Excelu
Jak znaleźć CAGR (compound annual growth rate) w Excelu
Obliczanie procentu w Excelu z przykładami formuł

jak zrobić harmonogram amortyzacji pożyczki w Excelu (z dodatkowymi płatnościami)