Kako stvoriti vlastiti kalkulator amortizacije kredita?

Većina ljudi misli da postoji neka vrsta voodoo magije koja ide u stvaranje zajmova raspored amortizacije . Ali sretan sam vam što vam kažem da ne postoji - pa čak i bolje - čak i početnik može lako stvoriti pomoću programa Microsoft Excel.

Tablica amortizacije zajma daje vam mnogo dobrih informacija: ukupni iznos koji se plaća zajmodavcu, dio koji je kamata, dio koji je glavna isplata i nepodmirena bilanca kredita.

Kad se zajam amortizira, to jednostavno znači da je plaćanje kamata i glavna plaćanja kombinirana u jedno periodično plaćanje, a iznosi za plaćanje su jednaki za svako razdoblje plaćanja. No, jedna od najzanimljivijih značajki amortiziranog kredita je da se s svakom plaćanjem mijenja iznos iznosa kamata i glavnice.

Excel je iznimno koristan alat za izračunavanje vlastite tablice amortizacije. Možete čak i eksperimentirati s različitim ulazima kako biste vidjeli kako različite kamatne stope, rok otplate i glavni iznosi kredita utječu na isplate zajma koje ćete izvršiti.

Pogledajmo primjer kako bismo ilustrirali taj proces.

Pretpostavimo da ste upravo kupili auto s kreditom od 20.000 dolara koji se mora potpuno vratiti u roku od 4 godine. Godišnja kamatna stopa na zajam iznosi 3%. Budući da je zajam amortiziran, mjesečne isplate će biti isti iznos.

Prvi korak je izračunavanje mjesečnog iznosa uplate. To se može učiniti pomoću programa PMT (što znači "plaćanje") u programu Excel.

Stupac A sadrži sljedeće oznake za naše podatke:

A1 = izvorni zajam glavnice (vaš iznos kredita)

Započni izradom nove proračunske tablice i unosom sljedećih podataka potrebnih za izračun iznosa uplate.

A2 = zajam u godinama

A3 = godišnja kamatna stopa

A4 = broj plaćanja godišnje

A5 = iznos uplate (ono što izračunavamo)

Stupac B sadrži odgovarajuće vrijednosti za svaku oznaku u stupcu A:

B1 = $ 20,000

B2 = 4

B3 = 3%

B4 = 12

B5 = vrijednost koju tražimo

Excelova PMT funkcija može se naći u izborniku financijskih funkcija. Prvo odaberite ćeliju B5, zatim kliknite izbornik financijskih funkcija na kartici formula i odaberite PMT funkciju s popisa. Na zaslonu će se prikazati okvir za unos podataka iz proračunske tablice baš kao što je na ovoj slici:

Ovdje je nekoliko važnih stvari koje treba spomenuti. Prvo, trebamo napraviti dvije važne prilagodbe jer izračunavamo mjesečno plaćanja. Trebamo podijeliti godišnju stopu za 12 godina kako bismo dobili mjesečnu kamatnu stopu (stopu), a mi moramo pomnožiti 4 do 12 kako bismo dobili ukupni broj mjeseci u našem roku otplate (Nper).

Drugo, sadašnja vrijednost (PV) unosi se kao negativna vrijednost jer je taj iznos dužan zajmodavcu.

Kada se okviri popunjavaju, pritisnite "U redu".

Vaša proračunska tablica sada bi trebala izgledati ovako:

Sada ćemo početi graditi tablicu amortizacije.

Preskočite red ispod proračunske tablice napravljene u dijelu 1 i, počevši od reda 7, oznake koje počinju od stupca A i završavaju u stupcu E:

A7 = Mjesec

B7 = Plaćanje

C7 = Kamata

D7 = Principal

E7 = Stanje

pet naslova za tablicu amortizacije.

Sada je vrijeme za početak popunjavanja tablice. Prvi korak je unijeti broj mjeseci u životu zajma. Pod naslovom "mjesec" upišite "0" u ćeliju A8 (vrijeme izdavanja zajma). Popunite sljedeće stanice u rednom slijedu preko broja 48 (koji će vas staviti na ćeliju A56). Jedan izigrati: nakon što unesete 0, provjerite je li odabrana ćelija (A8), zatim povucite donji desni kut ćelije prema dolje do retka 56. Zatim odaberite "serijski niz" iz malog okvira koji se pojavljuje s desne strane posljednju ćeliju. Ovo bi trebalo automatski popuniti numeričku seriju za 48 mjeseci.

Zatim se sva plaćanja mogu popuniti jer je vrijednost svakog mjesečnog plaćanja već utvrđena. Počevši od prvog mjeseca (nema plaćanja u mjesecu 0 jer se plaćanja izvršavaju na kraju svakog mjeseca) ispunite vrijednost od 442,69 USD za svaki mjesec 1-48. Nakon toga, iznosi kamata i glavnice mogu se izračunati pomoću njihovih Excel funkcija.

Izračunavanje kamata i glavnih komponenti plaćanja pomoću funkcije Excel je jednostavan i može se upotrijebiti za popunjavanje cijele tablice amortizacije. Da biste izračunali plaćanje kamate, odaberite ćeliju C9, a zatim kliknite izbornik financijskih funkcija na kartici formula. Na popisu odaberite funkciju IPMT (za "plaćanje kamata"). Na zaslonu će se prikazati okvir na kojemu ćete unijeti podatke iz proračunske tablice baš kao što se nalazi na ovoj slici:

Napomena: znakovi "$" između ćelija i brojeva nužni su za kopiranje formule kasnije do preostalih stanica. "$" Briše navedenu ćeliju, tako da formule ostaju netaknute kada ih kopiramo preostale ćelije. Možete zatvoriti ćelije (i vidjeti "$") pritiskom na F4.

Dalje možemo izračunati iznos glavnice u plaćanju. Slično kao i izračun IPMT-a, odaberite ćeliju D9, a zatim kliknite izbornik financijskih funkcija na kartici formula. Na popisu odaberite funkciju PPMT (za "glavnu uplatu"). Prikazat će se okvir na kojemu ćete unijeti podatke iz proračunske tablice baš kao što je na ovoj slici:

Napomena: Glavni bi mogao može se izračunati pomoću ove funkcije ili jednostavno oduzimanjem iznosa kamate od ukupnog iznosa uplate. Isto tako, kamate se mogu izračunati ako smo najprije izračunali glavnicu pomoću PPMT funkcije, a zatim oduzeli tu vrijednost iz ukupne uplate. Međutim, to je dobro da znate kako koristiti obje formule, što također omogućuje da se ostatak tablice popuni na učinkovitiji način kako ćete vidjeti u nastavku.

Završni stupac (stupac E) je preostali stupac stanja. svaki mjesec nakon što smo utvrdili iznose kamate i glavnice, a za mjesec 0 još nije izvršena plaćanja, pa je saldo 20.000 $, no želimo naredbu Excel automatski izračunati saldo u svakom razdoblju, pa umjesto Manu saveznik unosi "20.000 USD", unesite "= B1", koji se odnosi na gornju tablicu za izračun plaćanja. Za mjesec 1, budući da se saldo računa pomoću oduzimanja stanja prethodnog mjeseca za glavno plaćanje tekućeg mjeseca, unesite "= E8-D9" u ćeliju E9. Ravnoteža će automatski biti izračunata. Tablica amortizacije sada je završena za mjesec 1 i trebala bi izgledati upravo ovako:

Svi se podaci za preostale mjesece mogu popuniti jednostavnim odabirom ćelija B9-E9, kopiranjem odabira, a zatim lijepljenjem odabira u ćelije B10-E10. Budući da smo unijeli formule za svaku ćeliju i zaključali naše ćelije koje će se koristiti u svakom izračunu, automatski se izračunavaju vrijednosti za kamate, glavnicu i ravnotežu. Vidjevši taj rad za 2. mjesec, čitava tablica može se dovršiti jednostavnim odabirom stupaca B-E do 48. mjeseca i lijepljenjem već kopiranih podataka. Excel je sada izračunao svaku vrijednost za tablicu amortizacije pomoću formula koje smo unijeli. Vaš dovršeni stol treba izgledati ovako:

Prva godina zajma:

Zadnja godina zajma:

Napomena: Ove tablice sadrže samo prve i posljednje godine tablica amortizacije. 2. i 3. godina bi se između ovih dviju tablica sastojale od jednog stalnog amortizacijskog stola za svih 48 mjeseci.

Ako ste zainteresirani za izračunavanje hipoteke za hipoteku s fiksnim stopama, možete sami napraviti tablicu amortizacije pomoću ovih koraka, ili možete upotrijebiti naš financijski kalkulator da to učinimo za vas. Naša hipoteka kalkulator će vam pokazati svoje mjesečne uplate, kao i puni raspored amortizacije.