Llogaritja e pagesës së pensionit në Microsoft Excel

Para se të merrni një kredi, do të ishte mirë të llogaritni të gjitha pagesa në të. Kjo do ta shpëtojë huamarrësin në të ardhmen nga telashet dhe zhgënjimet e ndryshme të papritura kur rezulton se pagesa e tepërt është shumë e madhe. Mjetet e Excel mund të ndihmojnë me këtë llogaritje. Le të zbulojmë se si të llogaritim pagesa të huasë vjetore në këtë program.

Llogaritja e pagesës

Para së gjithash, duhet të thuhet se ekzistojnë dy lloje të pagesave të kredisë:

• diferencuar;
• Pensioni.

Në një skemë të diferencuar, klienti bën shuma të barabarta pagesa në trupin e kredisë plus pagesa të interesit në bazë mujore në bankë. Sasia e pagesave të interesit zvogëlohet çdo muaj, pasi trupi i kredisë nga i cili llogariten zvogëlohet. Kështu, ulet edhe pagesa totale mujore.

Një skemë pensioni përdor një qasje paksa të ndryshme. Klienti mujor bën të njëjtën sasi të pagesës totale, e cila konsiston në pagesa në organin e kredisë dhe pagesa të interesit. Fillimisht, pagesa e interesit llogaritet për të gjithë shumën e kredisë, por ndërsa organi zvogëlohet, akruali i kamatës zvogëlohet. Por shuma e përgjithshme e pagesës mbetet e pandryshuar për shkak të rritjes mujore të sasisë së pagesave në trupin e kredisë. Kështu, me kalimin e kohës, përqindja e interesit në totalin e pagesës mujore zvogëlohet, dhe përqindja e pagesës nga trupi rritet. Për më tepër, pagesa totale mujore në vetvete nuk ndryshon gjatë gjithë afatit të huasë.

Vetëm në llogaritjen e pagesës së pensionit, ne do të ndalemi. Për më tepër, kjo është e rëndësishme, pasi aktualisht shumica e bankave përdorin këtë skemë të veçantë. Shtë i përshtatshëm për klientët, sepse në këtë rast shuma totale e pagesës nuk ndryshon, duke mbetur fikse. Konsumatorët e dinë gjithmonë sa duhet të paguajnë.

Faza 1: llogaritja e kësteve mujore

Për llogaritjen e kontributit mujor kur përdorni skemën e pensionit në Excel ekziston një funksion i veçantë - PMT. I përket kategorisë së operatorëve financiarë. Formula për këtë funksion është si më poshtë:

= PLT (norma; nper; ps; bs; lloji)

Siç mund ta shihni, ky funksion ka një numër mjaft të madh argumentesh. E vërtetë, dy prej tyre janë opcionale.

argument "Bet" tregon normën e interesit për një periudhë të caktuar. Nëse për shembull përdoret norma vjetore, por kredia paguhet çdo muaj, atëherë norma vjetore duhet të ndahet me 12 dhe përdor rezultatin si argument. Nëse përdoret një lloj pagese tremujore, atëherë në këtë rast niveli vjetor duhet të ndahet me 4 etj

"NPER" tregon numrin e përgjithshëm të periudhave të ripagimit të huasë. Kjo do të thotë, nëse një kredi merret për një vit me një pagese mujore, atëherë konsiderohet numri i periudhave 12nëse për dy vjet, atëherë numri i periudhave është 24. Nëse kredia merret për dy vjet me pagesa tremujore, atëherë numri i periudhave është i barabartë 8.

"Ps" tregon vlerën aktuale për momentin. Me fjalë të thjeshta, kjo është shuma totale e kredisë për të filluar huadhënien, domethënë shuma që huazoni, duke përjashtuar interesin dhe pagesa të tjera shtesë.

"BS" është vlera e së ardhmes. Kjo vlerë, e cila do të jetë organi i kredisë në kohën e përfundimit të marrëveshjes së kredisë. Në shumicën e rasteve, ky argument është "0", pasi huamarrësi në fund të afatit të kredisë duhet të paguajë plotësisht huadhënësin. Argumenti i specifikuar është opsional. Prandaj, nëse bie, atëherë konsiderohet e barabartë me zero.

argument "Type" përcakton kohën e llogaritjes: në fund ose në fillim të periudhës. Në rastin e parë, ajo merr vlerën "0"dhe në të dytën - "1". Shumica e institucioneve bankare përdorin saktësisht opsionin me pagesa në fund të periudhës. Ky argument është gjithashtu opsional, dhe nëse lihet, ai konsiderohet se është zero.

Tani është koha për të kaluar në një shembull specifik të llogaritjes së këstit mujor duke përdorur funksionin PMT. Për llogaritjen, ne përdorim tabelën me të dhënat e burimit, ku tregohet norma e interesit në kredi (12%) shumën e kredisë (500,000 rubla) dhe afati i huasë (24 muaj). Për më tepër, pagesa bëhet çdo muaj në fund të çdo periudhe.

1. Zgjidhni elementin në fletë në të cilën do të shfaqet rezultati i llogaritjes dhe klikoni në ikonën "Vendos funksionin"vendosur pranë shiritit të formulës.



2. Dritarja është hedhur në treg. Magjistarët e funksionit. Në kategori "Financial" zgjidhni emrin "PMT" dhe klikoni në butonin "OK".



3. Pas kësaj, hapet dritarja e argumenteve të operatorit. PMT.

   Në fushë "Bet" shkruani përqindjen për periudhën. Kjo mund të bëhet me dorë, thjesht duke vendosur përqindjen, por ne e kemi treguar atë në një qelizë të veçantë në fletë, kështu që ne do t'i japim një lidhje asaj. Ne e vendosim kursorin në fushë, dhe pastaj klikojmë në qelizën përkatëse. Por, siç kujtojmë, në tabelën tonë është caktuar norma e interesit vjetor, dhe periudha e pagesës është e barabartë me një muaj. Prandaj, ne ndajmë numrin vjetor, ose më saktë lidhjen me qelizën në të cilën është e përfshirë 12që korrespondon me numrin e muajve në një vit. Ndarja kryhet direkt në fushën e dritares së argumentit.

   Në fushë "NPER" është caktuar një afat kredie. Ai është i barabartë me ne 24 me muaj. Mund të futni një numër në fushë 24 manualisht, por ne, si në rastin e mëparshëm, tregojmë një lidhje me vendndodhjen e këtij treguesi në tabelën origjinale.

   Në fushë "Ps" Tregohet shuma fillestare e kredisë. Ajo është e barabartë 500,000 rubla. Si në rastet e mëparshme, ne tregojmë lidhjen me elementin e fletës në të cilën përmbahet ky tregues.

   Në fushë "BS" tregon shumën e kredisë, pas pagimit të plotë. Ndërsa kujtojmë, kjo vlerë është pothuajse gjithmonë zero. Vendosni numrin në këtë fushë "0". Edhe pse ky argument mund të lihet krejt.

   Në fushë "Type" tregoni në fillim ose në fund të muajit është bërë pagesa. Këtu, si në shumicën e rasteve, prodhohet në fund të muajit. Prandaj, vendosni numrin "0". Ashtu si në rastin me argumentin e mëparshëm, nuk mund të futni asgjë në këtë fushë, atëherë programi do të supozojë si parazgjedhje se përmban një vlerë të barabartë me zero.

   Pasi të futen të gjitha të dhënat, klikoni në butonin "OK".



4. Pas kësaj, rezultati i llogaritjes shfaqet në qelizë që kemi theksuar në paragrafin e parë të këtij manuali. Siç mund ta shihni, shuma e pagesës totale mujore të huasë është 23536.74 rubla. Mos u hutoni nga shenja “-” para kësaj shume. Kështu që Excel tregon që kjo është një shpenzim në para, domethënë një humbje.



5. Për të llogaritur shumën totale të pagesës për të gjithë afatin e kredisë, duke marrë parasysh shlyerjen e trupit të kredisë dhe interesin mujor, mjafton të shumëzoni shumën e pagesës mujore (23536.74 rubla) me numrin e muajve (24 muaj). Siç mund ta shihni, shuma totale e pagesave për tërë afatin e kredisë në rastin tonë arriti në 564881.67 rubla.



6. Tani mund të llogaritni shumën e mbipagesës në kredi. Për ta bërë këtë, zbritni nga shuma totale e pagesave në kredi, përfshirë interesin dhe organin e kredisë, shumën fillestare të huazuar. Por kujtojmë që e para nga këto vlera është nënshkruar tashmë "-". Prandaj, në rastin tonë të veçantë, rezulton se ato duhet të palosen. Siç mund ta shihni, mbipagesa totale e huasë për të gjithë periudhën arriti 64 881.67 rubla.

mësim: Magjistar i Karakteristikave Excel

Faza 2: detajet e pagesës

Dhe tani, me ndihmën e operatorëve të tjerë të Excel, ne do të bëjmë detaje mujore të pagesave për të parë se sa paguajmë një kredi në një muaj të caktuar, dhe sa është interesi. Për këto qëllime, ne tërheqim në Excel një tabelë që do të mbushim me të dhëna. Rreshtat në këtë tabelë do të korrespondojnë me periudhën përkatëse, domethënë, në muaj. Duke pasur parasysh që periudha e huazimit me ne është 24 muaj, atëherë numri i rreshtave do të jetë gjithashtu i përshtatshëm. Kolonat tregojnë pagesën e trupit të kredisë, pagesën e interesit, pagesën totale mujore, që është shuma e dy kolonave të mëparshme, si dhe shuma e mbetur e pagueshme.

1. Për të përcaktuar shumën e pagesës nga organi i kredisë, përdorni funksionin OSPLT, i cili është krijuar thjesht për këto qëllime. Vendoseni kursorin në qelizën që është në rresht "1" dhe në kolonë "Shlyerja në trupin e kredisë". Klikoni në butonin "Vendos funksionin".



2. Shko tek Wizard Feature. Në kategori "Financial" shëno emrin "OSPLT" dhe shtypni butonin "OK".



3. Fillon dritarja e argumenteve të operatorit OSPLT. Ajo ka sintaksën e mëposhtme:

   = OSPLT (Bet; Periudha; Nper; Ps; BS)

   Siç mund ta shihni, argumentet e këtij funksioni pothuajse plotësisht përkojnë me argumentet e operatorit PMT, vetëm në vend të një argumenti opsional "Type" argument i kërkuar u shtua "Periudha". Ai tregon numrin e periudhës së pagesës, dhe në rastin tonë të veçantë, numrin e muajit.

   Ne plotësojmë fushat tashmë të njohura të dritares së argumentit të funksionit OSPLT të njëjtat të dhëna që janë përdorur për funksionin PMT. Vetëm duke pasur parasysh faktin se në të ardhmen formula do të kopjohet duke përdorur shënuesin e mbushjes, ju duhet të bëni të gjitha lidhjet në fushat absolute në mënyrë që ato të mos ndryshojnë. Për ta bërë këtë, vendosni një shenjë dollari përpara secilës vlerë koordinative vertikalisht dhe horizontale. Por është më lehtë ta bësh këtë duke thjesht theksuar koordinatat dhe duke shtypur tastin e funksionit F4. Shenja e dollarit do të vendoset automatikisht në vendet e duhura. Gjithashtu mos harroni se norma vjetore duhet të ndahet me 12.



4. Por kemi edhe një argument të ri që funksioni nuk e kishte. PMT. Ky argument "Periudha". Në fushën përkatëse, vendosni lidhjen me qelizën e parë të kolonës "Periudha". Ky element fletë përmban një numër "1", që tregon numrin e muajit të parë të huadhënies. Por ndryshe nga fushat e mëparshme, në fushën e specifikuar ne e lëmë lidhjen relative, dhe nuk e bëjmë atë absolute.

   Pasi të jenë futur të gjitha të dhënat për të cilat folëm më lart, klikoni në butonin "OK".



5. Pas kësaj, në qelizën që kemi ndarë më parë, do të shfaqet shuma e ripagimit në trupin e kredisë për muajin e parë. Ajo do të bëjë 18,536.74 rubla.



6. Pastaj, siç u përmend më lart, duhet ta kopjojmë këtë formulë në qelizat e mbetura të kolonës duke përdorur shënuesin e mbushjes. Për ta bërë këtë, vendoseni kursorin në këndin e poshtëm të djathtë të qelizës që përmban formulën. Kursori konvertohet në një kryq, i cili quhet shënuesi i mbushjes. Mbajeni butonin e majtë të miut dhe tërhiqeni atë deri në fund të tabelës.



7. Si rezultat, të gjitha qelizat në kolonë janë të mbushura. Tani kemi një orar mujor të ripagimit të huasë. Siç u përmend më lart, masa e pagesës sipas këtij neni rritet me secilën periudhë të re.



8. Tani duhet të bëjmë një llogaritje mujore të pagesave të interesit. Për këto qëllime ne do të përdorim operatorin IPMT. Zgjidhni qelizën e parë të zbrazët në kolonë Pagesa e interesit. Klikoni në butonin "Vendos funksionin".



9. Në dritaren e fillimit Magjistarët e funksionit në kategori "Financial" bëjmë zgjedhjen IPMT. Klikoni në butonin. "OK".



10. Fillon dritarja e argumenteve të funksionit. IPMT. Sintaksa e saj është si më poshtë:

    = PRPLT (Bet; Periudha; Nper; Ps; BS)

    Siç mund ta shihni, argumentet e këtij funksioni janë absolutisht identikë me elementët e ngjashëm të operatorit OSPLT. Prandaj, thjesht futim të njëjtat të dhëna në dritaren që kemi futur në dritaren e mëparshme të argumenteve. Ne nuk harrojmë në të njëjtën kohë lidhjen në fushë "Periudha" duhet të jetë relativ, dhe në të gjitha fushat e tjera koordinatat duhet të reduktohen në një formë absolute. Pas kësaj, klikoni në butonin "OK".



11. Pastaj, rezultati i llogaritjes së shumës së pagesës së interesit në kredi për muajin e parë është paraqitur në kutinë përkatëse.



12. Duke aplikuar shënuesin e mbushjes, ne kopjojmë formulën në elementët e mbetur të kolonës, duke marrë kështu një plan pagese mujore për interesin e kredisë. Siç e shohim, siç u tha më herët, nga muaji në muaj vlera e këtij lloji të pagesës zvogëlohet.



13. Tani duhet të llogarisim pagesën totale mujore. Për këtë llogaritje, nuk duhet të drejtoheni te ndonjë operator, pasi mund të përdorni një formulë të thjeshtë aritmetike. Shtoni përmbajtjen e qelizave të muajit të parë të kolonave "Shlyerja në trupin e kredisë" dhe Pagesa e interesit. Për ta bërë këtë, vendosni shenjën "=" te qeliza e parë e zbrazët e një kolone "Pagesa totale mujore". Pastaj klikojmë në dy elementët e mësipërm, duke vendosur një shenjë midis tyre "+". Klikoni në butonin hyj.



14. Tjetra, duke përdorur shënuesin e mbushjes, si në rastet e mëparshme, mbushni kolonën me të dhëna. Siç mund ta shihni, gjatë gjithë kohëzgjatjes së kontratës, pagesa totale mujore, përfshirë pagesën nga organi i kredisë dhe interesat, do të jetë 23536.74 rubla. Në fakt, ne e kemi llogaritur tashmë këtë tregues duke përdorur PMT. Por në këtë rast ajo paraqitet më qartë, saktësisht si shuma e pagesës në trupin e kredisë dhe interesat.



15. Tani duhet të shtoni të dhënat në kolonë, e cila do të tregojë çdo muaj bilancin e shumës së kredisë që akoma duhet të paguhet. Në qelizën e parë të kolonës "Bilanci i pagueshëm" llogaritja do të jetë më e lehtë. Duhet të zbrisim nga shuma fillestare e kredisë, e cila tregohet në tabelë me të dhënat parësore, pagesa në bazë të kredisë për muajin e parë në tabelën e llogaritjes. Por, duke pasur parasysh faktin se një nga numrat ne tashmë kemi një shenjë "-", atëherë ata nuk duhet të hiqen, por të palosur. Ne e bëjmë këtë dhe klikoni në butonin hyj.



16. Por llogaritja e bilancit për shkak të muajve të dytë dhe të mëvonshëm do të jetë disi më e ndërlikuar. Për ta bërë këtë, duhet të zbresim nga trupi i kredisë në fillim të kredisë shumën totale të pagesave në trupin e kredisë për periudhën e mëparshme. Vendosni shenjën "=" në qelizën e dytë të kolonës "Bilanci i pagueshëm". Tjetra, ne tregojmë lidhjen me qelizën, e cila përmban shumën fillestare të kredisë. Bëni atë absolut duke theksuar dhe shtypur tastin F4. Pastaj vendosëm një shenjë "+", pasi vlera e dytë në rastin tonë do të jetë negative. Pas kësaj, klikoni në butonin "Vendos funksionin".



17. Fillon lart Wizard Featurenë të cilën ju duhet të kaloni në kategori "Matematike". Aty nxjerrim në pah mbishkrimin "SUM" dhe klikoni në butonin "OK".



18. Fillon dritarja e argumentit të funksionit SUM. Operatori i specifikuar shërben për të përmbledhur të dhënat në qeliza, të cilat duhet t'i kryejmë në kolonë "Shlyerja në trupin e kredisë". Ajo ka sintaksën e mëposhtme:

    = SUM (numri 1; numri2; ...)

    Argumentet janë referenca për qelizat që përmbajnë numra. Ne e vendosëm kursorin në fushë "Number 1". Pastaj mbajmë butonin e majtë të miut dhe zgjedhim dy qelizat e para të kolonës në fletë "Shlyerja në trupin e kredisë". Në fushë, siç shohim, shfaqet një lidhje me diapazonin. Përbëhet nga dy pjesë, të ndara nga një zorrë e trashë: lidhje me qelizën e parë të rangut dhe të fundit. Për të qenë në gjendje të kopjojmë formulën e specifikuar duke përdorur shënuesin e mbushjes në të ardhmen, ne bëjmë pjesën e parë të lidhjes për diapazonin absolut. Zgjidhni atë dhe klikoni në butonin e funksionit F4. Pjesa e dytë e lidhjes është ende relative. Tani, kur përdorni shënuesin e mbushjes, qeliza e parë e diapazonit do të fiksohet, dhe e fundit do të shtrihet ndërsa lëviz poshtë. Kjo është ajo që ne kemi nevojë për të përmbushur qëllimet tona. Tjetra, klikoni në butonin "OK".



19. Pra, rezultati i bilancit të borxhit të kredisë pas muajit të dytë shfaqet në qeli. Tani, duke u nisur nga kjo qelizë, ne kopjojmë formulën në elementë kolonë bosh duke përdorur shënuesin e mbushjes.



20. Një llogaritje mujore e bilancit të kredisë për të gjithë periudhën e kredisë. Siç pritej, në fund të afatit kjo shumë është zero.

Kështu, ne nuk kemi llogaritur vetëm pagesën në kredi, por kemi organizuar një lloj kalkulatori kredie. E cila do të veprojë në një skemë pensioni. Nëse në tabelën origjinale, për shembull, ndryshojmë madhësinë e kredisë dhe normën vjetore të interesit, atëherë në tabelën përfundimtare të dhënat do të rillogariten automatikisht.Prandaj, mund të përdoret jo vetëm një herë për një rast specifik, por përdoret në situata të ndryshme për të llogaritur opsionet e kredisë sipas një skeme pensioni.

mësim: Funksionet financiare në Excel

Siç mund ta shihni, duke përdorur programin Excel në shtëpi, ju lehtë mund të llogaritni totalin e pagesës mujore të kredisë sipas skemës së pensionit, duke përdorur operatorin për këto qëllime PMT. Përveç kësaj, duke përdorur funksione OSPLT dhe IPMT ju mund të llogaritni shumën e pagesave në trupin e kredisë dhe interesin për periudhën e caktuar. Duke aplikuar të gjithë këtë bagazh funksionesh së bashku, është e mundur të krijoni një kalkulator të fuqishëm kredie që mund të përdoret më shumë se një herë për të llogaritur një pagesë pensioni.