IRR - ndi chiyani ndi momwe mungawerengere mu Excel?

pulogalamu kupambana pachiyambi analengedwa kuti magawowa m'dera zosiyanasiyana, kuphatikizapo malonda. Pogwiritsa ntchito nkhani zake, inu mwamsanga kuchita kuwerengetsera zovuta, kuphatikizapo ulosi wa kubweranso kwa ntchito imeneyi. Mwachitsanzo, kupambana limakupatsani mosavuta kuwerengera IRR cha polojekiti. Kodi kuchita izo mu kuchita, nkhaniyi idzafotokoza.

Kodi IRR

chidule izi kutanthauza mlingo mkati adzabweranso (IRR) yeniyeni ntchito ndalama. chizindikiro ichi kawirikawiri amagwiritsidwa ntchito ndi cholinga kuyerekeza maganizo kwa profitability ndi kukula chiyembekezo. Mu n'zosangalatsa kumva mawu IRR - ndi chiwongola dzanja limene bwererani phindu panopa utuluke ndalama zonse chofunika kwa kukhazikitsa ntchitoyi ndalama (anasankha NPV kapena NPV). The IRR ndi apamwamba, m'pamenenso zingamuthandize ndi ntchito ndalama.

momwe kupenda

Kupeza kunja IRR polojekiti, mukhoza kusankha kuti muthamange kapena kukana izo. Mwachitsanzo, ngati mukupita kutsegula ntchito watsopano chikuyembekezeka ndalama ngongole yake yokwanira anatengedwa ku banki, ndiye mawerengedwe IRR kudziwa chocheperako chapamwamba malire a mlingo chidwi. Ngati kampani amagwiritsa gwero oposa mmodzi wa ndalama fanizo la makhalidwe IRR ndi ndalama zawo zingakuthandizeni kuti apange zochita kaya kukhazikitsa ntchitoyi. Mtengo umodzi gwero ndalama masamu ndi otchedwa chilinganizo masamu zolemeletsedwa ndikutanthauza. Iwo ankatchedwa "mtengo likulu" kapena "Mtengo likulu patsogolo" (amaimira SS).

Ntchito chithunzi ichi, tili:

ngati:

• IRR> CC, ndiye polojekiti zikhoza anapezerapo;
• IRR = CC, ndiye polojekiti Sindizakubweretsanso phindu kapena imfa;
• IRR

Kodi kuwerengera IRR pamanja

Kale cha kubwera kwa makompyuta GNI kuchita masamu ndi kuthetsa anthu aone kuti pali m'malo zovuta (onani. Mmunsimu).

Pamafunikanso mfundo zotsatirazi:

• CFt - otaya ndalama pa nthawi imeneyi T;
• IC - ndalama pa Launch gawo cha polojekitiyo
• N - chiwerengero cha mosinthanasinthana.

Popanda mapulogalamu kuwerengera IRR polojekiti n'kotheka, ntchito njira chiyerekezo motsatizana kapena iteration. Kuti vuto limeneli chisanadze ayenera kusankhidwa mlingo kuti mupeze mfundo osachepera NPV kwa gawo ndi kuchita chiyerekezo.

The njira ndi approximations motsatizana

Choyamba, muyenera kupita ku ntchito chinenero. Mu lembali, IRR adzakhala anamvetsa zokolola ndi r Mukhoza phindu limene NPV, pokhala ntchito ya r Mukhoza, amakhala ziro.

M'mawu ena, IRR = r Mukhoza ngati kuti pamene mmalo mwa NPV mawu (f (R)) ndi zeroed.

Tsopano ife kuthetsa ntchito anakonza njira approximations motsatizana.

Ndi iteration ambiri amadziwira monga zotsatira za ntchito kambirimbiri ntchito masamu. Phindu la kugwira ntchito masamu mu sitepe yapita, nthawi yotsatira kumakhaladi mfundo yomweyo.

Chizindikiro IRR ikuchitika mu magawo 2:

• IRR mawerengedwe a mfundo muyeso wa zokolola yachibadwa r1 ndi R2 ngati kuti r1
• mawerengedwe a index pa mfundo r Mukhoza pafupi mtengo IRR analandira chifukwa cha mawerengedwe m'mbuyomu.

Pothetsa vuto r1 ndi R2 amasankhidwa mu njira yakuti NPV = f (R) anasintha kufunika kwake kwa opanda kuti kuphatikiza kapena mosinthanitsa mkati osiyanasiyana (r1, R2).

Motero tili ndi chilinganizo cha kuwerengetsa chithunzi IRR mawu pansipa.

Kuti mupeze zinafunika mfundo IRR precomputed kwa mfundo zosiyanasiyana NPV% TH mlingo.

Pakati zizindikiro za NPV, PI ndi SS pali ubale zotsatirazi:

• NPV ngati mtengo ndi zabwino, ndiye IRR> SS ndi PI> 1;
• ngati NPV = 0, ndiye IRR = kukolopa ndi PI = 1;
• ngati mtengo wa NPV ndi zoipa, ndi IRR

njira mawonekedwe

Tsopano inu mukudziwa chomwe izo ziri ndi mmene kuwerengera IRR ndi dzanja, ndi ofunika kuti tidziwe bwino za njira ina pothetsa vuto limeneli, m'modzi wa otchuka kwambiri pamaso panali kompyuta. Tikuyankhula zokhudza Baibulo likutipatsa za tanthauzo la IRR. Charting chofunika kupeza phindu NPV, m'malo mwa chilinganizo ake kuwerengetsa mfundo zosiyanasiyana za mlingo kuchotsera.

Kodi kuwerengera IRR mu kupambana

Monga mukuonera, pamanja kupeza GNI - zovuta. Zimenezi zimafuna kudziwa zina masamu ndi nthawi. Zimakhala zosavuta kudziwa mmene kuwerengera IRR kuti kupambana (onani chitsanzo. Mmunsimu).

Chifukwa chaichi, mu Microsoft spreadsheet ntchito ali ndi wapadera anamanga-mu ntchito kuwerengera mkati kuchotsera mlingo - IRR, amene amapereka kufunika mtengo IRR mawu kuchuluka.

malembedwe

IRR (icho chiri ndi mmene kuwerengera m'pofunika kudziwa akatswiri komanso borrowers wamba) mu kupambana ikhale IRR (Abwino; tiziike).

Tiyeni tione mwatsatanetsatane malembedwe:

• Pansi anamvetsa mfundo zosiyanasiyana kapena kutchula maselo muli nambala imene kuwerengera VSD, kuganizira zinthu zonse mwachindunji kwa chizindikiro ichi.
• The kopanda ndi phindu limene amadziwika kuti ali pafupi chifukwa IRR.

Mu Microsoft kupambana kwa mawerengedwe a IRR pogwiritsa ntchito njira pamwamba pa iterations. Iye akuyamba ndi mtengo wa "kopanda", ndipo amachita kuwerengetsera ozungulira kubala chifukwa ndi lolondola 0,00001%. Ngati anamanga-HDR sangabereke chifukwa pambuyo zoyesayesa 20, ndiye spreadsheet chikalata zolakwa mtengo, ikhale "# NUM!".

Monga mmene kuchita, nthawi zambiri palibe kufunika mwatchutchutchu kufunika kwa ukulu wa "kopanda". Ngati mulibe, a purosesa amaona kuti kukhala 0.1 (10%).

Ngati anamanga-mu ntchito IRR akubwerera cholakwa "# NUM!" Kapena ngati chifukwa si mmene anali kuyembekezera, n'zotheka kuchita kuwerengetsera kachiwiri, koma ndi phindu osiyana a "kopanda" kutsutsa.

Zochita "Eksele": Yankho 1

Yesani kuwerengera IRR (icho chiri ndi momwe aziwerenga izi pamanja mukudziwa kale) mwa ntchito anamanga-HDR. Tiyerekeze tili deta kwa zaka 9 pasadakhale, amene analowa ndi spreadsheet kupambana.

	A	B	C	D	E
1	Nthawi (chilichonse) T	ndalama koyamba	ndalama ndalama	malowedwe andalama	malowedwe andalama
2	0	200 000 pakani.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	30000 pa p.	20000 pa 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 pa p.	35000 p.
7	5	- p.	53000 p.	20000 pa p.	33000 p.
8	6	- p.	47000 p.	18000 pa 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%

Selo ndi adiresi E12 anayambitsa mfundo ya "= VSD. (E3: E2)" Chifukwa cha ntchito gome lake purosesa zotuluka mtengo wa 6%.

Zochita "Eksele": Version 2

Malinga deta kuperekedwa Mwachitsanzo yapita, kuwerengera IRR kumatanthauza "kufunafuna njira zothetsera mavuto" superstructure.

Timatha inu kufunafuna phindu mulingo woyenera kwa IRR NPV = 0. Kuti tichite zimenezi, muyenera kuwerengera NPV (kapena NPV). Ndi wofanana ndi Uwerenge Kuchotsera otaya ndalama chaka ndi chaka.

	A	B	C	D	E	F
1	Nthawi (chilichonse) T	ndalama koyamba	ndalama ndalama	malowedwe andalama	malowedwe andalama	Kuchotsera Cash mumayenda
2	0	200000 p.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	20 000 p.	20000 pa p.	20000 pa p.
4	2	- p.	60000 p.	20000 pa p.	27000 p.	27000 p.
5	3	- p.	45000 p.	20000 pa p.	17000 p.	17000 p.
6	4	- p.	50000 p.	20000 pa p.	35000 p.	35000 p.
7	5	- p.	53000 p.	20000 pa p.	33000 p.	33 000 p.
8	6	- p.	47000 p.	20000 pa p.	29000 p.	29000 Mtsinje.
9	7	- p.	62000 p.	20000 pa p.	37000 p.	37000 p.
10	8	- p.	70000 p.	20000 pa p.	40000 p.	40000 p.
11	9	- p.	64000 p.	20000 pa p.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

Kuchotsera otaya ndalama kuchita masamu ndi chilinganizo «= E5 / (1 + $ F $ 11) ^ A5».

Ndiye, tikutsata ndondomeko za NPV "= ndalama (F5: F13) -B7».

Kenako, muyenera kupeza pamaziko a kukhathamiritsa mwa pokha-mvu "Solver" ndi mtengo wa IRR mlingo kuchotsera pa zimene NPV cha polojekiti ndi wofanana ndi ziro. mukufuna kutsegula "Data" gawo menyu chachikulu kuti cholinga ndi kupeza ntchito "Funani njira."

Pazenera limapezeka, lembani mzere "Muike chandamale foni", kuwapatsa malangizo adiresi NPV mawerengedwe chilinganizo, T. E. + $ F $ 16. ndiye:

• anasankha mtengo ichi "0" maselo;
• zenera "kusintha selo" kupanga chizindikiro + $ F $ 17, T. e. kufunika kwa mlingo mkati adzabweranso.

Chifukwa cha kukhathamiritsa spreadsheet amadzadza selo kanthu ndi adiresi F17 mtengo wa ndalama kuchotsera. Malinga pa gome, chifukwa ndi wofanana 6%, amene chikugwirizana ndi mawerengedwe a chizindikiro chomwecho analandira ntchito anamanga-chilinganizo kupambana.

MIRR

Nthawi zina m'pofunika kuwerengera kusinthidwa mlingo mkati adzabweranso. Limangotsatira IRR osachepera polojekiti nkhani ya reinvestment. Tikutsata ndondomeko za kuwerengetsa MIRR motere.

kumene:

• MIRR - mlingo mkati mwa kubwerera kwa ndalama ya;
• COFt - outflow wa ndalama pa ntchito nthawi T;
• CIFt - ndi kuchuluka kwa ndalama;
• r Mukhoza - kuchotsera mtengo, umene uli wofanana ndi zolemeletsedwa ndalama pafupifupi likulu padera ndi WACC;
• D -% mlingo Star reinvested;
• N - chiwerengero cha nthaŵi.

Yowerengera MIRR mu purosesa tabular

Bwino ndi katundu IRR (icho chiri ndi momwe aziwerenga zimaonetsa mukudziwa kale), inu mosavuta kuphunzira mmene kuwerengera kusinthidwa mlingo mkati adzabwerera kupambana.

Mupulogalamuyi spreadsheet, wapadera anamanga-mu ntchito MIRR. Tengani chomwecho, takambiranazi chitsanzo onse. Kodi kuwerengera IRR pa izo, kale kale. MIRR gome motere.

	A	B	C	D	E
1	kuchuluka ngongole monga kuchuluka ndi		10%
2	mlingo wa reinvestment		12%
3	Nthawi (chilichonse) T	ndalama koyamba	ndalama ndalama	malowedwe andalama	malowedwe andalama
4	0	200000 p.	- p.	200000 p.	200000 p.
5	1	- p.	50000 p.	30000 pa p.	20000 pa 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 pa p.	35000 p.
9	5	- p.	53000 p.	20000 pa p.	33000 p.
10	6	- p.	47000 p.	18000 pa p.	29000 p.
11	7	- p.	62000 p.	25000 p.	37000 p.
12	8	- p.	70000 p.	30000 pa p.	40000 p.
13	9	- p.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

Selo umayamba E14 chilinganizo cha MIRR «= MIRR (E3: E13; C1; C2)».

Ubwino ndi kuipa kwa ntchito chizindikiro cha mlingo mkati adzabweranso

The njira ya kuyerekeza ndi chiyembekezo ntchito ndi kuwerengetsa ndi IRR ndi poyerekeza ndi mtengo wa mtengo wa likulu si wangwiro. Komabe sikuti ali ndi zinthu zina. Njirazi ndi monga:

• Luso kuyerekeza ndalama osiyana mu mawu a chikoka awo ndi ntchito imayenera likulu padera. Mwachitsanzo, n'zotheka kuti achite poyerekeza ndi zokolola pa nkhani ya chuma ngozi ufulu.
• Luso kuyerekeza ntchito zosiyanasiyana ndalama ndi osiyana kaja ndalama.

Pa nthawi yomweyo zofooka zoonekeratu chizindikiro ichi. Njirazi ndi monga:

• kulephera index mlingo mkati adzabweranso kusonyeza kuchuluka kwa reinvestment ntchitoyo;
• n'kovuta yotani malipiro ndalama, monga phindu zimakhudza zinthu zambiri chiopsezo, cholinga ziyenera amene ali ovuta kwambiri;
• kulephera kusonyeza kuchuluka mtheradi ndalama (ndalama zimene anapeza) pa mtengo wa ndalama.

Samalani! drawback Last linathera posunga chizindikiro MIRR, umene anafotokoza mwatsatanetsatane pamwamba.

Kutha kuwerengera IRR kungakhale kothandiza kuti borrowers

Malinga ndi zofunikira za Bank Russian Central, onse mabanki kugwira ntchito mu gawo la Chitaganya cha Russia, adzakhala amasonyeza mlingo ogwira chidwi (ESR). Iwo akhoza mosadalira kuwerengetsa wobwereka iliyonse. Kuchita ichi adzakhala ndi ntchito spreadsheet, Mwachitsanzo, Microsoft kupambana, ndipo sankhani anamanga-HDR. Zotsatira izi selo yemweyo kuti kuchulukitsa ndi malipiro nthawi T (ngati mwezi, ndiye T = 12 ngati fulorosenti, ndiye T = 365) popanda nazo.

Tsopano, ngati inu mukudziwa zomwe mlingo mkati mwa kubwerera, ngati inu mukuti, "Pakuti aliyense wa ntchito zotsatirazi, kuwerengera IRR», mulibe vuto.