Jak sporządzić harmonogram spłat pożyczki w programie Excel (z dodatkowymi płatnościami)

Jak sporządzić harmonogram spłat pożyczki w programie Excel (z dodatkowymi płatnościami)
Category: Jeśli Możesz
16 października 2020

Samouczek pokazuje, jak utworzyć harmonogram spłat w programie Excel, aby szczegółowo określić okresowe płatności amortyzowanej pożyczki lub kredytu hipotecznego.

Pożyczka amortyzacji to tylko sposób fantazyjny zdefiniować pożyczkę, która jest wypłacana z powrotem w ratach przez cały okres kredytowania.

Zasadniczo wszystkie pożyczki podlegają amortyzacji w taki czy inny sposób. Na przykład, w pełni amortyzowana pożyczka na 24 miesiące będzie miała 24 równe miesięczne raty. Każda płatność obejmuje część kwoty głównej, a część odsetek. Aby szczegółowo opisać każdą spłatę pożyczki, możesz utworzyć harmonogram spłaty pożyczki.

Harmonogram amortyzacji to tabela wymienia okresowe na pożyczkę lub kredyt hipoteczny w czasie, rozbija każdej wpłaty na kapitał i odsetki, i pokazuje pozostałego salda po każdej płatności.

Jak stworzyć harmonogram spłat kredytu w Excelu

Aby zbudować harmonogram spłat kredytu lub spłaty kredytu hipotecznego w Excelu, będziemy musieli skorzystać z następujących funkcji:

  • Funkcja PMT – oblicza całkowitą kwotę okresowej płatności. Kwota ta pozostaje stała przez cały okres trwania pożyczki.
  • Funkcja PPMT – pobiera główną część każdej płatności, która trafia na kapitał pożyczki, czyli pożyczoną kwotę. Kwota ta wzrasta w przypadku kolejnych płatności.
  • Funkcja IPMT – znajduje część odsetkową każdej płatności zaliczanej na poczet odsetek. Kwota ta zmniejsza się z każdą płatnością.

Teraz przejdźmy przez ten proces krok po kroku.

1. Skonfiguruj tabelę amortyzacji

Na początek zdefiniuj komórki wejściowe, w których wprowadzisz znane składniki pożyczki:

  • C2 – roczna stopa procentowa
  • C3 – okres kredytowania w latach
  • C4 – liczba płatności w ciągu roku
  • C5 – kwota pożyczki

Następną rzeczą, którą musisz zrobić, jest utworzenie tabeli amortyzacji z etykietami ( Okres , Płatność , Odsetki , Kwota główna , Saldo ) w A7: E7. W kolumnie Okres wprowadź ciąg liczb równy łącznej liczbie płatności (w tym przykładzie 1-24):

Mając na miejscu wszystkie znane elementy, przejdźmy do najciekawszej części – formuł amortyzacji kredytu.

2. Oblicz całkowitą kwotę płatności (formuła PMT)

Kwota płatności jest obliczana za pomocą funkcji PMT (rate, nper, pv, [fv], [type]).

Aby poprawnie obsługiwać różne częstotliwości płatności (np. Co tydzień, co miesiąc, co kwartał itp.), Należy zachować zgodność z wartościami podanymi dla argumentów stopa i liczba_okresów :

  • Stopa – podziel roczną stopę procentową przez liczbę okresów płatności w roku (2 CAD / 4 CAD).
  • Liczba_okresów – pomnóż liczbę lat przez liczbę okresów płatności w roku (3 C $ * 4 C $).
  • Jako argument pv wprowadź kwotę pożyczki (5 C $).
  • Argumenty fv i type można pominąć, ponieważ ich wartości domyślne działają dla nas dobrze (saldo po ostatniej płatności powinno wynosić 0; płatności są dokonywane na koniec każdego okresu).

Łącząc powyższe argumenty, otrzymujemy następujący wzór:

Zwróć uwagę, że używamy bezwzględnych odwołań do komórek, ponieważ ta formuła powinna zostać skopiowana 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:

3. Oblicz odsetki (formuła IPMT)

Aby znaleźć część odsetkową każdej płatności okresowej, użyj funkcji IPMT (rate, per, nper, pv, [fv], [type]):

Wszystkie argumenty są takie same, jak we wzorze PMT, z wyjątkiem argumentu per, który określa okres płatności. Ten argument jest podawany jako względne odwołanie do komórki (A8), ponieważ ma się on zmieniać w zależności od względnego położenia wiersza, do którego kopiowana jest formuła.

Ta formuła trafia do C8, a następnie kopiujesz ją do dowolnej liczby komórek:

4. Znajdź zleceniodawcę (formuła PPMT)

Aby obliczyć część główną każdej płatności okresowej, użyj następującego wzoru PPMT:

Składnia i argumenty są dokładnie takie same, jak we wzorze IPMT omówionym powyżej:

Ta formuła przechodzi do kolumny D, zaczynając od D8:

5. Zdobądź pozostałe saldo

Aby obliczyć pozostałe saldo dla każdego okresu, użyjemy dwóch różnych formuł.

Aby znaleźć saldo po pierwszej płatności w E8, zsumuj kwotę pożyczki (C5) i kwotę główną pierwszego okresu (D8):

Ponieważ kwota pożyczki jest liczbą dodatnią, a kwota główna jest liczbą ujemną, ta ostatnia jest faktycznie odejmowana od pierwszej.

Dla drugiego i wszystkich kolejnych okresów zsumuj poprzednie saldo i kwotę główną tego okresu:

Powyższa formuła trafia do E9, a następnie kopiujesz ją w dół kolumny. Ze względu na użycie względnych odwołań do komórek formuła dostosowuje się poprawnie dla każdego wiersza.

Otóż ​​to! Nasz miesięczny harmonogram spłaty pożyczki odbywa się:

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

Ponieważ pożyczka jest wypłacana z konta bankowego, funkcje programu Excel zwracają płatność, odsetki i kapitał jako liczby ujemne . Domyślnie te wartości są podświetlone na czerwono i umieszczone w nawiasach, jak widać na powyższym obrazku.

Jeśli wolisz, aby wszystkie wyniki były liczbami dodatnimi , 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 na zmienną liczbę okresów

W powyższym przykładzie zbudowaliśmy harmonogram spłat kredytu dla określonej liczby okresów spłaty. To szybkie jednorazowe rozwiązanie sprawdza się dobrze w przypadku określonej pożyczki lub kredytu hipotecznego.

Jeśli chcesz utworzyć harmonogram amortyzacji wielokrotnego użytku ze zmienną liczbą okresów, będziesz musiał zastosować bardziej kompleksowe podejście opisane poniżej.

1. Wprowadź maksymalną liczbę okresów

W kolumnie Okres wprowadź maksymalną liczbę płatności, na jaką zamierzasz zezwolić na jakąkolwiek pożyczkę, powiedzmy od 1 do 360. Możesz skorzystać z funkcji automatycznego wypełniania programu Excel, aby szybciej wprowadzić serię liczb.

2. Używać instrukcji IF we wzorach amortyzacji

Ponieważ masz teraz wiele nadmiernych liczb okresów, musisz w jakiś sposób ograniczyć obliczenia do faktycznej liczby spłat danej pożyczki. Można to zrobić, opakowując każdą formułę w instrukcję IF. Test logiczny wyciągu FI sprawdza, czy numer okresu w bieżącym wierszu jest mniejszy lub równy całkowitej liczbie płatności. Jeśli test logiczny ma wartość TRUE, obliczana jest odpowiednia funkcja; jeśli FALSE, zwracany jest pusty ciąg.

Zakładając, że okres 1 znajduje się 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):

Odsetki (C8):

Główny (D8):

Saldo :

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

Dla okresu 2 (E9) i wszystkich kolejnych okresów formuła przyjmuje następujący kształt:

W efekcie masz poprawnie obliczony harmonogram spłat i kilka pustych wierszy z numerami okresów po spłacie kredytu.

3. Ukryj dodatkowe okresy

Jeśli możesz żyć z wieloma zbędnymi liczbami okresów wyświetlanymi po ostatniej płatności, możesz rozważyć wykonaną pracę i pominąć ten krok. Jeśli dążysz do perfekcji, ukryj wszystkie nieużywane okresy, tworząc warunkową regułę formatowania, która ustawia kolor czcionki na biały dla wszystkich wierszy po dokonaniu ostatniej płatności.

W tym celu zaznacz wszystkie wiersze danych , jeśli tabela amortyzacji (A8: E360 w naszym przypadku) i kliknij Start kartę> Formatowanie warunkowe > Nowa reguła … > Użyj formuły do określenia, które komórki do formatu .

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

Następnie kliknij przycisk Format… i wybierz biały kolor czcionki. Gotowy!

4. Zrób podsumowanie pożyczki

Aby szybko przejrzeć podsumowanie informacji o pożyczce, dodaj kilka dodatkowych formuł u góry harmonogramu spłat.

Całkowite płatności (F2):

Odsetki ogółem (F3):

Jeśli masz płatności jako liczby dodatnie, usuń znak minus z powyższych wzorów.

Otóż ​​to! Nasz harmonogram spłaty pożyczki jest zakończony i gotowy do pracy!

Jak sporządzić harmonogram spłat pożyczki z dodatkowymi płatnościami w programie Excel

Harmonogramy amortyzacji omówione w poprzednich przykładach są łatwe do stworzenia i przestrzegania (miejmy nadzieję). Pomijają jednak przydatną funkcję, którą interesuje się wielu pożyczkodawców – dodatkowe płatności, aby szybciej spłacić pożyczkę. W tym przykładzie przyjrzymy się, jak utworzyć harmonogram spłat kredytu z dodatkowymi płatnościami.

1. Zdefiniuj komórki wejściowe

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

  • InterestRate – C2 (roczna procentowej)
  • LoanTerm – C3 (okres kredytowania w latach)
  • PaymentsPerYear – C4 (liczba płatności rocznie)
  • Kwota pożyczki – C5 (całkowita kwota pożyczki)
  • ExtraPayment – C6 (dodatkowa płatność za okres)

2. Oblicz zaplanowaną płatność

Oprócz komórek wejściowych do dalszych obliczeń potrzebna jest jeszcze jedna wstępnie zdefiniowana komórka – kwota planowanej spłaty , czyli kwota do spłaty pożyczki w przypadku braku dopłat. Kwota ta jest obliczana według następującego wzoru:

= JEŻELI BŁĄD (-PMT (stopa procentowa / płatności na rok, okres pożyczki * płatności na rok, kwota pożyczki); „”)

Zwróć uwagę, że przed funkcją PMT umieściliśmy znak minus, aby wynik był liczbą dodatnią. Aby zapobiec błędom w przypadku, gdy niektóre komórki wejściowe są puste, umieszczamy formułę PMT w funkcji JEŻELI.BŁĄD.

Wprowadź tę formułę w jakiejś komórce (w naszym przypadku G2) i nazwij tę komórkę ScheduledPayment .

3. Skonfiguruj tabelę amortyzacji

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

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).

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

4. Zbuduj formuły harmonogramu spłat z dopłatami

To kluczowa część naszej pracy. Ponieważ wbudowane funkcje programu Excel nie przewidują dodatkowych opłat, będziemy musieli wykonać wszystkie obliczenia samodzielnie.

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 ScheduledPayment ilość (o nazwie G2 komórki) jest mniejsza lub równa pozostałej bilansu (G9), użyj zaplanowanej płatności. W przeciwnym razie dodaj pozostałe saldo i odsetki za poprzedni miesiąc.

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

Dodatkowa płatność (C10):

Użyj formuły JEŻELI z następującą logiką:

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

Całkowita płatność (D10)

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

Główny (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łe saldo (G9); w przeciwnym razie zwraca zero.

= JEŻELI BŁĄD (JEŻELI (B10> 0; MIN (B10-F10; G9); 0); „”)

Należy pamiętać, że kwota główna obejmuje tylko część zaplanowanej spłaty (a nie dodatkową płatność!), Która idzie na poczet kwoty głównej pożyczki.

Odsetki (F10)

Jeśli planowana płatność za dany okres jest większa od zera, podziel roczną stopę procentową (nazwana komórka C2) przez liczbę płatności rocznie (nazwana komórka C4) i pomnóż wynik przez saldo pozostałe po poprzednim okresie; w przeciwnym razie zwraca 0.

= JEŻELI BŁĄD (JEŻELI (B10> 0; Stopa procentowa / Płatności za rok * G9; 0); „”)

Równowaga (G10)

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

= JEŻELI BŁĄD (JEŻELI (G9> 0; G9-E10-C10; 0); „”)

Jeśli wszystko zostanie wykonane poprawnie, Twój harmonogram spłaty pożyczki w tym momencie powinien wyglądać mniej więcej tak:

5. Ukryj dodatkowe okresy

Skonfiguruj regułę formatowania warunkowego, aby ukryć wartości w nieużywanych okresach, jak wyjaśniono w tej wskazówce. Różnica polega na tym, że tym razem stosujemy biały kolor czcionki do wierszy, w których suma płatności (kolumna D) i saldo (kolumna G) są równe zero lub puste:

Voil, wszystkie wiersze z zerowymi wartościami są ukryte:

6. Zrób podsumowanie pożyczki

Na koniec możesz przedstawić najważniejsze informacje o pożyczce, korzystając z następujących wzorów:

Planowana liczba płatności:

Pomnóż liczbę lat przez liczbę płatności w roku:

Rzeczywista liczba płatności:

Policz komórki w kolumnie Całkowita płatność, które są większe od zera, zaczynając od okresu 1:

Suma dodatkowych płatności:

Dodaj komórki w kolumnie Dodatkowa płatność , zaczynając od Okresu 1:

Całkowite odsetki:

Dodaj komórki w kolumnie Odsetki , zaczynając od Okresu 1:

Opcjonalnie ukryj wiersz Okres 0 , a harmonogram spłaty pożyczki z dodatkowymi płatnościami zostanie wykonany! Poniższy zrzut ekranu przedstawia ostateczny wynik:

Harmonogram amortyzacji Szablon programu Excel

Aby w mgnieniu oka opracować najlepszy harmonogram spłat pożyczki, skorzystaj z wbudowanych szablonów programu Excel. Po prostu przejdź do Plik > Nowy , wpisz „ harmonogram spłat” 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 spłat kredytu lub spłaty kredytu hipotecznego w Excelu. Dziękuję za przeczytanie i do zobaczenia na naszym blogu w przyszłym tygodniu!

We use cookies to provide you with the best possible experience. By continuing, we will assume that you agree to our cookie policy