Compound Annual Growth Rate, CAGR, chiyero chako chekudzoka kwekudyara pane imwe nguva yakatarwa.
Kuverenga CAGR neruoko inzira inobatanidzwa, saka pazasi tichaenda pamusoro pekuti iwe unogona kukurumidza kuverenga CAGR muExcel.
CAGR Excel Formula
Iyo formula yekuverenga CAGR muExcel ndeye:
=(Kukosha Kwekupera/Kukosha Kwekutanga) ^ (1/Nhamba Yemakore) – 1
Iyo equation inoshandisa matatu akasiyana maitiro:
- Kupera kukosha, inova iyo mari yauchava nayo mushure mekunge nguva yadarika.
- Kutanga kukosha, inova mari yawakatanga nayo.
- Nhamba yemakore, inova nhamba yose yemakore akapfuura.
Pazasi isu tichaenda pamusoro pemuenzaniso wekuverenga CAGR yenguva yemakore mashanu muExcel uchishandisa iyo data data seti inoratidzwa pazasi:
1. Ziva nhamba dzauchazoshandisa mukuenzanisa kwako. Uchishandisa sampuli data yakaiswa pamusoro,
- Iko kukosha kwekupedzisira ndeye 2143 (musero B6).
- Iko kukosha kwekutanga i1000 (musero B2).
- Nhamba yemakore ndeye 5 (musero A6).
2. Isa tsika dzako mufomula.
Excel inopa akawanda mapfupi, saka iwe unogona kungoisa nhamba dzesero dzine yega yega kukosha kwako muequation. Uchishandisa sampuli yedata yakaiswa pamusoro, iyo equation ingave
=(B6/B2) ^ (1/A6) – 1
Izvi ndizvo zvazvinoita muExcel sheet yangu:
Ziva kuti equation inochinja ruvara kuti ienderane nemasero auri kushandisa, saka unogona kutarisa kumashure wotarisa kuti zvaunopinza ndizvo here usati waita equation.
Iwe unogona zvakare kuisa ma values chaiwo mune fomula pane maseru nhamba. Iyo equation yaizotaridzika seizvi:
=(2143/1000) ^ (1/5) – 1
3. Kana wangoisa kukosha kwako, tinya pinda uye mhanyisa equation. Mhedzisiro yako ichaonekwa musero rine equation, sezvakaratidzwa mumufananidzo uri pazasi.
CAGR Formula muExcel sePesenti
Mhedzisiro yako yakagara icharatidzwa sedesimali. Kuti uione sechikamu, tinya-kurudyi pasero rauri kuwana, sarudza Fomati Masero ndokuzo chikamu muzana mubhokisi renhaurirano.
Mhedzisiro yako ichashandurwa kuita chikamu, sezvakaratidzwa mumufananidzo uri pazasi.
Zvino ngatiendei pamusoro pekapfupi kekuverengera CAGR muExcel tichishandisa Rate basa.
Maitiro ekuverengera CAGR Uchishandisa RATE Basa
Basa reRATE rinokubatsira kuverenga mubereko wemari pakudyara kwechinguvana
=CHIYERO(nper,, pv, fv)
- nper ihwerengedzo yenhamba dzenhambo dzenguva yauri kuyera. Sezvo uri kuverenga gore negore kukura, izvi zvingava 12.
- pv iko kukosha kwazvino kwekudyara kwako (inofanira kugara ichimiririrwa seyakaipa)
- fv kukosha kweramangwana.
Ziva kuti iyo yakajairwa RATE equation inosanganisira akawanda akasiyana, asi iwe unongoda iwo matatu ari pamusoro kuti uverenge CAGR yako.
Ngatimhanyei equation tichishandisa tafura yemuenzaniso pazasi apo nperi iri gumi nembiri, pv i12, uye fv i100.
1. Mubepa rako, sarudza sero raunoda kuve neCAGR yako. Ndakasarudza cell B5.
2. Isa iyo RATE formula uye isa nhamba dzako. Ziva kuti iwe nguva dzose unoda kuratidza kukosha kwako kwazvino sechinhu chisina kunaka, kana kuti uchagamuchira mhosho.
Izvi ndizvo zvinoita fomula yangu.
=CHIYERO(A2,,-B2,C2)
3. Dzvanya pinda uye shandisa equation yako. Ndichishandisa data remuenzaniso, CAGR yangu ndeye 14%.
Iye zvino iwe unoziva kukurumidza uye nyore kuverenga CAGR yako muExcel, hapana maverengero emaoko anodiwa.