Pagkalkula ng bayad sa annuity sa Microsoft Excel

Bago kumuha ng pautang, mas mabuti na makalkula ang lahat ng mga pagbabayad dito. Makakatipid ito sa nanghihiram sa hinaharap mula sa iba't ibang mga hindi inaasahang problema at pagkabigo kapag lumiliko na ang sobrang bayad. Ang mga tool sa Excel ay maaaring makatulong sa pagkalkula na ito. Alamin natin kung paano makalkula ang mga bayad sa utang ng annuity sa program na ito.

Pagkalkula ng pagbabayad

Una sa lahat, dapat sabihin na mayroong dalawang uri ng mga pagbabayad sa pautang:

• Iba-iba;
• Kawastuhan.

Sa isang naiibang pamamaraan, ang kliyente ay gumagawa ng pantay na halaga ng mga pagbabayad sa katawan ng pautang kasama ang mga bayad sa interes sa buwanang batayan sa bangko. Ang halaga ng mga pagbabayad ng interes ay bumabawas sa bawat buwan, dahil ang katawan ng pautang na kung saan sila ay kinakalkula ay bumababa. Kaya, ang kabuuang buwanang pagbabayad ay nabawasan din.

Ang isang scheme ng annuity ay gumagamit ng isang medyo magkakaibang pamamaraan. Ang buwanang kliyente ay gumagawa ng parehong halaga ng kabuuang pagbabayad, na binubuo ng mga pagbabayad sa katawan ng pautang at pagbabayad ng interes. Sa una, ang pagbabayad ng interes ay kinakalkula para sa buong halaga ng pautang, ngunit habang bumababa ang katawan, nabawasan ang accrual ng interes. Ngunit ang kabuuang halaga ng pagbabayad ay nananatiling hindi nagbabago dahil sa buwanang pagtaas sa halaga ng mga pagbabayad sa katawan ng pautang. Kaya, sa paglipas ng panahon, ang porsyento ng interes sa kabuuang buwanang pagbabayad ay bumababa, at ang proporsyon ng pagbabayad sa pamamagitan ng pagtaas ng katawan. Bukod dito, ang kabuuang buwanang pagbabayad mismo ay hindi nagbabago sa buong termino ng pautang.

Sa pagkalkula lamang ng bayad sa annuity, titigil kami. Bukod dito, ito ay may kaugnayan, dahil sa kasalukuyan ang karamihan sa mga bangko ay gumagamit ng partikular na pamamaraan na ito. Ito ay maginhawa para sa mga customer, dahil sa kasong ito ang kabuuang halaga ng pagbabayad ay hindi nagbabago, natitirang maayos. Laging alam ng mga customer kung magkano ang babayaran.

Yugto 1: buwanang pagkalkula ng pag-install

Para sa pagkalkula ng buwanang kontribusyon kapag ginagamit ang scheme ng annuity sa Excel mayroong isang espesyal na function - PMT. Ito ay nabibilang sa kategorya ng mga pinansiyal na operator. Ang pormula para sa pagpapaandar na ito ay ang mga sumusunod:

= PLT (rate; nper; ps; bs; type)

Tulad ng nakikita mo, ang pagpapaandar na ito ay may isang medyo malaking bilang ng mga argumento. Totoo, ang huling dalawa sa kanila ay opsyonal.

Pangangatwiran Bid nagpapahiwatig ng rate ng interes para sa isang partikular na panahon. Kung, halimbawa, ang taunang rate ay ginagamit, ngunit ang utang ay binabayaran buwan-buwan, kung gayon ang taunang rate ay dapat na hinatiin 12 at gamitin ang resulta bilang isang argumento. Kung ang isang quarterly na uri ng pagbabayad ay ginagamit, kung gayon sa kasong ito ang taunang rate ay dapat na hinatiin 4 atbp.

"Nper" ay nagpapahiwatig ng kabuuang bilang ng mga oras ng pagbabayad ng utang. Iyon ay, kung ang isang pautang ay kinuha para sa isang taon na may buwanang pagbabayad, kung gayon ang pagsasaalang-alang sa bilang ng mga panahon 12kung sa loob ng dalawang taon, kung gayon ang bilang ng mga panahon ay 24. Kung ang pautang ay kinuha sa loob ng dalawang taon na may quarterly payment, kung gayon ang bilang ng mga panahon ay pantay 8.

Sal nagpapahiwatig ng kasalukuyang halaga sa ngayon. Sa mga simpleng salita, ito ang kabuuang halaga ng pautang sa simula ng pautang, iyon ay, ang halaga na hiniram mo, hindi kasama ang interes at iba pang mga karagdagang pagbabayad.

"Bs" ang halaga sa hinaharap. Ang halagang ito, na magiging katawan ng pautang sa oras ng pagkumpleto ng kasunduan sa utang. Sa karamihan ng mga kaso, ang argument na ito ay "0", dahil ang nanghihiram sa pagtatapos ng termino ng pautang ay dapat na ganap na magbayad sa nagpapahiram. Ang tinukoy na argument ay opsyonal. Samakatuwid, kung bumababa ito, kung gayon ito ay itinuturing na katumbas ng zero.

Pangangatwiran "Uri" tinutukoy ang oras ng pagkalkula: sa dulo o sa simula ng panahon. Sa unang kaso, kinakailangan sa halaga "0"at sa pangalawa - "1". Karamihan sa mga institusyon ng pagbabangko ay gumagamit ng eksaktong pagpipilian sa pagbabayad sa pagtatapos ng panahon. Ang argument na ito ay opsyonal din, at kung hindi tinanggal ito ay itinuturing na zero.

Ngayon ay oras na upang magpatuloy sa isang tiyak na halimbawa ng pagkalkula ng buwanang pag-install gamit ang PMT function. Para sa pagkalkula, ginagamit namin ang talahanayan na may data ng mapagkukunan, kung saan ipinapahiwatig ang rate ng interes sa pautang (12%), halaga ng pautang (500,000 rubles) at term ng pautang (24 na buwan) Bukod dito, ang pagbabayad ay ginawa buwanang sa pagtatapos ng bawat panahon.

1. Piliin ang elemento sa sheet kung saan ang resulta ng pagkalkula ay ipapakita, at mag-click sa icon "Ipasok ang function"inilagay malapit sa formula bar.



2. Inilunsad ang window. Mga Wizards ng Function. Sa kategorya "Pinansyal" piliin ang pangalan "PLT" at mag-click sa pindutan "OK".



3. Pagkatapos nito, bubukas ang window ng mga argumento ng operator. PMT.

   Sa bukid Bid ipasok ang porsyento para sa panahon. Maaari itong gawin nang manu-mano, sa pamamagitan lamang ng pagtatakda ng porsyento, ngunit ipinakilala namin ito sa isang hiwalay na cell sa sheet, kaya bibigyan kami ng isang link dito. Inilalagay namin ang cursor sa bukid, at pagkatapos ay mag-click sa kaukulang cell. Ngunit, tulad ng natatandaan natin, sa aming talahanayan itinakda ang taunang rate ng interes, at ang panahon ng pagbabayad ay katumbas ng isang buwan. Samakatuwid, hinati namin ang taunang rate, o sa halip na ang link sa cell kung saan ito ay nilalaman, sa bilang 12naaayon sa bilang ng mga buwan sa isang taon. Ang paghati ay isinasagawa nang direkta sa larangan ng window window.

   Sa bukid "Nper" nakatakda ang isang termino ng pautang. Siya ay pantay-pantay sa atin 24 para sa mga buwan. Maaari kang magpasok ng isang numero sa bukid 24 manu-mano, ngunit kami, tulad ng sa nakaraang kaso, ay nagpapahiwatig ng isang link sa lokasyon ng tagapagpahiwatig na ito sa orihinal na talahanayan.

   Sa bukid Sal Ang paunang halaga ng pautang ay ipinahiwatig. Siya ay pantay 500,000 rubles. Tulad ng sa mga nakaraang kaso, ipinapahiwatig namin ang link sa elemento ng sheet kung saan nakapaloob ang tagapagpahiwatig na ito.

   Sa bukid "Bs" ay nagpapahiwatig ng halaga ng utang, pagkatapos ng buong pagbabayad. Sa natatandaan natin, ang halagang ito ay halos palaging zero. Itakda ang numero sa larangang ito "0". Bagaman ang pagtatalo na ito ay maaaring matanggal sa kabuuan.

   Sa bukid "Uri" ipahiwatig sa simula o sa pagtatapos ng buwan ay ginawa. Dito, tulad ng sa karamihan ng mga kaso, ginawa ito sa katapusan ng buwan. Samakatuwid, itakda ang numero "0". Tulad ng sa kaso ng nakaraang pagtatalo, hindi ka maaaring magpasok ng anumang bagay sa larangan na ito, kung gayon ang programa ay aakalain sa pamamagitan ng default na naglalaman ito ng isang halaga na katumbas ng zero.

   Matapos ipasok ang lahat ng data, mag-click sa pindutan "OK".



4. Pagkatapos nito, ang resulta ng pagkalkula ay ipinapakita sa cell na na-highlight namin sa unang talata ng manwal na ito. Tulad ng nakikita mo, ang halaga ng buwanang kabuuang bayad sa pautang ay 23536.74 rubles. Huwag malito sa pag-sign sa "-" sa harap ng halagang ito. Kaya ipinapahiwatig ng Excel na ito ay isang paggasta sa cash, iyon ay, isang pagkawala.



5. Upang makalkula ang kabuuang halaga ng pagbabayad para sa buong termino ng pautang, isinasaalang-alang ang pagbabayad ng katawan ng pautang at buwanang interes, sapat na upang maparami ang halaga ng buwanang pagbabayad (23536.74 rubles) sa bilang ng mga buwan (24 na buwan) Tulad ng nakikita mo, ang kabuuang halaga ng mga pagbabayad para sa buong termino ng pautang sa aming kaso ay nagkakahalaga 564881.67 rubles.



6. Ngayon ay maaari mong kalkulahin ang halaga ng sobrang bayad sa utang. Upang gawin ito, ibawas mula sa kabuuang halaga ng mga pagbabayad sa utang, kabilang ang interes at ang katawan ng pautang, ang paunang halaga na hiniram. Ngunit naaalala namin na ang una sa mga halagang ito ay naka-sign na "-". Samakatuwid, sa aming partikular na kaso, lumiliko na kailangan nilang makatiklop. Tulad ng nakikita mo, ang kabuuang utang na bayad sa buong panahon ay nagkakahalaga 64 881.67 rubles.

Aralin: Ang Wizard ng Tampok ng Excel

Stage 2: mga detalye sa pagbabayad

At ngayon, sa tulong ng iba pang mga operator ng Excel, gagawa kami ng buwanang mga detalye ng mga pagbabayad upang makita kung magkano ang babayaran namin sa isang pautang sa isang partikular na buwan, at kung magkano ang interes. Para sa mga layuning ito, gumuhit kami sa Excel ng isang talahanayan na punan namin ng data. Ang mga hilera sa talahanayan na ito ay tumutugma sa kaukulang panahon, iyon ay, sa buwan. Dahil sa panahon ng pagpapahiram sa amin ay 24 buwan, kung gayon ang bilang ng mga hilera ay angkop din. Ipinapahiwatig ng mga haligi ang pagbabayad ng katawan ng pautang, pagbabayad ng interes, ang kabuuang buwanang pagbabayad, na kung saan ay ang kabuuan ng nakaraang dalawang mga haligi, pati na rin ang natitirang halaga na babayaran.

1. Upang matukoy ang halaga ng pagbabayad sa pamamagitan ng katawan ng pautang, gamitin ang function OSPLT, na sadyang dinisenyo para sa mga layuning ito. Itakda ang cursor sa cell na nasa hilera "1" at sa haligi "Pagbabayad sa katawan ng pautang". Mag-click sa pindutan "Ipasok ang function".



2. Pumunta sa Tampok Wizard. Sa kategorya "Pinansyal" markahan ang pangalan OSPLT at pindutin ang pindutan "OK".



3. Nagsisimula ang window ng OSPLT operator. Mayroon itong mga sumusunod na syntax:

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

   Tulad ng nakikita mo, ang mga argumento ng pagpapaandar na ito ay halos ganap na nag-tutugma sa mga argumento ng operator PMT, sa halip na isang opsyonal na argumento "Uri" idinagdag na idinagdag na argumento "Panahon". Ipinapahiwatig nito ang bilang ng panahon ng pagbabayad, at sa aming partikular na kaso, ang bilang ng buwan.

   Pinupuno namin ang mga pamilyar na larangan ng window ng pagpapaandar ng argumento OSPLT ang parehong data na ginamit para sa pagpapaandar PMT. Ibinibigay lamang ang katotohanan na sa hinaharap ang formula ay makopya gamit ang fill marker, kailangan mong gawin ang lahat ng mga link sa mga patlang na ganap upang hindi sila magbago. Upang gawin ito, maglagay ng isang senyas na dolyar sa harap ng bawat halaga ng coordinate nang patayo at pahalang. Ngunit mas madaling gawin ito sa pamamagitan lamang ng pag-highlight ng mga coordinate at pagpindot sa function key F4. Ang dollar sign ay ilalagay sa tamang mga awtomatikong lugar. Gayundin huwag kalimutan na ang taunang rate ay dapat na hinatiin 12.



4. Ngunit mayroon kaming isa pang bagong argumento na wala ang pag-andar. PMT. Ang pangangatwiran na ito "Panahon". Sa kaukulang patlang, itakda ang link sa unang cell ng haligi "Panahon". Ang elementong sheet na ito ay naglalaman ng isang numero "1", na nagpapahiwatig ng bilang ng unang buwan ng pagpapahiram. Ngunit hindi katulad ng nakaraang mga patlang, sa tinukoy na larangan ay iniiwan namin ang link na kamag-anak, at hindi natin ito ganap.

   Matapos ang lahat ng data tungkol sa kung saan kami nagsalita sa itaas ay ipinasok, mag-click sa pindutan "OK".



5. Pagkatapos nito, sa cell na dati naming inilalaan, ang halaga ng pagbabayad sa katawan ng pautang para sa unang buwan ay ipapakita. Gagawin niya 18,536.74 rubles.



6. Pagkatapos, tulad ng nabanggit sa itaas, dapat nating kopyahin ang pormula na ito sa natitirang mga cell ng haligi gamit ang marker ng punan. Upang gawin ito, itakda ang cursor sa ibabang kanang sulok ng cell na naglalaman ng pormula. Ang cursor ay na-convert sa isang krus, na kung saan ay tinatawag na punong marker. Hawakan ang kaliwang pindutan ng mouse at i-drag ito hanggang sa dulo ng talahanayan.



7. Bilang isang resulta, ang lahat ng mga cell sa haligi ay napuno. Ngayon ay mayroon kaming iskedyul ng pagbabayad sa buwanang pautang. Tulad ng nabanggit sa itaas, ang halaga ng pagbabayad sa ilalim ng artikulong ito ay nagdaragdag sa bawat bagong panahon.



8. Ngayon kailangan nating gumawa ng isang buwanang pagkalkula ng mga pagbabayad ng interes. Para sa mga layuning ito gagamitin namin ang operator PRPLT. Piliin ang unang walang laman na cell sa haligi Bayad sa Interes. Mag-click sa pindutan "Ipasok ang function".



9. Sa panimulang window Mga Wizards ng Function sa kategorya "Pinansyal" ginagawa namin ang pagpili PRPLT. Mag-click sa pindutan. "OK".



10. Ang pagsisimula ng window ng pag-andar ay nagsisimula. PRPLT. Ang syntax nito ay ang mga sumusunod:

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

    Tulad ng nakikita mo, ang mga argumento ng pagpapaandar na ito ay ganap na magkapareho sa magkatulad na elemento ng operator OSPLT. Samakatuwid, ipinasok lamang namin ang parehong data sa window na naipasok namin sa nakaraang window ng argumento. Hindi namin nakalimutan ang parehong oras na ang link sa bukid "Panahon" dapat na kamag-anak, at sa lahat ng iba pang mga patlang ang mga coordinate ay dapat mabawasan sa isang ganap na form. Pagkatapos nito, mag-click sa pindutan "OK".



11. Pagkatapos, ang resulta ng pagkalkula ng halaga ng pagbabayad ng interes sa pautang para sa unang buwan ay ipinapakita sa naaangkop na kahon.



12. Nag-aaplay ng marker ng fill, kinokopya namin ang formula sa natitirang mga elemento ng haligi, sa gayon nakakakuha ng isang buwanang iskedyul ng pagbabayad para sa interes sa utang. Tulad ng nakikita natin, tulad ng sinabi ng mas maaga, mula buwan hanggang buwan ang halaga ng ganitong uri ng pagbabayad ay nabawasan.



13. Ngayon kailangan nating kalkulahin ang kabuuang buwanang pagbabayad. Para sa pagkalkula na ito, hindi ka dapat mag-resort sa anumang operator, dahil maaari kang gumamit ng isang simpleng formula ng aritmetika. Idagdag ang mga nilalaman ng mga cell ng unang buwan ng mga haligi "Pagbabayad sa katawan ng pautang" at Bayad sa Interes. Upang gawin ito, itakda ang tanda "=" sa unang walang laman na cell ng isang haligi "Kabuuang buwanang pagbabayad". Pagkatapos ay mag-click kami sa dalawang elemento sa itaas, na naglalagay ng tanda sa pagitan nila "+". Mag-click sa key Ipasok.



14. Susunod, gamit ang marker ng fill, tulad ng sa mga nakaraang kaso, punan ang haligi ng data. Tulad ng nakikita mo, sa buong tagal ng kontrata, ang kabuuang buwanang pagbabayad, kabilang ang pagbabayad ng katawan ng pautang at interes, ay magiging 23536.74 rubles. Sa totoo lang, kinakalkula na namin ang tagapagpahiwatig na ito gamit PMT. Ngunit sa kasong ito ito ay ipinakita nang mas malinaw, tumpak na bilang ang halaga ng pagbabayad sa katawan ng pautang at interes.



15. Ngayon kailangan mong magdagdag ng data sa haligi, na buwanang ipakita ang balanse ng halaga ng pautang na kailangan pa ring bayaran. Sa unang cell ng haligi "Balanse na babayaran" ang pagkalkula ang magiging pinakamadali. Kailangan nating ibawas mula sa paunang halaga ng pautang, na kung saan ay ipinahiwatig sa talahanayan na may pangunahing data, ang pagbabayad batay sa pautang para sa unang buwan sa talahanayan ng pagkalkula. Ngunit, binigyan ng katotohanang ang isa sa mga numero ay mayroon na tayong tanda "-", kung gayon hindi sila dapat makuha, ngunit nakatiklop. Gawin namin ito at mag-click sa pindutan Ipasok.



16. Ngunit ang pagkalkula ng balanse dahil sa pangalawa at kasunod na buwan ay magiging mas kumplikado. Upang gawin ito, kailangan nating ibawas mula sa katawan ng pautang sa simula ng pautang ang kabuuang halaga ng mga pagbabayad sa katawan ng pautang para sa nakaraang panahon. Itakda ang tanda "=" sa pangalawang cell ng haligi "Balanse na babayaran". Susunod, ipinapahiwatig namin ang link sa cell, na naglalaman ng paunang halaga ng pautang. Gawin itong ganap sa pamamagitan ng pag-highlight at pagpindot sa susi F4. Pagkatapos ay naglalagay kami ng isang senyas "+", dahil ang pangalawang halaga sa ating kaso ay magiging negatibo. Pagkatapos nito, mag-click sa pindutan "Ipasok ang function".



17. Nagsisimula Tampok Wizardkung saan kailangan mong lumipat sa kategorya "Matematika". Doon namin i-highlight ang inskripsyon SUM at mag-click sa pindutan "OK".



18. Nagsisimula ang window ng function na argumento SUM. Ang tinukoy na operator ay nagsisilbi upang mabilang ang data sa mga cell, na kailangan nating gawin sa haligi "Pagbabayad sa katawan ng pautang". Mayroon itong mga sumusunod na syntax:

    = SUM (number1; number2; ...)

    Ang mga argumento ay sanggunian sa mga cell na naglalaman ng mga numero. Itinakda namin ang cursor sa bukid "Number1". Pagkatapos ay hinawakan namin ang kaliwang pindutan ng mouse at piliin ang unang dalawang mga cell ng haligi sa sheet "Pagbabayad sa katawan ng pautang". Sa bukid, tulad ng nakikita natin, ang isang link sa saklaw ay ipinapakita. Binubuo ito ng dalawang bahagi, na pinaghiwalay ng isang colon: mga link sa unang cell ng saklaw at sa huli. Upang ma-kopyahin ang tinukoy na pormula gamit ang fill marker sa hinaharap, ginagawa namin ang unang bahagi ng link sa ganap na saklaw. Piliin ito at mag-click sa function na key F4. Ang pangalawang bahagi ng link ay pa rin kamag-anak. Ngayon, kapag ginagamit ang marker ng punan, ang unang cell ng saklaw ay maaayos, at ang huli ay mabatak habang gumagalaw ito. Ito ang kailangan natin upang matupad ang ating mga hangarin. Susunod, mag-click sa pindutan "OK".



19. Kaya, ang resulta ng balanse ng utang sa credit pagkatapos ng ikalawang buwan ay ipinapakita sa cell. Ngayon, simula sa cell na ito, kinokopya namin ang formula sa mga walang laman na elemento ng haligi gamit ang marker na punan.



20. Isang buwanang pagkalkula ng mga balanse ng pautang para sa buong panahon ng pautang. Tulad ng inaasahan, sa pagtatapos ng term na ito ang halaga ay zero.

Kaya, hindi lamang namin kinakalkula ang pagbabayad sa utang, ngunit inayos ang isang uri ng calculator ng pautang. Alin ang kumikilos sa isang annuity scheme. Kung sa orihinal na talahanayan, halimbawa, baguhin ang laki ng pautang at taunang rate ng interes, kung gayon sa pangwakas na talahanayan ang data ay awtomatikong mabibilang.Samakatuwid, maaari itong magamit hindi lamang isang beses para sa isang tiyak na kaso, ngunit ginamit sa iba't ibang mga sitwasyon upang makalkula ang mga pagpipilian sa kredito ayon sa isang scheme ng annuity.

Aralin: Mga Function sa Pinansyal sa Excel

Tulad ng nakikita mo, gamit ang programa ng Excel sa bahay, madali mong kalkulahin ang kabuuang buwanang pagbabayad ng utang ayon sa annuity scheme gamit ang operator para sa mga layuning ito PMT. Bilang karagdagan, ang paggamit ng mga pag-andar OSPLT at PRPLT maaari mong kalkulahin ang halaga ng mga pagbabayad sa katawan ng pautang at interes para sa tinukoy na tagal. Ang paglalapat ng lahat ng mga bagahe ng mga pag-andar nang magkasama, posible na lumikha ng isang malakas na calculator ng pautang na maaaring magamit nang higit sa isang beses upang makalkula ang isang bayad sa annuity.