IRR - he aha te mea reira me pehea ki te tātai i roto i Excel?

I hanga tuatahi hōtaka Excel ki te whakahaere whakataunga i roto i mara maha, tae atu pakihi. Mā te ona āhuatanga, ka taea e hohoro koe te mahi tātai uaua, tae atu i te matapae o te hokinga mai o enei kaupapa. Hei tauira, ka taea Excel ki a koe te tātai rawa ngāwari te IRR o te kaupapa. Me pēhea te ki te mahi i te reira i roto i te mahi, ka korero i tenei tuhinga.

He aha te mea te IRR

Tenei whakarāpopototanga rawea, auau ā-o hoki (IRR) kaupapa haumi motuhake. Kei te maha tenei tohu e whakamahia ana mo te whakaaro o te whakatairite tono mō te moni me te tupu opuaraa. I roto i ngā tau, IRR - ko te auau huamoni i ai tautuhi anō te uara hakari o kapewhiti katoa e hiahiatia ana mo te whakatinanatanga o te kaupapa haumi (NPV tohua NPV ranei). Ko teitei te whaihua ake ko te kaupapa haumi te IRR.

pehea ki te aromātai i

Te rapu i te IRR o te kaupapa, ka taea e koe te whakatau ki te rere i te reira ranei ki te kore i te reira. Hei tauira, ki te e haere koe ki te whakatuwhera i tētahi pakihi hou, me te te tūmanakohia reira ki te pūtea ona loans tika tangohia i te pēke, ka te tātai IRR ki te whakatau i te rohe o runga Erangi o te auau huamoni. Ki te whakamahia e te kamupene neke atu i te kotahi puna o haumi, ka meinga he whakarite o uara IRR ki to ratou utu reira taea ki te hanga i te whakatau ngā i runga ranei ki te whakarewa i te kaupapa. Ko te utu o te puna pūtea kotahi tātai e te pera-ka karanga tātai tauhanga tautaha tikanga. I huaina te reira "utu o te whakapaipai" ranei "te utu o te whakapaipai matatau" (denoted SS).

Mā te tenei ahua, kua tatou:

Ki te:

• IRR> CC, ka taea te whakarewa te kaupapa;
• IRR = CC, na e kore e kawea ki te kaupapa tetahi pai ranei mate;
• IRR

Me pēhea te ki te tātai ā te IRR

Roa i mua i te taeraa mai o rorohiko tatau GNI e whakaoti te whārite, ko te pai uaua (kite. Raro).

Kei roto te reira i te uara e whai ake nei:

• CFt - rere moni i roto i te t te wā wā;
• IC - haumi i roto i te wā whakarewatanga o te kaupapa;
• N - tau katoa o wā.

Te kore pūmanawa motuhake ki te tātai he taea te IRR o te kaupapa, te whakamahi i te tikanga o te āwhiwhitanga ngā whitiauau ranei. Hoki tenei tauārai mua kia whiriwhiria auau kia rite ki te kitea uara iti o NPV mo te kōwae, me te mahi i āwhiwhitanga.

Ko te otinga e whakatau tata whai

Tuatahi o te katoa, whai koe ki te haere ki ngā mahi reo. I roto i tenei horopaki, ka kia mohio ki te IRR tukua he r uara i ai NPV, he mahi o te r, riro te kore.

I roto i te mau parau te tahi atu, IRR = r taua e ka whakakapia ki te faaiteraa NPV (f (r)) taui te.

Na whakaoti tatou te mahi whakatakoto i te tikanga o ngā whakatau tata whai.

Na roto i te whitiauau kei te matau rite te hua o te tono toutou o te mahi pāngarau. Ko te uara o te mahi tātai i roto i te taahiraa o mua, te wa i muri riro te reira i te tautohe taua.

kawea te IRR tohu i roto i te 2 ngā wāhanga:

• tātaitanga IRR o nga uara tino o loto noa r1 me r2 pērā e r1
• tātaitanga o te taupū i ngā uara r tata ki te uara IRR whiwhi rite ki te hua o te tātai o mua.

I roto i whakaoti i te raruraru e tīpakohia r1 me r2 i roto i te ara taua e te NPV = f (r) ke tona uara i tango ki me hurihia ranei i roto i te awhe (r1, r2).

Ko te kupu to tatou he tātai mō te tātai i te faaiteraa ahua IRR whakaaturia ana i raro.

whai te reira i taua ki te whiwhi e hiahiatia uara IRR precomputed mō ngā uara rerekē o te NPV% th auau.

I waenganui i tohu o NPV, PI me SS reira ko te hononga e whai ake nei:

• NPV ki te ko te uara pai, ka te IRR> SS me PI> 1;
• ki te NPV = 0, na te IRR = ūkui me te PI = 1;
• ki te he kino te uara o NPV, te IRR

tikanga kauwhata

Na e matau ana koutou he aha te mea te reira, ka pehea ki te tātai i te IRR i ringa, he mea utu ki te kia maheni ki tetahi atu tikanga o whakaoti tenei raruraru, i ko tetahi o te rongonui tino i mua i reira nga rorohiko. Kei te korero tatou e pā ana ki te putanga whakairoiro o te whakamāramatanga o te IRR. Vanuatu, e hiahiatia ana ki te kitea te uara o NPV, kua hoatu i roto i tona tātai mō te tātai i te uara rerekē o te auau hekenga.

Me pēhea te ki te tātai i te IRR i roto i Excel

Ka rite ki a koutou e taea e kite, kitea ā te GNI - he uaua. Me whiwhi tēnei etahi matauranga pāngarau, me te wā. He nui māmā ki te ako ki te tātai i te IRR ki Excel (kite i te tauira. Raro).

Hoki tenei whakaaro, i roto i te tono ripanga Microsoft kua he mahi hanga-i roto i te motuhake ki te tātai i te tere hekenga ā-- IRR, e homai i te uara IRR hiahiatia i roto i ngā ōrau.

wetereo

IRR (he aha te mea reira me pehea ki te tātai i te mea e tika ana ki te mohio ngaio kore anake, engari ano kaitono mau) i roto i Excel whakaritea rite IRR (Uara: Guess).

Kia whai whakaaro ano tatou i roto i ngā kōrero i te wetereo:

• I raro i ngā uara ngohi i matau te tohutoro ki ngā pūtau kei roto i te tau mo e ki te tātai i te VSD, whakaaro nga whakaritenga katoa i tohua mo tenei tawhā ranei.
• Ko te whakaaro ko te uara i te mohiotia e he reira tata ki te hua IRR.

I roto i te Microsoft Excel mo te tātaitanga o te IRR te whakamahi i te tikanga i runga ake o tāruarua. Tīmata ia ki te uara o "whakaaro", a ka mahi i te tātai huri taiāwhio ki te whakaputa i te hua ki te tika o 0,00001%. Ki te te hanga-i roto i te kore e HDR hua he hua i muri i te nganatanga 20, na te hapa uara ripanga tukuna, whakaritea rite "# NUM!".

Ka rite ki whakaaturanga mahi, i roto i te nuinga o te wā i reira he kore hiahia ki te tautuhi i te uara mo te nui o te "whakaaro". Ki te mahue ai, whakaaro te pūtukatuka ki reira kia 0.1 (10%).

Ki te hoki te IRR mahi hanga-i roto i te hapa "# NUM!" Ranei ki te kore te te hua rite tūmanakohia, he reira taea ki te kawe ano i te tātai, engari ki te uara rerekē mo te "whakaaro" o te tautohe.

Whakatau "Eksele": Kōwhiringa 1

Whakamātauria ki te tātai i te IRR (he aha te mea reira me pehea ki te tātai i tenei uara ā koutou kua mohio) i te mahi hanga-i roto i te HDR. Pea to tatou raraunga mō te 9 tau i roto i te mua, e kua tomo ki roto ki te ripakaute Excel.

	He	B	C	D	E
1	Wā (tau) T	utu tuatahi	moni moni	rere moni	rere moni
2	0	200 000 te horoitanga i.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	30000 runga p.	20000 runga p.
4	2	- p.	60000 p.	33000 p.	27000 p.
5	3	- p.	45000 p.	28000 p.	17000 p.
6	4	- p.	50000 p.	15000 runga p.	35000 p.
7	5	- p.	53000 p.	20000 runga p.	33000 p.
8	6	- p.	47000 p.	18000 runga p.	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%

Ko te pūtau ki wāhitau E12 whakaurua te tātai "= VSD. (E3: E2)" Ka rite ki te hua o tona pūtukatuka tepu tono ngä te uara o te 6%.

Whakatau "Eksele": Putanga 2

E ai ki te raraunga i homai i roto i te tauira o mua, ki te tātai i te IRR tikanga "rapu mō rongoā" superstructure.

āhei te reira ki a koutou te rapu mo te uara tino pai mō te te IRR NPV = 0. Ki te mahi i tenei, e hiahia ana koe ki te tātai i te NPV (NPV ranei). Ko reira rite ki te moni o te rere moni utu i tau ki te tau.

	He	B	C	D	E	F
1	Wā (tau) T	utu tuatahi	moni moni	rere moni	rere moni	Discount Kapewhiti
2	0	200000 p.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	20 000 p.	20000 runga p.	20000 runga p.
4	2	- p.	60000 p.	20000 runga p.	27000 p.	27000 p.
5	3	- p.	45000 p.	20000 runga p.	17000 p.	17000 p.
6	4	- p.	50000 p.	20000 runga p.	35000 p.	35000 p.
7	5	- p.	53000 p.	20000 runga p.	33000 p.	33 000 p.
8	6	- p.	47000 p.	20000 runga p.	29000 p.	29000 p.
9	7	- p.	62000 p.	20000 runga p.	37000 p.	37000 p.
10	8	- p.	70000 p.	20000 runga p.	40000 p.	40000 p.
11	9	- p.	64000 p.	20000 runga p.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

tātai rere moni utu e te tātai «= E5 / (1 + $ F $ 11) ^ A5».

Na, ko te tātai hoki NPV "= SUM (F5: K13) -B7».

Next, Me koe ki te kitea i runga i te pūtake o te arotautanga i te tikanga o te tāpiri-ake "Solver" Ko te uara o te auau hekenga IRR i ai te NPV o te kaupapa, ko te rite ki te kore. e hiahia ana koe ki te whakatuwhera i te wāhanga "Raraunga" i roto i te tahua matua i roto i te tikanga ki te tutuki ai tēnei whāinga, me te kitea i te mahi "Rapu mō rongoā."

I roto i te matapihi e puta, whakakiia i roto i te rārangi "pūtau Tautuhi ūnga", te tauwhāiti NPV wāhitau tātaitanga tātai, t. E. + $ F $ 16. na:

• uara tīpakohia hoki tenei "0" pūtau;
• te matapihi "huringa pūtau" hanga i te tawhā + $ F $ 17, t. e. te uara o te auau ā-o hoki.

Ka rite ki te hua o te arotautanga ripakaute whakakiia te pūtau kau ki wāhitau te uara F17 o te auau hekenga. Ka rite ki kite i te tepu, he rite ki te 6%, e hāngai ana ki te tātaitanga o te tawhā taua riro te whakamahi i te tātai Excel hanga-i roto i te hua.

MIRR

I roto i ētahi wā he mea e tika ana ki te tātai i te tere ā-whakarerekētia o hoki. whakaata te reira i te IRR iti o te kaupapa i roto i te take o te kapo. Ko te tātai mō te tātai i MIRR e whai ake.

te wahi:

• MIRR - tere ā-o hoki o te haumi;
• COFt - matamua o moni i roto i te t kaupapa wā wa;
• CIFt - te tomonga o te pūtea;
• r - tere hekenga, i te mea e rite ana ki te tautaha utu toharite o te whakapaipai haumi e te WACC;
• d -% auau Star huamoni;
• n - maha o ngā wā wā.

MIRR Tātaitanga i roto i pūtukatuka ripa

Maheni ki te āhuatanga IRR (he aha te mea reira me pehea ki te tātai i tona uara kauwhata kua matau koutou), koe e taea e ngāwari te ako e founga ki te tātai i te tere ā-whakarerekētia o hoki ki Excel.

Hoki tenei tono ripanga, he MIRR mahi motuhake hanga-i roto i. Tangohia te taua, kua whakaaro te tauira katoa. Me pēhea te ki te tātai i te IRR runga reira, kua kua reira i whakaaro. MIRR ki te tepu e whai ake.

	He	B	C	D	E
1	nui taurewa rite te ōrau		10%
2	taumata o te kapo		12%
3	Wā (tau) T	utu tuatahi	moni moni	rere moni	rere moni
4	0	200000 p.	- p.	200000 p.	200000 p.
5	1	- p.	50000 p.	30000 runga p.	20000 runga p.
6	2	- p.	60000 p.	33000 p.	27000 p.
7	3	- p.	45000 p.	28000 p.	17000 p.
8	4	- p.	50000 p.	15000 runga p.	35000 p.
9	5	- p.	53000 p.	20000 runga p.	33000 p.
10	6	- p.	47000 p.	18000 runga p.	29000 p.
11	7	- p.	62000 p.	25000 p.	37000 p.
12	8	- p.	70000 p.	30000 runga p.	40000 p.
13	9	- p.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

whakaurua te pūtau te E14 tātai mō MIRR «= MIRR (E3: E13: C1: C2)».

Huapai me ngā huakore o te whakamahi i te tohu o te auau ā-o hoki

E kore te mea tino tika te tikanga o ine i te opuaraa o kaupapa mā te tātai i te IRR me whakaritea ki te uara o te utu o te whakapaipai. Heoi, e whai reira etahi painga. Ko ētahi o ēnei:

• Ko te kaha ki te whakarite haumi ngā i roto i ngā o ratou painga, me te whakamahi pai o te whakapaipai haumi. Hei tauira, he reira taea ki te kawe i roto i te whakarite ki te tukua i roto i te take o ngā rawa mōrearea-free.
• Ko te kaha ki te whakarite kaupapa haumi rerekē ki rerekē pae haumi.

I te taua wa paruparu kitea o tenei tohu. Ko ētahi o ēnei:

• kore ki te taupū te auau ā-o hoki ki te whakaata i te nui o te kapo i te kaupapa;
• te uaua o te forecasting utu moni, rite pāngia ratou uara e maha ngā āhuatanga mōrearea, te aromatawai whāinga i te mea uaua atu;
• kore ki te whakaata i te nui tino o te moni (puta moni) i runga i te uara o haumi.

Kia whai whakaaro! I whakatau drawback whakamutunga i te haapa'oraa i te tohu MIRR, i faataa i roto i te taipitopito i runga.

Ka rite ki te kaha ki te tātai i te IRR taea e whai hua ki ngā kaitono e

E ai ki nga whakaritenga o te Russian Central Bank, pareparenga katoa mahi i roto i te rohe o te Russian Federation, ka tohu i te auau huamoni whai hua (ESR). Ka taea e takitahi tātai te reira i tetahi nama. Ki te mahi i tenei ka whai ia ki te whakamahi i ripanga, tauira, Microsoft Excel, ka tīpako i te hanga-i roto i te HDR. Hoki ki te kia whakanuia e te utu wā T tenei hua i roto i te pūtau taua (ki te marama, na T = 12 ki te kōrekoreko, na T = 365) kahore whakaawhiwhi.

Na, ki te mohio koutou ki ta te tere ā-o hoki, na, ki te mea a koutou, "hoki ia o nga kaupapa e whai ake nei, tātai te IRR», e kore e whai koe i tetahi uaua.