Formula yekuverenga annuity kubhadhara muExcel

Kubhadhara kwechikwereti kuri nyore uye nekukurumidza kuverenga neMicrosoft Office Excel. Yakawanda nguva yakawanda inopedzwa pakuverenga nemaoko. Ichi chinyorwa chichatarisa pane annuity kubhadhara, maficha ekuverenga kwavo, zvakanakira uye zvazvakaipira.

Chii chinonzi annuity payment

Nzira yekubhadhara chikwereti chemwedzi wega wega, umo mari yakachengetwa haishanduki panguva yose yechikwereti. Avo. pamazuva akati omwedzi woga woga, munhu anoisa chitsama chakati chemari kutozosvikira chikwereti chabhadharwa zvizere.

Uyezve, mubereko pachikwereti chatobatanidzwa mumari yese yakabhadharwa kubhangi.

Classification annuity

Annuity kubhadhara kunogona kukamurwa mumhando dzinotevera:

1. Fixed. Maripo isingashanduki ine mwero wakasarudzika zvisinei nemamiriro ekunze.
2. Mari. Iko kukwanisa kushandura huwandu hwekubhadhara kana kudonha kana kusimuka mumutengo wekuchinjana.
3. indexed. Kubhadhara zvichienderana nehuwandu, inflation chiratidzo. Munguva yechikwereti, saizi yavo inowanzochinja.
4. Variables. Annuity, iyo inogona kuchinja zvichienderana nemamiriro emari system, zviridzwa.

Teerera! Fixed kubhadhara kunodiwa kune vese vanokwereta, nekuti vane njodzi shoma.

Zvakanakira nekuipira kwe annuity payments

Kuti unzwisise zviri nani musoro, zvinodikanwa kuti udzidze zvakakosha zverudzi urwu rwekubhadhara chikwereti. Iine zvinotevera zvakanakira:

• Kugadzira imwe mari yekubhadhara uye zuva rekubhadhara kwayo.
• Kuwanikwa kwepamusoro kune vanokwereta. Anenge munhu wese anogona kunyorera annuity, zvisinei nemamiriro avo emari.
• Iko mukana wekudzikisa huwandu hwemari yemwedzi nekuwedzera kwekuwedzera kwemari.

Kwete pasina zvipingamupinyi:

• High rate. Mukwereti achabhadhara mari yakawanda kana ichienzaniswa nekubhadhara kwakasiyana.
• Matambudziko anokonzerwa nechishuvo chekubhadhara chikwereti mberi kwenguva.
• Hapana maverengero ekutanga kubhadhara.

Kubhadhara kwechikwereti chii?

Kubhadhara kweAnnuity kune zvinotevera zvikamu:

• Interest inobhadharwa nemunhu pakubhadhara chikwereti.
• Chikamu chemari huru.

Somugumisiro, chitsama chose chemhindu chinenge nguva dzose chinodarika chitsama chinogoverwa nomukwereti kuderedza chikwereti.

Yekutanga Annuity Kubhadhara Formula muExcel

Sezvambotaurwa pamusoro, muMicrosoft Office Excel unogona kushanda nemhando dzakasiyana dzekubhadhara zvikwereti uye kumberi. Annuity haina mutsauko. Muzhinji, iyo formula iyo iwe yaunogona kukurumidza kuverenga mipiro ye annuity ndeiyi inotevera:  

Zvinokosha! Hazvibviri kuvhura mabhuraketi mudhinominata yeshoko iri kuti rive nyore.

Izvo zvakakosha zvakakosha zveformula zvinotsanangurwa sezvizvi:

• AP - annuity kubhadhara (zita rakapfupikiswa).
• O - ukuru hwechikwereti chikuru chemukwereti.
• PS - mubereko unoiswa mberi pamwedzi neimwe bhangi.
• C ndiyo nhamba yemwedzi iyo chikwereti chinotora.

Kuenzanisa ruzivo, zvakakwana kupa mienzaniso mishoma yekushandiswa kweiyi fomula. Dzichakurukurwa mberi.

Mienzaniso yekushandisa iyo PMT basa muExcel

Isu tinopa mamiriro akareruka edambudziko. Izvo zvinodikanwa kuverenga kubhadharwa kwechikwereti chemwedzi wega wega kana bhangi ichiisa mberi mubereko we23%, uye iyo yakazara mari 25000 rubles. Kukweretesa kuchagara kwemakore matatu. Dambudziko rinogadziriswa maererano ne algorithm:

1. Gadzira general spreadsheet muExcel zvichibva pane iyo source data.

2. Shandisa PMT basa uye isa nharo dzayo mubhokisi rakakodzera.
3. Mundima ye "Bet", pinda iyo formula "B3/B5". Ichi chichava mubereko wechikwereti pachikwereti.
4. Mumutsara "Nper" nyora kukosha muchimiro "B4 * B5". Iyi ichava nhamba yese yekubhadhara kwenguva yese yechikwereti.
5. Zadza mundima ye "PS". Pano iwe unoda kuratidza mari yekutanga yakatorwa kubva kubhangi, kunyora kukosha "B2".

6. Ita shuwa kuti mushure mekudzvanya "OK" mune iyo tafura tafura, kukosha kwe "Mwedzi kubhadhara" kwakaverengerwa.

Mamwe Mashoko! Nhamba isina kunaka inoratidza kuti mukwereti ari kushandisa mari.

Muenzaniso wekuverenga huwandu hwekubhadhara zvakanyanya pachikwereti muExcel

Mune dambudziko iri, iwe unofanirwa kuverenga iyo mari iyo munhu akatora chikwereti che50000 rubles pamubhadharo we27% wemakore mashanu achabhadhara. Pakazara, mukwereti anoita 5 kubhadhara pagore. Solution:

1. Gadzira iyo yekutanga data tafura.

2. Kubva pamari yese yekubhadhara, bvisa mari yekutanga zvichienderana neformula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Inofanirwa kuiswa mubhawa yeformula kumusoro kwemenyu huru yechirongwa.
3. Nekuda kweizvozvo, huwandu hwekubhadhara kwakawandisa huchaonekwa mumutsara wekupedzisira weplate yakagadzirwa. Mukwereti achabhadhara 41606 rubles pamusoro.

Iyo fomula yekuverenga iyo yakakwana yemwedzi yekubhadhara chikwereti muExcel

Basa rine mamiriro anotevera: mutengi akanyoresa bhangi account ye200000 rubles pamwe nemukana wekuzadzazve pamwedzi. Zvakakosha kuverenga mari yekubhadhara iyo munhu anofanira kuita mwedzi woga woga, kuitira kuti mushure memakore mana ane 4 rubles muakaunti yake. Mutengo we2000000%. Solution:

1. Gadzira spreadsheet zvichienderana nedata rekutanga.

2. Pinda fomula mumutsara wekuisa weExcel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» uye tinya "Enter" kubva keyboard. Mavara achasiyana zvichienderana nemasero anoiswa tafura.
3. Tarisa kuti mari yemupiro inoverengerwa otomatiki mumutsara wekupedzisira wetafura.

Teerera! Saka, kuitira kuti mutengi aunganidze 4 rubles pamwero we2000000% mumakore gumi nerimwe, anofanira kuisa 11 rubles mwedzi wega wega. Iyo minus mumari inoratidza kuti mutengi anowana kurasikirwa nekupa mari kubhangi.

Zvimiro zvekushandisa iyo PMT basa muExcel

Kazhinji, iyi fomula yakanyorwa seizvi: =PMT(chiyero; nper; ps; [bs]; [rudzi]). Basa racho rine zvinotevera:

1. Apo mipiro yemwedzi nomwedzi inoverengwa, mwero wepagore chete unorangarirwa.
2. Kana uchitsanangura mubereko, zvakakosha kuti uverenge zvakare zvichienderana nehuwandu hwezvikamu pagore.
3. Panzvimbo pekupokana "Nper" mufomula, nhamba chaiyo inoratidzwa. Iyi ndiyo nguva yekubhadhara.

Payment calculation

Kazhinji, kubhadhara kwe annuity kunoverengwa mumatanho maviri. Kuti unzwisise musoro, imwe neimwe yenhanho inofanirwa kutariswa zvakasiyana. Izvi zvichakurukurwa mberi.

Nhanho 1: kuverenga kwemari yemwedzi

Kuti uverenge muExcel mari yaunofanirwa kubhadhara mwedzi wega wega pachikwereti chine chiyero chakatarwa, unofanirwa:

1. Gadzira iyo tafura yekubva uye sarudza sero yaunoda kuratidza mhedzisiro uye tinya bhatani rekuti "Isa basa" pamusoro.

2. Mune runyorwa rwemabasa, sarudza "PLT" uye tinya "OK".

3. Muhwindo rinotevera, isa nharo dzebasa racho, zvichiratidza mitsara inoenderana mutafura yakagadzirwa. Pakupera kwemutsara wega wega, unofanirwa kudzvanya pane icon, uye wozosarudza sero raunoda muhurongwa.

4. Kana nharo dzese dzazadzwa, fomura yakakodzera ichanyorwa mumutsara wekupinda kukosha, uye mhedzisiro yekuverenga ine minus sign ichaonekwa mumunda we "Mwedzi kubhadhara" tafura.

Zvinokosha! Mushure mekuverenga chikamu, zvinogoneka kuverenga iyo mari iyo mukwereti achawedzera kubhadhara kwenguva yese yechikwereti.

Nhanho 2: ruzivo rwekubhadhara

Huwandu hwekubhadhara zvakanyanya hunogona kuverengerwa pamwedzi. Somugumisiro, munhu achanzwisisa kuti imarii yaachashandisa pachikwereti mwedzi woga woga. Detail calculation inoitwa sezvinotevera:

1. Gadzira spreadsheet kwemwedzi makumi maviri nemana.

2. Isa chitubu muchitokisi chekutanga chetafura uye isa "OSPLT" basa.

3. Zadza basa nharo nenzira imwechete.

4. Paunenge uchizadza mundima ye "Period", unofanirwa kutarisa kumwedzi wekutanga patafura, uchiratidza sero 1.

5. Tarisa kuti sero rekutanga muchikamu "Kubhadhara nemuviri wechikwereti" razadzwa.
6. Kuzadza mitsara yese yekoramu yekutanga, unofanirwa kutambanudza sero kusvika kumagumo etafura

7. Sarudza iyo "PRPLT" basa rekuzadza muchikamu chechipiri chetafura.
8. Zadza nharo dzese mufafitera rakavhurwa zvinoenderana neiyo skrini pazasi.

9. Verenga mubhadharo wepamwedzi wega wega nekuwedzera kukosha mumakoramu maviri apfuura.

10. Kuti uverenge "Balance inobhadharwa", unoda kuwedzera mubereko wekubhadhara pamutumbi wechikwereti uye kutambanudza kusvika kumagumo eplate kuti uzadze mwedzi yose yechikwereti.

Mamwe Mashoko! Paunenge uchiverenga zvasara, zviratidzo zvemadhora zvinofanira kusungirirwa pane fomu kuitira kuti zvirege kubuda kana zvakatambanudzwa.

Kuverengera kwe annuity kubhadhara pane chikwereti muExcel

Basa re PMT rine basa rekuverenga iyo annuity muExcel. Nheyo yekuverenga kazhinji ndeyekuita nhanho dzinotevera:

1. Gadzira iyo yekutanga data tafura.
2. Gadzira chirongwa chekubhadhara chikwereti chemwedzi wega wega.
3. Sarudza sero rekutanga mukoramu "Payments pachikwereti" uye isa fomula yekuverenga “PLT ($B3/12;$B$4;$B$2)”.
4. Iko kukosha kunoguma kwakatambanudzwa kune ese makoramu eplate.

Kuverengera muMS Excel kubhadhara kweiyo mari yechikwereti

Kubhadhara kweAnnuity kunofanirwa kuitwa pamwedzi mumari yakatarwa. Uye mubereko hauchinji.

Kuverengerwa kwechiyero chemutengo mukuru (neBS=0, mhando=0)

Ngatitii chikwereti che100000 rubles chinotorwa kwemakore gumi pa10%. Izvo zvinodiwa kuverenga chiyero chechikwereti chikuru mumwedzi wekutanga wegore re9. Solution:

1. Unganidza dheti uye uverenge mubhadharo wepamwedzi uchishandisa iri pamusoro PV formula.
2. Verenga chikamu chekubhadhara kunodiwa kubhadhara chikamu chechikwereti uchishandisa fomula «=-PMT-(PS-PS1)* chinhu=-PMT-(PS +PMT+PS* chinhu)».
3. Verenga huwandu hwechikwereti chikuru kwenguva 120 uchishandisa formula inozivikanwa.
4. Uchishandisa HPMT opareta tsvaga huwandu hwemubereko hwakabhadharwa kumwedzi wechimakumi maviri neshanu.
5. Tarisa mhinduro.

Kuverengera huwandu hwemukuru hwakabhadharwa pakati penguva mbiri

Kuverenga uku kunoitwa zvakanakisa nenzira iri nyore. Iwe unofanirwa kushandisa anotevera mafomula kuti uverenge huwandu muchikamu chenguva mbiri:

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

Teerera! Mabhii ari muzvikomberedzo anotsiviwa nehunhu chaihwo.

Kurumidza kubhadhara nenguva yakaderedzwa kana kubhadhara

Kana iwe uchida kudzikisa nguva yechikwereti, uchafanirwa kuita mamwe maverengero uchishandisa IF opareta. Saka zvichave zvichibvira kudzora zero chiyero, iyo isingafaniri kusvika kusati kwapera nguva yekubhadhara.

Kuti uderedze kubhadhara, unofanirwa kuverenga zvakare mupiro wemwedzi wega wega wapfuura.

Chikwereti chekuverenga chine mubhadharo usingaenzaniswi

Kune akati wandei annuity sarudzo apo mukwereti anogona kuisa mari dzakasiyana pane chero zuva remwedzi. Mumamiriro ezvinhu akadaro, chiyero chechikwereti uye chido chinoverengerwa zuva rega rega. Panguva imwecheteyo muExcel iwe unoda:

1. Isa mazuva emwedzi unobhadharwa, uye ratidza nhamba yavo.
2. Tarisa kune zvakaipa uye zvakanaka mari. Negative ndiyo inodiwa.
3. Verenga mazuva pakati pemazuva maviri akaiswa mari.

Kuverengera kweiyo periodic kubhadhara muMS Excel. Term deposit

MuExcel, unogona kukurumidza kuverenga huwandu hwekubhadhara nguva dzose, chero mari yakatarwa yakatounganidzwa. Ichi chiito chinoitwa uchishandisa PMT basa mushure mekunge tafura yekutanga yagadzirwa.

mhedziso

Saka, kubhadhara kwe annuity kuri nyore, nekukurumidza uye kwakanyanya kushanda kuverengera muExcel. PMT opareta ndiye ane basa rekuverenga kwavo. Mimwe mienzaniso yakadzama inogona kuwanikwa pamusoro.