IRR - ukuthi kuyini nokuthi ukubala Excel?

Uhlelo Excel ekuqaleni umuntu wadalwa lula zokuhlala emikhakheni eminingi, kuhlanganise ibhizinisi. Ukusebenzisa izici zayo, ungakwazi ngokushesha ukwenza izibalo eziyinkimbinkimbi, kuhlanganise isibikezelo ukubuya kwala maphrojekthi. Ngokwesibonelo, i-Excel ikuvumela ukuba ngempela kalula ukubala IRR yephrojekthi. Kanjani ukuba sikwenze umkhuba, kulesi sihloko sizoxoxa ngako.

Uyini IRR

Lokhu isifinyezo selisho isilinganiso sangaphakathi senzuzo (IRR) ethize utshalomali phrojekthi. Le nkomba ngokuvamile sisetshenziswa ngenjongo Ngokuqhathanisa iziphakamiso nenzuzo kanye nokukhula amathemba. Ngamagama zezinombolo, IRR - kuyinto inani lenzalo lapho kabusha inani lamanje zonke kwemali imali ekusetjentisweni yephrojekthi utshalomali (NPV akhonjwe NPV). I-IRR lingaphezulu kwezithembiso esiningi iphrojekthi utshalomali.

indlela yokwazi

Ukuthola ukuthi IRR yephrojekthi, unganquma ukuthi uyisebenzise noma wenqabe. Ngokwesibonelo, uma uya ukuvula ibhizinisi elisha futhi kulindeleke ukuba sikhokhele ukubolekisa yayo ngenxa ethathwe ebhange, ke ukubala IRR ukucacisa ngcono mkhawulo we inani lenzalo. Uma inkampani isebenzisa ezingaphezu kweyodwa umthombo utshalomali, ukuqhathanisa IRR amagugu nge izindleko zabo kuyokwenzeka ukuba wenze isinqumo unolwazi ekutheni ukuqalisa iphrojekthi. Izindleko umthombo ngezimali olulodwa ebalwe okuthiwa ifomula izibalo kwesamba ukuthini. Lalibizwa ngokuthi "izindleko zemali yokuqhuba ibhizinisi" noma "Intengo yenhloko-dolobha eziphambili" (ezikhonjiswe SS).

Ukusebenzisa lesi sibalo, sinazo:

Uma:

• IRR> CC ke iphrojekthi kungenziwa umkhankaso;
• IRR = CC ke iphrojekthi ngeke abalethanga inzuzo noma ukulahlekelwa;
• IRR

Kanjani ukubala IRR ngesandla

Esikhathini eside ngaphambi kokuba Njengoba sekunama-computer GNI ibalwa ngokuxazulula ndaba kuyindlela kunalokho xaka (bheka. Ngezansi).

Kuhlanganisa amanani alandelayo:

• CFT - kwemali ngesikhathi t isikhawu;
• IC - ukutshalwa kwezimali ngesikhathi sesigaba kwethulwa yephrojekthi;
• N - Inombolo ephelele ngezikhawu.

Ngaphandle isofthiwe ekhethekile ukuze ukubala IRR yephrojekthi kungenzeka, usebenzisa indlela ye isilinganiselo ngokulandelana noma kuphindelelwa. Ngenxa yalesi isithiyo pre kufanele izinga ekhethiwe ukuze uthole amanani ubuncane NPV for module futhi enze isilinganiselo.

Isixazululo ngu ziwukulinganisela ngokulandelana

Okokuqala, kufanele ukuya imisebenzi ulimi. Kulesisimo-ke, i-IRR kuyothathwa kuqondwe isivuno value r lapho NPV, ngokuba umsebenzi r, iba zero.

Ngamanye amazwi, IRR = r yokuthi lapho ukufaka endaweni lapho ukhuluma NPV (f (r)) is zeroed.

Manje thina ukuxazulula umsebenzi washayelwa indlela ziwukulinganisela ngokulandelana.

Ngu kuphindelelwa elivame ukuqondwa ngayo njengoba umphumela isicelo okuphindiwe operation zezibalo. Inani umsebenzi kubalwa esinyathelweni odlule, ngesikhathi esilandelayo uma kuba agumenti efanayo.

I-IRR inkomba wenziwa 2 izigaba:

• ukubala IRR lamanani kakhulu ezithela evamile R1 R2 yokuthi R1
• ekubalweni lwezikhombo r amagugu eduze IRR ukubaluleka etholwe ngenxa ukubala odlule.

Ekuxazululeni izinkinga R1 R2 akhethiwe ngendlela yokuthi NPV = f (r) washintsha inani layo kusukela lokususa plus noma okuphambene nalokho ngaphakathi kobubanzi (R1, R2).

Ngakho sinesizathu ifomula kuhlaziywa inkulumo sibalo IRR kukhonjisiwe ngezansi.

Kusobala ukuthi ukuthola edingekayo amagugu IRR precomputed amanani ahlukene NPV% th izinga.

Phakathi izinkomba NPV, PI futhi SS kukhona ubuhlobo ezilandelayo:

• NPV uma inani omuhle, ke IRR> SS PI> 1;
• uma NPV = 0, bese IRR = MOP futhi PI = 1;
• uma ukubaluleka NPV alivumi, le IRR

indlela sokuqhafaza

Manje ngoba sewuyazi ukuthi lokho nokuthi ukubala IRR ngesandla, kuwufanele kakhulu sokuba bajwayelane enye indlela yokuxazulula le nkinga, okwakuyigama omunye ethandwa kakhulu ngaphambi kokuba kubuse amakhompyutha. Sikhuluma version ingcaca yencazelo IRR. Charting umzukuzuku ukuthola ukubaluleka NPV, esikhundleni ikufomula yayo ukubala amanani ahlukene lesaphulelo.

Kanjani ukubala IRR Excel

Njengoba ubona, ngesandla ukuthola GNI - kunzima. Lokhu kudinga ezinye ilwazi leembalo nesikhathi. lula kakhulu ukuba ufunde indlela ukubala IRR kuya Excel (bheka isibonelo. Ngezansi).

Ngenxa yale njongo, ku-spreadsheet isicelo Microsoft has a umsebenzi okhethekile esakhelwe ukubala yangaphakathi lesaphulelo - IRR, onikeza oyifunayo IRR Inani iphesenti imigomo.

-syntax

I-IRR (ukuthi iyini nokuthi ukubala kubalulekile ukwazi ochwepheshe nje kuphela kodwa futhi ababoleka abavamile) Excel eyabizwa ngokuthi IRR (Amanani; Guess).

Ake sicabangele ngokuningiliziwe syntax:

• Ngaphansi waqonda afanayo amagugu noma ireferensi amaseli eziqukethe inombolo okufanele ukubala VSD, sicabangisisa ngazo zonke izimfuneko ezishiwo kule ipharamitha.
• Nokucabanga liyinani okuyinto ukuthi iseduze IRR yi ezaziwayo.

Ngo-Microsoft Excel sokubalwa le IRR kusetshenziswa indlela ngenhla iterations. Yena uqala nge value of "kucatshangwa", futhi yenza izibalo eyindilinga ukuze kuvezwe umphumela othile nge ukunemba 0,00001%. Uma esakhelwe HDR akusho ukukhiqiza umphumela ngemva kwemizamo 20, ngaleso sikhathi i-spreadsheet ekhishwe iphutha value, azibiza ngokuthi "#-NUM!".

Njengoba umkhuba ubonisa, ezimweni eziningi asikho isidingo ucacise inani ubukhulu bale "kucatshangwa". Uma likhishiwe, wokugaya ubona ukuba 0.1 (10%).

Uma esakhelwe umsebenzi IRR ibuyisela iphutha "#-NUM!" Noma uma yi akuyona njengoba kulindeleke, kungenzeka ukukhiqiza ukubala futhi, kodwa nge-value ehlukile "kucatshangwa" lwempikiswano.

Izinqumo "Eksele": Inketho 1

Zama ukubala IRR (ukuthi iyini nokuthi ukubala kwaleli nani ngesandla usuwazi) by umsebenzi esakhelwe HDR. Ake sithi ube idatha iminyaka 9 kusengaphambili, angena i isipredishithi Excel.

	A	B	C	D	E
1	Inkathi (ngonyaka) T	nezindleko kokuqala	ukheshe engenayo	kwemali	kwemali
2	0	200 000 deda.	- k.	200000 p.	200000 p.
3	1	- k.	50000 p.	30000 ku p.	20000 ku p.
4	2	- k.	60000 p.	33000 p.	27000 p.
5	3	- k.	45000 p.	28000 p.	17000 p.
6	4	- k.	50000 p.	15000 ku p.	35000 p.
7	5	- k.	53000 p.	20000 ku p.	33000 p.
8	6	- k.	47000 p.	18000 ku p.	29000 p.
9	7	- k.	62000 p.	25000 p.	37000 p.
10	8	- k.	70000 p.	30 000 k.	40000 p.
11	9	- k.	64000 p.	33000 p.	31000 p.
12				IRR	6%

Iseli ngekheli E12 esethula ifomula "= VSD. (E3: E2)" Ngenxa yalokho kwethebula isicelo yayo processor okukhiphayo ukubaluleka 6%.

Izinqumo "Eksele": Inguqulo 2

Ngokusho basebentisa idatha leniketiwe kulesibonelo langaphambilini, ukubala IRR kusho "izixazululo" superstructure.

It ikuvumela ukucinga ukubaluleka eliphezulu IRR NPV = 0. Ukuze wenze lokhu, kudingeka ukubala NPV (noma NPV). Kuyinto ilingana nenani lezinombolo of the ehlisiwe kwemali unyaka nonyaka.

	A	B	C	D	E	F
1	Inkathi (ngonyaka) T	nezindleko kokuqala	ukheshe engenayo	kwemali	kwemali	Isaphulelo Cash Flow
2	0	200000 p.	- k.	200000 p.	200000 p.
3	1	- k.	50000 p.	20 000 k.	20000 ku p.	20000 ku p.
4	2	- k.	60000 p.	20000 ku p.	27000 p.	27000 p.
5	3	- k.	45000 p.	20000 ku p.	17000 p.	17000 p.
6	4	- k.	50000 p.	20000 ku p.	35000 p.	35000 p.
7	5	- k.	53000 p.	20000 ku p.	33000 p.	33 000 k.
8	6	- k.	47000 p.	20000 ku p.	29000 p.	29000 p.
9	7	- k.	62000 p.	20000 ku p.	37000 p.	37000 p.
10	8	- k.	70000 p.	20000 ku p.	40000 p.	40000 p.
11	9	- k.	64000 p.	20000 ku p.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

Kwehliswe kwemali ibalwa ifomula «= E5 / (1 + $ F $ 11) ^ A5».

Khona-ke, i-formula for NPV "= INANI (F5: F13) -B7».

Okulandelayo, udinga ukuthola ngesisekelo nokwenza esebenzisa izengezo "Solver" kuyinto ukubaluleka IRR lesaphulelo lapho NPV msebenzi ilingane no-zero. ufuna ukuvula "Idatha" kwesigaba kwimenyu enkulu ukuze sifinyelele lo mgomo futhi uthole umsebenzi "Sesha izixazululo."

Ngo ewindini elivelayo, ugcwalise umugqa "Setha target cell", ecacisa ikheli NPV ukubala ifomula, t. E. + $ F $ 16. ke:

• Inani ekhethelwe lolu amaseli "0";
• iwindi "cell izinguquko" ukwenza ipharamitha + $ F $ 17, t. e. ukubaluleka kwengosi kuba isilinganiso sangaphakathi senzuzo.

Ngenxa nokwenza i-spreadsheet igcwalisa eyeseli elingenalutho ngekheli F17 ukubaluleka rate isaphulelo. Njengoba ungase ubone etafuleni, umphumela ilingana 6%, okuyinto ezokwenzeka ekubalweni ipharamitha efanayo ezamukelwe kusetshenziswa esakhelwe ifomula Excel.

MIRR

Kwezinye izimo kubalulekile ukubala Ushintshe isilinganiso sangaphakathi senzuzo. It ibonisa IRR ubuncane yephrojekthi uma reinvestment. Ifomula kuhlaziywa MIRR kanje.

lapho:

• MIRR - isilinganiso sangaphakathi senzuzo ye-investimenti;
• COFt - outflow kwezimali phakathi nomsebenzi isikhathi t;
• CIFt - ukuthutheleka ezezimali;
• r - lesaphulelo, okuyinto ilingana kwesamba izindleko isilinganiso etshaliwe yi WACC;
• d -% Star izinga reinvested;
• n - nenani ngezikhathi.

Ukubalwa MIRR e processor yethebula

Bajwayelane izakhiwo IRR (ukuthi iyini nokuthi ukubala inani layo imidwebo usuwazi), ungakwazi kalula ufunde indlela ukubala Ushintshe isilinganiso sangaphakathi senzuzo kuya Excel.

Ukuze lolu hlelo isipredishithi, a ekhethekile esakhelwe umsebenzi MIRR. Thatha wonke efanayo, kakade kubhekwe isibonelo. Kanjani ukubala IRR kuso, sekuvele kubhekwe. MIRR etafuleni kanje.

	A	B	C	D	E
1	isamba semalimboleko njengoba amaphesenti		10%
2	ezingeni reinvestment		12%
3	Inkathi (ngonyaka) T	nezindleko kokuqala	ukheshe engenayo	kwemali	kwemali
4	0	200000 p.	- k.	200000 p.	200000 p.
5	1	- k.	50000 p.	30000 ku p.	20000 ku p.
6	2	- k.	60000 p.	33000 p.	27000 p.
7	3	- k.	45000 p.	28000 p.	17000 p.
8	4	- k.	50000 p.	15000 ku p.	35000 p.
9	5	- k.	53000 p.	20000 ku p.	33000 p.
10	6	- k.	47000 p.	18000 ku p.	29000 p.
11	7	- k.	62000 p.	25000 p.	37000 p.
12	8	- k.	70000 p.	30000 ku p.	40000 p.
13	9	- k.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

Iseli yethulwa E14 formula for MIRR «= MIRR (E3: E13; C1; C2)».

Emihle nemibi usebenzisa inkomba isilinganiso sangaphakathi senzuzo

Indlela kulinganiswa amathemba amaphrojekthi by kuhlaziywa IRR futhi uma kuqhathaniswa ukubaluleka izindleko zemali yokuqhuba ibhizinisi aluphelele. Nokho, lokho kuyobe kube nezinzuzo zako. Lezi zihlanganisa:

• Ikhono uqhathanise utshalomali ehlukahlukene ngokuya elikhangayo yabo futhi ekusetshenzisweni etshaliwe. Ngokwesibonelo, kungenzeka ukuba afeze ziqhathaniswa isivuno esimweni amafa engozini-free.
• Ikhono uqhathanise amaphrojekthi ezahlukene zokutshala izimali ezahlukene utshalomali umkhathizwe.

Ngesikhathi esifanayo ubuthakathaka obusobala ngaloluhla inkomba. Lezi zihlanganisa:

• ukwehluleka Inkomba izinga sangaphakathi senzuzo ukubonakalisa inani reinvestment kuphrojekthi;
• nobunzima ekubikezeleni uyikhokhe ngokheshe, njengoba value abo kuthintwa izici eziningi ingozi, ukuhlolwa Inhloso okuyinto nzima;
• ukwehluleka ingabonisi inani eliphelele imali (cash ukuqhutshwa) kwi nokubaluleka kokutshalwa.

Nakani! ethile Kugcine yaxazululwa ngokugcina inkomba MIRR, okuyinto kwachazwa ngokuningiliziwe ngenhla.

Njengoba ikhono ukubala IRR kungabasiza ababoleka

Ngokusho nezidingo Russian Central Bank, kwalo lonke usebe ezisebenza endaweni WaseRussia, kufanele lukhombise Isilinganiso senzalo esisebenzayo (ESR). Kungaba ngokuzimela ukubala yimuphi umboleki. Ukwenza lokhu, kufanele ukwazi ukusebenzisa i-spreadsheet, isibonelo, i-Microsoft Excel, bese ukhetha esakhelwe HDR. Ngenxa yalesi Imiphumela kuseli efanayo ukuba siphindwe lokukhokha isikhathi T (uma nyangazonke ke T = 12 uma Fluorescent ke T = 365) ngaphandle ukubopha.

Manje, uma ukwazi lokho isilinganiso sangaphakathi senzuzo, ngakho-ke uma uthi, "Ukuze ngamunye lemisebenzi elandelayo, ukubala IRR», ngeke babe kobunzima.