Excel Kalkulator hipoteczny (spis treści)

  • Przegląd kalkulatora hipotecznego Excel
  • Jak obliczyć miesięczne płatności za pożyczkę w programie Excel?

Przegląd kalkulatora hipotecznego Excel

Wszyscy bierzemy kredyty hipoteczne / pożyczki na nasze potrzeby. Może to być na zakup domu, pożyczki na samochód, pożyczki osobiste itp. Bierzemy pożyczkę długoterminową na okres do 5, 10 lub nawet 20 lat.

Musimy spłacać te pożyczki w miesięcznych ratach. Obejmuje to odsetki i część podstawowych pieniędzy w uzgodnionym okresie. Część zasadniczych płatności powoli zmniejsza saldo kredytu, ostatecznie do 0. W przypadku dokonania dodatkowych zasadniczych płatności pozostałe saldo zmniejszy się szybciej niż okres kredytowania. Pożyczkodawca, zwykle Banki lub inne instytucje finansowe, bierze trzy elementy i używa go we wzorze, aby obliczyć miesięczną płatność. Te trzy kluczowe elementy to:

  1. Zasada (kwota pożyczki)
  2. Oprocentowanie
  3. Okres (liczba lat lub miesięcy, na które pożyczyłeś pożyczkę)

Te elementy są używane we wzorach do obliczania miesięcznych płatności za spłatę pożyczki. Obliczenia te wydają się kłopotliwe dla laika.

Za pomocą programu Excel możesz utworzyć arkusz kalkulacyjny i samodzielnie obliczyć miesięczne płatności.

Jak obliczyć miesięczne płatności za pożyczkę w programie Excel?

Możemy obliczyć miesięczne raty kredytu / pożyczki za pomocą wbudowanych funkcji, takich jak PMT i innych funkcji, takich jak IPMT i PPMT .

Możesz pobrać ten szablon kalkulatora hipotecznego Excel tutaj - Szablon kalkulatora hipotecznego Excel

Funkcja PMT służy do obliczania miesięcznych płatności dokonywanych w celu spłaty pożyczki lub kredytu hipotecznego.

= PMT (stopa, nper, pv)

Funkcja PMT wymaga 3 elementów do obliczenia płatności miesięcznych:

  • RATE - oprocentowanie pożyczki. Jeśli stawka wynosi 4% rocznie, miesięcznie będzie to 4/12, co stanowi 0, 33% procent miesięcznie.
  • NPER - liczba okresów spłaty kredytu. Przykład - przez 5 lat mamy 60 okresów miesięcznych.
  • PV - bieżąca wartość pożyczki. Jest to kwota pożyczona.

Istnieją jednak inne opcjonalne elementy, które w razie potrzeby można wykorzystać do określonych obliczeń. Oni są:

  • FV - przyszła wartość inwestycji po dokonaniu wszystkich płatności okresowych. Zazwyczaj jest to 0.
  • TYP - „0” lub „1” służą do ustalenia, czy płatność ma być dokonana na początku czy na końcu miesiąca.

Jak użyć formuły, aby uzyskać miesięczną kwotę płatności?

Teraz nauczymy się, jak korzystać z funkcji PMT do obliczania miesięcznej płatności. Weźmy przykład, aby zrozumieć, jak działa ta funkcja.

Przykład 1

Supp + ose wzięliśmy kredyt mieszkaniowy na kwotę 2 000 000 USD na 10 lat przy oprocentowaniu 6%. Zróbmy tabelę w Excelu, jak poniżej.

Teraz, aby obliczyć miesięczną płatność, wprowadzimy wszystkie punkty danych w funkcji, jak poniżej:

W komórce C8 zaczniemy pisać formułę, naciskając =, a następnie pisząc PMT. Następnie wprowadzimy punkty danych zgodnie ze składnią. Należy pamiętać, że ponieważ nasza pożyczka opiera się na płatnościach miesięcznych, musimy podzielić stopę procentową przez 12 i pomnożyć liczbę lat przez 12 (aby dać nam całkowitą liczbę płatności miesięcznych).

Stąd stawka 6% będzie wynosić 0, 5% (6% / 12) miesięcznie, a okres czasu będzie wynosił 120 okresów miesięcznych. pv wyniesie 200000, kwota pożyczona. Fv i typ są w tym przypadku opcjonalne, więc je zostawimy. Po wprowadzeniu danych do formuły naciśnij klawisz Enter. Zobaczymy poniższy wynik.

W przypadku pożyczki w wysokości 200000 USD przy oprocentowaniu 6% na 10 lat miesięczna rata wyniesie 2 220, 41 USD

W ten sposób obliczamy miesięczne płatności za pomocą funkcji PMT w Excelu. Ta miesięczna płatność obejmuje również część kwoty podstawowej i odsetki. Cóż, jeśli chcemy poznać kwotę zasady i wysokość odsetek zawartych w tej miesięcznej płatności, możemy to zrobić. W tym celu mamy dwie inne funkcje, którymi są PPMT i IPMT .

Funkcja PPMT służy do obliczania podstawowej części płatności, a funkcja IPMT służy do obliczania części odsetkowej płatności. Teraz zobaczymy, jak korzystać z tych funkcji, aby poznać skład miesięcznej płatności.

Biorąc powyższy przykład, znajdziemy teraz PPMT i IPMT. Napiszemy Numer płatności w komórce B8, Miesięczna płatność w C8, zasada w D8 i Odsetki w E8. W komórce B9 pod nagłówkiem Nr płatności napiszemy 1, jak w przypadku pierwszej płatności.

Teraz w komórce C9 obliczymy miesięczną płatność za pomocą funkcji PMT.

Aby obliczyć kwotę podstawową w płatności miesięcznej, użyjemy funkcji PPMT. Napiszemy funkcję w komórce D9, jak pokazano poniżej.

W funkcji PPMT wprowadzimy dane zgodnie ze składnią. Stawka wyniesie 6% / 12, aby uzyskać miesięczną stopę procentową. Następnie w „ per ” napiszemy numer płatności, który w tym przypadku wynosi 1. Potem czas (nper) 10 lat * 12 na przekształcenie go w nie. miesięcy i wreszcie kwota podstawowa (pv).

Naciśnij Enter, aby uzyskać PPMT.

Na koniec obliczymy część odsetkową w miesięcznej płatności według funkcji IPMT w komórce E9.

Napiszemy = IPMT w komórce E9 i wprowadzimy dane w taki sam sposób, jak w funkcji PPMT. Naciśnij Enter, a my dostaniemy IPMT.

To pokazuje, że przy miesięcznej płatności w wysokości 2 220, 41 USD podstawową częścią jest 1 220, 41 USD, a 1000 USD to odsetki. Aby uzyskać większą przejrzystość wszystkich wyżej omawianych funkcji, oto kolejny przykład.

Przykład nr 2

Mark zaciągnął kredyt samochodowy na 50 000 USD na 4% na 3 lata. Stworzymy tabelę w Excelu, jak poniżej:

Mamy więc dwie tabele, mniejsza pokazuje miesięczną płatność PMT (komórka I3). Większa tabela pokazuje łącznie 36 płatności kwoty pożyczki, która reprezentuje zarówno zasadę, jak i odsetki.

Przede wszystkim obliczymy PMT w komórce I3, jak pokazano poniżej:

Teraz obliczymy PPMT w komórce G10.

Naciśnij Enter, aby uzyskać PPMT.

Teraz obliczymy IPMT w komórce H10 jako:

Tak więc teraz otrzymujemy 1309, 53 USD jako PPMT i 166, 67 USD jako IPMT, co zwiększy się do kwoty 1476, 20 USD (miesięczna płatność). Aby wyświetlić wszystkie płatności, nadamy wartościom dynamicznym zarówno w funkcji PPMT, jak i IPMT, jak pokazano poniżej.

Funkcja IPMT pokazano poniżej.

Teraz przeciągniemy PPMT (G10) i IPMT (H10) w dół do ostatniej płatności (36 tys .).

Widzimy, że wraz ze wzrostem liczby płatności część zasady rośnie, a odsetek maleje.

W ten sposób możemy stworzyć kalkulator hipoteczny w Excelu.

O czym należy pamiętać o kalkulatorze hipotecznym Excel

  • Excel pokazuje miesięczną spłatę kredytu hipotecznego jako wartość ujemną. To dlatego, że są to wydawane pieniądze. Jeśli jednak chcesz, możesz uczynić go pozytywnym również poprzez dodanie - podpisania przed kwotą pożyczki.
  • Jednym z typowych błędów, które często popełniamy podczas korzystania z funkcji PMT, jest to, że nie zamykamy nawiasu i dlatego otrzymujemy komunikat o błędzie.
  • Ostrożnie dostosowuj stopę procentową według miesięcznych wartości (dzieląc przez 12) i okres kredytowania od lat do nie. miesięcy (pomnożenie przez 12).

Polecane artykuły

Jest to przewodnik po kalkulatorze hipotecznym Excel. Tutaj omawiamy, jak obliczać miesięczne płatności za pożyczkę, z przykładami i szablonem programu Excel. Możesz także przejrzeć nasze inne sugerowane artykuły, aby dowiedzieć się więcej -

  1. SZUKAJ Formułę w Excelu
  2. Funkcja Excel NORMSINV
  3. Nazwa i jej zastosowania w programie Excel
  4. Połącz łańcuchy w programie Excel

Kategoria:

Porady Excel