Formula para sa pagkalkula ng pagbabayad ng annuity sa Excel

Ang mga pagbabayad ng pautang ay mas madali at mas mabilis na kalkulahin gamit ang Microsoft Office Excel. Mas maraming oras ang ginugugol sa manu-manong pagkalkula. Ang artikulong ito ay tumutuon sa mga pagbabayad sa annuity, ang mga tampok ng kanilang pagkalkula, mga pakinabang at disadvantages.

Ano ang pagbabayad ng annuity

Isang paraan ng buwanang pagbabayad ng isang pautang, kung saan ang halagang idineposito ay hindi nagbabago sa buong panahon ng pautang. Yung. sa ilang mga petsa ng bawat buwan, ang isang tao ay nagdeposito ng isang tiyak na halaga ng pera hanggang sa ganap na mabayaran ang utang.

Bukod dito, ang interes sa utang ay kasama na sa kabuuang halagang ibinayad sa bangko.

Pag-uuri ng annuity

Ang mga pagbabayad sa annuity ay maaaring nahahati sa mga sumusunod na uri:

1. Nakapirming. Ang mga pagbabayad na hindi nagbabago ay may nakapirming rate anuman ang mga panlabas na kondisyon.
2. Pera. Ang kakayahang baguhin ang halaga ng pagbabayad sa kaso ng pagbagsak o pagtaas sa halaga ng palitan.
3. na-index. Mga pagbabayad depende sa antas, tagapagpahiwatig ng inflation. Sa panahon ng pautang, ang kanilang laki ay madalas na nagbabago.
4. Mga variable. Annuity, na maaaring magbago depende sa estado ng sistema ng pananalapi, mga instrumento.

Magbayad ng pansin! Ang mga nakapirming pagbabayad ay mas mainam para sa lahat ng nanghihiram, dahil may maliit na panganib.

Mga kalamangan at kawalan ng mga pagbabayad sa annuity

Upang mas maunawaan ang paksa, kinakailangang pag-aralan ang mga pangunahing tampok ng ganitong uri ng mga pagbabayad sa pautang. Ito ay may mga sumusunod na pakinabang:

• Pagtatatag ng isang tiyak na halaga ng pagbabayad at ang petsa ng pagbabayad nito.
• Mataas na kakayahang magamit para sa mga nanghihiram. Halos kahit sino ay maaaring mag-aplay para sa isang annuity, anuman ang kanilang sitwasyon sa pananalapi.
• Ang posibilidad ng pagpapababa ng halaga ng buwanang installment na may pagtaas ng inflation.

Hindi walang mga sagabal:

• Mataas na rate. Ang nanghihiram ay magso-overpay ng mas malaking halaga ng pera kumpara sa differential payment.
• Mga problema na nagmumula sa pagnanais na bayaran ang utang nang mas maaga sa iskedyul.
• Walang muling pagkalkula para sa mga maagang pagbabayad.

Ano ang bayad sa utang?

Ang pagbabayad ng annuity ay may mga sumusunod na bahagi:

• Interes na binabayaran ng isang tao kapag nagbabayad ng utang.
• Bahagi ng pangunahing halaga.

Bilang resulta, ang kabuuang halaga ng interes ay halos palaging lumalampas sa halagang iniambag ng nanghihiram upang mabawasan ang utang.

Pangunahing Formula sa Pagbabayad ng Annuity sa Excel

Tulad ng nabanggit sa itaas, sa Microsoft Office Excel maaari kang magtrabaho sa iba't ibang uri ng mga pagbabayad para sa mga pautang at advance. Ang annuity ay walang pagbubukod. Sa pangkalahatan, ang formula kung saan mabilis mong makalkula ang mga kontribusyon sa annuity ay ang mga sumusunod:  

Mahalaga! Imposibleng buksan ang mga bracket sa denominator ng expression na ito upang gawing simple ito.

Ang mga pangunahing halaga ng formula ay na-decipher tulad ng sumusunod:

• AP – pagbabayad sa annuity (pinaikli ang pangalan).
• O – ang laki ng pangunahing utang ng nanghihiram.
• PS – ang rate ng interes na ipinasa sa buwanang batayan ng isang partikular na bangko.
• Ang C ay ang bilang ng mga buwan na tumatagal ang utang.

Upang ma-assimilate ang impormasyon, sapat na magbigay ng ilang mga halimbawa ng paggamit ng formula na ito. Pag-uusapan pa sila.

Mga halimbawa ng paggamit ng PMT function sa Excel

Nagbibigay kami ng isang simpleng kondisyon ng problema. Kinakailangang kalkulahin ang buwanang pagbabayad ng utang kung ang bangko ay naglalagay ng interes na 23%, at ang kabuuang halaga ay 25000 rubles. Ang pagpapautang ay tatagal ng 3 taon. Ang problema ay nalutas ayon sa algorithm:

1. Gumawa ng pangkalahatang spreadsheet sa Excel batay sa source data.

2. I-activate ang PMT function at ilagay ang mga argumento para dito sa naaangkop na kahon.
3. Sa field na “Taya”, ilagay ang formula na “B3/B5”. Ito ang magiging rate ng interes sa utang.
4. Sa linyang "Nper" isulat ang halaga sa form na "B4*B5". Ito ang magiging kabuuang bilang ng mga pagbabayad para sa buong termino ng utang.
5. Punan ang field na "PS". Dito kailangan mong ipahiwatig ang paunang halaga na kinuha mula sa bangko, isulat ang halaga na "B2".

6. Siguraduhin na pagkatapos i-click ang "OK" sa source table, ang halaga na "Buwanang pagbabayad" ay nakalkula.

Karagdagang impormasyon! Ang isang negatibong numero ay nagpapahiwatig na ang nanghihiram ay gumagastos ng pera.

Isang halimbawa ng pagkalkula ng halaga ng sobrang bayad sa isang pautang sa Excel

Sa problemang ito, kailangan mong kalkulahin ang halaga na ang isang tao na kumuha ng pautang na 50000 rubles sa isang rate ng interes na 27% sa loob ng 5 taon ay labis na magbabayad. Sa kabuuan, ang nanghihiram ay gumagawa ng 12 pagbabayad bawat taon. Solusyon:

1. I-compile ang orihinal na talahanayan ng data.

2. Mula sa kabuuang halaga ng mga pagbabayad, ibawas ang paunang halaga ayon sa formula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Dapat itong ipasok sa formula bar sa tuktok ng pangunahing menu ng programa.
3. Bilang resulta, ang halaga ng mga sobrang bayad ay lalabas sa huling linya ng nilikhang plato. Ang nanghihiram ay labis na magbabayad ng 41606 rubles sa itaas.

Ang formula para sa pagkalkula ng pinakamainam na buwanang pagbabayad ng utang sa Excel

Isang gawain na may sumusunod na kondisyon: ang kliyente ay nakarehistro ng isang bank account para sa 200000 rubles na may posibilidad ng buwanang muling pagdadagdag. Kinakailangang kalkulahin ang halaga ng pagbabayad na dapat gawin ng isang tao bawat buwan, upang pagkatapos ng 4 na taon ay mayroon siyang 2000000 rubles sa kanyang account. Ang rate ay 11%. Solusyon:

1. Gumawa ng spreadsheet batay sa orihinal na data.

2. Ipasok ang formula sa linya ng pag-input ng Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» at pindutin ang "Enter" mula sa keyboard. Mag-iiba ang mga titik depende sa mga cell kung saan inilalagay ang talahanayan.
3. Suriin na ang halaga ng kontribusyon ay awtomatikong kinakalkula sa huling linya ng talahanayan.

Magbayad ng pansin! Kaya, upang ang kliyente ay makaipon ng 4 na rubles sa rate na 2000000% sa loob ng 11 taon, kailangan niyang magdeposito ng 28188 rubles bawat buwan. Ang minus sa halaga ay nagpapahiwatig na ang kliyente ay nagkakaroon ng mga pagkalugi sa pamamagitan ng pagbibigay ng pera sa bangko.

Mga tampok ng paggamit ng PMT function sa Excel

Sa pangkalahatan, ang formula na ito ay nakasulat bilang mga sumusunod: =PMT(rate; nper; ps; [bs]; [uri]). Ang function ay may mga sumusunod na tampok:

1. Kapag kinakalkula ang buwanang kontribusyon, ang taunang rate lamang ang isasaalang-alang.
2. Kapag tinukoy ang rate ng interes, mahalagang muling kalkulahin batay sa bilang ng mga installment bawat taon.
3. Sa halip na ang argumentong "Nper" sa formula, isang tiyak na numero ang ipinahiwatig. Ito ang panahon ng pagbabayad.

Pagkalkula ng pagbabayad

Sa pangkalahatan, ang pagbabayad ng annuity ay kinakalkula sa dalawang yugto. Upang maunawaan ang paksa, ang bawat yugto ay dapat isaalang-alang nang hiwalay. Ito ay tatalakayin pa.

Stage 1: pagkalkula ng buwanang installment

Upang kalkulahin sa Excel ang halaga na kailangan mong bayaran bawat buwan sa isang pautang na may nakapirming rate, dapat mong:

1. I-compile ang source table at piliin ang cell kung saan mo gustong ipakita ang resulta at mag-click sa "Insert function" na button sa itaas.

2. Sa listahan ng mga function, piliin ang "PLT" at i-click ang "OK".

3. Sa susunod na window, itakda ang mga argumento para sa function, na nagpapahiwatig ng kaukulang mga linya sa pinagsama-samang talahanayan. Sa dulo ng bawat linya, kailangan mong mag-click sa icon, at pagkatapos ay piliin ang nais na cell sa array.

4. Kapag napunan ang lahat ng mga argumento, ang naaangkop na formula ay isusulat sa linya para sa pagpasok ng mga halaga, at ang resulta ng pagkalkula na may minus sign ay lilitaw sa field ng talahanayan ng "Buwanang pagbabayad".

Mahalaga! Pagkatapos kalkulahin ang installment, posibleng kalkulahin ang halaga na labis na babayaran ng nanghihiram para sa buong panahon ng pautang.

Stage 2: mga detalye ng pagbabayad

Ang halaga ng sobrang bayad ay maaaring kalkulahin buwan-buwan. Bilang resulta, mauunawaan ng isang tao kung gaano karaming pera ang gagastusin niya sa isang pautang bawat buwan. Ang pagkalkula ng detalye ay isinasagawa tulad ng sumusunod:

1. Gumawa ng spreadsheet para sa 24 na buwan.

2. Ilagay ang cursor sa unang cell ng talahanayan at ipasok ang function na "OSPLT".

3. Punan ang mga argumento ng function sa parehong paraan.

4. Kapag pinupunan ang field na "Panahon", kailangan mong sumangguni sa unang buwan sa talahanayan, na nagpapahiwatig ng cell 1.

5. Suriin na ang unang cell sa column na "Pagbabayad ng katawan ng utang" ay napunan.
6. Upang punan ang lahat ng mga hilera ng unang hanay, kailangan mong i-stretch ang cell sa dulo ng talahanayan

7. Piliin ang function na "PRPLT" upang punan ang pangalawang column ng talahanayan.
8. Punan ang lahat ng mga argumento sa binuksan na window alinsunod sa screenshot sa ibaba.

9. Kalkulahin ang kabuuang buwanang pagbabayad sa pamamagitan ng pagdaragdag ng mga halaga sa nakaraang dalawang column.

10. Upang kalkulahin ang "Babayarang balanse", kailangan mong idagdag ang rate ng interes sa pagbabayad sa katawan ng utang at iunat ito sa dulo ng plato upang mapunan ang lahat ng buwan ng utang.

Karagdagang impormasyon! Kapag kinakalkula ang natitira, ang mga palatandaan ng dolyar ay dapat na nakabitin sa formula upang hindi ito gumalaw kapag naunat.

Pagkalkula ng mga pagbabayad sa annuity sa isang pautang sa Excel

Ang PMT function ay responsable para sa pagkalkula ng annuity sa Excel. Ang prinsipyo ng pagkalkula sa pangkalahatan ay upang maisagawa ang mga sumusunod na hakbang:

1. I-compile ang orihinal na talahanayan ng data.
2. Bumuo ng iskedyul ng pagbabayad ng utang para sa bawat buwan.
3. Piliin ang unang cell sa column na "Mga pagbabayad sa utang" at ilagay ang formula ng pagkalkula “PLT ($B3/12;$B$4;$B$2)”.
4. Ang resultang halaga ay nakaunat para sa lahat ng mga haligi ng plato.

Pagkalkula sa pagbabayad ng MS Excel ng pangunahing halaga ng utang

Ang mga pagbabayad sa annuity ay dapat gawin buwan-buwan sa mga nakapirming halaga. At ang rate ng interes ay hindi nagbabago.

Pagkalkula ng balanse ng pangunahing halaga (na may BS=0, uri=0)

Ipagpalagay na ang isang pautang na 100000 rubles ay kinuha para sa 10 taon sa 9%. Kinakailangang kalkulahin ang halaga ng pangunahing utang sa unang buwan ng ika-1 taon. Solusyon:

1. Mag-compile ng datasheet at kalkulahin ang buwanang pagbabayad gamit ang PV formula sa itaas.
2. Kalkulahin ang bahagi ng pagbabayad na kinakailangan upang mabayaran ang bahagi ng utang gamit ang formula «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Kalkulahin ang halaga ng pangunahing utang para sa 120 mga panahon gamit ang isang kilalang formula.
4. Gamit ang HPMT operator, hanapin ang halaga ng interes na binayaran para sa ika-25 buwan.
5. Suriin ang resulta.

Kinakalkula ang halaga ng prinsipal na binayaran sa pagitan ng dalawang panahon

Ang pagkalkula na ito ay pinakamahusay na ginawa sa isang simpleng paraan. Kailangan mong gamitin ang mga sumusunod na formula upang kalkulahin ang halaga sa pagitan para sa dalawang panahon:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(rate; start_period-1; plt; [ps]; [type]) /IF(start_period =1; 1; 1+type *rate)”.

Magbayad ng pansin! Ang mga titik sa panaklong ay pinapalitan ng mga partikular na halaga.

Maagang pagbabayad na may pinababang termino o pagbabayad

Kung kailangan mong bawasan ang termino ng pautang, kailangan mong magsagawa ng mga karagdagang kalkulasyon gamit ang operator ng IF. Kaya posible na kontrolin ang zero na balanse, na hindi dapat maabot bago matapos ang panahon ng pagbabayad.

Upang bawasan ang mga pagbabayad, kailangan mong kalkulahin muli ang kontribusyon para sa bawat nakaraang buwan.

Loan calculator na may hindi regular na pagbabayad

Mayroong ilang mga opsyon sa annuity kung saan maaaring magdeposito ang nanghihiram ng mga variable na halaga sa anumang araw ng buwan. Sa ganitong sitwasyon, ang balanse ng utang at interes ay kinakalkula para sa bawat araw. Sa parehong oras sa Excel kailangan mo:

1. Ilagay ang mga araw ng buwan kung saan ginawa ang mga pagbabayad, at ipahiwatig ang kanilang numero.
2. Tingnan ang mga negatibo at positibong halaga. Mas pinipili ang mga negatibo.
3. Bilangin ang mga araw sa pagitan ng dalawang petsa kung kailan idineposito ang pera.

Pagkalkula ng pana-panahong pagbabayad sa MS Excel. Term na deposito

Sa Excel, mabilis mong makalkula ang halaga ng mga regular na pagbabayad, sa kondisyon na ang isang nakapirming halaga ay naipon na. Isinasagawa ang pagkilos na ito gamit ang PMT function pagkatapos ma-compile ang paunang talahanayan.

Konklusyon

Kaya, ang mga pagbabayad sa annuity ay mas madali, mas mabilis at mas mahusay na kalkulahin sa Excel. Ang operator ng PMT ay responsable para sa kanilang pagkalkula. Ang mas detalyadong mga halimbawa ay matatagpuan sa itaas.