Excel

Obliczanie płatności dożywotniej w programie Microsoft Excel

Przed zaciągnięciem pożyczki dobrze byłoby obliczyć wszystkie płatności na jego rzecz. To uratuje pożyczkobiorcę w przyszłości przed różnymi nieoczekiwanymi problemami i rozczarowaniami, kiedy okaże się, że nadpłata jest zbyt duża. Pomoc w tym obliczeniu może narzędzia Excel. Dowiedzmy się, jak obliczyć spłatę rat pożyczki w tym programie.

Obliczanie płatności

Przede wszystkim muszę powiedzieć, że istnieją dwa rodzaje płatności pożyczek:

  • Zróżnicowany;
  • Dożywocie.

Przy zróżnicowanym schemacie klient wpłaca do banku równy miesięczny udział w płatnościach na rzecz instytucji pożyczkowej plus płatności odsetek. Wartość wypłat odsetek maleje co miesiąc, wraz ze spadkiem masy kredytu, od którego są naliczane. W ten sposób zmniejsza się również łączna miesięczna rata.

W systemie renty stosuje się nieco inne podejście. Klient dokonuje co miesiąc takiej samej kwoty całkowitej płatności, która składa się z płatności na rzecz instytucji pożyczki i spłaty odsetek. Początkowo wypłaty odsetek dotyczą pełnej kwoty pożyczki, ale wraz ze spadkiem kwoty odsetek zmniejszają się również odsetki. Ale całkowita kwota płatności pozostaje niezmieniona ze względu na miesięczny wzrost kwoty płatności dla instytucji kredytowej. Z czasem udział odsetek w całkowitej miesięcznej płatności maleje, a konkretna waga płatności dla podmiotu wzrasta. W tym samym czasie całkowita miesięczna rata nie zmienia się przez cały okres trwania pożyczki.

Tylko przy obliczaniu wypłaty renty zatrzymamy się. Co więcej, jest to faktyczne, ponieważ obecnie większość banków korzysta z tego programu. Jest to również wygodne dla klientów, ponieważ w tym przypadku łączna kwota płatności nie ulega zmianie, pozostaje stała. Klienci zawsze wiedzą, ile trzeba zapłacić.

Krok 1: Obliczanie miesięcznej raty

Aby obliczyć opłatę miesięczną przy korzystaniu z programu rentowego w Excelu, istnieje specjalna funkcja - PLT . Należy do kategorii podmiotów finansowych. Wzór dla tej funkcji jest następujący:

=ПЛТ(ставка;кпер;пс;бс;тип)

Jak widać, ta funkcja ma sporo argumentów. To prawda, że ​​ostatnie dwa z nich nie są obowiązkowe.

Argument "Bet" wskazuje oprocentowanie dla określonego okresu. Jeśli na przykład stosuje się roczną stawkę, ale pożyczka jest wypłacana co miesiąc, wówczas roczna stopa powinna zostać podzielona przez 12, a wynik wykorzystany jako argument. W przypadku stosowania metody płatności kwartalnej, w tym przypadku roczna stawka powinna zostać podzielona przez 4 itd.

"Kper" odnosi się do całkowitej liczby okresów spłaty kredytu. Oznacza to, że jeśli pożyczka jest pobierana przez jeden rok z płatnością miesięczną, wówczas liczba okresów wynosi 12 , jeśli przez dwa lata, wtedy liczba okresów wynosi 24 . Jeżeli pożyczka jest pobierana przez dwa lata z płatnością kwartalną, wówczas liczba okresów wynosi 8 .

"Ps" wskazuje aktualną wartość. W prostych słowach jest to całkowita kwota pożyczki na początku pożyczki, czyli kwota, którą pożyczasz, bez uwzględniania odsetek i innych dodatkowych płatności.

"Bs" jest wartością przyszłą. Kwota, która będzie stanowić podstawę pożyczki w momencie zakończenia umowy pożyczki. W większości przypadków ten argument jest równy "0" , ponieważ kredytobiorca musi w pełni uregulować z kredytodawcą pod koniec okresu kredytowania. Podany argument jest opcjonalny. Dlatego jeśli zostanie pominięty, przyjmuje się, że wynosi zero.

Argument "Typ" określa czas obliczeń: na końcu lub na początku okresu. W pierwszym przypadku przyjmuje wartość "0" , aw drugim - "1" . Większość instytucji bankowych korzysta z opcji z płatnością na koniec okresu. Ten argument jest opcjonalny, a jeśli zostanie pominięty, przyjmuje się, że wynosi zero.

Nadszedł czas, aby przejść do konkretnego przykładu obliczania miesięcznej opłaty za pomocą funkcji PLT. Do obliczeń używamy tabeli z danymi początkowymi, która pokazuje oprocentowanie pożyczki ( 12% ), wartość pożyczki ( 500 000 rubli ) i okres pożyczki ( 24 miesiące ). W takim przypadku płatność dokonywana jest co miesiąc na koniec każdego okresu.

  1. Wybierz element na arkuszu, na którym zostanie wyświetlony wynik obliczenia, i kliknij ikonę "Wstaw funkcję" znajdującą się w pobliżu paska formuły.

    2. Przejdź do Kreatora funkcji w programie Microsoft Excel

  2. Otworzy się okno kreatora funkcji . W kategorii "Finanse" wybieramy nazwę "PLT" i klikamy przycisk "OK" .

    3. Przejdź do okna argumentów funkcji sterownika PLC w programie Microsoft Excel

  3. Następnie otwiera się okno argumentów operatora PLT .

    W polu "Stawka" należy wpisać kwotę odsetek za dany okres. Można to zrobić ręcznie, wystarczy umieścić procent, ale mamy go na liście w osobnej komórce na arkuszu, więc dajmy mu link. Ustaw kursor w polu, a następnie kliknij odpowiednią komórkę. Ale, jak pamiętamy, w naszej tabeli ustalana jest roczna stopa oprocentowania, a okres płatności równy jest miesiącowi. Dlatego dzielimy roczną stawkę, a raczej odniesienie do komórki, w której jest zawarta, przez liczbę 12 , odpowiadającą liczbie miesięcy w roku. Podział wykonywany jest bezpośrednio w polu argumentów.

    W polu "Kper" ustawiony jest okres kredytowania. Mamy go przez 24 miesiące. Możesz wpisać numer 24 ręcznie, ale my, tak jak w poprzednim przypadku, wskazujemy link do lokalizacji tego wskaźnika w tabeli źródłowej.

    W polu "Ps" wskazana jest pierwotna wartość pożyczki. Jest równa 500 000 rubli . Podobnie jak w poprzednich przypadkach, wskazujemy link do elementu arkusza, który zawiera ten wskaźnik.

    Pole "Bs" wskazuje kwotę pożyczki po pełnej spłacie. Jak pamiętacie, ta wartość jest prawie zawsze zerowa. W tym polu ustawiamy liczbę "0" . Chociaż ten argument można całkowicie pominąć.

    W polu "Typ" wskazujemy na początku lub na końcu miesiąca płatność. My, jak w większości przypadków, produkujemy je pod koniec miesiąca. Dlatego ustawiamy liczbę "0" . Podobnie jak w przypadku poprzedniego argumentu, nie można wprowadzić niczego w tym polu, wtedy program domyślnie przyjmuje, że ma wartość zero.

    Po wprowadzeniu wszystkich danych kliknij przycisk "OK" .

    4. Okno argumentu funkcji PLT w Microsoft Excel

  4. Następnie wynik obliczeń jest wyświetlany w komórce wybranej w pierwszym akapicie tego podręcznika. Jak widać, wartość miesięcznej całkowitej kwoty pożyczki wynosi 23 536,74 rubla . Nie należy mylić znaku "-" przed tą kwotą. Axel zwraca uwagę, że jest to przepływ środków pieniężnych, czyli strata.

    5. Wynik obliczania miesięcznej płatności w programie Microsoft Excel

  5. Aby obliczyć całkowitą kwotę płatności za cały okres kredytowania, biorąc pod uwagę spłatę kredytu i miesięczne odsetki, wystarczy pomnożyć wartość miesięcznej płatności ( 2356,74 rubli ) przez liczbę miesięcy ( 24 miesiące ). Jak widać, całkowita kwota płatności za cały okres wypożyczenia w naszym przypadku wynosiła 564881.67 rubli .

    6. Łączna kwota płatności w programie Microsoft Excel

  6. Teraz możesz obliczyć kwotę nadpłaty na pożyczce. Aby to zrobić, należy odjąć całkowitą kwotę płatności od kredytu, w tym odsetki i instytucję pożyczkową, początkową pożyczoną kwotę. Ale pamiętamy, że pierwsza z tych wartości ma już znak "-" . Dlatego w naszym przypadku okazuje się, że trzeba je złożyć. Jak widać, łączna kwota nadpłaty za pożyczkę w całym okresie wyniosła 6481,67 rubli .

Nadpłata za pożyczkę w programie Microsoft Excel

Lekcja: Kreator funkcji w programie Excel

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

A teraz, z pomocą innych operatorów Axela, wykonamy szczegóły dotyczące miesięcznych płatności, aby sprawdzić, ile w danym miesiącu płacimy w instytucji pożyczki i ile to odsetki. W tym celu narysujemy tabelę w Excelu, którą wypełnimy danymi. Wiersze tej tabeli będą odpowiadać odpowiadającemu okresowi, to jest miesiącowi. Biorąc pod uwagę, że okres kredytowania dla nas wynosi 24 miesiące, liczba wierszy będzie również odpowiednia. Kolumny wskazują płatność jednostki pożyczkowej, wypłatę odsetek, całkowitą miesięczną opłatę, która jest sumą dwóch poprzednich kolumn, oraz pozostałą kwotę do zapłaty.

Tabela wypłat w programie Microsoft Excel

  1. W celu określenia kwoty płatności dla instytucji kredytowej, używamy funkcji OSPLT , która jest właśnie zaprojektowana do tych celów. Ustaw kursor w komórce znajdującej się w wierszu "1" oraz w kolumnie "Płatność według masy kredytu". Kliknij przycisk "Wstaw funkcję" .

    2. Wstaw funkcję w Microsoft Excel

  2. Przejdź do Kreatora funkcji . W kategorii "Finanse" oznaczamy nazwę "OSPLT" i klikamy przycisk "OK" .

    3. Przejdź do okna argumentów funkcji OSPLT w programie Microsoft Excel

  3. Wyświetlane jest okno z argumentami operatora OSPLT. Ma następującą składnię:

    =ОСПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Jak widać, argumenty tej funkcji prawie całkowicie pokrywają się z argumentami operatora PLT , tylko opcjonalny argument "Okres" został dodany zamiast opcjonalnego argumentu "Typ" . Wskazuje on numer okresu wypłaty, aw naszym konkretnym przypadku numer miesiąca.

    Wypełniamy pola okna argumentów funkcji OSPLT, które są nam już znane przez te same dane, które zostały użyte do funkcji PLT . Biorąc pod uwagę fakt, że w przyszłości formuła będzie kopiowana przy użyciu znacznika wypełnienia, wszystkie łącza w polach muszą być bezwzględne, aby się nie zmieniały. W tym celu wymagane jest umieszczenie znaku dolara przed każdą wartością współrzędnych wzdłuż pionowych i poziomych linii. Ale łatwiej to zrobić, po prostu wybierając współrzędne i naciskając klawisz funkcyjny F4 . Znak dolara zostanie umieszczony we właściwych miejscach automatycznie. Nie zapominaj również, że roczna stawka powinna zostać podzielona przez 12 .

    4. Okno argumentu funkcji OBSFT w Microsoft Excel

  4. Ale wciąż mamy kolejny nowy argument, którego funkcja PLT nie miała. Ten argument to "Okres" . W odpowiednim polu ustaw odwołanie do pierwszej komórki w kolumnie "Okres" . Ten element arkusza zawiera liczbę "1" , która oznacza liczbę pierwszego miesiąca kredytowania. Ale w przeciwieństwie do poprzednich pól, w określonym polu zostawiamy referencyjną relację i nie sprawiamy, że jest bezwzględna.

    Po wprowadzeniu wszystkich danych, o których mówiliśmy powyżej, kliknij przycisk "OK" .

    5. Argument okresu w oknie argumentów funkcji OBSF w Microsoft Excel

  5. Następnie w komórce, którą wcześniej przydzieliliśmy, zostanie wyświetlona kwota płatności na instytucji kredytowej za pierwszy miesiąc. Będzie 1856,74 rubli .

    6. Wynik obliczenia funkcji OSPLT w Microsoft Excel

  6. Następnie, jak wspomniano powyżej, powinniśmy skopiować tę formułę do pozostałych komórek w kolumnie za pomocą uchwytu wypełnienia. Aby to zrobić, ustaw kursor w prawym dolnym rogu komórki zawierającej formułę. Kursor jest konwertowany na krzyżyk, który jest nazywany znacznikiem wypełnienia. Zacisnąć lewy przycisk myszy i przeciągnąć go na koniec stołu.

    7. Wypełniacz w programie Microsoft Excel

  7. W rezultacie wszystkie komórki w kolumnie są wypełnione. Teraz mamy harmonogram spłat kredytu miesięcznie. Jak wspomniano powyżej, kwota płatności za ten artykuł z każdym nowym okresem wzrasta.

    8. Kwota płatności dla jednostki pożyczki miesięcznie w programie Microsoft Excel

  8. Teraz musimy dokonać miesięcznego obliczenia płatności odsetek. Do tych celów użyjemy operatora PRPLT . Wybierz pierwszą pustą komórkę w kolumnie "Płatność odsetkowa" . Kliknij przycisk "Wstaw funkcję" .

    9. Przejdź do Kreatora funkcji w programie Microsoft Excel

  9. W oknie kreatorów funkcji w kategorii "Finanse" wybieramy nazwę PRPLT . Kliknij przycisk "OK" .

    10. Przejdź do okna argumentu funkcji PRPLT w programie Microsoft Excel

  10. Otworzy się okno argumentu funkcji PRPLT . Jego składnia jest następująca:

    =ПРПЛТ(Ставка;Период;Кпер;Пс;Бс)

    Jak widać, argumenty tej funkcji są absolutnie identyczne z analogicznymi elementami operatora OSPLT . Dlatego po prostu wstawiamy do okna te same dane, które wprowadziliśmy w poprzednim oknie argumentów. Nie zapominamy jednak, że link w polu "Okres" powinien być względny, a we wszystkich innych polach współrzędne powinny być doprowadzone do absolutnej formy. Następnie kliknij przycisk "OK" .

    11. Okno argumentów funkcji PRPLT w Microsoft Excel

  11. Następnie wynik obliczenia kwoty odsetek za pożyczkę za pierwszy miesiąc jest wyświetlany w odpowiedniej komórce.

    12. Wynik obliczenia funkcji PRPLT w Microsoft Excel

  12. Stosując znacznik wypełnienia, kopiujemy formułę do pozostałych elementów kolumny, uzyskując w ten sposób miesięczny harmonogram płatności odsetek od pożyczki. Jak widzimy, jak powiedziano wcześniej, wartość tego rodzaju płatności maleje z miesiąca na miesiąc.

    13. Harmonogram płatności odsetek od pożyczki w programie Microsoft Excel

  13. Teraz musimy obliczyć całkowitą miesięczną opłatę. Do tego obliczenia nie należy odwoływać się do żadnego operatora, ponieważ można zastosować prostą formułę arytmetyczną. Dodajemy zawartość komórek z pierwszego miesiąca kolumn "Płatność przez instytucję pożyczkową" i "Płatność odsetkowa" . Aby to zrobić, ustaw znak "=" na pierwszej pustej komórce kolumny "Całkowita miesięczna płatność" . Następnie kliknij dwa powyższe elementy, ustawiając między nimi znak "+" . Naciśnij klawisz Enter .

    14. Suma całkowitej miesięcznej płatności w programie Microsoft Excel

  14. Ponadto, używając znacznika wypełnienia, tak jak w poprzednich przypadkach, wypełniamy kolumnę danymi. Jak widzimy, w całym okresie obowiązywania umowy kwota całkowitej miesięcznej płatności, w tym płatności na rzecz instytucji pożyczki i spłaty odsetek wyniesie 2356,74 rubla . Właściwie to już policzono tę liczbę przy pomocy PLT . Ale w tym przypadku jest ona przedstawiana bardziej precyzyjnie, dokładnie tak, jak kwota płatności dla instytucji kredytowej i odsetek.

    15. Łączna miesięczna płatność w programie Microsoft Excel

  15. Teraz musisz dodać dane do kolumny, w której saldo kwoty pożyczki będzie wyświetlane co miesiąc, co jest nadal wymagane do zapłaty. W pierwszej komórce kolumny "Saldo do wypłaty" obliczenia będą najprostsze. Musimy zabrać z pierwotnej kwoty kredytu, który jest wskazany w tabeli z danymi pierwotnymi, płatności na instytucji kredytowej za pierwszy miesiąc w tabeli rozliczenia. Ale biorąc pod uwagę fakt, że jedna z liczb, które mamy już ze znakiem "-" , nie należy ich zabierać, ale składać. Zrób to i naciśnij przycisk Enter .

    16. Saldo do zapłaty po pierwszym miesiącu kredytowania w programie Microsoft Excel

  16. Ale obliczenie salda do zapłaty po drugim i kolejnych miesiącach będzie nieco bardziej skomplikowane. Aby to zrobić, musimy zabrać ze środka pożyczki na początku pożyczki całkowitą kwotę płatności na rzecz instytucji kredytowej za poprzedni okres. Ustaw znak "=" w drugiej komórce kolumny "Saldo do zapłaty" . Następnie wskazujemy link do komórki, która zawiera pierwotną kwotę pożyczki. Ustawiamy wartość bezwzględną, wybierając i naciskając klawisz F4 . Następnie umieszczamy znak "+" , ponieważ druga wartość będzie dla nas negatywna. Następnie kliknij przycisk "Wstaw funkcję" .

    17. Wstaw funkcję w Microsoft Excel

  17. Uruchomi się Kreator funkcji , w którym konieczne jest przejście do kategorii "Matematyczne" . Tam wybieramy napis "SUMA" i klikamy przycisk "OK" .

    18. Przejdź do okna argumentów funkcji SUMA w programie Microsoft Excel

  18. Otworzy się okno argumentu funkcji SUM . Podany operator służy do podsumowania danych w komórkach, które musimy wykonać w kolumnie "Płatność przez jednostkę pożyczki". Ma następującą składnię:

    =СУММ(число1;число2;…)

    Argumenty są odniesieniami do komórek zawierających liczby. Ustawiamy kursor w polu "Numer 1" . Następnie przytrzymaj lewy przycisk myszy i wybierz pierwsze dwie komórki kolumny "Wypłata przez kredyt" na arkuszu. W polu, jak widać, wyświetlono odniesienie do zakresu. Składa się z dwóch części oddzielonych dwukropkiem: odniesień do pierwszej komórki zakresu i do ostatniej. Aby móc skopiować wskazaną formułę w przyszłości za pomocą znacznika wypełnienia, tworzymy pierwszą część odniesienia do bezwzględnego zakresu. Wybierz i kliknij przycisk funkcyjny F7 . Druga część odniesienia jest względna. Teraz, gdy użyjesz znacznika wypełnienia, pierwsza komórka zakresu zostanie ustalona, ​​a ostatnia komórka zostanie rozciągnięta podczas ruchu w dół. To jest to, czego potrzebujemy, aby zrealizować nasze cele. Następnie kliknij przycisk "OK" .

    19. Okno argumentu funkcji SUMA w Microsoft Excel

  19. Tak więc wynik salda zadłużenia kredytowego po drugim miesiącu jest wyświetlany w komórce. Teraz, zaczynając od tej komórki, kopiujemy formułę do pustych elementów kolumn za pomocą uchwytu wypełnienia.

    20. Wypełniacz w programie Microsoft Excel

  20. Miesięczne wyliczenia sald do spłaty pożyczki dokonywane są za cały okres kredytowania. Zgodnie z oczekiwaniami na koniec okresu kwota ta wynosi zero.

Obliczanie salda do wypłaty pożyczki w programie Microsoft Excel

Tak więc, nie tylko obliczyliśmy płatność za pożyczkę, ale zorganizowaliśmy rodzaj kalkulatora kredytowego. Kto będzie działał w systemie rentowym. Jeśli w oryginalnej tabeli, na przykład, zmieniamy kwotę kredytu i roczną stopę procentową, to w tabeli końcowej nastąpi automatyczne przeliczenie danych. W związku z tym można go wykorzystać nie tylko raz w konkretnym przypadku, ale można go również wykorzystać w różnych sytuacjach do obliczenia opcji pożyczki w systemie rent.

Oryginalne dane zostały zmienione w programie Microsoft Excel

Lekcja: Funkcje finansowe w Excelu

Jak widać, korzystając z programu Excel w domu, można łatwo obliczyć całkowitą miesięczną spłatę pożyczki w systemie rent, wykorzystując do tych celów operatora PLT . Ponadto, za pomocą funkcji OPST i MTEF, możliwe jest obliczenie kwoty płatności na pożyczce i odsetki za określony okres. Stosując cały ten bagaż funkcji razem, możliwe jest stworzenie potężnego kalkulatora pożyczkowego, którego można użyć więcej niż jeden raz do obliczenia wypłaty renty.