Ta wikiHow uczy, jak obliczyć wydatki związane z kredytem hipotecznym, takie jak odsetki, miesięczne płatności i całkowita kwota pożyczki, za pomocą arkusza kalkulacyjnego Microsoft Excel. Gdy to zrobisz, możesz również utworzyć harmonogram płatności, który wykorzystuje Twoje dane do generowania miesięcznego planu płatności, aby zapewnić terminową spłatę kredytu hipotecznego.
Kroki
Metoda 1 z 2: Tworzenie kalkulatora hipotecznego
Krok 1. Otwórz Microsoft Excel
Jeśli nie masz zainstalowanego programu Excel na swoim komputerze, możesz zamiast niego użyć rozszerzenia programu Outlook do programu Outlook. Może być konieczne uprzednie utworzenie konta programu Outlook.
Krok 2. Wybierz Pusty skoroszyt
Spowoduje to otwarcie nowego arkusza kalkulacyjnego Excel.
Krok 3. Utwórz kolumnę „Kategorie”
To trafi do kolumny „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 Ci miejsca do pisania. Będziesz potrzebować łącznie ośmiu komórek dla następujących kategorii:
- Kwota pożyczki $
- Roczna stopa procentowa
- Pożyczka na życie (w latach)
- Liczba płatności rocznie
- Całkowita liczba płatności
- Płatność za okres
- Suma płatności
- Koszt odsetek
Krok 4. Wprowadź swoje wartości
Zostaną one umieszczone w kolumnie „B”, bezpośrednio po prawej stronie kolumny „Kategorie”. Musisz wprowadzić odpowiednie wartości dla swojego kredytu hipotecznego.
- Twój Kwota pożyczki wartość to całkowita kwota, którą jesteś winien.
- Twój Roczna stopa procentowa wartość to procent odsetek naliczanych co roku.
- Twój Pożyczka na życie wartość to ilość czasu, jaką masz w latach na spłatę pożyczki.
- Twój Liczba płatności rocznie wartość to 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 Płatność za okres wartość to kwota, którą płacisz za płatność.
- Twój Suma płatności wartość pokrywa całkowity koszt kredytu.
- Twój Koszt odsetek wartość określa całkowity koszt odsetek w ciągu wartości Life Loan.
Krok 5. Oblicz całkowitą liczbę płatności
Ponieważ jest to wartość Twojej pożyczki na życie pomnożona przez wartość Płatności rocznie, nie potrzebujesz formuły do obliczenia tej wartości.
Na przykład, jeśli spłacasz co miesiąc 30-letnią pożyczkę dożywotnią, wpiszesz tutaj „360”
Krok 6. Oblicz miesięczną opłatę
Aby obliczyć, ile miesięcznie musisz spłacić kredyt hipoteczny, użyj następującej formuły: "= -PMT(Oprocentowanie/Płatności rocznie, Całkowita liczba spłat, Kwota pożyczki, 0)".
- W przypadku dostarczonego zrzutu ekranu formuła to „-PMT(B6/B8, B9, B5, 0)”. Jeśli twoje 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 należnej kwoty.
Krok 7. Oblicz całkowity koszt pożyczki
Aby to zrobić, pomnóż wartość „płatności w okresie” przez wartość „całkowitej 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
Krok 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, twój kalkulator hipoteczny jest gotowy.
Metoda 2 z 2: Sporządzanie harmonogramu płatności (amortyzacja)
Krok 1. Utwórz szablon harmonogramu płatności po prawej stronie szablonu kalkulatora hipotecznego
Ponieważ harmonogram spłat korzysta z kalkulatora hipotecznego, aby dokładnie oszacować, ile będziesz winien/spłaciłeś miesięcznie, powinny one znaleźć się w tym samym dokumencie. Będziesz potrzebować oddzielnej kolumny dla każdej z następujących kategorii:
- Data - Data dokonania przedmiotowej płatności.
- Płatność (liczba) – Numer płatności z całkowitej liczby płatności (np. „1”, „6” itd.).
- Płatność ($) - Całkowita zapłacona kwota.
- Zainteresowanie - Kwota całkowitej zapłaconej kwoty, czyli odsetki.
- Główny - Kwota całkowitej zapłaconej kwoty, która nie jest odsetkami (np. spłata pożyczki).
- Dodatkowa płatność - Kwota wszelkich dodatkowych płatności dokonanych w dolarach.
- Pożyczka - Kwota pożyczki, która pozostaje po spłacie.
Krok 2. Dodaj pierwotną kwotę pożyczki do harmonogramu spłat
Zostanie to umieszczone w pierwszej pustej komórce u góry kolumny „Pożyczka”.
Krok 3. Skonfiguruj pierwsze trzy komórki w kolumnach „Data” i „Płatność (numer)”
W kolumnie daty wprowadzisz datę zaciągnięcia pożyczki, a także dwie pierwsze daty, w których planujesz dokonywać miesięcznej spłaty (np. 02.01.2005, 03.03.2005 i 4. /1/2005). W kolumnie Płatność wprowadź pierwsze trzy numery płatności (np. 0, 1, 2).
Krok 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 pierwszy wpis w kolumnie Płatność (liczba).
- Przeciągnij kursor w dół, aż wyróżnisz liczbę odnoszącą się do liczby dokonywanych płatności (na przykład 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 opcja „Liniowa” jest zaznaczona w sekcji „Typ” (kiedy robisz kolumnę Data, pole „Data” powinno być zaznaczone).
- Kliknij OK.
Krok 5. Wybierz pierwszą pustą komórkę w kolumnie „Płatność ($)”
Krok 6. Wprowadź formułę Płatność za okres
Formuła obliczania wartości płatności w okresie opiera się na następujących informacjach w następującym formacie: „Płatność w okresie<Całkowita pożyczka+(Łączna pożyczka*(Roczna stopa oprocentowania/Liczba płatności w roku)), Płatność w okresie, Całkowita pożyczka+(Całkowita pożyczka*(roczna stopa procentowa/liczba płatności rocznie)))".
- Aby zakończyć obliczenia, musisz poprzedzić tę formułę znacznikiem „=IF”.
- Twoje wartości „Roczna stopa oprocentowania”, „Liczba płatności w roku” i „Płatność w okresie” muszą być zapisane w następujący sposób: $litera$liczba. Na przykład: $B$6
- Biorąc pod uwagę zrzuty ekranu tutaj, formuła wyglądałaby tak: „= JEŻELI ($ B $ 10 < K8 + (K8 * ($ B $ 6 / $ B $ 8)), $ B $ 10, K8 + (K8 * ($ B $ 6 / $ B) $8)))" (bez cudzysłowów).
Krok 7. Naciśnij ↵ Enter
Spowoduje to zastosowanie formuły płatności za okres 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”
Krok 8. Wybierz pierwszą pustą komórkę w kolumnie „Zainteresowanie”
Krok 9. Wprowadź wzór do obliczenia 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 na rok”.
- Ta formuła musi być poprzedzona znakiem „=”, aby działała.
- Na dostarczonych zrzutach ekranu formuła wyglądałaby tak: „=K8*$B$6/$B8$" (bez cudzysłowów).
Krok 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ć wcześniej używanej funkcji „Wypełnij”
Krok 11. Wybierz pierwszą pustą komórkę w kolumnie „Principal”
Krok 12. Wprowadź formułę Principal
W przypadku tej formuły wystarczy 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
Krok 13. Naciśnij ↵ Enter
Spowoduje to zastosowanie formuły głównej 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”
Krok 14. Wybierz pierwszą pustą komórkę w kolumnie „Pożyczka”
Powinno to być bezpośrednio poniżej początkowej kwoty pożyczki, którą zaciągnąłeś (np. druga komórka w tej kolumnie).
Krok 15. Wprowadź formułę Pożyczki
Obliczenie wartości Pożyczki wiąże się z następującymi wartościami: "Pożyczka"-"Kapitał"-"Dodatkowy".
W przypadku dostarczonych zrzutów ekranu należy wpisać „=K8-I8-J8” bez cytatów
Krok 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”
Krok 17. Użyj funkcji Wypełnij, aby uzupełnić kolumny z formułami
Twoja płatność powinna być taka sama do końca. Oprocentowanie i kwota pożyczki powinny się zmniejszyć, a kwoty kapitału wzrosnąć.
Krok 18. Podsumuj harmonogram płatności
Na dole tabeli zsumuj płatności, odsetki i kapitał. Porównaj te wartości z kalkulatorem kredytów hipotecznych. Jeśli się zgadzają, formuły zostały wykonane poprawnie.
- Twój kapitał powinien dokładnie odpowiadać oryginalnej kwocie pożyczki.
- Twoje płatności powinny odpowiadać całkowitemu kosztowi pożyczki z kalkulatora hipotecznego.
- Twoje oprocentowanie powinno odpowiadać kosztowi odsetek z kalkulatora kredytu hipotecznego.
Przykładowy kalkulator spłaty kredytu hipotecznego
Kalkulator spłaty kredytu hipotecznego
Porady
- Znak "-" przed funkcją PMT jest konieczny, w przeciwnym razie wartość będzie ujemna. Powodem, dla którego stopa procentowa jest dzielona 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 do przodu w drugiej komórce, a następnie zaznacz obie komórki i wykonaj autouzupełnianie zgodnie z powyższym opisem. Jeśli Autouzupełnianie rozpozna wzór, automatycznie wypełni za Ciebie.
- Spróbuj najpierw zbudować tabelę jak w przykładzie, wprowadzając przykładowe wartości. Po sprawdzeniu wszystkiego i upewnieniu się, że formuły są prawidłowe, wprowadź własne wartości.
