Wzór do obliczania płatności renty w Excelu

Płatności pożyczki są łatwiejsze i szybsze do obliczenia z Microsoft Office Excel. Znacznie więcej czasu poświęca się na ręczne obliczenia. W tym artykule skupimy się na płatnościach rentowych, cechach ich obliczania, zaletach i wadach.

Co to jest wypłata renty

Sposób comiesięcznej spłaty kredytu, w którym zdeponowana kwota nie zmienia się przez cały okres kredytowania. Tych. w określone dni każdego miesiąca osoba deponuje określoną sumę pieniędzy do czasu całkowitej spłaty kredytu.

Co więcej, odsetki od kredytu są już uwzględnione w łącznej kwocie wpłacanej do banku.

Renta klasyfikacyjna

Płatności renty można podzielić na następujące typy:

1. Naprawił. Płatności, które się nie zmieniają, mają stałą stawkę niezależnie od warunków zewnętrznych.
2. Waluta. Możliwość zmiany kwoty płatności w przypadku spadku lub wzrostu kursu walutowego.
3. indeksowane. Płatności w zależności od poziomu, wskaźnika inflacji. W okresie kredytowania często zmienia się ich wielkość.
4. Zmienne. Renta, która może się zmieniać w zależności od stanu systemu finansowego, instrumentów.

Uwaga! Stałe płatności są preferowane dla wszystkich kredytobiorców, ponieważ mają niewielkie ryzyko.

Zalety i wady płatności rentowych

Aby lepiej zrozumieć temat, konieczne jest przestudiowanie kluczowych cech tego typu spłat kredytu. Ma następujące zalety:

• Ustalenie konkretnej kwoty płatności i terminu jej zapłaty.
• Wysoka dostępność dla pożyczkobiorców. Prawie każdy może ubiegać się o rentę, niezależnie od swojej sytuacji finansowej.
• Możliwość obniżenia wysokości miesięcznej raty wraz ze wzrostem inflacji.

Nie bez wad:

• Wysoka ocena. Pożyczkobiorca przepłaci większą kwotę w porównaniu do płatności różnicowej.
• Problemy wynikające z chęci spłaty zadłużenia przed terminem.
• Brak przeliczeń dla wcześniejszych płatności.

Jaka jest spłata pożyczki?

Wypłata renty składa się z następujących elementów:

• Odsetki płacone przez osobę przy spłacie pożyczki.
• Część kwoty głównej.

W rezultacie łączna kwota odsetek prawie zawsze przekracza kwotę wniesioną przez kredytobiorcę w celu zmniejszenia zadłużenia.

Podstawowa formuła płatności renty w programie Excel

Jak wspomniano powyżej, w Microsoft Office Excel możesz pracować z różnymi rodzajami płatności za pożyczki i pożyczki. Renta nie jest wyjątkiem. Ogólnie wzór, za pomocą którego można szybko obliczyć składki rentowe, jest następujący:  

Ważne! Nie można otworzyć nawiasów w mianowniku tego wyrażenia, aby je uprościć.

Główne wartości wzoru są rozszyfrowane w następujący sposób:

• AP – wypłata renty (nazwa skrócona).
• O – wielkość długu głównego kredytobiorcy.
• PS – stopa procentowa proponowana co miesiąc przez dany bank.
• C to liczba miesięcy trwania pożyczki.

Aby przyswoić sobie informacje, wystarczy podać kilka przykładów użycia tej formuły. Zostaną one omówione dalej.

Przykłady użycia funkcji PMT w Excelu

Podajemy prosty warunek problemu. Konieczne jest obliczenie miesięcznej spłaty kredytu, jeśli bank zaproponuje odsetki w wysokości 23%, a łączna kwota to 25000 rubli. Kredyt potrwa 3 lata. Problem jest rozwiązywany według algorytmu:

1. Utwórz ogólny arkusz kalkulacyjny w programie Excel na podstawie danych źródłowych.

2. Aktywuj funkcję PMT i wprowadź jej argumenty w odpowiednim polu.
3. W polu „Zakład” wpisz formułę „B3/B5”. Będzie to oprocentowanie pożyczki.
4. W wierszu „Nper” wpisz wartość w postaci „B4*B5”. Będzie to łączna liczba spłat przez cały okres pożyczki.
5. Wypełnij pole „PS”. Tutaj musisz wskazać początkową kwotę pobraną z banku, wpisując wartość „B2”.

6. Upewnij się, że po kliknięciu „OK” w tabeli źródłowej została obliczona wartość „Płatność miesięczna”.

Dodatkowe informacje! Liczba ujemna oznacza, że ​​pożyczkobiorca wydaje pieniądze.

Przykład obliczenia kwoty nadpłaty pożyczki w Excelu

W tym problemie musisz obliczyć kwotę, którą osoba, która zaciągnęła pożyczkę w wysokości 50000 rubli z oprocentowaniem 27% przez 5 lat, przepłaci. Łącznie pożyczkobiorca dokonuje 12 płatności rocznie. Rozwiązanie:

1. Skompiluj oryginalną tabelę danych.

2. Od całkowitej kwoty płatności odejmij początkową kwotę zgodnie ze wzorem «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Musi być wstawiony do paska formuły u góry głównego menu programu.
3. W efekcie kwota nadpłat pojawi się w ostatniej linii utworzonej tabliczki. Kredytobiorca przepłaci 41606 rubli na górze.

Wzór na obliczenie optymalnej miesięcznej spłaty kredytu w Excelu

Zadanie z następującym warunkiem: klient zarejestrował konto bankowe na 200000 rubli z możliwością comiesięcznego uzupełniania. Konieczne jest obliczenie kwoty płatności, którą osoba musi dokonywać co miesiąc, aby po 4 latach miała na swoim koncie 2000000 rubli. Stawka wynosi 11%. Rozwiązanie:

1. Utwórz arkusz kalkulacyjny na podstawie oryginalnych danych.

2. Wprowadź formułę w wierszu wprowadzania Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» i naciśnij „Enter” z klawiatury. Litery będą się różnić w zależności od komórek, w których znajduje się tabela.
3. Sprawdź, czy kwota składki jest automatycznie obliczana w ostatnim wierszu tabeli.

Uwaga! Tak więc, aby klient zgromadził 4 ruble w tempie 2000000% w ciągu 11 lat, musi co miesiąc wpłacać 28188 rubli. Minus w kwocie oznacza, że ​​klient ponosi straty przekazując pieniądze do banku.

Funkcje korzystania z funkcji PMT w programie Excel

Ogólnie ta formuła jest zapisana w następujący sposób: =PMT(stopa;nper;ps;[bs];[typ]). Funkcja posiada następujące cechy:

1. Przy obliczaniu składek miesięcznych brana jest pod uwagę tylko stawka roczna.
2. Przy określaniu oprocentowania ważne jest przeliczenie na podstawie liczby rat w ciągu roku.
3. Zamiast argumentu „Nper” w formule wskazana jest konkretna liczba. To jest okres płatności.

Obliczanie płatności

Zasadniczo wypłata renty jest obliczana w dwóch etapach. Aby zrozumieć temat, każdy z etapów należy rozpatrywać osobno. Zostanie to omówione dalej.

Etap 1: wyliczenie raty miesięcznej

Aby obliczyć w programie Excel kwotę, którą musisz płacić co miesiąc z tytułu pożyczki o stałym oprocentowaniu, musisz:

1. Skompiluj tabelę źródłową i wybierz komórkę, w której chcesz wyświetlić wynik, a następnie kliknij przycisk „Wstaw funkcję” na górze.

2. Na liście funkcji wybierz „PLT” i kliknij „OK”.

3. W następnym oknie ustaw argumenty funkcji, wskazując odpowiednie wiersze w skompilowanej tabeli. Na końcu każdej linii musisz kliknąć ikonę, a następnie wybrać żądaną komórkę w tablicy.

4. Po wypełnieniu wszystkich argumentów w linii do wprowadzania wartości zostanie zapisana odpowiednia formuła, a wynik obliczenia ze znakiem minus pojawi się w polu tabeli „Opłata miesięczna”.

Ważne! Po obliczeniu raty będzie można obliczyć kwotę, jaką kredytobiorca przepłaci za cały okres kredytowania.

Etap 2: szczegóły płatności

Kwotę nadpłaty można obliczać co miesiąc. W rezultacie dana osoba zrozumie, ile pieniędzy wyda na pożyczkę co miesiąc. Obliczanie szczegółów odbywa się w następujący sposób:

1. Utwórz arkusz kalkulacyjny na 24 miesiące.

2. Umieść kursor w pierwszej komórce tabeli i wstaw funkcję „OSPLT”.

3. Wypełnij argumenty funkcji w ten sam sposób.

4. Wypełniając pole „Okres”, należy odwołać się do pierwszego miesiąca w tabeli, wskazując komórkę 1.

5. Sprawdź, czy wypełniona jest pierwsza komórka w kolumnie „Płatność według treści pożyczki”.
6. Aby wypełnić wszystkie wiersze pierwszej kolumny, musisz rozciągnąć komórkę do końca tabeli

7. Wybierz funkcję „PRPLT”, aby wypełnić drugą kolumnę tabeli.
8. Wypełnij wszystkie argumenty w otwartym oknie zgodnie z poniższym zrzutem ekranu.

9. Oblicz całkowitą miesięczną opłatę, dodając wartości z dwóch poprzednich kolumn.

10. Aby obliczyć „Saldo do zapłacenia”, musisz dodać stopę procentową do płatności w treści pożyczki i rozciągnąć ją do końca tablicy, aby wypełnić wszystkie miesiące pożyczki.

Dodatkowe informacje! Obliczając resztę, znaki dolara muszą być zawieszone na wzorze, aby nie przesuwał się po rozciągnięciu.

Obliczanie spłat dożywotnich pożyczki w Excelu

Funkcja PMT odpowiada za obliczanie renty w Excelu. Ogólna zasada obliczeń polega na wykonaniu następujących kroków:

1. Skompiluj oryginalną tabelę danych.
2. Zbuduj harmonogram spłaty zadłużenia na każdy miesiąc.
3. Wybierz pierwszą komórkę w kolumnie „Płatności za pożyczkę” i wprowadź wzór obliczeniowy "PLT ($B3/12;$B$4;$B$2)".
4. Wynikowa wartość jest rozciągana na wszystkie kolumny płyty.

Obliczanie w MS Excel spłaty kwoty głównej zadłużenia

Płatności renty muszą być dokonywane co miesiąc w stałych kwotach. A oprocentowanie się nie zmienia.

Obliczanie salda kwoty głównej (przy BS=0, typ=0)

Załóżmy, że pożyczka w wysokości 100000 10 rubli jest zaciągana na 9 lat po 1%. Konieczne jest obliczenie kwoty długu głównego w 3. miesiącu XNUMX. roku. Rozwiązanie:

1. Skompiluj arkusz danych i oblicz miesięczną opłatę za pomocą powyższej formuły PV.
2. Oblicz udział w płatności wymaganej do spłaty części zadłużenia, korzystając ze wzoru «=-PMT-(PS-PS1)*pozycja=-PMT-(PS +PMT+PS*pozycja)».
3. Oblicz kwotę długu głównego na 120 okresów, korzystając ze znanego wzoru.
4. Korzystając z operatora HPMT znajdź kwotę odsetek zapłaconych za 25. miesiąc.
5. Sprawdź wynik.

Obliczanie kwoty kapitału, która została zapłacona między dwoma okresami

To obliczenie najlepiej wykonać w prosty sposób. Aby obliczyć kwotę w przedziale dla dwóch okresów, należy użyć następujących formuł:

• =«-BS(pozycja; okres_konfiguracji; plt; [ps]; [typ]) /(1+typ *pozycja)».
• = „+ BS(stawka; okres_początku-1; plt; [ps]; [typ]) /IF(okres_początkowy =1; 1; 1+typ *stawka)”.

Uwaga! Litery w nawiasach zastępowane są określonymi wartościami.

Wcześniejsza spłata ze skróconym terminem lub płatnością

Jeśli potrzebujesz skrócić okres kredytowania, będziesz musiał wykonać dodatkowe obliczenia za pomocą operatora IF. Dzięki temu będzie można kontrolować saldo zerowe, które nie powinno zostać osiągnięte przed końcem okresu płatności.

Aby zmniejszyć płatności, musisz ponownie obliczyć składkę za każdy poprzedni miesiąc.

Kalkulator kredytowy z nieregularnymi płatnościami

Istnieje kilka opcji rent dożywotnich, w których pożyczkobiorca może wpłacać zmienne kwoty w dowolnym dniu miesiąca. W takiej sytuacji na każdy dzień naliczane jest saldo zadłużenia wraz z odsetkami. Jednocześnie w Excelu potrzebujesz:

1. Wprowadź dni miesiąca, za który dokonywane są płatności, i podaj ich liczbę.
2. Sprawdź kwoty ujemne i dodatnie. Preferowane są negatywne.
3. Policz dni między dwiema datami wpłaty pieniędzy.

Obliczanie płatności okresowych w MS Excel. Lokata terminowa

W Excelu możesz szybko obliczyć kwotę regularnych płatności, pod warunkiem, że została już zgromadzona stała kwota. Ta akcja jest wykonywana za pomocą funkcji PMT po skompilowaniu tabeli początkowej.

Wnioski

Dzięki temu płatności dożywotnie są łatwiejsze, szybsze i bardziej wydajne do obliczenia w programie Excel. Za ich kalkulację odpowiada operator PMT. Bardziej szczegółowe przykłady można znaleźć powyżej.