Formula za izračun anuiteta u Excelu

Plaćanje kredita lakše je i brže izračunati uz Microsoft Office Excel. Puno se više vremena troši na ručni izračun. Ovaj će se članak usredotočiti na isplate anuiteta, značajke njihovog izračuna, prednosti i nedostatke.

Što je anuitetna isplata

Način mjesečne otplate kredita, kod kojeg se položeni iznos ne mijenja tijekom cijelog razdoblja kredita. Oni. na određene datume u svakom mjesecu, osoba polaže određeni iznos novca dok se zajam u potpunosti ne otplati.

Štoviše, kamata na kredit već je uključena u ukupni iznos plaćen banci.

Klasifikacijska renta

Anuitetna plaćanja mogu se podijeliti u sljedeće vrste:

1. Popravljeno. Plaćanja koja se ne mijenjaju imaju fiksnu stopu bez obzira na vanjske uvjete.
2. Valuta. Mogućnost promjene iznosa uplate u slučaju pada ili rasta tečaja.
3. indeksirano. Plaćanja ovisno o razini, pokazatelj inflacije. Tijekom trajanja posudbe njihova se veličina često mijenja.
4. Varijable. Anuitet, koji se može mijenjati ovisno o stanju financijskog sustava, instrumentima.

Obratiti pažnju! Fiksna plaćanja poželjna su za sve zajmoprimce jer imaju mali rizik.

Prednosti i nedostaci isplate anuiteta

Za bolje razumijevanje teme potrebno je proučiti ključne značajke ove vrste otplate kredita. Ima sljedeće prednosti:

• Utvrđivanje određenog iznosa plaćanja i datuma njegovog plaćanja.
• Visoka dostupnost za zajmoprimce. Gotovo svatko može podnijeti zahtjev za rentu, bez obzira na njihovu financijsku situaciju.
• Mogućnost snižavanja iznosa mjesečne rate s porastom inflacije.

Nije bez nedostataka:

• Visoka stopa. Zajmoprimac će preplatiti veći iznos novca u usporedbi s diferencijalnom otplatom.
• Problemi koji proizlaze iz želje da se dug isplati prije roka.
• Nema preračunavanja za prijevremene uplate.

Što je plaćanje kredita?

Anuitetna isplata ima sljedeće komponente:

• Kamate koje plaća osoba prilikom otplate kredita.
• Dio iznosa glavnice.

Kao rezultat toga, ukupni iznos kamata gotovo uvijek premašuje iznos koji je zajmoprimac dao za smanjenje duga.

Osnovna formula za plaćanje rente u Excelu

Kao što je gore spomenuto, u programu Microsoft Office Excel možete raditi s različitim vrstama plaćanja za kredite i predujmove. Renta nije iznimka. Općenito, formula pomoću koje možete brzo izračunati rentne doprinose je sljedeća:  

Važno! Nemoguće je otvoriti zagrade u nazivniku ovog izraza da bi se pojednostavio.

Glavne vrijednosti formule dešifriraju se na sljedeći način:

• AP – isplata rente (naziv je skraćen).
• O – veličina glavnice duga zajmoprimca.
• PS – kamatna stopa koju na mjesečnoj razini propisuje određena banka.
• C je broj mjeseci koliko kredit traje.

Za asimilaciju informacija dovoljno je dati nekoliko primjera korištenja ove formule. O njima će se dalje raspravljati.

Primjeri korištenja PMT funkcije u Excelu

Dajemo jednostavan uvjet problema. Potrebno je izračunati mjesečnu otplatu kredita ako banka iznosi kamatu od 23%, a ukupni iznos je 25000 3 rubalja. Kreditiranje će trajati XNUMX godine. Problem se rješava prema algoritmu:

1. Napravite opću proračunsku tablicu u Excelu na temelju izvornih podataka.

2. Aktivirajte PMT funkciju i unesite argumente za nju u odgovarajući okvir.
3. U polje “Bet” unesite formulu “B3/B5”. To će biti kamatna stopa na kredit.
4. U retku “Nper” upišite vrijednost u obliku “B4*B5”. To će biti ukupan broj plaćanja za cijelo razdoblje kredita.
5. Ispunite polje “PS”. Ovdje morate navesti početni iznos uzet od banke, upisujući vrijednost "B2".

6. Provjerite je li nakon klika na “OK” u izvornoj tablici izračunata vrijednost “Mjesečno plaćanje”.

Dodatne informacije! Negativan broj označava da zajmoprimac troši novac.

Primjer izračuna iznosa preplate zajma u Excelu

U ovom problemu morate izračunati iznos koji će preplatiti osoba koja je uzela kredit od 50000 27 rubalja po kamatnoj stopi od 5% na 12 godina. Ukupno, zajmoprimac čini XNUMX plaćanja godišnje. Riješenje:

1. Sastavite izvornu tablicu podataka.

2. Od ukupnog iznosa uplata oduzmite početni iznos prema formuli «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Mora se umetnuti u traku formule na vrhu glavnog izbornika programa.
3. Kao rezultat toga, iznos preplaćenih iznosa pojavit će se u zadnjem retku stvorene ploče. Zajmoprimac će preplatiti 41606 rubalja na vrhu.

Formula za izračun optimalne mjesečne rate kredita u Excelu

Zadatak sa sljedećim uvjetom: klijent je registrirao bankovni račun od 200000 4 rubalja s mogućnošću mjesečne nadopune. Potrebno je izračunati iznos plaćanja koji osoba mora izvršiti svaki mjesec, tako da nakon 2000000 godine na svom računu ima 11 rubalja. Stopa je XNUMX%. Riješenje:

1. Napravite proračunsku tablicu na temelju izvornih podataka.

2. Unesite formulu u Excel redak za unos «=ПЛТ(B3/B5;B6*B5;-B2;B4)» i pritisnite "Enter" na tipkovnici. Slova će se razlikovati ovisno o ćelijama u koje se nalazi tablica.
3. Provjerite je li iznos doprinosa automatski izračunat u zadnjem retku tablice.

Obratiti pažnju! Dakle, da bi klijent akumulirao 4 rublje po stopi od 2000000% u 11 godina, mora svaki mjesec položiti 28188 rubalja. Minus u iznosu označava da klijent daje gubitke davanjem novca banci.

Značajke korištenja PMT funkcije u Excelu

Općenito, ova se formula piše na sljedeći način: =PMT(stopa; nper; ps; [bs]; [vrsta]). Funkcija ima sljedeće značajke:

1. Pri obračunu mjesečnih doprinosa u obzir se uzima samo godišnja stopa.
2. Prilikom određivanja kamatne stope važno je izvršiti preračun prema broju rata godišnje.
3. Umjesto argumenta "Nper" u formuli je naznačen određeni broj. Ovo je razdoblje plaćanja.

Obračun plaćanja

Općenito, anuitet se obračunava u dvije faze. Da bismo razumjeli temu, svaku od faza treba razmotriti zasebno. O tome će se dalje raspravljati.

1. faza: obračun mjesečne rate

Da biste izračunali u Excelu iznos koji trebate platiti svaki mjesec za kredit s fiksnom stopom, morate:

1. Sastavite izvornu tablicu i odaberite ćeliju u kojoj želite prikazati rezultat te kliknite gumb "Umetni funkciju" na vrhu.

2. Na popisu funkcija odaberite “PLT” i kliknite “OK”.

3. U sljedećem prozoru postavite argumente za funkciju, označavajući odgovarajuće retke u sastavljenoj tablici. Na kraju svakog retka potrebno je kliknuti na ikonu, a zatim odabrati željenu ćeliju u nizu.

4. Kada su svi argumenti popunjeni, odgovarajuća formula bit će ispisana u retku za unos vrijednosti, a rezultat izračuna s predznakom minus pojavit će se u polju tablice “Mjesečno plaćanje”.

Važno! Nakon izračuna rate moći će se izračunati iznos koji će korisnik kredita preplatiti za cijelo razdoblje kredita.

Faza 2: detalji plaćanja

Iznos preplate može se izračunati mjesečno. Kao rezultat toga, osoba će razumjeti koliko će novca potrošiti na kredit svaki mjesec. Detaljni izračun se izvodi na sljedeći način:

1. Izradite tablicu za 24 mjeseca.

2. Postavite kursor u prvu ćeliju tablice i umetnite funkciju “OSPLT”.

3. Ispunite argumente funkcije na isti način.

4. Prilikom popunjavanja polja "Razdoblje" morate se pozvati na prvi mjesec u tablici, označavajući ćeliju 1.

5. Provjerite je li popunjena prva ćelija u koloni “Plaćanje po tijelu kredita”.
6. Da biste ispunili sve retke prvog stupca, morate rastegnuti ćeliju do kraja tablice

7. Odaberite funkciju “PRPLT” za popunjavanje drugog stupca tablice.
8. Ispunite sve argumente u otvorenom prozoru u skladu s donjom snimkom zaslona.

9. Izračunajte ukupnu mjesečnu uplatu zbrajanjem vrijednosti u prethodna dva stupca.

10. Da biste izračunali "Ostalo plaćanje", trebate dodati kamatnu stopu na plaćanje na tijelu zajma i rastegnuti ga do kraja ploče kako biste ispunili sve mjesece zajma.

Dodatne informacije! Pri izračunavanju ostatka na formulu moraju biti obješeni znakovi dolara kako se ne bi pomaknula kada se rasteže.

Izračun anuiteta po kreditu u Excelu

Funkcija PMT odgovorna je za izračun anuiteta u Excelu. Općenito načelo izračuna je izvođenje sljedećih koraka:

1. Sastavite izvornu tablicu podataka.
2. Napravite raspored otplate duga za svaki mjesec.
3. Odaberite prvu ćeliju u stupcu "Plaćanja po kreditu" i unesite formulu za izračun “PLT ($B3/12;$B$4;$B$2)”.
4. Dobivena vrijednost rasteže se za sve stupce ploče.

Obračun u MS Excelu otplate glavnice duga

Isplate anuiteta moraju se vršiti mjesečno u fiksnim iznosima. A kamata se ne mijenja.

Izračun stanja glavnice (sa BS=0, tip=0)

Pretpostavimo da se zajam od 100000 10 rubalja uzima na 9 godina uz 1%. Potrebno je izračunati iznos glavnice duga u 3. mjesecu XNUMX. godine. Riješenje:

1. Sastavite podatkovnu tablicu i izračunajte mjesečnu uplatu pomoću gornje PV formule.
2. Izračunajte udio plaćanja potrebnog za otplatu dijela duga pomoću formule «=-PMT-(PS-PS1)*stavka=-PMT-(PS +PMT+PS*stavka)».
3. Izračunajte iznos glavnice duga za 120 razdoblja koristeći poznatu formulu.
4. Pomoću HPMT operatera pronađite iznos plaćenih kamata za 25. mjesec.
5. Provjerite rezultat.

Izračunavanje iznosa glavnice koja je plaćena između dva razdoblja

Ovaj izračun najbolje je napraviti na jednostavan način. Za izračun iznosa u intervalu za dva razdoblja morate koristiti sljedeće formule:

• =«-BS(stavka; con_period; plt; [ps]; [tip]) /(1+tip *stavka)».
• = “+ BS(stopa; početno_razdoblje-1; plt; [ps]; [tip]) /IF(početno_razdoblje =1; 1; 1+tip *stopa)”.

Obratiti pažnju! Slova u zagradama zamijenjena su određenim vrijednostima.

Prijevremena otplata sa skraćenim rokom ili plaćanjem

Ako trebate skratiti rok kredita, morat ćete izvršiti dodatne izračune pomoću IF operatora. Tako će biti moguće kontrolirati nulti saldo, koji ne bi trebao biti dostignut prije kraja razdoblja plaćanja.

Da biste smanjili uplate, morate ponovno izračunati doprinos za svaki prethodni mjesec.

Kreditni kalkulator s neredovitim otplatama

Postoji nekoliko opcija anuiteta gdje zajmoprimac može položiti varijabilne iznose bilo kojeg dana u mjesecu. U takvoj situaciji stanje duga i kamate se obračunavaju za svaki dan. U isto vrijeme u Excelu trebate:

1. Unesite dane u mjesecu za koje se vrše uplate i navedite njihov broj.
2. Provjerite negativne i pozitivne iznose. Negativni su poželjni.
3. Brojite dane između dva datuma kada je novac položen.

Obračun periodičnog plaćanja u MS Excelu. Oročeni depozit

U Excelu možete brzo izračunati iznos redovnih uplata, pod uvjetom da je već akumuliran fiksni iznos. Ova se radnja izvodi pomoću PMT funkcije nakon što je početna tablica sastavljena.

Zaključak

Stoga je plaćanje rente lakše, brže i učinkovitije izračunati u Excelu. Za njihov izračun odgovoran je operater PMT-a. Detaljnije primjere možete pronaći gore.