Izračunavanje plaćanja anuiteta u Microsoft Excelu

Prije nego što preuzmete zajam, bilo bi lijepo izračunati sve isplate za to. To će u budućnosti spasiti dužnika od raznih neočekivanih nevolja i razočaranja kada se ispostavi da je prekoračenje preveliko. Pomoć u ovom izračunu može alati Excel. Doznajemo kako izračunati otplate kredita za rentu u ovom programu.

Izračun plaćanja

Prije svega, moram reći da postoje dvije vrste plaćanja zajma:

• diferencirani;
• Renta.

S diferenciranom shemom, klijent ulazi u banku jednakim mjesečnim udjelom plaćanja na kreditnom tijelu uz plaće kamata. Vrijednost plaćanja kamata smanjuje se svaki mjesec, jer se tijelo kredita iz kojeg se obračunavaju umanjuje. Tako se također smanjuje ukupna mjesečna isplata.

S shemom renta, koristi se malo drugačiji pristup. Klijent izrađuje isti iznos ukupne uplate svakog mjeseca, koji se sastoji od plaćanja na kreditnom tijelu i plaćanja kamata. U početku, kamate su za puni iznos zajma, ali kako se tijelo smanjuje, također se smanjuje interes. No, ukupni iznos plaćanja ostaje nepromijenjen zbog mjesečnog povećanja iznosa uplata na kreditni organ. Dakle, tijekom vremena, udio interesa u ukupnom mjesečnom plaćanju se smanjuje, a specifična težina plaćanja za tijelo se povećava. Istovremeno, ukupna mjesečna plaćanja ne mijenjaju se tijekom trajanja zajma.

Samo za izračun plaćanja rente, prestat ćemo. Štoviše, ovo je stvarna, jer trenutno većina banaka koristi ovu shemu. Korisnik je također prikladan jer se u tom slučaju ukupni iznos plaćanja ne mijenja i ostaje fiksan. Kupci uvijek znaju koliko će platiti.

Korak 1: Izračunavanje mjesečne rate

Za izračunavanje mjesečne naknade pri korištenju sheme renta u programu Excel, postoji posebna funkcija - PLT . Pripada kategoriji financijskih operatora. Formula za ovu funkciju je kako slijedi:

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

Kao što možete vidjeti, ova funkcija ima dosta argumenata. Istina, posljednja dva od njih nisu obvezna.

Argument "Bet" označava kamatnu stopu za određeno razdoblje. Ako se, na primjer, koristi godišnja stopa, ali zajam se plaća mjesečno, tada se godišnja stopa treba podijeliti s 12, a rezultat se koristi kao argument. Ako se koristi tromjesečni način plaćanja, tada u ovom slučaju godišnja stopa treba podijeliti s 4 , itd.

"Kper" se odnosi na ukupan broj razdoblja otplate kredita. To jest, ako se zajam uzima za jednu godinu uz mjesečnu uplatu, tada se broj razdoblja smatra 12 , ako za dvije godine, onda je broj razdoblja 24 . Ako se zajam uzme tromjesečno plaćanje dvije godine, tada je broj razdoblja 8 .

"Ps" označava trenutnu vrijednost. Jednostavno rečeno, to je ukupni iznos zajma na početku zajma, odnosno iznos koji ste posudili bez uzimanja u obzir kamata i drugih dodatnih plaćanja.

"B" je buduća vrijednost. Taj iznos, koji će biti tijelo zajma u vrijeme završetka ugovora o zajmu. U većini slučajeva ovaj argument je jednak "0" , jer dužnik na kraju razdoblja kredita mora u potpunosti podmiriti vjerovniku. Navedeni argument nije obavezan. Stoga, ako je izostavljeno, smatra se nultom.

Argument "Tip" određuje vrijeme obračuna: na kraju ili na početku razdoblja. U prvom slučaju, ona uzima vrijednost "0" , au drugom - "1" . Većina bankarskih institucija koristi opciju s plaćanjem na kraju razdoblja. Ovaj argument je također neobavezan, a ako se izostavlja pretpostavlja se da je nula.

Sada je vrijeme da se prebacujemo na konkretan primjer izračuna mjesečne naknade pomoću funkcije PLT-a. Za obračun koristimo tablicu s početnim podacima koji prikazuju kamatnu stopu na zajam ( 12% ), vrijednost zajma ( 500.000 rubalja ) i kreditni rok ( 24 mjeseca ). U tom slučaju plaćanje se vrši mjesečno na kraju svakog razdoblja.

1. Odaberite element na listu na kojem će se prikazati rezultat izračuna, a zatim kliknite ikonu "Umetni funkciju" koja se nalazi pored linije formule.
2. Pokreće se prozor čarobnjaka funkcija . U kategoriji "Financijska" odaberemo naziv "PLT" i kliknite na gumb "OK" .
3. Nakon toga otvara se prozor argumenata operatora PLT-a .

   U polju "Cijena" unesite iznos kamate za razdoblje. To se može obaviti ručno, samo stavljanjem postotka, ali imamo navedenu u zasebnoj ćeliji na listi pa im dajemo vezu. Postavite pokazivač u polje, a zatim kliknite odgovarajuću ćeliju. No, kao što se sjećamo, u našem se tablici postavlja godišnja kamatna stopa, a rok plaćanja iznosi mjesec dana. Stoga, podijelimo godišnju stopu, odnosno referencu na ćeliju u kojoj se nalazi, brojem 12 , što odgovara broju mjeseci u godini. Podjela se izvodi izravno u polju prozora argumenata.

   U polju "Kper" postavlja se razdoblje kreditiranja. Imamo ga za 24 mjeseca. Ručno možete upisati broj 24 , ali mi, kao u prethodnom slučaju, označimo vezu do mjesta ovog indikatora u izvornoj tablici.

   U polju "P" naznačena je izvorna vrijednost zajma. To je jednako 500.000 rubalja . Kao i kod prethodnih slučajeva, označavamo vezu s elementom listova koji sadrži taj pokazatelj.

   Polje "Bs" označava iznos kredita, nakon pune isplate. Kao što se sjećate, ta je vrijednost gotovo uvijek nula. U ovom polju postavili smo broj "0" . Iako se ovaj argument može izostaviti uopce.

   U polju "Vrsta" naznačujemo na početku ili na kraju mjeseca uplate. Mi, kao u većini slučajeva, proizvodi se krajem mjeseca. Stoga smo postavili broj "0" . Kao i kod prethodnog argumenta, ne možete unijeti ništa u ovo polje, a program će pretpostaviti da je vrijednost nula.

   Nakon što unesete sve podatke, kliknite gumb "OK" .

4. Nakon toga, rezultat izračuna prikazuje se u ćeliji koju smo odabrali u prvom stavku ovog priručnika. Kao što vidite, vrijednost mjesečne ukupne uplate zajma iznosi 23.536,74 rubalja . Nemojte biti zbunjeni znakom "-" prije tog iznosa. Axel ističe da je to novčani tok, odnosno gubitak.
5. Kako bi se izračunao ukupni iznos plaćanja za cijelo vrijeme zajma, uzimajući u obzir otplatu kreditnog tijela i mjesečne kamate, dovoljno je povećati vrijednost mjesečne uplate ( 23536,74 rubalja ) za mjesec mjeseci ( 24 mjeseca ). Kao što vidite, ukupan iznos uplata za cijelo vrijeme zajma u našem je slučaju 564881,67 rubalja .
6. Sada možete izračunati iznos preplate na zajam. Da biste to učinili, potrebno je oduzeti od ukupnog iznosa plaćanja na zajam, uključujući kamate i zajam tijelo, početni iznos posuđene. Ali imamo na umu da prva od tih vrijednosti već ima znak "-" . Stoga, u našem konkretnom slučaju, ispostavilo se da je potrebno presavijati. Kao što vidite, ukupni iznos preplate za zajam za cijelo razdoblje iznosio je 64.881,67 rubalja .

Pouka: Čarobnjak za funkcije u Excelu

Korak 2: pojedinosti o plaćanjima

A sada ćemo uz pomoć drugih Axelovih operatora izvršiti mjesečne podatke o plaćanju kako bismo vidjeli koliko u određenom mjesecu plaćamo na kreditnom tijelu i koliko je to interesa. U tu svrhu nacrtat ćemo tablicu u Excelu, koju ćemo popuniti podacima. Redci ove tablice odgovaraju odgovarajućem razdoblju, odnosno mjesecu. Obzirom da je razdoblje kreditiranja za nas 24 mjeseca, tada će i broj redaka biti prikladan. Stupci ukazuju na plaćanje kreditnog tijela, plaćanje kamate, ukupnu mjesečnu uplatu, što je zbroj prethodnih dvaju stupaca, a preostali iznos koji se plaća.

1. Kako bismo odredili iznos uplate za kreditno tijelo, koristimo funkciju OSPLT-a , koja je namijenjena samo za te svrhe. Postavite pokazivač u ćeliju koja je u retku "1" iu stupcu "Plaćanje po tijelu zajma". Kliknite gumb "Umetni funkciju" .
2. Idite na Čarobnjak za funkcije . U kategoriji "Financijska" označavamo naziv "OSPLT" i kliknite gumb "U redu" .
3. Pokreće se prozor argumenata operatera OSPLT. Ona ima sljedeću sintaksu:

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

   Kao što vidite, argumenti ove funkcije gotovo se podudaraju s argumentima operatora PLT-a , samo je obvezni argument "Period" dodan umjesto opcionalnog argumenta "Tip" . Označava broj razdoblja isplate i, u našem konkretnom slučaju, broj mjeseca.

   Ispunjavamo polja prozora argumenata OSPLT funkcije koji su nam već poznati po istim podacima koji su korišteni za funkciju PLT-a . Uzimajući u obzir samo činjenicu da će se formula u budućnosti kopirati pomoću oznake za ispunjavanje, morate učiniti sve veze u apsolutnim poljima tako da se oni ne mijenjaju. Za to je potrebno staviti znak dolara prije svake vrijednosti koordinata duž vertikalnih i vodoravnih linija. Ali to je lakše to učiniti, jednostavno odabirom koordinata i pritiskom na tipku F4 . Dolar će se automatski nalaziti na pravim mjestima. Također ne zaboravite da godišnju stopu treba podijeliti s 12 .

4. No, još imamo još jedan novi argument koji PLT funkcija nije imala. Ovaj argument je "Period" . U odgovarajućem polju postavite referencu na prvu ćeliju stupca "Period" . Ovaj element lista sadrži broj "1" , koji označava broj prvog mjeseca kreditiranja. Ali, za razliku od prethodnih polja, u navedenom polju ostavljamo referentni rođak, a ne od njega čine apsolutno.

   Nakon što unesete sve podatke o kojima smo već govorili, kliknite na gumb "OK" .

5. Nakon toga, u ćeliji, koju smo ranije dodijelili, prikazat će se iznos uplate na kreditni organ za prvi mjesec. To će biti 18536,74 rubalja .
6. Zatim, kao što je gore spomenuto, trebamo kopirati ovu formulu na ostatak ćelija u stupcu pomoću ručke za punjenje. Da biste to učinili, postavite pokazivač u donji desni kut ćelije koji sadrži formulu. Kursor se pretvara u križ, koji se zove oznaka za ispunjavanje. Pritisnite lijevu tipku miša i povucite je do kraja tablice.
7. Kao rezultat toga, sve stanice u stupcu su ispunjene. Sada imamo rok otplate zajma mjesečno. Kao što je spomenuto, povećava se iznos uplate za ovaj članak za svako novo razdoblje.
8. Sada moramo napraviti mjesečni obračun plaćanja kamata. U tu svrhu koristit ćemo operatera PRPLT . Odaberite prvu praznu ćeliju u stupcu "Plaćanje kamata" . Kliknite gumb "Umetni funkciju" .
9. U prozoru čarobnjaka funkcija u kategoriji "Financijska" odabiremo naziv PRPLT . Kliknite gumb "U redu" .
10. Pokrenut je argumentni prozor PRPLT funkcije. Sintaksa je sljedeća:

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

    Kao što vidite, argumenti ove funkcije apsolutno su identični analognim elementima OSPLT operatora. Stoga smo samo stavili u prozor iste podatke koje smo unijeli u prethodnom prozoru argumenata. Ne zaboravimo, međutim, da je veza u polju "Period" trebala biti relativna, au svim ostalim područjima koordinate bi trebale biti dovedene u apsolutni oblik. Nakon toga kliknite gumb "U redu" .

11. Zatim se u odgovarajućoj ćeliji prikazuje rezultat izračuna iznosa kamate za zajam za prvi mjesec.
12. Primjenom markera za ispunjavanje, kopirajte formulu na preostale dijelove stupca, tako da dobijemo mjesečni raspored plaćanja za kamate na zajam. Kao što vidimo, kao što je rečeno ranije, vrijednost ove vrste plaćanja smanjuje se od mjeseca do mjeseca.
13. Sada moramo izračunati ukupnu mjesečnu uplatu. Za ovaj izračun ne treba se pribjeći ni jednom operatoru jer se može koristiti jednostavna aritmetička formula. Dodat ćemo sadržaj ćelija prvog mjeseca stupaca "Plaćanje kreditnim tijelom" i "Plaćanje kamata" . Da biste to učinili, postavite znak "=" na prvu praznu ćeliju stupca "Ukupno mjesečna plaćanja" . Zatim kliknite dva gore navedena elementa, postavljajući znak "+" između njih. Pritisnite tipku Enter .
14. Nadalje, upotrebom oznake za ispunjavanje, kao u prethodnim slučajevima, ispunjavamo stupac s podacima. Kao što vidimo, tijekom cijele operacije ugovora iznos ukupne mjesečne uplate, uključujući plaćanje na kredit tijelo i plaćanje kamata će iznositi 23536,74 rubalja . Zapravo smo već računali ovu figuru uz pomoć PLT-a . No, u ovom slučaju je jasnije prikazan, upravo kao iznos plaćanja za kredit tijelo i kamate.
15. Sada trebate dodati podatke u stupac, gdje će saldo iznosa kredita biti prikazan mjesečno, što je i dalje potrebno platiti. U prvoj ćeliji stupca "Ravnoteža do isplata" izračun će biti najjednostavniji. Moramo ukloniti izvorni iznos kredita koji je naveden u tablici s primarnim podacima, isplatu na kreditnom tijelu za prvi mjesec u tablici za namiru. No, s obzirom na činjenicu da jedan od brojeva koje već imamo s znakom "-" , ne treba ih oduzeti, već presavijeni. Učinite to i pritisnite gumb Enter .
16. Ali izračun salda za plaćanje nakon drugog i sljedećeg mjeseca bit će nešto složeniji. Da bismo to učinili, moramo ukloniti od tijela zajma na početku zajma ukupan iznos isplate na kreditni organ za prethodno razdoblje. Postavite znak "=" u drugu ćeliju stupca "Ravnoteža u isplatu" . Zatim naznačujemo vezu s ćelijom koja sadrži izvorni iznos kredita. Apsolutni smo ga odabirom i pritiskom tipke F4 . Zatim stavljamo znak "+" , jer će druga vrijednost biti negativna za nas. Nakon toga kliknite na gumb "Umetni funkciju" .
17. Pokreće se čarobnjak za funkcije , u kojemu je potrebno prijeći u kategoriju "Matematički" . Tu odabire natpis "SUM" i kliknite na "OK" gumb.
18. Otvori se prozor argumenta funkcije SUM . Navedeni operater služi za sažimanje podataka u ćelijama, koje moramo obaviti u stupcu "Plaćanje po tijelu zajma". Ona ima sljedeću sintaksu:

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

    Argumenti su reference na stanice koje sadrže brojeve. Postavili smo kursor u polje "Broj1" . Zatim zadržite lijevu tipku miša i odaberite prve dvije ćelije stupca "Isplata po kreditnom tijelu" na listi. U polju, kao što možete vidjeti, prikazana je referenca na raspon. Sastoji se od dva dijela odvojena dvotočkom: reference na prvu ćeliju raspona i do posljednjeg. Da bismo u budućnosti mogli kopirati navedenu formulu pomoću oznake za ispunjavanje, prvi dio referenciramo na apsolutni raspon. Odaberite ga i kliknite funkcijsku tipku F7 . Drugi dio referenca ostaje relativan. Sada kada koristite oznaku za punjenje, prva ćelija raspona bit će fiksirana, a zadnja ćelija će se rastegnuti dok se kreće prema dolje. To je ono što moramo ispuniti naše ciljeve. Zatim kliknite gumb "OK" .

19. Dakle, rezultat salda kreditnog duga nakon drugog mjeseca prikazan je u ćeliji. Sada, počevši od ove ćelije, kopiramo formulu na prazne elemente stupaca pomoću ručice za punjenje.
20. Mjesečni obračun stanja za plaćanje kredita vrši se za cijelo kreditno razdoblje. Kao što se očekivalo, na kraju razdoblja taj iznos je nula.

Dakle, nismo samo izračunali plaćanje za kredit, već smo organizirali neku vrstu kreditnog kalkulatora. Tko će djelovati na shemi renta. Ako, primjerice, u izvornoj tablici mijenjamo iznos kredita i godišnju kamatnu stopu, tada će u završnom stolu biti automatski ponovni izračun podataka. Stoga se može koristiti ne samo jednom za određeni slučaj, već se može koristiti u različitim situacijama za izračunavanje opcija kredita na shemi renta.

Pouka: Financijske funkcije u Excelu

Kao što možete vidjeti, pomoću programa Excel kod kuće možete jednostavno izračunati ukupnu mjesečnu isplatu kredita na shemu anuiteta, koristeći se za te svrhe operateru PLT . Uz to, uz pomoć funkcija OPST-a i MTEF- a , moguće je izračunati iznos uplate na kreditni organ i na kamate za određeno razdoblje. Primjenjujući sve ove prtljage funkcija zajedno, moguće je stvoriti snažan kredit kalkulator koji možete koristiti više od jednom za izračunavanje renta plaćanja.

• ← Postavite dan u tjednu po datumu u programu Microsoft Excel
• Primjena BPR funkcije u Microsoft Excelu →