Zawartość

• Kroki
• Pytania i odpowiedzi społeczności
• Porady

Inne sekcje

To wikiHow uczy, jak obliczyć wydatki związane z kredytem hipotecznym, takie jak odsetki, miesięczne raty i całkowita kwota pożyczki, przy użyciu arkusza kalkulacyjnego Microsoft Excel. Gdy to zrobisz, możesz również utworzyć harmonogram spłat, który na podstawie Twoich danych wygeneruje miesięczny plan spłat, aby zapewnić terminową spłatę kredytu hipotecznego.

Kroki

Metoda 1 z 2: Tworzenie kalkulatora hipotecznego

1. 

   Otwórz Microsoft Excel. Jeśli nie masz zainstalowanego programu Excel na swoim komputerze, możesz zamiast niego użyć rozszerzenia programu Outlook online do programu Excel. Konieczne może być najpierw utworzenie konta Outlook.

2. 

   
   
   Wybierz Pusty skoroszyt. Spowoduje to otwarcie nowego arkusza kalkulacyjnego Excel.

3. 

   
   
   Utwórz kolumnę „Kategorie”. Znajdziesz się w kolumnie „A”. Aby to zrobić, należy najpierw kliknąć i przeciągnąć separator między kolumnami „A” i „B” w prawo o co najmniej trzy spacje, aby nie zabrakło miejsca do pisania. Będziesz potrzebować łącznie ośmiu komórek dla następujących kategorii:
   • Kwota pożyczki $
   • Roczna stopa procentowa
   • Kredyt na życie (w latach)
   • Liczba płatności rocznie
   • Całkowita liczba płatności
   • Okres płatności
   • Suma płatności
   • Koszt odsetek

4. 

   
   
   Wprowadź swoje wartości. Będą one znajdować się w kolumnie „B”, bezpośrednio po prawej stronie kolumny „Kategorie”. Musisz wprowadzić odpowiednie wartości kredytu hipotecznego.
   • Twój Kwota pożyczki wartość to całkowita kwota, jaką jesteś winien.
   • Twój Roczna stopa procentowa wartość to procent odsetek narastających każdego roku.
   • Twój Pożyczka na życie wartość to ilość czasu, który masz w latach na spłatę pożyczki.
   • Twój Liczba płatności rocznie wartość określa, ile razy dokonujesz płatności w ciągu jednego roku.
   • Twój Całkowita liczba płatności wartość to wartość pożyczki na życie pomnożona przez wartość płatności rocznie.
   • Twój Okres płatności wartość to kwota, jaką płacisz za każdą płatność.
   • Twój Suma płatności wartość obejmuje całkowity koszt kredytu.
   • Twój Koszt odsetek wartość określa całkowity koszt odsetek w trakcie trwania wartości Kredytu na Życie.

5. 

   Oblicz całkowitą liczbę płatności. Ponieważ jest to wartość Twojej pożyczki na życie pomnożona przez wartość płatności rocznych, nie potrzebujesz wzoru do obliczenia tej wartości.
   • Na przykład, jeśli spłacasz miesięczną spłatę 30-letniej pożyczki na życie, wpisz tutaj „360”.

6. 

   Oblicz miesięczną płatność. Aby obliczyć, ile musisz spłacać co miesiąc kredyt hipoteczny, użyj następującego wzoru: „= -PMT (stopa procentowa / płatności rocznie, łączna liczba płatności, kwota pożyczki, 0)”.
   • Na przedstawionym zrzucie ekranu formuła to „-PMT (B6 / B8, B9, B5,0)”. Jeśli wartości są nieco inne, wprowadź je z odpowiednimi numerami komórek.
   • Powodem, dla którego możesz umieścić znak minus przed PMT, jest to, że PMT zwraca kwotę do odliczenia od kwoty należnej.

7. 

   Oblicz całkowity koszt pożyczki. Aby to zrobić, wystarczy pomnożyć wartość „płatności w okresie” przez wartość „łącznej liczby płatności”.
   • Na przykład, jeśli dokonasz 360 płatności w wysokości 600,00 USD, całkowity koszt pożyczki wyniesie 216 000 USD.

8. 

   Oblicz całkowity koszt odsetek. Wszystko, co musisz tutaj zrobić, to odjąć początkową kwotę pożyczki od całkowitego kosztu pożyczki, który obliczyłeś powyżej. Gdy to zrobisz, kalkulator kredytu hipotecznego jest kompletny.

Metoda 2 z 2: Sporządzenie harmonogramu płatności (amortyzacja)

1. 

   Utwórz szablon harmonogramu płatności po prawej stronie szablonu kalkulatora hipotecznego. Ponieważ Harmonogram spłat korzysta z Kalkulatora hipotecznego, aby zapewnić dokładną ocenę miesięcznej kwoty długu / spłaty, należy je umieścić w tym samym dokumencie. Będziesz potrzebować osobnej kolumny dla każdej z następujących kategorii:
   • Data - data dokonania danej płatności.
   • Płatność (numer) - Numer płatności z całkowitej liczby płatności (np. „1”, „6” itp.).
   • Płatność ($) - Całkowita zapłacona kwota.
   • Zainteresowanie - Kwota całkowitej zapłaconej kwoty, czyli odsetki.
   • Dyrektor - Kwota całkowitej zapłaconej kwoty niebędąca odsetkami (np. Spłata pożyczki).
   • Dodatkowa płatność - Kwota wszelkich dodatkowych płatności, które dokonałeś.
   • Pożyczka - Kwota pożyczki pozostająca po spłacie.

2. 

   Dodaj pierwotną kwotę pożyczki do harmonogramu spłat. Znajdzie się to w pierwszej pustej komórce na górze kolumny „Kredyt”.

3. 

   Skonfiguruj pierwsze trzy komórki w swoim „Data”i„ Płatność (numer) ”. W kolumnie z datą wpiszesz datę zaciągnięcia pożyczki, a także dwie pierwsze daty, w których planujesz dokonać miesięcznej spłaty (np. 01.02.2005, 01.03.2005 i 4 / 1/2005). W kolumnie Płatność wprowadź pierwsze trzy numery płatności (np. 0, 1, 2).

4. 

   Użyj funkcji „Wypełnij”, aby automatycznie wprowadzić pozostałe wartości płatności i daty. Aby to zrobić, musisz wykonać następujące czynności:
   • Wybierz pierwszą pozycję w kolumnie Płatność (numer).
   • Przeciągnij kursor w dół, aż podświetlisz liczbę odpowiadającą liczbie dokonanych płatności (np. 360). Ponieważ zaczynasz od „0”, przeciągnij w dół do wiersza „362”.
   • Kliknij Wypełnij w prawym górnym rogu strony programu Excel.
   • Wybierz serię.
   • Upewnij się, że w sekcji „Typ” jest zaznaczona opcja „Liniowy” (w kolumnie Data należy zaznaczyć opcję „Data”).
   • Kliknij OK.

5. 

   Wybierz pierwszą pustą komórkę w kolumnie „Płatność ($)”.

6. 

   Wprowadź formułę Okres płatności. Formuła obliczania wartości Okres płatności opiera się na następujących informacjach w następującym formacie: „Okres płatności
   • Aby zakończyć obliczenia, należy poprzedzić tę formułę znacznikiem „= IF”.
   • Wartości „Rocznej stopy procentowej”, „Liczba płatności w roku” i „Okres płatności” należy zapisać w następujący sposób: $ litera $ liczba. Na przykład: 6 $ B $
   • Biorąc pod uwagę zrzuty ekranu, wzór wyglądałby tak: "= JEŻELI (10 $ B $

7. 

   naciśnij ↵ Enter. Spowoduje to zastosowanie formuły Okres płatności do wybranej komórki.
   • Aby zastosować tę formułę do wszystkich kolejnych komórek w tej kolumnie, musisz użyć wcześniej używanej funkcji „Wypełnij”.

8. 

   Wybierz pierwszą pustą komórkę w kolumnie „Odsetki”.

9. 

   Wprowadź wzór do obliczania wartości odsetek. Formuła obliczania wartości odsetek opiera się na następujących informacjach w następującym formacie: „Pożyczka ogółem * Roczna stopa procentowa / Liczba płatności w ciągu roku”.
   • Aby formuła działała, należy poprzedzić ją znakiem „=”.
   • Na przedstawionych zrzutach ekranu formuła wyglądałaby następująco: "= K8 * $ B 6 $ / $ B 8 $" (bez cudzysłowów).

10. 

    naciśnij ↵ Enter. Spowoduje to zastosowanie formuły odsetek do wybranej komórki.
    • Aby zastosować tę formułę do wszystkich kolejnych komórek w tej kolumnie, musisz użyć używanej wcześniej funkcji „Wypełnij”.

11. 

    Wybierz pierwszą pustą komórkę w kolumnie „Principal”.

12. 

    Wprowadź formułę główną. W przypadku tej formuły wszystko, co musisz zrobić, to odjąć wartość „Odsetki” od wartości „Płatność ($)”.
    • Na przykład, jeśli komórka „Odsetki” to H8, a komórka „Płatność ($)” to G8, wpisz „= G8 - H8” bez cudzysłowów.

13. 

    naciśnij ↵ Enter. Spowoduje to zastosowanie formuły Principal do wybranej komórki.
    • Aby zastosować tę formułę do wszystkich kolejnych komórek w tej kolumnie, musisz użyć używanej wcześniej funkcji „Wypełnij”.

14. 

    Wybierz pierwszą pustą komórkę w kolumnie „Pożyczka”. Powinien znajdować się bezpośrednio pod początkową kwotą pożyczki, którą zaciągnąłeś (np. Druga komórka w tej kolumnie).

15. 

    Wprowadź formułę pożyczki. Obliczenie wartości kredytu obejmuje: „Kredyt” - „Kapitał” - „Dodatek”.
    • Na dostarczonych zrzutach ekranu wpisz „= K8-I8-J8” bez cudzysłowów.

16. 

    naciśnij ↵ Enter. Spowoduje to zastosowanie formuły pożyczki do wybranej komórki.
    • Aby zastosować tę formułę do wszystkich kolejnych komórek w tej kolumnie, musisz użyć wcześniej używanej funkcji „Wypełnij”.

17. 

    Użyj funkcji Fill, aby uzupełnić kolumny z formułami. Twoja płatność powinna być taka sama przez cały czas. Oprocentowanie i kwota kredytu powinny ulec zmniejszeniu, a wartości kapitału wzrosnąć.

18. 

    Zsumuj harmonogram płatności. U dołu tabeli zsumuj płatności, odsetki i kwotę główną. Porównaj te wartości z kalkulatorem kredytu hipotecznego. Jeśli pasują, formuły zostały utworzone poprawnie.
    • Twoja kwota główna powinna dokładnie odpowiadać pierwotnej kwocie pożyczki.
    • Twoje płatności powinny odpowiadać całkowitemu kosztowi pożyczki z kalkulatora hipotecznego.
    • Twoje odsetki powinny odpowiadać kosztom odsetek z kalkulatora kredytu hipotecznego.

Przykładowy kalkulator spłaty kredytu hipotecznego

Kalkulator spłaty kredytu hipotecznego

Pytania i odpowiedzi społeczności

Jeśli dokonuję dodatkowych spłat pożyczki, czy istnieje formuła, która ponownie obliczy miesięczną ratę?

Uwzględnij 2 kolumny, jedną dla składnika odsetkowego, a drugą ze składnikiem głównym. Dodać nową kolumnę obok tytułu głównego „dodatkowy podmiot”. Teraz odejmij ją od pierwotnego kapitału.

Jaki jest wzór w programie Excel na obliczanie kwoty pożyczki, jeśli znam spłatę pożyczki, stopę procentową i okres?

Użyj tych samych wzorów, co w metodzie 1 powyżej; jednak użyj funkcji Szukanie wyniku, aby automatycznie obliczyć kwotę pożyczki.

Dodanie dodatkowych płatności do pożyczki powoduje odpowiednie dostosowanie harmonogramów spłat, ale czy istnieje wzór pokazujący, ile odsetek lub miesięcy oszczędzam, dokonując płatności?

Skonfiguruj dwa arkusze kalkulacyjne, jeden zawierający „pożyczkę podstawową” bez dodatkowych płatności, a drugi zawierający dodatkowe płatności. „Suma płatności” zmniejszy się w miarę dodawania dodatkowych płatności w drugim arkuszu. Odejmij tę wartość od sumy płatności „pożyczki bazowej”, aby zobaczyć, ile oszczędzasz, wpłacając dodatkowe pieniądze na swoją pożyczkę.

Krok 4 metody 2 odwołuje się do formuł u góry zrzutów ekranu i tabeli referencyjnej. Gdzie one są?

Opcja Wypełnij zwykle znajduje się na karcie Strona główna w programie Excel w sekcji „Edycja”. Nie jestem pewien, do czego odnosi się „formuły u góry zrzutów ekranu” lub „tabela referencyjna”, ponieważ żaden z nich nie został wymieniony w tym artykule.

Jaka jest formuła płatności na okres przy użyciu prostych odsetek?

Użyj tego prostego kalkulatora odsetek, aby znaleźć A, Ostateczną Wartość Inwestycji, używając prostego wzoru na odsetki: A = P (1 + rt), gdzie P to kwota główna, która ma być zainwestowana przy stopie procentowej R% na okres dla t Liczba okresów czasu.

Jeśli oprocentowanie jest stałe przez pierwsze 5 lat, ale potem zmienia się na coś innego, jak mam przeliczyć nową stopę w pozostałych latach?

W przypadku regulowanego kredytu hipotecznego nie jesteś w stanie przewidzieć dokładnej przyszłej wartości procentowej. Dodaj kolumnę korekty odsetek do każdej płatności. Przez pierwsze pięć lat korekta wynosi 0. W szóstym roku korekta = do maksymalnego rocznego wzrostu. W siódmym roku wprowadź korektę = do maksymalnego rocznego wzrostu. Po osiągnięciu maksymalnego dopuszczalnego oprocentowania pożyczki nadal z niej korzystaj. Dzięki tej metodzie przewidujesz najgorszy scenariusz dla ARM.

• 
  
  Jak obliczyć spłatę pożyczki w MS Excel? Odpowiedź

Porady

• Znak „-” przed funkcją PMT jest konieczny, w przeciwnym razie wartość będzie ujemna. Ponadto powodem podzielenia stopy procentowej przez liczbę płatności jest to, że oprocentowanie dotyczy roku, a nie miesiąca.
• Aby automatycznie wypełnić datę za pomocą arkusza kalkulacyjnego Google Dogs, wpisz datę w pierwszej komórce, a następnie miesiąc naprzód w drugiej komórce, a następnie zaznacz obie komórki i wykonaj Autouzupełnianie, jak opisano powyżej. Jeśli funkcja autouzupełniania rozpozna wzór, zostanie automatycznie wypełniona.
• Spróbuj najpierw zbudować tabelę jak w przykładzie, wprowadzając przykładowe wartości. Gdy wszystko się sprawdzi i masz pewność, że formuły są prawidłowe, wprowadź własne wartości.