Excel

Izračun plačila rente v programu Microsoft Excel

Preden vzamete posojilo, bi bilo lepo izračunati vsa plačila za to. To bo prihranilo posojilojemalca v prihodnosti iz različnih nepričakovanih težav in razočaranj, če se izkaže, da je preplačilo preveliko. Pomoč pri tem izračunu lahko orodje Excel. Ugotovimo, kako izračunati rentna posojila v tem programu.

Izračun plačila

Najprej moram reči, da obstajajo dve vrsti plačil posojil:

  • Diferencirana;
  • Anuitet.

Z diferencirano shemo odjemalec vstopi v banko enak mesečni delež plačil na organ posojila plus plačilo obresti. Vrednost plačil obresti se vsak mesec zmanjšuje, saj se zmanjša organ posojila, iz katerega so izračunani. Tako je tudi skupno mesečno plačilo zmanjšano.

Z rentno shemo se uporablja nekoliko drugačen pristop. Stranka vsak mesec izplača isti znesek celotnega plačila, ki je sestavljen iz plačil na organ za posojila in plačila obresti. Prvotno so plačila obresti za celoten znesek posojila, vendar se zmanjšuje obresti, ker se telo zmanjšuje. Toda skupni znesek plačila ostaja nespremenjen zaradi mesečnega povečanja zneska plačil na organu posojila. Tako se sčasoma delež obresti v skupnem mesečnem plačilu zmanjša in se poveča specifična teža plačila za telo. Hkrati se skupno mesečno plačilo ne spremeni v času trajanja posojila.

Samo pri izračunih rente bomo stopili. Poleg tega je to dejstvo, saj trenutno večina bank uporablja to shemo. Primeren je tudi za stranke, saj se v tem primeru celotni znesek plačila ne spremeni, ostane nespremenjen. Kupci vedno vedo, koliko plačati.

Korak 1: Izračun mesečne obroke

Če želite izračunati mesečno naročnino pri uporabi sheme rente v Excelu, obstaja posebna funkcija - PLT . Spada v kategorijo finančnih operaterjev. Formula za to funkcijo je naslednja:

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

Kot vidite, ima ta funkcija veliko argumentov. Res je, da zadnja dva nista obvezna.

Argument "Stavka" prikazuje obrestno mero za določeno obdobje. Če se na primer uporablja letna obrestna mera, posojilo pa se izplača mesečno, nato pa se letna stopnja razdeli na 12, rezultat pa se uporabi kot argument. Če se uporablja četrtletna metoda plačila, se v tem primeru letna stopnja razdeli s 4 , itd.

"Kper" se nanaša na skupno število obdobij odplačevanja posojila. To pomeni, da če se posojilo vzame eno leto z mesečnim izplačilom, se šteje število obdobij 12 , če je za dve leti število rokov 24 . Če posojilo traja dve leti s četrtletnim plačilom, potem je število obdobij 8 .

"Ps" označuje sedanjo vrednost. Z enostavnimi besedami je to skupni znesek posojila na začetku posojila, to je znesek, ki ga izposodite, ne da bi upoštevali obresti in druga dodatna plačila.

"Bs" je prihodnja vrednost. Ta znesek, ki bo organ posojila ob zaključku posojilne pogodbe. V večini primerov je ta trditev enaka "0" , ker mora posojilojemalec polno poravnati s posojilodajalcem ob koncu obdobja knjiženja. Navedeni argument ni obvezen. Zato se šteje, da je nič, če je izpuščen.

Argument "Vrsta" določa čas izračuna: na koncu ali na začetku obdobja. V prvem primeru je vrednost "0" , v drugi pa "1" . Večina bančnih institucij uporablja možnost plačila ob koncu obdobja. Ta argument je tudi neobvezen, in če je izpuščen, se domneva, da je nič.

Zdaj je čas za prehod na konkreten primer izračuna mesečne naročnine z uporabo funkcije PLT. Za izračun smo uporabili tabelo z začetnimi podatki, ki prikazuje obrestno mero za posojilo ( 12% ), vrednost posojila ( 500.000 rubljev ) in obdobje posojila ( 24 mesecev ). V tem primeru se plačilo opravi mesečno ob koncu vsakega obdobja.

  1. Izberite element na listu, na katerem bodo prikazani rezultati izračuna, in kliknite ikono »Vstavi funkcijo« , ki se nahaja v bližini vrstice s formulo.

    2. Odprite čarovnika za funkcije v programu Microsoft Excel

  2. Odpre se okno čarovnika funkcij . V kategoriji "Finančni" izberemo ime "PLT" in kliknite gumb "OK" .

    3. Odprite okno argumentov funkcije PLC v programu Microsoft Excel

  3. Po tem se odpre okno argumentov operaterja PLT .

    V polju »Stopnja« vpišite znesek obresti za obdobje. To je mogoče storiti ročno, samo dodati odstotek, vendar ga imamo na seznamu v ločeni celici, tako da ji dam povezavo. V polju postavite kazalko in nato kliknite ustrezno celico. Ampak, kot se spominjamo, je v naši tabeli določena letna obrestna mera, rok plačil pa je en mesec. Zato razdelimo letno stopnjo, oziroma s sklicevanjem na celico, v kateri je vsebovana, s številko 12 , ki ustreza številu mesecev v letu. Delitev se izvaja neposredno na področju okna argumentov.

    V polju "Kper" je nastavljeno kreditno obdobje. Imamo ga 24 mesecev. Številko 24 lahko vnesete ročno, vendar kot v prejšnjem primeru v izvorni tabeli označimo povezavo z lokacijo tega kazalnika.

    V polju "Ps" je označena izvirna vrednost posojila. To je enako 500.000 rubljev . Kot v prejšnjih primerih označujemo povezavo do elementa stanja, ki vsebuje ta indikator.

    Polje »Bs« označuje znesek posojila po celotnem plačilu. Kot se spomnite, je ta vrednost skoraj vedno nič. V tem polju smo nastavili številko »0« . Čeprav je ta argument mogoče izpustiti v celoti.

    V polju »Vrsta« označujemo v začetku ali konec meseca plačilo. Mi se v večini primerov izdelujemo ob koncu meseca. Zato smo nastavili številko »0« . Kot pri prejšnjem argumentu v to polje ne morete ničesar vnesti, potem bo program privzeto prevzel, da ima vrednost nič.

    Ko so vsi podatki vneseni, kliknite na gumb »V redu« .

    4. Okno argumenta funkcije PLT v programu Microsoft Excel

  4. Po tem se rezultat izračuna prikaže v celici, ki smo jo izbrali v prvem odstavku tega priročnika. Kot lahko vidite, je vrednost mesečnega skupnega plačila za posojilo 23.536,74 rubljev . Pred tem zneskom se ne sme zamenjati znak "-". Torej Axel poudarja, da je to denarni tok, to je izguba.

    5. Rezultat izračunavanja mesečnega plačila v programu Microsoft Excel

  5. Za izračun celotnega zneska plačila za celotno obdobje posojila, ob upoštevanju vračila posojilnega organa in mesečnih obresti, zadostuje, da se vrednost mesečnega plačila ( 23536,74 rubljev ) pomnoži s številom mesecev ( 24 mesecev ). Kot vidite, skupni znesek plačil za celotno posojilno obdobje v našem primeru je znašal 564881,67 rubljev .

    6. Skupni znesek plačil v programu Microsoft Excel

  6. Zdaj lahko izračunate znesek preplačila na posojilo. Da bi to naredili, je treba odvzeti celoten znesek plačil na posojilo, vključno z obrestmi in posojilnim organom, izposojeni začetni znesek. Vendar se spomnimo, da ima prva od teh vrednosti že znak "-" . Zato se v našem posebnem primeru izkaže, da jih je treba zložiti. Kot lahko vidite, je skupni znesek preplačila za posojilo za celotno obdobje znašal 64881,67 rubljev .

Prevelik znesek za posojilo v programu Microsoft Excel

Lekcija: Čarovnik za funkcije v Excelu

2. korak: podrobnosti o plačilih

In zdaj s pomočjo drugih operaterjev Axel bomo naredili mesečne podrobnosti o plačilu, da bi ugotovili, koliko v določenem mesecu plačujemo na posojilno telo in koliko je obresti. V ta namen izdelamo tabelo v Excelu, ki jo bomo izpolnjevali s podatki. Vrstice te tabele bodo ustrezale ustreznemu obdobju, to je mesecu. Ker je kreditno obdobje za nas 24 mesecev, bo tudi ustrezno število vrstic. V stolpcih je prikazano plačilo posojilnega organa, plačilo obresti, skupno mesečno plačilo, ki je vsota prejšnjih dveh stolpcev in preostali znesek, ki ga je treba plačati.

Tabela izplačil v programu Microsoft Excel

  1. Za določitev zneska plačila za posojilno telo uporabljamo funkcijo OSPLT , ki je pravkar namenjena za te namene. Nastavite kazalko v celici, ki je v vrstici "1" in v stolpcu "Plačilo, ki ga organ posojila . " Kliknite na gumb "Vstavi funkcijo" .

    2. Vstavi funkcijo v programu Microsoft Excel

  2. Odprite čarovnika za funkcije . V kategoriji "Finančni" označujemo ime "OSPLT" in kliknite gumb "V redu" .

    3. Pojdite v okno argumentov funkcije OSPLT v programu Microsoft Excel

  3. Odpre se okno argumentov operaterja OSPLT. Ima naslednjo skladnjo:

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

    Kot lahko vidite, argumenti te funkcije skoraj popolnoma sovpadajo z argumenti operaterja PLT , namesto opcijskega "tipa" argumenta je bil dodan obvezen argument "Period" . Navede številko obdobja izplačil in v našem posameznem primeru številko meseca.

    Polja okna argumentov funkcije OSPLT, ki nam jih že poznajo, zapolnimo z istimi podatki, ki smo jih uporabili za funkcijo PLT . Le ob upoštevanju dejstva, da se bo formula v prihodnosti kopirala z oznako polnila, morate vse poljubne povezave narediti absolutno, da se ne spremenijo. Za to je potrebno postaviti znak za dolar pred vsako vrednostjo koordinat vzdolž navpičnih in vodoravnih linij. Toda to je lažje, preprosto izberete koordinate in pritisnete funkcijsko tipko F4 . Znak dolarja bo samodejno nameščen na pravih mestih. Prav tako ne pozabite, da je treba letno stopnjo razdeliti na 12 .

    4. Okno zaslona funkcije OBSFT v programu Microsoft Excel

  4. Vendar imamo še vedno nov argument, ki ga funkcija PLT ni imela. Ta argument je "Period" . V ustreznem polju nastavite sklic na prvo celico stolpca »Obdobje« . Ta element lista vsebuje številko »1« , ki označuje številko prvega meseca kreditiranja. Toda za razliko od prejšnjih polj v navedenem polju zapustimo referenčni relativ in ga ne naredimo absolutno.

    Ko vnesete vse podatke, o katerih smo govorili zgoraj, kliknite gumb »V redu« .

    5. Argument za obdobje v oknu argumentov funkcije OBSF v programu Microsoft Excel

  5. Po tem bomo v celici, ki smo jo prej dodelili, prikazan znesek plačila na organu za posojila za prvi mesec. To bo 18536,74 rubljev .

    6. Rezultat izračuna funkcije OSPLT v programu Microsoft Excel

  6. Potem, kot smo omenili zgoraj, moramo to formulo kopirati na ostale celice v stolpcu z uporabo polnilnega ročaja. Če želite to narediti, nastavite kazalko v spodnji desni kot celice, ki vsebuje formulo. Kazalec se pretvori v križec, ki se imenuje oznaka za polnjenje. Pritegnite levi gumb miške in ga povlecite do konca mize.

    7. Polnilo v programu Microsoft Excel

  7. Posledično se polnijo vse celice v stolpcu. Zdaj imamo mesečni čas odplačila posojila. Kot je navedeno zgoraj, se znesek plačila za ta člen z vsakim novim obdobjem poveča.

    8. Znesek plačila za telo posojila na mesec v Microsoft Excelu

  8. Zdaj moramo mesečno izračunati plačila obresti. Za te namene bomo uporabili operaterja PRPLT . V stolpcu »Plačevanje obresti« izberite prvo prazno celico. Kliknite na gumb "Vstavi funkcijo" .

    9. Odprite čarovnika za funkcije v programu Microsoft Excel

  9. V oknu Čarovnikov funkcij v kategoriji "Finančno" izberemo ime PRPLT-a . Kliknite gumb »V redu« .

    10. Pojdite v okno argumentov funkcije PRPLT v programu Microsoft Excel

  10. Začelo se je okno argumentov funkcije PRPLT . Sintaksa je naslednja:

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

    Kot lahko vidite, so argumenti te funkcije povsem enaki analognim elementom operaterja OSPLT . Zato smo v okno vnesli le iste podatke, ki smo jih vnesli v prejšnje okno argumentov. Vendar ne pozabljamo, da bi moralo biti referenca v polju "Obdobje" relativna, na vseh ostalih področjih pa je treba koordinate prenesti v absolutno obliko. Nato kliknite gumb "OK" .

    11. Okno argumentov funkcije PRPLT v programu Microsoft Excel

  11. Nato je rezultat izračuna zneska obresti za posojilo za prvi mesec prikazan v ustrezni celici.

    12. Rezultat izračunavanja funkcije PRPLT v programu Microsoft Excel

  12. Z uporabo označevalnika polnjenja kopiramo formulo na preostale elemente stolpca, s čimer dobimo mesečno plačilno shemo za obresti na posojilo. Kot vidimo, kot je bilo že rečeno, se vrednost tega tipa plačil zmanjšuje iz meseca v mesec.

    13. Urnik plačil obresti za posojilo v programu Microsoft Excel

  13. Sedaj moramo izračunati skupno mesečno plačilo. Za ta izračun se ne smemo zateči k nobenemu operatorju, saj lahko uporabimo preprosto aritmetično formulo. Dodamo vsebino celic prvega meseca stolpcev "Plačilo s strani posojilnega organa" in "Izplačilo obresti" . Če želite to narediti, vnesite znak "=" v prvo prazno celico stolpca »Skupno mesečno plačilo« . Nato kliknite na dva zgornja elementa, med njimi nastavite znak "+" . Pritisnite tipko Enter .

    14. Vsota skupnega mesečnega plačila v programu Microsoft Excel

  14. Nadalje, z uporabo označevalnika polnila, kot v prejšnjih primerih, kolono napolnimo s podatki. Kot vidimo, bo celotna mesečna plačila, vključno s plačilom na organu za posojila, in plačilom obresti v celotnem času trajanja pogodbe znašala 23536,74 rubljev . Pravzaprav smo to številko že šteli s pomočjo PLT . Toda v tem primeru je bolj jasno predstavljen, točno kot znesek plačila za posojilni organ in obresti.

    15. Skupno mesečno plačilo v programu Microsoft Excel

  15. Zdaj morate dodati podatke v stolpec, kjer se bo mesečno prikazalo stanje posojila, ki ga je še treba plačati. V prvi celici stolpca »Balance to Payout« bo izračun najpreprostejši. Od prvotnega zneska posojila, ki je naveden v tabeli s primarnimi podatki, moramo odvzeti plačilo za organ posojila za prvi mesec v tabeli poravnave. Toda, glede na dejstvo, da ena od številk, ki jih že imamo z znakom "-" , jih ne bi smeli odvzeti, ampak zložiti. Naredite to in pritisnite gumb Enter .

    16. Stanje, ki se plača po prvem mesecu kreditiranja v programu Microsoft Excel

  16. Toda izračun plačilne bilance po drugem in naslednjih mesecih bo nekoliko bolj zapleten. Da bi to naredili, moramo na začetku posojila odvzeti od organa posojila skupni znesek plačil na organu za posojila za prejšnje obdobje. V drugi celici stolpca »Balance to Payout« nastavite znak "=" . Nato označujemo povezavo do celice, ki vsebuje prvotni znesek posojila. To naredimo absolutno tako, da izberemo in pritisnemo tipko F4 . Potem postavimo znak "+" , ker bo druga vrednost negativna za nas. Nato kliknite na gumb "Vstavi funkcijo" .

    17. Vstavi funkcijo v programu Microsoft Excel

  17. Začne se čarovnik za funkcije , v katerem je treba premakniti v kategorijo "Matematični" . Tam izberemo napis "SUM" in kliknite gumb "OK" .

    18. Pojdite v okno argumentov funkcije SUM v programu Microsoft Excel

  18. Odpre se okno argumentov SUM funkcije. Navedeni operater služi za povzemanje podatkov v celicah, ki jih moramo opraviti v stolpcu "Plačilo s strani posojila . " Ima naslednjo skladnjo:

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

    Argumenti so sklicevanja na celice, ki vsebujejo številke. Kazalko smo nastavili v polje "Number1" . Nato držite levi gumb miške in izberite prvi dve celici stolpca »Izplačilo s kreditnim telesom« na listu. V polju je bilo prikazano sklicevanje na obseg. Sestavljen je iz dveh delov, ločenih z dvopičjem: sklicevanje na prvo celico območja in na zadnjo. Da bi lahko v prihodnosti kopirali navedeno formulo s pomočjo označevalnika polnjenja, naredimo prvi del sklicevanja na absolutno območje. Izberite in kliknite funkcijsko tipko F7 . Drugi del sklica ostane relativen. Zdaj, ko uporabljate marker za polnjenje, se bo določila prva celica območja, zadnja celica pa se bo raztegnila, ko se premika navzdol. To je tisto, kar potrebujemo za izpolnjevanje naših ciljev. Nato kliknite gumb "OK" .

    19. Okno argumenta funkcije SUM v programu Microsoft Excel

  19. Torej, rezultat plačilne bilance po drugem mesecu je prikazan v celici. Zdaj, začenši s to celico, kopiramo formulo za prazne elemente stolpcev z uporabo polnilnega ročaja.

    20. Polnilo v programu Microsoft Excel

  20. Mesečni izračun bilanc za plačilo na posojilo se opravi za celotno kreditno obdobje. Kot je bilo pričakovano, je na koncu obdobja ta znesek nič.

Izračun stanja za plačilo posojila v programu Microsoft Excel

Tako smo ne samo izračunali plačilo za posojilo, temveč smo organizirali vrsto posojilnega kalkulatorja. Kdo bo ukrepal na shemi rentiranja. Če v prvotni tabeli na primer spremenimo znesek posojila in letno obrestno mero, potem v zadnji tabeli pride do samodejnega preračunavanja podatkov. Zato ga je mogoče uporabiti ne samo za določen primer, ampak ga je mogoče uporabiti v različnih situacijah za izračun možnosti posojila na shemi rent.

Izvirni podatki so bili spremenjeni v programu Microsoft Excel

Lekcija: Finančne funkcije v Excelu

Kot lahko vidite, z uporabo programa Excel doma, lahko enostavno izračunate skupno mesečno plačilo posojila na shemi rent, pri čemer uporabite za te namene operator PLT . Poleg tega je mogoče s pomočjo funkcij OPST-a in MTEF izračunati višino plačil za organ posojila in obresti za določeno obdobje. Če uporabite to skupno prtljago funkcij skupaj, je mogoče ustvariti močan kreditni kalkulator, ki ga lahko uporabite večkrat za izračun rente.