Ifomula yokubala inkokhelo yempesheni ku-Excel

Izinkokhelo zemalimboleko kulula futhi ziyashesha ukubala nge-Microsoft Office Excel. Kuchithwa isikhathi esiningi ekubaleni mathupha. Lesi sihloko sizogxila ezinkokhelweni zempesheni, izici zokubala kwazo, izinzuzo kanye nokubi.

Iyini inkokhelo yempesheni

Indlela yokukhokha nyanga zonke yemali mboleko, lapho inani elifakiwe lingashintshi phakathi naso sonke isikhathi semali mboleko. Labo. ngezinsuku ezithile zenyanga ngayinye, umuntu ufaka inani elithile lemali kuze kube yilapho isikhokhelwe ngokugcwele.

Ngaphezu kwalokho, inzalo yemali mboleko isivele ifakiwe enanini eliphelele elikhokhelwe ebhange.

Classification annuity

Izinkokhelo ze-annuity zingahlukaniswa ngezinhlobo ezilandelayo:

1. Kulungisiwe. Izinkokhelo ezingashintshi zinenani elinqunyiwe kungakhathaliseki izimo zangaphandle.
2. Imali. Ikhono lokushintsha inani lenkokhelo uma kwenzeka kuwa noma lenyuka izinga lokushintshisana.
3. okukhonjiwe. Izinkokhelo kuye ngezinga, inkomba yokwehla kwamandla emali. Ngesikhathi sokubolekwa kwemali, ubukhulu bawo buvame ukushintsha.
4. Okuguquguqukayo. I-Annuity, engashintsha kuye ngesimo sesistimu yezezimali, amathuluzi.

Nakani! Izinkokhelo ezingaguquki zikhethwa kubo bonke ababoleki, ngoba zinobungozi obuncane.

Izinzuzo kanye nokubi kwezinkokhelo zempesheni

Ukuze usiqonde kangcono isihloko, kuyadingeka ukufunda izici ezibalulekile zalolu hlobo lokukhokha imali mboleko. Inezinzuzo ezilandelayo:

• Ukusungula inani elithile lenkokhelo kanye nosuku lokukhokha kwayo.
• Ukutholakala okuphezulu kwababoleki. Cishe noma ubani angafaka isicelo somholo, kungakhathaliseki ukuthi isimo sakhe sezimali sinjani.
• Amathuba okwehlisa inani lesitolimende sanyanga zonke ngokunyuka kwe-inflation.

Hhayi ngaphandle kwezihibe:

• Izinga eliphezulu. Obolekayo uzokhokha inani elikhulu lemali uma kuqhathaniswa nenkokhelo ehlukile.
• Izinkinga ezibangelwa isifiso sokukhokha isikweletu ngaphambi kwesikhathi esimisiwe.
• Akukho ukubalwa kabusha kwezinkokhelo zangaphambi kwesikhathi.

Iyini inkokhelo yemalimboleko?

Inkokhelo ye-Annuity inezingxenye ezilandelayo:

• Inzalo ekhokhwa ngumuntu lapho ekhokha imali ebolekiwe.
• Ingxenye yenani eliyinhloko.

Ngenxa yalokho, inani eliphelele lenzalo cishe njalo lidlula inani elinikelwe ngumboleki ukuze kuncishiswe isikweletu.

I-Basic Annuity Payment Formula ku-Excel

Njengoba kushiwo ngenhla, ku-Microsoft Office Excel ungasebenza ngezinhlobo ezahlukene zokukhokha imali ebolekiwe kanye nentuthuko. I-Annuity ayihlukile. Ngokuvamile, ifomula ongakwazi ukubala ngayo ngokushesha iminikelo yempesheni imi kanje:  

Kubalulekile! Akunakwenzeka ukuvula amabakaki edinomineyitha yalesi sisho ukuze usenze sibe lula.

Amanani ayinhloko wefomula achazwa kanje:

• I-AP - inkokhelo ye-annuity (igama lifinyeziwe).
• O - usayizi wesikweletu esiyinhloko somboleki.
• I-PS - izinga lenzalo elibekwe phambili njalo ngenyanga yibhange elithile.
• C inombolo yezinyanga imali ebolekiwe.

Ukuze uhlanganise ulwazi, kwanele ukunikeza izibonelo ezimbalwa zokusebenzisa le fomula. Kusazoxoxwa ngakho.

Izibonelo zokusebenzisa umsebenzi we-PMT ku-Excel

Sinikeza isimo esilula senkinga. Kuyadingeka ukubala inkokhelo yemalimboleko yanyanga zonke uma ibhange libeka phambili inzalo ka-23%, futhi inani eliphelele lingama-ruble angu-25000. Ukuboleka kuzohlala iminyaka emi-3. Inkinga ixazululwa ngokuya nge-algorithm:

1. Yenza isipredishithi esivamile ku-Excel ngokusekelwe kudatha yomthombo.

2. Yenza kusebenze umsebenzi we-PMT bese ufaka izimpikiswano zawo ebhokisini elifanele.
3. Esigabeni esithi “Bet”, faka ifomula “B3/B5”. Lokhu kuzoba yizinga lenzalo yemalimboleko.
4. Emgqeni othi "Nper" bhala inani kwifomu elithi "B4*B5". Leli kuzoba isamba senani lezinkokhelo zaso sonke isikhathi semalimboleko.
5. Gcwalisa inkambu ethi “PS”. Lapha udinga ukukhombisa inani lokuqala elithathwe ebhange, ubhala inani "B2".

6. Qiniseka ukuthi ngemva kokuchofoza "KULUNGILE" kuthebula lomthombo, inani "Inkokhelo yanyanga zonke" libaliwe.

Ulwazi Olwengeziwe! Inombolo enegethivu ibonisa ukuthi obolekayo usebenzisa imali.

Isibonelo sokubala inani lemali ekhokhwa ngokweqile emalimboleko ku-Excel

Kule nkinga, udinga ukubala inani lemali umuntu othathe imali ebolekiwe yama-ruble angu-50000 ngenani lenzalo engu-27% iminyaka engu-5 uzokhokha ngokweqile. Sekukonke, umboleki wenza izinkokhelo eziyi-12 ngonyaka. Isixazululo:

1. Hlanganisa ithebula ledatha langempela.

2. Kusukela enanini eliphelele lezinkokhelo, susa inani lokuqala ngokwefomula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Kufanele ifakwe kubha yefomula phezulu kumenyu eyinhloko yohlelo.
3. Ngenxa yalokho, inani lezinkokhelo ezingaphezulu lizovela kulayini wokugcina wepuleti elidaliwe. Umboleki uzokhokha ngaphezulu kwama-ruble angama-41606 ngaphezulu.

Ifomula yokubala inkokhelo yemalimboleko yanyanga zonke e-Excel

Umsebenzi onombandela olandelayo: iklayenti libhalise i-akhawunti yasebhange ngama-ruble angu-200000 kanye nethuba lokugcwalisa nyanga zonke. Kudingekile ukubala inani lenkokhelo umuntu okufanele ayenze njalo ngenyanga, ukuze emva kweminyaka engu-4 abe nama-ruble angu-2000000 ku-akhawunti yakhe. Izinga ngu-11%. Isixazululo:

1. Dala isipredishithi ngokusekelwe kudatha yangempela.

2. Faka ifomula kulayini wokufaka we-Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» bese ucindezela "Enter" kusukela kukhibhodi. Izinhlamvu zizohluka kuye ngamaseli lapho ithebula libekwe khona.
3. Hlola ukuthi inani lomnikelo libalwa ngokuzenzakalelayo emgqeni wokugcina wethebula.

Nakani! Ngakho-ke, ukuze iklayenti liqongelele ama-ruble angu-4 ngenani lika-2000000% eminyakeni engu-11, udinga ukufaka ama-ruble angu-28188 njalo ngenyanga. Ukukhishwa enanini kubonisa ukuthi iklayenti lithola ukulahlekelwa ngokunikeza imali ebhange.

Izici zokusebenzisa umsebenzi we-PMT ku-Excel

Ngokuvamile, le fomula ibhalwe kanje: =PMT(isilinganiso; nper; ps; [bs]; [uhlobo]). Uhlelo lunezici ezilandelayo:

1. Lapho iminikelo yanyanga zonke ibalwa, kucatshangelwa inani lonyaka kuphela.
2. Lapho ucacisa inani lenzalo, kubalulekile ukubala kabusha ngokusekelwe enanini lezitolimende ngonyaka.
3. Esikhundleni sempikiswano ethi “Nper” kufomula, kuboniswa inombolo ethile. Lesi isikhathi sokukhokha.

Isibalo senkokhelo

Ngokuvamile, inkokhelo yempesheni ibalwa ngezigaba ezimbili. Ukuze uqonde isihloko, isigaba ngasinye kufanele sicatshangelwe ngokwehlukana. Lokhu kuzophinde kuxoxwe ngakho.

Isigaba 1: ukubalwa kwesitolimende sanyanga zonke

Ukuze ubale ku-Excel inani lemali okudingeka uyikhokhe nyanga zonke emalini ebolekiwe enenani elingaguquki, kufanele:

1. Hlanganisa ithebula lomthombo bese ukhetha iseli ofuna ukubonisa kulo umphumela bese uchofoza inkinobho ethi “Faka umsebenzi” ngaphezulu.

2. Ohlwini lwemisebenzi, khetha "PLT" bese uchofoza "Kulungile".

3. Efasiteleni elilandelayo, setha izimpikiswano zomsebenzi, ubonise imigqa ehambisanayo kuthebula elihlanganisiwe. Ekupheleni komugqa ngamunye, udinga ukuchofoza isithonjana, bese ukhetha iseli oyifunayo ohlwini.

4. Lapho zonke izimpikiswano sezigcwalisiwe, ifomula efanelekile izobhalwa emugqeni wokufaka amanani, futhi umphumela wokubala onophawu lokususa uzovela emkhakheni wethebula elithi “Inkokhelo yanyanga zonke”.

Kubalulekile! Ngemva kokubala isitolimende, kuzokwazi ukubala inani umboleki azolikhokha ngaphezu kwaso sonke isikhathi semalimboleko.

Isigaba 2: imininingwane yokukhokha

Inani lokukhokha ngokweqile lingabalwa nyanga zonke. Ngenxa yalokho, umuntu uzoqonda ukuthi ingakanani imali azoyisebenzisa ekubolekeni njalo ngenyanga. Ukubala kwemininingwane kwenziwa kanje:

1. Dala isipredishithi izinyanga ezingama-24.

2. Beka ikhesa kuseli lokuqala lethebula bese ufaka umsebenzi we-“OSPLT”.

3. Gcwalisa ama-agumenti omsebenzi ngendlela efanayo.

4. Uma ugcwalisa inkambu ethi “Period”, udinga ukubhekisa enyangeni yokuqala kuthebula, ekhombisa iseli 1.

5. Hlola ukuthi iseli lokuqala kukholamu ethi “Inkokhelo ngomzimba wemali mboleko” ligcwalisiwe.
6. Ukugcwalisa yonke imigqa yekholomu yokuqala, udinga ukwelula iseli ekupheleni kwetafula

7. Khetha umsebenzi we-“PRPLT” ukuze ugcwalise ikholamu yesibili yethebula.
8. Gcwalisa wonke ama-agumenti efasiteleni elivuliwe ngokuhambisana nesithombe-skrini esingezansi.

9. Bala inkokhelo yanyanga zonke ngokungeza amanani kumakholomu amabili adlule.

10. Ukuze ubale "Ibhalansi ekhokhwayo", udinga ukwengeza inani lenzalo enkokhelweni emzimbeni wemali mboleko futhi uyilule kuze kube sekupheleni kwepuleti ukuze ugcwalise zonke izinyanga zemboleko.

Ulwazi Olwengeziwe! Lapho kubalwa okusele, izimpawu zamadola kufanele zilengiswe kufomula ukuze zingasuki lapho zeluliwe.

Ukubalwa kwezinkokhelo zempesheni emalimboleko ku-Excel

Umsebenzi we-PMT unesibopho sokubala imali yonyaka ku-Excel. Umgomo wokubala ngokujwayelekile ukwenza lezi zinyathelo ezilandelayo:

1. Hlanganisa ithebula ledatha langempela.
2. Yakha uhlelo lokukhokha isikweletu senyanga ngayinye.
3. Khetha iseli lokuqala kukholomu ethi “Izinkokhelo emalimboleko” bese ufaka ifomula yokubala “PLT ($B3/12;$B$4;$B$2)”.
4. Inani eliwumphumela leluliwe kuwo wonke amakholomu epuleti.

Ukubalwa ngenkokhelo ye-MS Excel yenani eliyinhloko lesikweletu

Izinkokhelo zempesheni kufanele zenziwe nyanga zonke ngamanani angashintshi. Futhi izinga lenzalo alishintshi.

Ukubalwa kwebhalansi yenani eliyinhloko (nge-BS=0, uhlobo=0)

Ake sithi isikweletu se-ruble esingu-100000 sithathwa iminyaka eyi-10 ngo-9%. Kuyadingeka ukubala inani lesikweletu esiyinhloko enyangeni yoku-1 yonyaka wesi-3. Isixazululo:

1. Hlanganisa idatha bese ubala inkokhelo yanyanga zonke usebenzisa ifomula ye-PV engenhla.
2. Bala isabelo senkokhelo edingekayo ukuze ukhokhe ingxenye yesikweletu usebenzisa ifomula «=-PMT-(PS-PS1)*into=-PMT-(PS +PMT+PS*nto)».
3. Bala inani lesikweletu esiyinhloko izikhathi ezingu-120 usebenzisa ifomula eyaziwayo.
4. Usebenzisa opharetha we-HPMT thola inani lenzalo elikhokhelwe ngenyanga yama-25.
5. Hlola umphumela.

Ibala inani likathishanhloko elikhokhiwe phakathi kwezikhathi ezimbili

Lesi sibalo senziwa kangcono ngendlela elula. Udinga ukusebenzisa amafomula alandelayo ukuze ubale inani lesikhathi sokuphumula izinkathi ezimbili:

• =«-BS(into; con_period; plt; [ps]; [uhlobo]) /(1+uhlobo *into)».
• = “+ BS(isilinganiso; qala_inkathi-1; plt; [ps]; [uhlobo]) /IF(inkathi_yokuqala =1; 1; 1+hlobo *izinga)”.

Nakani! Izinhlamvu ezikubakaki zithathelwa indawo amanani athile.

Ukukhokha kusenesikhathi ngesikhathi esincishisiwe noma inkokhelo

Uma udinga ukunciphisa isikhathi semalimboleko, kuzodingeka wenze izibalo ezengeziwe usebenzisa u-opharetha we-IF. Ngakho kuzokwazi ukulawula ibhalansi enguziro, okungafanele ifinyelelwe ngaphambi kokuphela kwesikhathi sokukhokha.

Ukuze unciphise izinkokhelo, udinga ukubala kabusha umnikelo wenyanga ngayinye edlule.

Isibali semalimboleko esinezinkokhelo ezingajwayelekile

Kunezinketho ezimbalwa zempesheni lapho umboleki angafaka amanani aguquguqukayo nganoma yiluphi usuku lwenyanga. Esimeni esinjalo, ibhalansi yesikweletu nenzalo kubalwa usuku ngalunye. Ngesikhathi esifanayo ku-Excel udinga:

1. Faka izinsuku zenyanga okwenziwa ngazo izinkokhelo, futhi ubonise inombolo yazo.
2. Hlola amanani anegethivu kanye namaphozithivu. Ezingezinhle ziyakhethwa.
3. Bala izinsuku phakathi kwezinsuku ezimbili imali efakwe ngazo.

Ukubalwa kwenkokhelo yezikhathi ezithile ku-MS Excel. Idiphozi yesikhathi

Ku-Excel, ungakwazi ukubala ngokushesha inani lezinkokhelo ezijwayelekile, inqobo nje uma inani elinqunyiwe seliqoqiwe. Lesi senzo senziwa kusetshenziswa umsebenzi we-PMT ngemva kokuhlanganiswa kwethebula lokuqala.

Isiphetho

Ngakho-ke, izinkokhelo zempesheni zilula, zishesha futhi zisebenza kahle kakhulu ukubala ku-Excel. Umsebenzisi we-PMT unesibopho sokubala kwakhe. Izibonelo ezinemininingwane eminingi zingatholakala ngenhla.