IRR - chii uye sei kuverenga muna Excel?

Excel chirongwa pakutanga akasikwa kuti zviite kugarwa muminda zhinji, kusanganisira basa. Achishandisa mamiriro ayo, unogona kukurumidza kuita Masvomhu yakaoma, kusanganisira achifanotaura yokudzoka muzvirongwa izvi. Somuenzaniso, Excel anobvumira kuti ngaaverenge chaizvo zviri nyore IRR nechirongwa. How kuzviita tsika, munyaya ino ichataura.

Chii IRR

chidimbu ichi kureva nhamba dzakawanda chokudzoka (IRR) dzakananga inivhesitimendi chirongwa. chakarurama ichi rinowanzoshandiswa chinangwa kusiyana vaitaura kuti profitability uye kukura tarisiro. In kusanganiswa nemashoko, IRR - ndiye chimbadzo panguva iyo itangezve ino kukosha zvose mari Rwako zvinodiwa Implementation pamusoro mubhizimisi chirongwa (akasarudzwa NPV kana NPV). The IRR riri kumusoro zvikuru vaizotibata ndiye inivhesitimendi chirongwa.

sei kuongorora

Kuwana kunze IRR chirongwa, unogona kusarudza kumhanya kana kuti kukuramba. Somuenzaniso, kana uri kuenda kuvhura bhizimisi idzva uye anotarisirwa mari yakafanira zvikwereti zvayo akatorwa kubhangi, ipapo IRR kukarukureta yokuziva zvinotonzi yokumusoro panogumira chimbadzo. Kana boka anoshandisa zvinopfuura chimwe kunobva mari, nokuenzanisa IRR tsika pamwe mutengo yavo kuti tikwanise kuita chisarudzo musi kana kuti nokuparura basa. Mari rimwe mari panobva dzaona ne anonzi payakavakirwa weighted svomhu zvinorevei. Rainzi "mutengo guta" kana "The mutengo yepamusoro guru" (runotaurwa SS).

Achishandisa mufananidzo uyu, tine:

kana:

• IRR> CC, zvino basa racho inogona kwakatangwa;
• IRR = CC, ipapo chirongwa haazogumisi chero batsiro kana kurasikirwa;
• IRR

How kuverenga IRR manually

Long veMubatanidzwa makombiyuta GNI rinowanikwa nevanhu nekugadzirisa mukana wokuti vanhu vanga pamberi nani kunzwisisa (ona. Below).

Chinosanganisira anotevera tsika:

• CFt - mari aibuda panguva penguva t;
• IC - Investments panguva kanda chokutambudzika chirongwa;
• N - uwandu zvikamu.

Pasina-software chaiwo ngaaverenge IRR chirongwa zvinokwanisika, achishandisa nzira anotevedzana rakada kana iteration. Nokuti ichi pre chipingamupinyi zvinofanira kusarudzwa mwero kuitira kuwana shoma tsika NPV nokuda module uye kuita rakada.

The Kugadzirisa ne zvinotevedzana approximations

Chokutanga, unofanira kuenda mutauro mabasa. Mundima iyi, iyo IRR kuchaita kunzwisiswa goho kukosha R panguva iyo NPV, ari kushanda R, anova razero.

Nemamwe mashoko, IRR = R zvokuti kana kuchiiswa mu okuti NPV (f (R)) iri zeroed.

Zvino isu kugadzirisa akagadzira basa iri nzira zvinotevedzana approximations.

By iteration unonyanyozivikanwa kunzwisiswa sezvo mugumisiro akadzokorora kushanda kunoita kwemasvomhu kuvhiyiwa. Kukosha mashandiro dzaona muzvirevo danho, inotevera nguva zvinova zvakafanana nharo.

The chakarurama IRR rinoitwa muna 2 mumatanho:

• IRR kukarukureta of yokunyanyisa netsika kwomuzvarirwo Goho r1 uye r2 zvokuti r1
• kukarukureta of indekisi panguva R tsika pedyo IRR ukoshi vakawana somugumisiro yapfuura kukarukureta.

In kugadzirisa dambudziko r1 uye r2 vanosarudzwa nenzira yakadaro kuti NPV = d (R) akachinja mutengo wayo kubva kubvisa kuti ndinoiitawo kana vakutarisire mukati kuwanda (r1, r2).

Saka tine nzira kuti pakuverenga dimikira IRR mashoko pasi apa.

It anotevera kuti kuwana zvaida tsika IRR precomputed zvakasiyana tsika NPV% fung zvazvingava.

Pakati zviratidzi pamusoro NPV, PI uye SS pane zvinotevera ukama:

• NPV kana mutengo iri zvakanaka, zvino IRR> SS uye PI> 1;
• kana NPV = 0, ipapo IRR = MOP uye PI = 1;
• kana kukosha NPV ndiye akaipa, kuti IRR

graphical nzira

Zvino kuti munoziva chii uye sei kuverenga IRR noruoko, zvinokosha tinyatsoziva imwe nzira kugadzirisa dambudziko iri, iro raiva rimwe zvikuru nevanhu vasati paiva makombiyuta. Tiri kutaura pamusoro kwakajeka shanduro tsanangudzo IRR. Charting kunodiwa kuwana kukosha NPV, dzinomiririra ayo payakavakirwa nokuti pakuverenga dzakasiyana netsika dhisikaundi unodiwa.

How kuverenga IRR muna Excel

Sezvaunogona kuona, manually kuwana GNI - chakaoma. Izvi zvinoda vamwe ruzivo akarongeka uye nguva. Nyore kudzidza kuverenga IRR kuna Excel (ona muenzaniso. Below).

Nokuti chinangwa ichi, ari Microsoft spreadsheet kushanda ane yakavakwa muna basa kuverenga womukati dhisikaundi mwero - IRR, aunopiwa zvaaida IRR kukosha kubva muzana mazwi.

nemarongerwo

IRR (chii uye sei kuverenga zvakafanira kuziva kwete chete nenyanzvi asiwo vanhuwo anokwereta) muna Excel akasarudzwa sezvo IRR (Values; Unozivei).

Ngationgororei muudzame nemarongerwo:

• Under kunzwisisa kuko tsika kana Munongedzo masero ane nhamba iro kuverenga VSD, kufunga zvose zvinodiwa wainyatsotaura kuti parameter ichi.
• The kubvuma ane ukoshi rinozikamwa kuti zviri pedyo IRR mugumisiro.

In Microsoft Excel kuti kuverenga pamusoro IRR achishandisa nzira pamusoro iterations. Iye rinotanga kukosha "kubvuma", uye anoita cyclic Masvomhu kubudisa mugumisiro ane kururama 0,00001%. Kana pakuvara HDR haaiti mugumisiro mushure 20 kuedza, ipapo spreadsheet rakapa ukoshi kukanganisa, akasarudzwa se "# NUM!".

Sezvo tsika inoratidza kazhinji hapana chikonzero tsanangurai kukosha kuti kukura kwe "mafungiro". Kana Haatauri kuti processor anofunga kuti 0.1 (10%).

Kana pakuvara basa IRR anodzoka kukanganisa "# NUM!" Kana mugumisiro isina Sezvaitarisirwa, zvinokwanisika kuita Masvomhu zvakare, asi ane zvakasiyana ukoshi nokuti "kubvuma" renharo.

Zvisarudzo "Eksele": Option 1

Edza kuverenga IRR (chii uye sei kuverenga kukosha ichi manually aunotoziva) kuburikidza pakuvara HDR ushande. Ngatitii tine mashoko 9 pachine makore, ayo akapinda Excel spreadsheet.

	A	B	C	D	E
1	Period (gore) T	kutanga mari	mari mari	mari kuyerera	mari kuyerera
2	0	200 000 kuzora.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	30000 pap.	20000 pap.
4	2	- p.	60000 p.	33000 p.	27000 p.
5	3	- p.	45000 p.	28000 p.	17000 p.
6	4	- p.	50000 p.	15000 pap.	35000 p.
7	5	- p.	53000 p.	20000 pap.	33000 p.
8	6	- p.	47000 p.	18000 pap.	29000 p.
9	7	- p.	62000 p.	25000 p.	37000 p.
10	8	- p.	70000 p.	30 000 p.	40000 p.
11	9	- p.	64000 p.	33000 p.	31000 p.
12				IRR	6%

The muchitokisi kero E12 akatanga payakavakirwa "= VSD. (E3: E2)" Somugumisiro kwayo chikumbiro tafura processor outputs kukosha 6%.

Zvisarudzo "Eksele": Version 2

Maererano nemashoko apa yapfuura muenzaniso, kuverenga IRR rinoreva "tsvakai zvinogadzirisa" superstructure.

Kunoitawo kuti kutsvaka Zvakanyanya Kunaka Panguva Yepamuviri kukosha nokuti IRR NPV = 0. Kuti aite izvi, unofanira kuverenga NPV (kana NPV). It akaenzana Verengai discounted mari kuyerera kubva gore negore.

	A	B	C	D	E	F
1	Period (gore) T	kutanga mari	mari mari	mari kuyerera	mari kuyerera	Discount Cash Kuyerera
2	0	200000 p.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	20 000 p.	20000 pap.	20000 pap.
4	2	- p.	60000 p.	20000 pap.	27000 p.	27000 p.
5	3	- p.	45000 p.	20000 pap.	17000 p.	17000 p.
6	4	- p.	50000 p.	20000 pap.	35000 p.	35000 p.
7	5	- p.	53000 p.	20000 pap.	33000 p.	33 000 p.
8	6	- p.	47000 p.	20000 pap.	29000 p.	29000 p.
9	7	- p.	62000 p.	20000 pap.	37000 p.	37000 p.
10	8	- p.	70000 p.	20000 pap.	40000 p.	40000 p.
11	9	- p.	64000 p.	20000 pap.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

Discounted mari kuyerera rinowanikwa ne nzira «= E5 / (1 + $ F $ 11) ^ A5».

Zvadaro, nzira nokuda NPV "= Verengai (F5: F13) -B7».

Next, unofanira kuwana pahwaro Optimization achishandisa wedzera-Ons "Solver" Kukosha IRR dhisikaundi mwero uko NPV chirongwa akaenzana razero. uchida kuvhura chikamu chikuru Muterere iri "Data" kuti vawane chinangwa ichi uye kuwana basa "Tsvakai zvingaitwa."

Pahwindo kuti rinowanika, zadza mumutsara "Set chipfuro sero", kunyatsovatsanangurira kero NPV kukarukureta wevacheche, t. E. + $ F $ 16. zvino:

• yasarudzwa chaiko "0" masero ichi;
• hwindo "sero kuchinja" kuti parameter + $ F $ 17, t. e. kukosha nhamba dzakawanda yokudzoka.

Somugumisiro Optimization spreadsheet anozadza chinhu muchitokisi kero F17 kukosha dhisikaundi unodiwa. Sezvinoonekwa patafura, zvinoguma akaenzana 6%, rinobatana dzemaitirwe mumwechete parameter rakagamuchirwa kushandisa pakuvara fomura Excel.

MIRR

Mune zvimwe zviitiko zvinokosha kuverenga dzakachinjwa mwero dzakawanda chokudzoka. Zvinoratidza shoma IRR chirongwa kana reinvestment. The yemaitiro nokuti pakuverenga MIRR sezvinotevera.

apo:

• MIRR - womukati mwero kudzoka inivhesitimendi;
• COFt - water drainage mari panguva chirongwa nguva t;
• CIFt - mutsika mari;
• R - dhisikaundi zvazvingava, izvo akaenzana weighted paavhareji mutengo guta paakapiwa pedyo WACC;
• D -% Star zvazvingava reinvested;
• N - Nhamba nguva.

Kukarukureta MIRR mu tabular processor

Kunyatsoziva IRR ehupfumi (chii uye sei kuverenga kukosha kwayo yakanyatsotsanangura aunotoziva), unogona kudzidza nyore sei kuverenga dzakachinjwa mwero dzakawanda yokudzokera Excel.

Nokuti spreadsheet chikumbiro ichi, chaiwo yakavakwa muna shanda MIRR. Tora zvose zvakafanana, kare muenzaniso. How kuverenga IRR pamusoro payo, akatotanga zvakurukurwa. MIRR netafura sezvinotevera.

	A	B	C	D	E
1	chikwereti chitsama ave chikamu		10%
2	Hazvizivikanwi reinvestment		12%
3	Period (gore) T	kutanga mari	mari mari	mari kuyerera	mari kuyerera
4	0	200000 p.	- p.	200000 p.	200000 p.
5	1	- p.	50000 p.	30000 pap.	20000 pap.
6	2	- p.	60000 p.	33000 p.	27000 p.
7	3	- p.	45000 p.	28000 p.	17000 p.
8	4	- p.	50000 p.	15000 pap.	35000 p.
9	5	- p.	53000 p.	20000 pap.	33000 p.
10	6	- p.	47000 p.	18000 pap.	29000 p.
11	7	- p.	62000 p.	25000 p.	37000 p.
12	8	- p.	70000 p.	30000 pap.	40000 p.
13	9	- p.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

Sero kutaurwa E14 payakavakirwa nokuti MIRR «= MIRR (E3: E13; C1; C2)».

Zvakanakira nekuipira kushandisa chinoratidza mwero zvemukati chokudzoka

The nzira kupima tarisiro mabasa kupfurikidza pakuverenga ari IRR uye kuenzaniswa kukosha mutengo guru hakusi vakakwana. Zvisinei, izvozvo hazvirevi abatsirike. Idzi dzinosanganisira:

• Kugona kuenzanisa mari siyana panyaya Kukwezva dzavo uye kushandisa rine unyanzvi guta paakapiwa. Somuenzaniso, zvinokwanisika kuti aite rimwe tichienzanisa zvibereko panyaya ngozi-vakasununguka zvinhu.
• Kugona enzanisa zvakasiyana mubhizimisi muzvirongwa nedzimwe inivhesitimendi jengachenga.

Panguva iyoyowo pachena kusakwana chakarurama ichi. Idzi dzinosanganisira:

• kukundikana indekisi nhamba dzakawanda yokudzoka kuratidza huwandu reinvestment muna chirongwa;
• kuoma kufungidzira mari kubhadhara, sezvo mutengo yavo sei vakawanda zvinokonzera njodzi, chinangwa kupima riri kuoma;
• kukundikana kuratidza paMhedziso huwandu mari (mari mberi) kukosha upfumi.

Teererai! Last drawback kwakagadziriswa nekuchengeta chinoratidza MIRR, iyo akatsanangura zvizere kumusoro.

Sokukwanisa ngaaverenge IRR zvinogona kubatsira anokwereta

Maererano zvinodiwa Russian Central Bank, mabhanga kushanda munharaunda yeRussian Federation, anofanira kuratidza chinobudirira chimbadzo (ESR). Zvinogona vakazvimiririra ngaaverenge chero anokwereta. Kuti aite izvi achava kushandisa spreadsheet, muenzaniso, Microsoft Excel, uye sarudza pakuvara HDR. Nokuti ichi kunoguma muchitokisi chete kuti kane nguva T muripo (kana pamwedzi, zvino T = 12 kana fluorescent, ipapo T = 365) pasina rounding.

Zvino, kana imi muchiziva chii nhamba dzakawanda yokudzoka, saka kana iwe ukati, "Nokuti mumwe nomumwe zvinotevera mapurojekiti, ngaaverenge IRR», hamuna chinhu dambudziko.