Fomula yowerengera malipiro a annuity mu Excel

Kulipira ngongole ndikosavuta komanso mwachangu kuwerengera ndi Microsoft Office Excel. Nthawi yochulukirapo imathera pakuwerengera pamanja. Nkhaniyi ifotokoza za malipiro a annuity, zomwe zimawerengera, ubwino ndi zovuta zake.

Kodi malipiro a annuity ndi chiyani

Njira yobwezera ngongole mwezi uliwonse, momwe ndalama zomwe zasungidwa sizisintha panthawi yonse ya ngongole. Iwo. pa madeti ena a mwezi uliwonse, munthu amaika ndalama zinazake mpaka ngongoleyo itabwezeredwa.

Kuphatikiza apo, chiwongola dzanja cha ngongolecho chikuphatikizidwa kale mu ndalama zonse zomwe zaperekedwa kubanki.

Classification annuity

Malipiro a annuity akhoza kugawidwa m'magulu awa:

1. Zokhazikika. Malipiro omwe sasintha amakhala ndi mtengo wokhazikika mosasamala kanthu za zinthu zakunja.
2. Ndalama. Kutha kusintha kuchuluka kwa malipiro ngati kugwa kapena kukwera kwa mtengo wosinthira.
3. zolozera. Malipiro kutengera mulingo, chizindikiro cha inflation. Pa nthawi ya ngongole, kukula kwawo nthawi zambiri kumasintha.
4. Zosintha. Annuity, yomwe ingasinthe malinga ndi momwe ndalama zilili, zida.

Tcherani khutu! Malipiro osasunthika ndi abwino kwa onse obwereka, chifukwa alibe chiopsezo chochepa.

Ubwino ndi kuipa kwa malipiro a annuity

Kuti mumvetse bwino mutuwo, m'pofunika kuphunzira mbali zazikulu za mtundu uwu wa malipiro a ngongole. Ili ndi zabwino izi:

• Kukhazikitsa ndalama zenizeni za malipiro ndi tsiku la malipiro ake.
• Kupezeka kwakukulu kwa obwereka. Pafupifupi aliyense atha kulembetsa kuti alandire annuity, mosasamala kanthu za chuma chake.
• Kuthekera kochepetsa kuchuluka kwa gawo la pamwezi ndi kuwonjezeka kwa inflation.

Osati popanda zovuta:

• Mtengo wapamwamba. Wobwereka adzalipira ndalama zochulukirapo poyerekeza ndi malipiro osiyanitsidwa.
• Mavuto obwera chifukwa chofuna kubweza ngongoleyo pasanapite nthawi.
• Palibe kuwerengeranso zolipira msanga.

Kodi malipiro a ngongole ndi chiyani?

Malipiro a Annuity ali ndi zigawo izi:

• Chiwongola dzanja choperekedwa ndi munthu pobweza ngongole.
• Gawo la ndalama zazikulu.

Zotsatira zake, chiwongola dzanja chonse pafupifupi nthawi zonse chimaposa ndalama zomwe wobwereka amapereka kuti achepetse ngongoleyo.

Basic Annuity Payment Formula mu Excel

Monga tafotokozera pamwambapa, mu Microsoft Office Excel mutha kugwira ntchito ndi mitundu yosiyanasiyana yolipirira ngongole ndi zopititsa patsogolo. Annuity ndi chimodzimodzi. Mwambiri, njira yomwe mungawerengere mwachangu zopereka za annuity ndi motere:  

Zofunika! Ndikosatheka kutsegula m'mabulaketi m'gulu la mawu awa kuti muwachepetse.

Zofunikira zazikulu za fomula zimafotokozedwa motere:

• AP - malipiro a annuity (dzina lachidule).
• O - kukula kwa ngongole yaikulu ya wobwereka.
• PS - chiwongola dzanja chomwe chimaperekedwa pamwezi ndi banki inayake.
• C ndi chiwerengero cha miyezi ngongoleyo.

Kuti mudziwe zambiri, ndikwanira kupereka zitsanzo zingapo za kagwiritsidwe ntchito ka ndondomekoyi. Zikambidwanso mowonjezereka.

Zitsanzo zogwiritsira ntchito ntchito ya PMT mu Excel

Timapereka chikhalidwe chosavuta cha vuto. Ndikoyenera kuwerengera ngongole ya mwezi uliwonse ngati banki ikupereka chiwongoladzanja cha 23%, ndipo ndalama zonse ndi 25000 rubles. Kubwereketsa kudzatenga zaka 3. Vutoli limathetsedwa motsatira algorithm:

1. Pangani spreadsheet wamba mu Excel kutengera zomwe zachokera.

2. Yambitsani ntchito ya PMT ndikuyika zifukwa zake mubokosi loyenera.
3. Mu "Bet" munda, lowetsani chilinganizo "B3/B5". Ichi chidzakhala chiwongola dzanja pa ngongole.
4. Mu mzere "Nper" lembani mtengo mu mawonekedwe "B4 * B5". Izi zidzakhala chiwerengero chonse cha malipiro a nthawi yonse ya ngongole.
5. Lembani gawo la "PS". Apa muyenera kuwonetsa ndalama zoyamba zomwe zatengedwa ku banki, ndikulemba mtengo "B2".

6. Onetsetsani kuti mutatha kudina "Chabwino" mu tebulo lochokera, mtengo wa "Malipiro a pamwezi" unawerengedwa.

Zina Zowonjezera! Nambala yolakwika imasonyeza kuti wobwereka akuwononga ndalama.

Chitsanzo chowerengera kuchuluka kwa kubweza ngongole ku Excel

Muvutoli, muyenera kuwerengera ndalama zomwe munthu yemwe watenga ngongole ya 50000 rubles pa chiwongola dzanja cha 27% kwa zaka 5 adzabweza. Pazonse, wobwereka amapereka malipiro 12 pachaka. Yankho:

1. Lembani tebulo lachidziwitso choyambirira.

2. Pazolipira zonse, chotsani ndalama zoyambira molingana ndi ndondomekoyi «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Iyenera kuyikidwa mu kapamwamba kapamwamba kapamwamba ka pulogalamuyo.
3. Chotsatira chake, kuchuluka kwa malipiro owonjezera kudzawonekera pamzere womaliza wa mbale yopangidwa. Wobwereka adzalipira ma ruble 41606 pamwamba.

Njira yowerengera momwe mungalipire ngongole mwezi uliwonse ku Excel

Ntchito yokhala ndi zotsatirazi: kasitomala adalembetsa akaunti yakubanki ya ma ruble 200000 ndi kuthekera kobwezeretsanso mwezi uliwonse. Ndikofunika kuwerengera ndalama zomwe munthu ayenera kulipira mwezi uliwonse, kuti patatha zaka 4 ali ndi 2000000 rubles mu akaunti yake. Mtengo ndi 11%. Yankho:

1. Pangani spreadsheet kutengera deta yoyambirira.

2. Lowetsani fomula mu mzere wolowetsa wa Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» ndi kukanikiza "Enter" kuchokera kiyibodi. Zilembozi zidzasiyana malinga ndi ma cell omwe tebulo limayikidwa.
3. Onetsetsani kuti ndalama zomwe zaperekedwa zimawerengedwa zokha pamzere womaliza wa tebulo.

Tcherani khutu! Choncho, kuti kasitomala adziunjike ma ruble 4 pa mlingo wa 2000000% m'zaka 11, ayenera kuyika ma ruble 28188 mwezi uliwonse. Kuchotsera mu ndalamazo kumasonyeza kuti kasitomala amataya ndalama popereka ndalama kubanki.

Mawonekedwe ogwiritsira ntchito ntchito ya PMT mu Excel

Kawirikawiri, ndondomekoyi imalembedwa motere: =PMT(mlingo; nper; ps; [bs]; [mtundu]). Ntchitoyi ili ndi izi:

1. Pamene zopereka za mwezi ndi mwezi ziŵerengedwa, mlingo wapachaka wokha ndiwo umaganiziridwa.
2. Pofotokoza chiwongola dzanja, ndikofunika kuwerengeranso kutengera kuchuluka kwa magawo pachaka.
3. M'malo mwa mkangano "Nper" mu chilinganizo, nambala yeniyeni imasonyezedwa. Iyi ndi nthawi yolipira.

Kuwerengera malipiro

Kawirikawiri, malipiro a annuity amawerengedwa m'magawo awiri. Kuti timvetsetse mutuwo, gawo lililonse liyenera kuganiziridwa mosiyana. Izi zidzakambidwanso.

Gawo 1: kuwerengera ndalama zapamwezi

Kuti muwerengere ku Excel ndalama zomwe muyenera kulipira mwezi uliwonse pa ngongole ndi mtengo wokhazikika, muyenera:

1. Sungani tebulo loyambira ndikusankha selo lomwe mukufuna kuwonetsa zotsatira ndikudina batani la "Insert function" pamwamba.

2. Pa mndandanda wa ntchito, sankhani "PLT" ndikudina "Chabwino".

3. Pazenera lotsatira, ikani zotsutsana za ntchitoyo, kuwonetsa mizere yofananira patebulo lopangidwa. Pamapeto pa mzere uliwonse, muyenera dinani chizindikirocho, ndiyeno sankhani cell yomwe mukufuna pamndandanda.

4. Zotsutsana zonse zikadzazidwa, ndondomeko yoyenera idzalembedwa pamzere wolowetsa miyeso, ndipo zotsatira zowerengera ndi chizindikiro chochotsera zidzawonekera m'gawo la "Malipiro a Mwezi".

Zofunika! Pambuyo powerengera gawoli, zitheka kuwerengera ndalama zomwe wobwereka adzalipira nthawi yonse ya ngongole.

Gawo 2: zambiri zolipira

Kuchuluka kwa malipiro ochulukirapo kumatha kuwerengedwa pamwezi. Zotsatira zake, munthu amamvetsetsa kuchuluka kwa ndalama zomwe angawononge pa ngongole mwezi uliwonse. Kuwerengera mwatsatanetsatane kumachitika motere:

1. Pangani spreadsheet kwa miyezi 24.

2. Ikani cholozera mu selo loyamba la tebulo ndikuyika ntchito ya "OSPLT".

3. Lembani mikangano ya ntchito mofananamo.

4. Mukadzaza gawo la "Period", muyenera kutchula mwezi woyamba patebulo, kuwonetsa selo 1.

5. Onetsetsani kuti selo yoyamba mugawo la "Kulipira ndi thupi la ngongole" yadzazidwa.
6. Kuti mudzaze mizere yonse ya mzere woyamba, muyenera kutambasula selo mpaka kumapeto kwa tebulo

7. Sankhani "PRPLT" ntchito kuti mudzaze ndime yachiwiri ya tebulo.
8. Lembani mikangano yonse pa zenera lotseguka malinga ndi chithunzi chomwe chili pansipa.

9. Werengerani ndalama zonse zomwe zaperekedwa pamwezi powonjezera mitengo m'zaza ziwiri zam'mbuyo.

10. Kuti muwerenge "Balance yolipidwa", muyenera kuwonjezera chiwongoladzanja pa malipiro pa thupi la ngongole ndikuyitambasula mpaka kumapeto kwa mbale kuti mudzaze miyezi yonse ya ngongole.

Zina Zowonjezera! Powerengera zotsalazo, zizindikiro za dola ziyenera kupachikidwa pa fomula kuti zisasunthike zikatambasulidwa.

Kuwerengera kwa malipiro a annuity pa ngongole ku Excel

Ntchito ya PMT ili ndi udindo wowerengera annuity mu Excel. Mfundo yowerengera nthawi zambiri ndikuchita izi:

1. Lembani tebulo lachidziwitso choyambirira.
2. Pangani ndondomeko yobweza ngongole mwezi uliwonse.
3. Sankhani selo loyamba pagawo la "Malipiro pa ngongole" ndikulowetsa njira yowerengera “PLT ($B3/12;$B$4;$B$2)”.
4. Zotsatira zake zimatambasulidwa pamizere yonse ya mbale.

Kuwerengera mu MS Excel kubweza ndalama zazikulu zangongole

Malipiro a annuity amayenera kupangidwa mwezi uliwonse ndi ndalama zokhazikika. Ndipo chiwongola dzanja sichisintha.

Kuwerengera ndalama zoyambira (ndi BS=0, mtundu=0)

Tiyerekeze kuti ngongole ya 100000 rubles imatengedwa kwa zaka 10 pa 9%. Ndikofunikira kuwerengera kuchuluka kwa ngongole yayikulu m'mwezi woyamba wa chaka cha 1. Yankho:

1. Lembani deta ndikuwerengera malipiro a mwezi uliwonse pogwiritsa ntchito fomula ya PV yomwe ili pamwambapa.
2. Werengani gawo la malipiro ofunikira kuti mulipire gawo lina langongole pogwiritsa ntchito fomula «=-PMT-(PS-PS1)*chinthu=-PMT-(PS +PMT+PS*chinthu)».
3. Werengani kuchuluka kwa ngongole yayikulu kwa nthawi 120 pogwiritsa ntchito njira yodziwika bwino.
4. Pogwiritsa ntchito HPMT pezani kuchuluka kwa chiwongoladzanja chomwe chalipidwa m'mwezi wa 25.
5. Onani zotsatira.

Kuwerengera kuchuluka kwa principal yomwe idalipidwa pakati pa nthawi ziwiri

Kuwerengera kumeneku kumachitidwa bwino m'njira yosavuta. Muyenera kugwiritsa ntchito njira zotsatirazi kuti muwerenge kuchuluka kwa nthawiyi kwa nthawi ziwiri:

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

Tcherani khutu! Zilembo zomwe zili m'makolo zimasinthidwa ndi mfundo zenizeni.

Kubweza msanga ndi nthawi yochepetsedwa kapena kulipira

Ngati mukufuna kuchepetsa nthawi yobwereketsa, muyenera kuwerengeranso pogwiritsa ntchito IF. Kotero zidzakhala zotheka kulamulira malire a zero, omwe sayenera kufika kumapeto kwa nthawi yolipira.

Kuti muchepetse malipiro, muyenera kuwerengeranso zopereka za mwezi uliwonse wapitawo.

Ngongole yowerengera yokhala ndi malipiro osakhazikika

Pali zosankha zingapo za annuity pomwe wobwereketsa amatha kuyika ndalama zosinthika tsiku lililonse la mwezi. Zikatero, ngongole ndi chiwongola dzanja zimawerengedwa tsiku lililonse. Nthawi yomweyo mu Excel muyenera:

1. Lowetsani masiku a mwezi umene malipiro amaperekedwa, ndipo sonyezani chiwerengero chawo.
2. Yang'anani ndalama zosayenera ndi zabwino. Zolakwika zimakondedwa.
3. Werengani masiku pakati pa masiku awiri omwe ndalama zidasungidwa.

Kuwerengera kwa malipiro a periodic mu MS Excel. Term deposit

Ku Excel, mutha kuwerengera mwachangu kuchuluka kwamalipiro anthawi zonse, bola ngati ndalama zokhazikika zasonkhanitsidwa kale. Izi zimachitika pogwiritsa ntchito ntchito ya PMT pambuyo poti tebulo loyambirira lapangidwa.

Kutsiliza

Chifukwa chake, kulipira kwa annuity ndikosavuta, mwachangu komanso kothandiza kuwerengera mu Excel. Wothandizira PMT ndi amene amawerengera. Zitsanzo zatsatanetsatane zitha kupezeka pamwambapa.