Kuwerengeredwa kwa malipiro a annuity mu Microsoft Excel

Musanatenge ngongole, zingakhale bwino kuwerengetsa zolipirira zonse. Izi zimapulumutsa wobwereketsa mtsogolo ku zovuta zingapo zosayembekezereka ndikhumudwitsidwa zikadzapezeka kuti zochulukirapo ndizambiri. Zida za Excel zitha kuthandizira pakuwerengera izi. Tiyeni tiwone momwe angawerengere zolipirira ngongole za annuity mu pulogalamuyi.

Kuwerengera kwamalipiro

Choyamba, ziyenera kunenedwa kuti pali mitundu iwiri ya ngongole:

• Zosiyanitsa;
• Chuma.

Mu njira yosiyanikirana, kasitomala amapereka ndalama zofanana pakubweza ngongole zonse pamwezi kubanki. Kuchulukitsa kwa chiwongola dzanja kumachepera mwezi uliwonse, pamene thupi la ngongole yomwe amawerengedwa limachepera. Chifukwa chake, ndalama zonse zomwe zimaperekedwa pamwezi zimachepetsedwa.

Chiwembu cha ndalama chimagwiritsa ntchito njira yosiyana pang'ono. Makasitomala mwezi uliwonse amapanga ndalama zofanana, zomwe zimakhala zolipirira ngongole yanyimbo ndi chiwongola dzanja. Poyamba, ngongole za chiwongola dzanja zimawerengeredwa ngongole yonse, koma m'mene thupi limachepa, chiwongola dzanja chimachepa. Koma kuchuluka kwa zolipira kumakhalabe kosasinthika chifukwa kuwonjezeka pamwezi kwa kuchuluka kwa zolipiritsa pabungwe la ngongole. Chifukwa chake, pakapita nthawi, chiwongoladzanja chiwongoladzanja chonse chimachepa, ndipo gawo lolipira ndi thupi limakwera. Kuphatikiza apo, ngongole yonse pamwezi siyimasintha pakubweza kwathunthu.

Pongowerengera ndalama zolipiridwa, tidzaima. Komanso, izi ndizofunikira, chifukwa pakadali pano mabanki ambiri amagwiritsa ntchito njira imeneyi. Ndiosavuta kwa makasitomala, chifukwa pamenepa ndalama zolipirira sizisintha, zokhazikika. Makasitomala nthawi zonse amadziwa ndalama zolipira.

Gawo 1: kuwerengera kwamakedzedwe pamwezi

Kuwerengera zopereka za pamwezi mukamagwiritsa ntchito dongosolo la ndalama ku Excel pali ntchito yapadera - PMT. Ili m'gulu la ogwiritsa ntchito ndalama. Machitidwe ake a ntchitoyi ndi awa:

= PLT (mtengo; nper; ps; bs; mtundu)

Monga mukuwonera, ntchitoyi ili ndi ziwonetsero zambiri. Zowona, awiri omaliza awa ndiosankha.

Kukangana Pikisano chikuwonetsa chiwongola dzanja cha nthawi inayake. Mwachitsanzo, ngati chiwongola dzanja chikugwiritsidwa ntchito, koma ngongoleyo imalipidwa pamwezi, ndiye kuti mulingo wa pachaka uzigawidwa ndi 12 ndikugwiritsa ntchito zotsatira ngati mkangano. Ngati mtundu wa kotala umagwiritsidwa ntchito, ndiye kuti muyezo wapachaka uyenera kugawidwa ndi 4 etc.

"Nper" ikuwonetsa kuchuluka kwa nyengo yobweza ngongole. Ndiye kuti, ngati ngongole yatengedwa chaka chimodzi ndikulipira pamwezi, ndiye kuti kuchuluka kwa nthawi kumalingaliridwa 12ngati kwa zaka ziwiri, ndiye kuti kuchuluka kwa nyengo ndi 24. Ngati ngongoleyo imatengedwa zaka ziwiri ndikulipira kotala, ndiye kuti kuchuluka kwa nthawi kumakhala kofanana 8.

Sal chikuwonetsa mtengo womwe ulipo pakadali pano. M'mawu osavuta, iyi ndi ndalama yonse yomwe mumabwezerayo kumayambiriro kwa ngongole, ndiye kuti, ndalama zomwe mumabwereka, kupatula chiwongola dzanja ndi ziwongola dzanja zina.

"Bs" mtengo wamtsogolo. Mtengo uwu, womwe udzakhale thupi la ngongole panthawi yomaliza pangano la ngongole. Mwambiri, mkanganowu ndi "0", popeza wobwereketsa kumapeto kwa chindapusa, ayenera kulipira ngongole yonse. Mlandu wotsimikizidwa ndiosankha. Chifukwa chake, ngati chagwera, ndiye kuti imawerengedwa ngati ziro.

Kukangana "Mtundu" imawerengera kuwerengera nthawi: kumapeto kapena koyambirira kwa nthawi. Poyambirira, zimatengera mtengo wake "0"ndipo chachiwiri - "1". Mabungwe ambiri amabanki amagwiritsa ntchito njira yofanana ndi malipiro kumapeto kwa nthawi. Mtsutsowu ndiwosankha, ndipo ngati utasiyidwa umawonedwa kuti ndi zero.

Tsopano ndi nthawi yoti musunthiretu ku zitsanzo zenizeni zowerengera gawo la pamwezi pogwiritsa ntchito ntchito ya PMT. Pakuwerengera, timagwiritsa ntchito gome ndi gwero, komwe chiwongola dzanja cha ngongole chawonetsedwa (12%), chiwongola dzanja (500,000 ma ruble) ndi ngongole (Miyezi 24) Komanso, ndalama zimapangidwa pamwezi kumapeto kwa nthawi iliyonse.

1. Sankhani chinthucho papepala momwe malembedwe awonekera, ndikudina chizindikiro "Ikani ntchito"itayikidwa pafupi ndi baramu yamu formula.



2. Iwindo lakhazikitsidwa. Ogwira Ntchito. Gulu "Zachuma" sankhani dzinalo "PLT" ndipo dinani batani "Zabwino".



3. Pambuyo pake, zenera la wothandizira limatsegulidwa. PMT.

   M'munda Pikisano lowetsani kuchuluka kwa nthawi. Izi zitha kuchitika pamanja, kungokhazikitsa peresenti, koma tazinena mu khungu limodzi pa pepalalo, ndiye tiziwunikira. Timayika cholozera m'munda, kenako ndikudina foni yofananira. Koma, monga momwe timakumbukira, pagome lathu chiwongola dzanja cha pachaka chimakhazikitsidwa, ndipo nthawi yolipira ndiyofanana ndi mwezi. Chifukwa chake, timagawa kuchuluka kwa pachaka, kapena kuphatikiza ulalo wa foni yomwe ilimo, nambala 12malinga ndi kuchuluka kwa miyezi pachaka. Gawoli limachitika mwachindunji m'munda wa zenera la mkangano.

   M'munda "Nper" nthawi ya ngongole ikhazikitsidwa. Ndiwofanana ndi ife 24 kwa miyezi. Mutha kuyika nambala m'munda 24 pamanja, koma ife, monga momwe zinalili kale, tikuwonetsa kulumikizana ndi komwe kuli chizindikirochi patebulo loyambirira.

   M'munda Sal Kuchulukitsa koyamba kumawonetsedwa. Ndiwofanana 500,000 ma ruble. Monga momwe zidalili m'mbuyomu, tikuwonetsa kulumikizana ndi chidutswa cha pepalacho chomwe chikuyimira.

   M'munda "Bs" ikuwonetsa kuchuluka kwa ngongole, mutapereka zonse. Monga tikukumbukira, mtengo uwu nthawi zambiri umakhala wopanda zero. Khazikitsani nambala imeneyi "0". Ngakhale kutsutsana uku kungasiyidwe konse.

   M'munda "Mtundu" sonyezani kumayambiriro kapena kumapeto kwa mwezi ndalama zapangidwa. Pano, monga momwe zimakhalira nthawi zambiri, zimapangidwa kumapeto kwa mwezi. Chifukwa chake, khazikitsani nambala "0". Monga momwe ziliri ndi chitsimikizo chapitacho, simungathe kulowetsa chilichonse patsamba ili, ndiye pulogalamuyo imangoganiza zokhazokha kuti ili ndi mtengo wofanana ndi zero.

   Pambuyo polemba data yonse, dinani batani "Zabwino".



4. Pambuyo pake, zotsatira za kuwerengera zikuwonetsedwa mu foni yomwe tidawunikira m'ndime yoyamba ya bukuli. Monga mukuwonera, kuchuluka kwa ngongole yonse pamwezi ndi 23536.74 rubles. Musasokonezedwe ndi chizindikiro - "-" chomwe chili kutsogolo kwa ndalamayi. Chifukwa chake Excel akuwonetsa kuti izi ndizowononga ndalama, ndiye kuti, kutaya.



5. Kuti muwerengere ndalama zonse zolipirira ngongole yonse, poganizira kubweza kwa ngongole ndi chiwongola dzanja cha pamwezi, ndikokwanira kuchulukitsa kuchuluka kwa zolipirira pamwezi (23536.74 rublesndi kuchuluka kwa miyezi (Miyezi 24) Monga mukuwonera, ndalama zonse zolipirira ngongole yonse yomwe takhala nayo idakwaniritsidwa 564881.67 ma ruble.



6. Tsopano mutha kuwerengetsa kuchuluka kwa zolipira ngongole zambiri. Kuti muchite izi, chotsani ndalama zonse zomwe mwabweza ngongole, kuphatikizapo chiwongoladzanja ndi ngongole, ndalama yoyamba yobwereka. Koma tikukumbukira kuti choyambirira cha mfundo izi chidasainidwa kale "-". Chifukwa chake, m'malo mwathu, zikuwoneka kuti zikufunika kupindidwa. Monga mukuwonera, ngongole yonse yolipirira yonseyo idakwana 64 881.67 ma ruble.

Phunziro: Excel Feature Wizard

Gawo lachiwiri: zolipiritsa

Ndipo tsopano, mothandizidwa ndi ena ogwira ntchito ku Excel, tidzapanga ndalama zowerengera pamwezi kuti tiwone kuti ndi zingati zomwe timalipira ngongole mwezi umodzi, ndi chiwongola dzanja chotani. Pazifukwa izi, tikujambula mu Excel tebulo lomwe tidzadzaza ndi deta. Mizere yomwe ili patebulopo izikhala yolingana ndi nthawi yolingana, yomwe ndi mwezi. Popeza nthawi yobwereketsa nafe ndi 24 miyezi, ndiye kuti kuchuluka kwa mizere kumakhala koyenera. Malalamu akuwonetsa kulipira thupi la ngongole, chiwongola dzanja, chiwongola dzanja chonse pamwezi, zomwe ndi kuchuluka kwa mizati iwiri yapitayo, komanso ndalama zotsalira zomwe zingaperekedwe.

1. Kuti mudziwe ndalama zolipiridwa ndi ngongole, gwiritsani ntchito ntchitoyo OSPLT, zomwe zidangopangidwira izi. Khazikitsani cholowezera ku khungu lomwe lili mzere "1" ndi mzere "Kubwezera thupi la ngongoleyo". Dinani batani "Ikani ntchito".



2. Pitani ku Fotokozerani Wizard. Gulu "Zachuma" lembani dzina OSPLT ndikanikizani batani "Zabwino".



3. Windo la OSPLT lotsutsa likuyamba. Ili ndi mtundu wotsatira:

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

   Monga mukuwonera, zotsutsana za ntchitoyi zikugwirizana kwathunthu ndi malingaliro a wothandizira PMT, pokhapokha pazokambirana "Mtundu" kufunika kutsanulidwa "Nthawi". Zimawonetsa kuchuluka kwa nthawi yolipira, ndipo mwanjira yathu, kuchuluka kwa mwezi.

   Tikuzaza magawo omwe kale amadziwa zenera loti azigwira ntchito OSPLT deta yomweyo yomwe imagwiritsidwa ntchito PMT. Pokhapokha podziwa kuti mtsogolomo fayilo idzatengedwa pogwiritsa ntchito cholembera, muyenera kupanga maulalo onse m'minda kuti asasinthe. Kuti muchite izi, ikani chikwangwani cha dola patsogolo pa chinthu chilichonse chogwirizanitsa molunjika komanso molunjika. Koma ndikosavuta kuchita izi pongounikira zolumikizira ndi kukanikiza ntchito yofunikira F4. Chizindikiro cha dollar chiziikidwa m'malo oyenera. Komanso musaiwale kuti chiwerengero cha pachaka chikuyenera kugawidwa ndi 12.



4. Koma tili ndi chitsimikizo chimodzi chatsopano chomwe ntchitoyo idalibe. PMT. Kutsutsana uku "Nthawi". M'munda wofananira, ikani ulalo wa khungu loyambirira la chipilalacho "Nthawi". Chidutswa cha pepalachi chili ndi nambala "1", zomwe zikuwonetsa kuchuluka kwa mwezi woyamba wobwereketsa. Koma mosiyana ndi magawo am'mbuyomu, m'munda womwe watchulidwa timasiyira cholumikizacho, ndipo osapanga kuti akhale mtheradi.

   Pambuyo polemba data yonse yomwe tanena pamwambapa, dinani batani "Zabwino".



5. Pambuyo pake, mu cell yomwe tidagawa kale, kuchuluka kobwezerera kubungwe la ngongole mwezi woyamba ziziwonetsedwa. Akonza 18,536.74 rubles.



6. Kenako, monga tafotokozera pamwambapa, tiyenera kukopera fomuloli ku maselo otsala a chipilala pogwiritsa ntchito chodzaza. Kuti muchite izi, ikani cholozera mu ngodya ya kumunsi kwa foni yomwe ili ndi fomula. Chopereka chimasinthidwa kukhala mtanda, chomwe chimatchedwa chikhomo chodzaza. Gwirani batani lakumanzere ndikulikokera kumapeto kwa tebulo.



7. Zotsatira zake, maselo onse m'ndendamo amadzazidwa. Tsopano tili ndi ndondomeko yobweza ngongole mwezi uliwonse. Monga tafotokozera pamwambapa, kuchuluka kwa malipiro pansi pa nkhaniyi kumawonjezeka nthawi iliyonse yatsopano.



8. Tsopano tikuyenera kuwerengera pamwezi ndalama za chiwongola dzanja. Pazifukwa izi tidzagwiritsa ntchito wothandizira PRPLT. Sankhani selo yoyamba yopanda chilichonse Kulipira Kwachidwi. Dinani batani "Ikani ntchito".



9. Pazenera loyambira Ogwira Ntchito m'gulu "Zachuma" timapanga kusankhidwa PRPLT. Dinani batani. "Zabwino".



10. Ntchito yotsutsana ndi ntchito imayamba. PRPLT. Matchulidwe ake ndi awa:

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

    Monga mukuwonera, malingaliro a ntchitoyi ndi ofanana ndendende ndi zinthu zomwezo wothandizira OSPLT. Chifukwa chake, timangolowetsa zomwezo pawindo lomwe tidalowetsamo pawindo lotsutsa lakale. Sitimayiwala nthawi yomweyo kuti ulalo m'munda "Nthawi" ziyenera kukhala zapachibale, ndipo m'magawo ena onse magwirizanidwe ayenera kuchepetsedwa kukhala amtundu wathunthu. Pambuyo pake, dinani batani "Zabwino".



11. Kenako, zotsatira zakuwerengera kuchuluka kwa zolipira chiwongola dzanja cha mwezi woyamba zikuwonetsedwa m'bokosi loyenerera.



12. Kugwiritsa ntchito chikhomo, timakopera mafomuwo pazinthu zomwe zatsala, kuti tipeze dongosolo lakulipirira pamwezi chiwongola dzanja. Monga momwe tikuonera, monga tanena kale, mwezi ndi mwezi mtengo wamalipiro amtunduwu umatsika.



13. Tsopano tikuyenera kuwerengera ngongole yonse pamwezi. Pakuwerengera uku, simuyenera kugwiritsa ntchito aliyense wogwiritsa ntchito, chifukwa mutha kugwiritsa ntchito njira yosavuta yosankha. Onjezani zomwe zili m'maselo a mwezi woyamba wa mizati "Kubwezera thupi la ngongoleyo" ndi Kulipira Kwachidwi. Kuti muchite izi, ikani chikwangwani "=" ku cell yopanda chopanda chilichonse "Ndalama zonse zomwe zimalipidwa pamwezi". Kenako timadina pazinthu ziwiri zomwe zili pamwambapa, kuyika chikwangwani pakati pawo "+". Dinani pa kiyi Lowani.



14. Kenako, gwiritsani ntchito chikhomo, monga momwe zinalili m'mbuyomu, mudzazeni mzere ndi data. Monga mukuwonera, nthawi yonse ya mgwirizano, ndalama zonse zomwe zimaperekedwa pamwezi, kuphatikizapo kulipidwa ndi ngongole ndi chiwongola dzanja, zidzakhala 23536.74 rubles. Kwenikweni, tinawerengera kale chizindikiro ichi pogwiritsa ntchito PMT. Koma pankhaniyi zimaperekedwa momveka bwino, ndendende monga kuchuluka kwa zolipirira ngongole ndi chiwongola dzanja.



15. Tsopano mukufunikira kuwonjezera dawunilodi pagululi, yomwe imawonetsa pamwezi ndalama zonse zomwe zimafunikiridwabe. Mu cell yoyamba ya mzati "Kusunga zolipirira" kuwerengera kudzakhala kosavuta kwambiri. Tiyenera kuchotsa kuchokera ku ndalama zoyambira ngongole, zomwe zimasonyezedwa patebulopo ndi deta yoyamba, kulipira pamaziko a ngongole ya mwezi woyamba pagawoli. Koma, titazindikira kuti imodzi mwazomwe takhala nazo chikwangwani "-", ndiye kuti sayenera kuchotsedwa, koma yokulungidwa. Timachita izi ndikudina batani Lowani.



16. Koma kuwerengetsa ndalama zoyenera pambuyo pa miyezi yachiwiri ndi yotsatira kumakhala kovuta kwambiri. Kuti tichite izi, tifunikira kuchotsa kuchokera kubungwe la ngongole koyambirira kwa ngongole ndalama zonse zolipirira kubungwe la ngongole za nthawi yapita. Khazikitsani chikwangwani "=" mu cell yachiwiri ya mzati "Kusunga zolipirira". Chotsatira, tikuwonetsa kulumikizana ndi foni, yomwe ili ndi ngongole yoyamba. Pangani kukhala mtheradi powunikira ndi kukanikiza fungulo F4. Kenako timayika chikwangwani "+", popeza mtengo wachiwiri kwa ife ndiwopanda pake. Pambuyo pake, dinani batani "Ikani ntchito".



17. Iyamba Fotokozerani Wizardmomwe muyenera kusamukira ku gululi "Masamu". Pamenepo timatsimikizira zolembedwazo SUM ndipo dinani batani "Zabwino".



18. Tsamba la mkangano wa ntchito likuyamba SUM. Wogwiritsa ntchitoyo amatumizira kuchuluka kwa zomwe zili m'maselo, zomwe tifunikira kuzichita "Kubwezera thupi la ngongoleyo". Ili ndi mtundu wotsatira:

    = SUM (nambala1; nambala2; ...)

    Amatsutsidwewo amatanthauza maselo omwe ali ndi manambala. Tikhazikitsa chidziwitso kumunda "Nambala1". Kenako timagwira batani lakumanzere ndikusankha maselo awiri oyamba a pepalalo "Kubwezera thupi la ngongoleyo". M'munda, monga momwe tikuonera, ulalo wamtunduwu ukuwonetsedwa. Ili ndi magawo awiri, olekanitsidwa ndi colon: maulalo a selo loyamba la masanjidwewo ndi otsiriza. Pofuna kuti tithe kutengera njira yomwe tinafotokozeredwa pogwiritsa ntchito cholembera mtsogolo, timapanga gawo loyambirira la ulalo. Sankhani ndikudina batani la ntchito F4. Gawo lachiwiri la ulalo lidakali laling'ono. Tsopano, mukamagwiritsa ntchito chikhomo, selo yoyambayo idzakhazikitsidwa, ndipo yomaliza imatambasamba ikangotsika pansi. Izi ndizomwe tiyenera kukwaniritsa zolinga zathu. Kenako, dinani batani "Zabwino".



19. Chifukwa chake, zotsatira za kuchuluka kwa ngongole pambuyo mwezi wachiwiri zikuwonetsedwa mu cell. Tsopano, kuyambira selo iyi, timalemba chilinganizo pazinthu zopanda kanthu pogwiritsa ntchito cholembera.



20. Kuwerengera kwa pamwezi okwanira ngongole mwezi wonse. Monga zikuyembekezeredwa, kumapeto kwa ndalamayi ndi zero.

Chifukwa chake, sitinangowerengera zolipirira ngongole, koma tinalinganiza mtundu waungowerengera ngongole. Zomwe zigwira ntchito pachaka. Ngati mu gome loyambilira, mwachitsanzo, tikusintha kukula kwa ngongole ndi chiwongola dzanja cha pachaka, ndiye kuti patebulo lomaliza deta idzangoberekanso.Chifukwa chake, singagwiritsidwe ntchito osati kamodzi kokha pa mlandu winawake, koma wogwiritsidwa ntchito m'malo osiyanasiyana kuwerengera zomwe mungasankhe ngongole malinga ndi dongosolo la ndalama.

Phunziro: Ntchito Zachuma ku Excel

Monga mukuwonera, pogwiritsa ntchito pulogalamu ya Excel kunyumba, mutha kuwerengera ngongole yonse pamwezi malinga ndi chiwembu chogwiritsa ntchito ndalama zanu pogwiritsa ntchito opangira zinthuzi PMT. Kuphatikiza apo, kugwiritsa ntchito ntchito OSPLT ndi PRPLT mutha kuwerengera kuchuluka kwa zolipira pa ngongole yanyimbo ndi chiwongoladzanja pa nthawiyo. Kugwiritsa ntchito zonyamula katundu zonsezi pamodzi, ndizotheka kupanga zowerengera zamphamvu zomwe zingagwiritsidwe ntchito kopitilira kamodzi kuwerengera zolipirira.