Izračun rente plače v Excelu

Pred sprejetjem posojila bi bilo lepo izračunati vsa plačila na njem. Posojilojemalca bo v prihodnosti rešil iz različnih nepričakovanih težav in razočaranj, ko se izkaže, da je preplačilo prevelika. Pomoč pri tem izračunu lahko programska orodja Excel. Ugotovimo, kako izračunati plačila rente na posojilo v tem programu.

Izračun plačila

Najprej je treba povedati, da obstajata dve vrsti kreditnih plačil:

• Diferencirana;
• Rente.

Z diferencirano shemo, naročnik prinaša mesečni enak delež plačil na organu posojila plus plačila za obresti. Velikost plačil obresti vsak mesec zmanjšuje, ker se telo posojila zmanjša, iz katerega se izračunajo. Tako se tudi skupno mesečno plačilo zmanjša.

Ann Tea Shema uporablja nekoliko drugačen pristop. Naročnik naredi en znesek celotnega plačila mesečno, ki je sestavljen iz plačil na organu posojila in izplačila obresti. Na začetku so obrestni prispevki oštevilčeni na celotnem znesku posojila, odvisno pa se, ko se telo zmanjša, se obresti zmanjšajo in obrestne razmere. Toda skupni znesek plačila ostaja nespremenjen zaradi mesečnega povečanja zneska plačil s strani organa posojila. Tako je sčasoma delež interesa v skupnem mesečnem plačilu padla, in razmerska masa telesa raste. Hkrati se splošna mesečna plačila sama ne spreminja v celotnem kreditu.

Samo na izračun plačila rente bomo prenehali. Še posebej, to je pomembno, saj večina bank uporablja to posebno shemo. Primerna je za stranke, ker v tem primeru skupni znesek plačila ne spremeni, ostane določen. Kupci vedno vedo, koliko morate plačati.

1. korak: Mesečni izračun prispevka

Za izračun mesečnega prispevka pri uporabi rentnega vezja v Excele, je posebna funkcija - PPT. Nanaša se na kategorijo finančnih operaterjev. Formula te funkcije je naslednja:

= PPT (stopnja; Kper; PS; BS;

Kot smo videli, ima določena funkcija dokaj veliko število argumentov. Res je, zadnji dve od njih ni obvezen.

Argument "stopnja" kaže na odstotek za določeno obdobje. Če se na primer uporablja letna stopnja, vendar je plačilo posojila mesečno, potem je treba letno stopnjo razdeliti na 12 in rezultat se uporablja kot argument. Če se uporablja četrtletno vrsto plačila, je treba v tem primeru letna stava razdeliti na 4, itd.

"CPER" pomeni skupno število obdobij plačil posojil. To je, če se posojilo vzame eno leto z mesečnim plačilom, se število obdobij šteje za 12, če dve leti, nato število obdobij - 24. če se posojilo sprejme dve leti s četrtletno plačilo, potem Število obdobij je 8.

"PS" v tem trenutku označuje sedanjo vrednost. Ko govorimo s preprostimi besedami, je to skupni znesek posojila na začetku posojil, to je znesek, ki ste ga izposojeni, razen obresti in drugih dodatnih plačil.

"BS" je prihodnji stroški. Ta vrednost bo organ za posojilo ob zaključku posojilne pogodbe. V večini primerov je ta argument "0", saj bi se posojilojemalec na koncu kreditnega obdobja v celoti poravnal s posojilodajalcem. Navedeni argument ni obvezen. Če se torej spustite, se šteje za nič.

Argument "Tip" določa čas izračuna: na koncu ali na začetku obdobja. V prvem primeru je potrebna vrednost "0" in v drugem - "1". Večina bančnih institucij uporablja točno možnost s plačilom ob koncu obdobja. Ta argument je tudi neobvezen, in če je izpuščen, se verjame, da je nič.

Zdaj je čas, da se premaknete na poseben primer izračuna mesečnega prispevka z uporabo funkcije PL. Za izračun, uporabljamo tabelo z izvornimi podatki, kjer je navedena obrestna mera za posojilo (12%), vrednost posojila (500.000 rubljev) in obdobje posojila (24 mesecev). Hkrati se plačilo opravi mesečno na koncu vsakega obdobja.

1. Izberite element na listu, v katerega se prikaže rezultat rezultatov, in kliknite ikono »Vstavi funkcijo«, ki je nameščena v bližini vrstice formule.



2. Okno čarovnika za okno se sproži. V kategoriji "Financial" dodelite ime "PLT" in kliknite na gumb "OK".



3. Po tem odpre okno argumentov PL operaterja.

   V polju "stopnja", morate vnesti odstotno vrednost za obdobje. To je mogoče izvesti ročno, samo postavitev odstotka, vendar je naveden v ločeni celici na listu, zato bomo na to povezovali. Namestite kazalec na polje in nato kliknite na ustrezno celico. Ampak, kot se spomnim, imamo letno obrestno mero v naši tabeli, plačilni rok pa je enak mesecu. Zato delimo letno stavo in bolj povezavo s celico, v kateri je vsebovana s številko 12, ki ustreza številu mesecev v letu. Divizija teči neposredno v polju za argument okna.

   Na področju CPER je nastavljena posojila. On je enak 24 mesecev. Lahko se prijavite v ročnem polju št. 24, vendar smo, kot v prejšnjem primeru, navedite povezavo do lokacije tega kazalnika v izvorni tabeli.

   Na področju "PS" označuje prvotno vrednost posojila. To je enako 500.000 rubljev. Kot v prejšnjih primerih določimo povezavo do elementa listov, ki vsebuje ta kazalnik.

   Na področju "BS" označuje obseg posojila, po njegovem polnem plačilu. Ko se spomnite, je ta vrednost skoraj vedno nič. Namestite na tem polju številko "0". Čeprav se ta argument na splošno lahko izpusti.

   V polju "tipa" navajamo na začetku ali na koncu meseca plačila. Kot v večini primerov se proizvaja ob koncu meseca. Zato smo postavili številko "0". Kot v primeru prejšnjega argumenta, je mogoče vnesti karkoli na to polje, nato pa privzeti program predvideva, da je nič enaka.

   Ko vnesete vse podatke, pritisnite gumb "OK".



4. Po tem, v celici, ki smo jih dodelili v prvem odstavku tega priročnika, se prikaže rezultat izračuna. Kot lahko vidite, je obseg mesečnega splošnega plačila na posojilo 23536.74 rubljev. Pred tem zneskom ne zamenjajte znaka "-". Tako izgnanstvo kaže, da je to tok denarja, to je izguba.



5. Za izračun skupnega zneska plačila za celotno obdobje posojila, ob upoštevanju odplačevanja organa posojila in mesečnega interesa, precej pomnožite znesek mesečnega plačila (23536.74 rubljev) za število mesecev (24 mesecev ). Kot lahko vidite, skupni znesek plačil za celotno obdobje posojila v našem primeru je bilo 564881.67 rubljev.



6. Zdaj lahko izračunate znesek preplačila posojila. Če želite to narediti, je treba odvzeti od skupnega zneska plačil na posojilo, vključno z obrestmi in posojilnim organom, zahtevani začetni znesek. Vendar se spomnim, da je prvi od teh vrednot že z znakom "-". Zato se naš primer posebej izkaže, da ga je treba zložiti. Kot smo videli, je skupna preplačilo posojila v celotnem obdobju je bilo 64881.67 rubljev.

Lekcija: Mojster funkcij v Excelu

Faza 2: Podrobnosti o plačilu

In zdaj, s pomočjo drugih Excelovih operaterjev, smo mesečne podrobnosti plačil, da vidimo, koliko v določenem mesecu plačamo skozi telo posojila, in koliko je znesek obresti. Za te namene, kovač v tabeli izgnanstva, ki bomo izpolnili podatke. Vrstice te tabele bodo odgovorne za ustrezno obdobje, to je mesec. Glede na to, da je obdobje posojanja 24 mesecev, bo primerno tudi število vrstic. Stolpci so navedli posojilo, plačila obresti, skupno mesečno plačilo, ki je vsota prejšnjih dveh stolpcev, pa tudi preostali znesek za plačilo.

1. Za določitev zneska plačila s strani telesa posojila uporabite funkcijo OSP, ki je namenjena samo za te namene. V celici smo vzpostavili kazalko, ki se nahaja v vrstici "1" in v stolpcu "Plačilo s strani telesa posojila". Kliknite gumb »Funkcija Prilepi«.



2. Pojdite na mojster funkcij. V kategoriji "Financial", smo opazili ime "Osplt" in kliknite gumb "OK".



3. Trditve argumentov operaterja OSP se začnejo. Ima naslednjo sintakso:

   = Ospalt (stopnja; obdobje; KPER; PS; BS)

   Kot lahko vidimo, argumenti te značilnosti skoraj popolnoma sovpadajo z argumenti upravljavca PLT, le namesto neobveznega argumenta "Tip" je dodal obvezen argument "obdobje". Označuje število plačilnega obdobja in v našem posebnem primeru na številu meseca.

   Izpolnite argumente argumentov OSR Argumenti, ki nam jih že poznajo z istimi podatki, ki so bili uporabljeni za funkcijo PL. Pravkar sem glede na dejstvo, da bo kopiranja formula v prihodnosti uporabljena s polnilo, morate storiti vse povezave na poljih absolutno, tako da se ne spremenijo. To zahteva, da pred vsako vrednostjo navpičnih in horizontalnih koordinat postavlja znak dolarja. Ampak to je lažje to, preprosto izberete koordinate in s klikom na funkcijsko tipko F4. Znak dolarja bo samodejno nameščen na pravih mestih. Prav tako ne pozabimo, da mora biti letna stava razdeljena na 12.



4. Vendar imamo še en nov argument, ki ni bil iz funkcije PL. Ta argument "obdobje". Na ustreznem področju določite sklicevanje na prvo celico stolpca "Obdobje". Ta element lista vsebuje številko "1", ki označuje število prvega meseca posojanja. Toda za razliko od prejšnjih polj pustimo povezavo sorodnika na določenem polju in ne storiti absolutno od njega.

   Po vseh podatkih, o katerih smo govorili zgoraj, uvedeni, pritisnite gumb "OK".



5. Po tem, v celici, ki smo jo prej dodelili, se bo pojavil znesek plačila posojila za prvi mesec. To bo 18536.74 rubljev.



6. Potem, kot je navedeno zgoraj, moramo to formulo kopirati na preostale kolonske celice s polnilnikom. Če želite to narediti, nastavite kurzor v spodnji desni kot celice, ki vsebuje formulo. Kazalec se pretvori na križ, ki se imenuje polnilni znak. Kliknite levi gumb miške in ga povlecite do konca tabele.



7. Posledica tega je, da so vsi celični stolpci napolnjeni. Zdaj imamo grafikon plačevanja posojila mesečno. Kot je navedeno zgoraj, se znesek plačila na tem členu poveča z vsako novo obdobje.



8. Zdaj moramo plačati mesečni izračun plačila z obrestmi. Za te namene bomo uporabili operaterja PRT. Prvo prazno celico dodelimo v stolpcu "Plačilni odstotek". Kliknite gumb »Funkcija Prilepi«.



9. V funkcijah poveljnika funkcij v kategoriji "Financial" izdelujemo imena namga. Opravite klik na gumb "OK".



10. Trditve Funkcija TRP se začne. Njegova sintaksa izgleda takole:

    = PRT (stopnja; obdobje; CPU; PS; BS)

    Kot lahko vidimo, so argumenti te funkcije popolnoma enake podobnim elementom operaterja OSP. Zato vnesite iste podatke v okno, ki smo ga vnesli v prejšnje okno argumentov. Ne pozabimo, da mora biti referenca v polju "Obdobje" relativna, na vseh drugih področjih pa je treba koordinate pripeljati v absolutno obliko. Po tem kliknite na gumb "OK".



11. Potem je rezultat izračuna zneska plačila za obresti za posojilo za prvi mesec, je prikazan v ustrezni celici.



12. Uporaba polnilnega markerja, kopiranje formule v preostale elemente stolpca, na ta način, na ta način, ki prejema mesečni razpored za odstotke za posojilo. Kot lahko vidimo, kot je bilo že prej, iz meseca do meseca vrednost te vrste plačil zmanjša.



13. Zdaj moramo izračunati celotno mesečno plačilo. Za ta izračun ne smemo uporabiti nobenega operaterja, saj lahko uporabite preprosto aritmetično formulo. Vsebino celic prvega meseca stolpcev "Plačilo s strani telesa posojila" in "polni interes". Če želite to narediti, nastavite znak "=" v prvo prazno celico stolpca "Skupno mesečno plačilo". Nato kliknite na dva elementa z nastavitvijo znaka "+" med njimi. Kliknite na tipko ENTER.



14. Nato z uporabo označevalca polnilnika, kot v prejšnjih primerih, izpolnite programski stolpec. Kot lahko vidimo v celotnem delovanju pogodbe, bo znesek celotnega mesečnega plačila, ki vključuje plačilo s strani organa posojila in plačila obresti, 23536.74 rubljev. Pravzaprav smo ta kazalnik že izračunal pred uporabo PPT. Toda v tem primeru je jasneje predstavljen natančno kot znesek plačila s strani organa posojila in obresti.



15. Zdaj morate dodati podatke v stolpec, kjer se stanje zneska posojila prikaže mesečno, kar je še vedno potrebno plačati. V prvi celici kolone "Stanje za plačilo" bo izračun najlažji. Odvzeti ga je treba od začetne velikosti posojila, ki je določena v tabeli s primarnimi podatki, plačilo s strani organa posojila za prvi mesec v izračunani tabeli. Toda glede na dejstvo, da je ena od številk, ki jih že gredo z znakom "-", potem ne bi smeli biti odvzeti, ampak za kratko. Izdelujemo in kliknite na gumb ENTER.



16. Toda izračun bilance za plačilo po drugi in naslednjih mesecih bo nekoliko bolj zapleten. To storiti, moramo odpraviti od telesa posojila na začetek posojanja skupnega zneska plačil s strani organa posojila za preteklo obdobje. Namestite znak "=" v drugi celici stolpca "Palace za plačilo". Nato določite povezavo do celice, ki vsebuje začetni znesek posojila. To naredimo absolutno, označevanje in pritiskanje tipke F4. Potem smo postavili znak "+", saj imamo drugi pomen in tako negativen. Po tem kliknite na gumb "Vstavi funkcijo".



17. Ustvarjal se je mojster funkcij, v katerem se morate premakniti v kategorijo "Matematic". Tam dodelimo napis "Zneski" in pritisnite gumb "OK".



18. Okno argumentov začne argumente funkcije. Navedeni operater služi, da povzame podatke v celicah, ki jih moramo izvesti v stolpcu "Plačilo s kreditnim organom". Ima naslednjo sintakso:

    = Zneski (številka1; Številka2; ...)

    Kot argumenti se sklicevanja na celice, v katerih so vsebovane številke. Kurzor smo postavili v polje »številka1«. Nato navijte levi gumb miške in izberite prve dve celici stolpca kreditnega telesa na listu. Na polju, kot vidimo, se je pojavil povezava do območja. Sestavljen je iz dveh delov, ločenega debelega črevesa: sklicevanja na prvo paleto območja in na zadnji. Da bi lahko kopirali določeno formulo v prihodnosti s polnilnim označevalnikom, naredimo prvo povezavo do absolutnega območja. Označujemo ga in kliknite funkcijsko tipko F4. Drugi del reference in dopust. Zdaj, ko uporabljate oznako polnilnika, bo prvega razpona območja fiksno, slednje pa se raztezajo, ko se premika navzdol. To je potrebno, da izpolnimo cilje. Nato kliknite gumb »OK«.



19. Torej, rezultat bilance kreditnega dolga po drugi mesec se odvaja v celico. Zdaj, začenši s to celico, kopiramo formulo v prazne elemente stolpcev z oznako za polnjenje.



20. Mesečni izračun ostankov za plačilo posojila se izvede za celotno kreditno obdobje. Kot bi moralo biti, na koncu roka, ta znesek je nič.

Tako nismo preprosto izračunali plačila posojila, ampak organizirali nekakšen kreditni kalkulator. Ki bodo delovali na renlni shemi. Če v izvorni tabeli smo, na primer, spremenimo znesek posojila in letne obrestne mere, nato pa v finalni tabeli, da bo samodejno preračun podatkov. Zato se lahko uporabi ne samo enkrat za določen primer, temveč se uporablja v različnih situacijah za izračun kreditnih možnosti v renlni shemi.

Lekcija: Finančne funkcije v Excelu

Kot lahko vidite, z uporabo programa Excel doma lahko enostavno izračunate celotno mesečno plačilo posojila v sistemu rente, z uporabo PL operaterja za te namene. Poleg tega, s pomočjo funkcij OSR in PRT, je mogoče izračunati znesek plačil s strani organa posojila in odstotkov za določeno obdobje. Uporaba vseh teh funkcij prtljage skupaj, je mogoče ustvariti močan kreditni kalkulator, ki se lahko uporablja več kot enkrat, da izračuna rentno plačilo.