IRR - waxa uu yahay iyo sida loo xisaabiyo in Excel?

Barnaamijka Excel markii hore u abuuray si loo fududeeyo dejinta oo dhinacyo badan ah, oo ay ku jiraan ganacsi. Isticmaalka qaababka ay, aad si deg deg ah u oofin karin xisaabaha adag, oo ay ku jiraan saadaasha ee soo celinta mashaariicdaas. Tusaale ahaan, Excel kuu ogolaanaya in aad si sahal ah loo xisaabiyo IRR mashruuca. Sida loo sameeyo in la fuliyo, qodobkan sheegi doonaa.

Waa maxay IRR ah

gaabiyay middani waxa ay tilmaamaysaa heerka gudaha ee laabto mashruuc gaar ah maalgashiga (IRR). Tilmaamahan waxaa badanaa loo isticmaalaa ujeedada barbar jeedinta ee faa'iidada iyo koritaanka rajada. Marka la eego tirada, IRR - waa dulsaarka ee dib qiimaha jooga oo dhan qulqulka lacag caddaan ah looga baahan yahay fulinta mashruuca maal-(NPV loo qoondeeyey ama NPV) ah. IRR waa sare u sii ididiilo waa mashruuc maalgelin ah.

sida si ay u qiimeeyaan

Ogaanshaha IRR mashruuca, waxaad go'aansan kartaa in ay u maamulaan ama in la diido. Tusaale ahaan, haddii aad doonaysid in la furo ganacsi cusub iyo waxa la filayaa in ay maal-daynta ay sabab ka bangiga qaaday, ka dibna ku xisaabinta IRR si loo ogaado xadka fududaan sare ee dulsaarka. Haddii shirkad isticmaalaa in ka badan hal il ee maalgashiga, barbardhigga ah ee qiimaha IRR ay kharashka ka dhigi doonaa suurto gal ah in go'aan ah ku wargeliyay in ay u bilowdo mashruuca. Qiimaha il maalgelin kaliya xisaabiyaa by xisaabta miisaan leh wax-u formula ay ka dhigan tahay. Waxaa loo yaqaan "qiimaha caasimada" ama "Qiimaha caasimada sare" (muujinaysaa SS).

Isticmaalka tiradaasi, waxaan haysanaa:

Haddii:

• IRR> CC, ka dibna mashruucan la bilaabay karo;
• IRR = CC, ka dibna mashruuca ma keeni doono faa'iido ama khasaaro;
• IRR

Sida loo xisaabiyo IRR ah gacanta

Long ka hor dhalashada of kombiyuutarada GNI waxaa loo xisaabiyaa by xallinta isla'egta waa halkii adag (eeg. Hoos).

Waxaa ka mid ah qiyamka soo socda:

• CFt - kaashka inta lagu guda jiro t bareeg waqtiga;
• IC - maalgelin lagu jiro wajiga la bilaabay mashruuca ah;
• N - Tirada guud ee dura.

Aan software gaar ah si ay u xisaabiso IRR mashruuca waxaa suurtagal ah, iyadoo la isticmaalayo habka ay u dhaw isku xigta ama siyaalaha. Waayo, kanu caqabad ka hor waa in la doortay si ay heerka si aad u ogaato qiimaha ugu yar ee NPV for module ah oo uu ku sameeyo qiyaas.

Xalka by qiyaasida xigta

First of dhan, waa in aad si hawlaha afka tagaan. Macnaha halkan, IRR la fahamsan yahay doonaa dhalidda r qiimaha at kaas oo NPV, isagoo shaqo ah r, uu noqdo eber ah.

In si kale loo dhigo, IRR = r sida in markii la bedelay galay NPV hadal (f (r)) waxaa arbushaad.

Hadda waxaan u xaliyaan hawsha diyaariyey habka of qiyaasida isku xigta.

By siyaalaha waxaa caadi la fahamsan yahay oo ay sabab u codsiga soo noqnoqda ee hawlgal xisaabeed. Qiimaha shaqada xisaabiyaa in tallaabo hore, marka xigta waxa uu noqonayaa mid dood la mid ah.

IRR tilmaame waxaa fuliyay in 2 marxaladood:

• xisaabinta IRR oo ka mid ah qiyamka xad dhaaf ah oo soo dajinno Biyaha caadi R1 iyo Red2 sida in R1
• xisaabinta index ee qiimaha r u dhow qiimaha IRR helay iyadoo ay sabab u xisaabinta hore.

In la xaliyo dhibaatada R1 iyo Red2 lagu doorto sidan oo kale in NPV = f ah (r) bedelay miraheeda ka jaray in lagu daray ama qeybsanaan ku xigeenka gudahood kala duwan (R1, Red2).

Saasaana qaacidada xisaabinta hadal IRR tiradaasi hoos ku qoran.

Waxaa soo socota in si aad u hesho qiimaha IRR baahan precomputed qiimaha kala duwan ee NPV% th heerka.

Inta u dhaxaysa tilmaamayaasha NPV, PI iyo SS jiro xiriir soo socda:

• NPV haddii qiimaha waa ay wanaagsan tahay, ka dibna IRR> SS iyo PI> 1;
• haddii NPV = 0, ka dibna IRR = Xaaq iyo PI = 1;
• haddii qiimaha NPV waa negative, IRR ah

habka garaafyada

Haddaba si aad u ogaataan waxa ay tahay iyo sida ay u xisaabiso IRR ah by gacanta, waxaa haboon in ay bartaan hab kale oo ka xalinta dhibaatadan, taas oo ka mid ah kuwa ugu caansan ka hor waxaa jiray kombiyuutarada ahaa. Waxaan ka hadleynaa version graphic of qeexidda IRR. Kubaxsan loo baahan yahay si aad u hesho qiimaha NPV, badalaa in ay caanaha xisaabinta qiimaha kala duwan ee heerka sicir-dhimis.

Sida loo xisaabiyo IRR ee Excel

Sida aad arki karto, gacanta ka heli GNI - waa adag tahay. Tani waxay u baahan qaar ka mid ah aqoonta xisaabta iyo waqti. Inta badan ka sahlan in ay bartaan sida loo xisaabiyo IRR si Excel (eeg tusaalaha. Hoos).

Ujeedada this, in codsi isbiriidhshiit Microsoft ayaa shaqo gaar ah ka dhisay-in si ay u xisaabiso heerka gudaha dhimis - IRR, taas oo ku siinaysaa qiimaha la doonayo IRR marka la eego boqolkiiba.

Saan

IRR (waxa uu yahay iyo sida loo xisaabiyo waxaa lagama maarmaan ah in la ogaado xirfadlayaasha ma aha oo kaliya, laakiin sidoo kale deynsanaya caadiga ah) ee Excel loo qoondeeyey sida IRR (Qiimayaasha, Garo).

Ha ka fikiro si aynu si faahfaahsan Saan ah:

• Under qiimaha isugu soo fahamsan yahay ama marjic u tahay unugyada ay ku jiraan tirada taas oo si ay u xisaabiso VSD, ka fiirsaneysa inay dhammaan shuruudaha ku qeexan for dhimaya this.
• malo waa qiime kaas oo lagu yaqaan in ay ku dhow inay natiijo IRR waa.

In Microsoft Excel for xisaabinta IRR oo isticmaalaya habka kor ku xusan ee iterations. Waxa uu la qiime ah "malo" ay ka bilaabmayso, oo u qabata a xisaabaha xilliile ah in ay soo saaraan natiijo saxsanaan ah 0,00001%. Haddii dhisay-in HDR ma keento natiijo ka dib markii ay 20 isku day, ka dibna qalad qiimaha isbiriidhshiit soo saaray, qoondeeyey sida "# NUM!".

Sida dhaqanka shows, kiisaska intooda badan looma baahna in la qeexo qiimaha ah magnitude oo ka mid ah "malo". Haddii la saaray, processor u aragto inuu noqdo 0.1 (10%).

Haddii IRR function dhisay-in laabtay qaladkiisii "# NUM!" Ama haddii natiijada sida lama filayo, waxaa suurtagal ah in ay mar kale fuliyaan xisaabinta, laakiin qiimaha kala duwan ee "malo" doodda.

Go'aamada "Eksele": Xulashada 1

Isku day in aad xisaabiso IRR ah (waxa uu yahay iyo sida loo xisaabiyo qiimaha this gacanta aad ogtahay) by function dhisay-in HDR. Ka soo qaad aan helnay warbixin 9 sano ka hor, kuwaas oo la galay isbiriidhshiit Excel ah.

	A	B	C	D	E
1	Period (sanadka) T	kharashka bilowga ah	dakhli lacag caddaan ah	kaashka	kaashka
2	0	200 000 masaxo.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	30000 on p.	20000 on p.
4	2	- p.	60000 p.	33000 p.	27000 p.
5	3	- p.	45000 p.	28000 p.	1,7000 p.
6	4	- p.	50000 p.	15000 on p.	35000 p.
7	5	- p.	53000 p.	20000 on p.	33000 p.
8	6	- p.	47000 p.	18000 on 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%

cell The cinwaanka E12 soo bandhigay formula ah "= VSD. (E3: E2)" Sidaas darteed ee ay processor miiska codsiga soo saarka qiimaha 6%.

Go'aamada "Eksele": Version 2

Sida laga soo xigtay xogta siiyey in tusaale ahaan la soo dhaafay, si ay u xisaabiso IRR ka dhigan tahay "raadiyo xal" superstructure.

Waxay kuu ogolaanaysaa inaad raadiso qiimaha fiicnayn for NPV IRR ah = 0. Si arrintan loo sameeyo, waxaad u baahan tahay si ay u xisaabiso NPV (ama NPV). Waxaa si siman u soo tiriya reer reer socodka laga dhimay lacag caddaan ah oo sannad ka sannad.

	A	B	C	D	E	F
1	Period (sanadka) T	kharashka bilowga ah	dakhli lacag caddaan ah	kaashka	kaashka	Dhimis Cash Flow
2	0	200000 p.	- p.	200000 p.	200000 p.
3	1	- p.	50000 p.	20 000 p.	20000 on p.	20000 on p.
4	2	- p.	60000 p.	20000 on p.	27000 p.	27000 p.
5	3	- p.	45000 p.	20000 on p.	1,7000 p.	1,7000 p.
6	4	- p.	50000 p.	20000 on p.	35000 p.	35000 p.
7	5	- p.	53000 p.	20000 on p.	33000 p.	33 000 p.
8	6	- p.	47000 p.	20000 on p.	29000 p.	29000 p.
9	7	- p.	62000 p.	20000 on p.	37000 p.	37000 p.
10	8	- p.	70000 p.	20000 on p.	40000 p.	40000 p.
11	9	- p.	64000 p.	20000 on p.	31000 p.	31000 p.
12					NPV	69000 p.
					IRR

kaashka dhimay waxaa loo xisaabiyaa by «^ = E5 / (1 + $ F $ 11) J5» caanaha.

Markaas, formula ee NPV "= wadarta (F5: F13) -B7».

Next, waxaad u baahan tahay si aad u ogaato oo ku saleysan ayna taasi oo ka dar-ons "Solver" waa qiimaha heerka sicir-dhimis IRR at kaas oo NPV mashruuca waa loo siman yahay si eber. aad rabto in aad la furo qaybta "Data" in menu ugu weyn in si loo gaaro hadafka iyo u hesho shaqo "Ka raadi xal."

In uu furmo suuqa muuqata, buuxi laynka "cell diirada Set", la tilmaamayo cinwaanka NPV formula xisaabinta, t. E. + $ F $ 16. markaas:

• Qiimaha soo xulay for this "0" unugyada;
• daaqadda "isbedel cell" dhigi dhimaya ah + $ F $ 17, t. e. qiimaha heerka gudaha ee soo laabtay.

Sidaas darteed of isbiriidhshiit ayna buuxiyaan cell ah oo madhan, oo qiimaha F17 cinwaanka heerka sicir-dhimista. Sida shaxda ka arkay, natiijadu waa loo siman yahay ilaa 6%, taas oo ku beegan tahay xisaabinta dhimaya isla helay isticmaalaya formula Excel dhisay-in.

MIRR

Xaaladaha qaarkood waxaa lagama maarmaan ah si ay u xisaabiso heerka dib u habaynta gudaha ee soo laabto. Waxa ay ka tarjumaysaa IRR ugu yar ee mashruuca ay dhacdo reinvestment. formula ee xisaabinta MIRR sida soo socota.

halkaas oo:

• MIRR - heerka gudaha ee soo celinta maalgelinta;
• COFt - dheecaanka lacagaha lagu jiro t wakhtiga mashruuca;
• CIFt - qulqulaya ee maaliyadda;
• r - heerka sicir-dhimis, oo waa loo siman yahay si ay kharashka miisaan leh celcelis ahaan caasimada maal by WACC ah;
• d - heerka% Star maalgeliyo,
• n - tirada xilliyada.

MIRR Xisaabinta ee processor tabular

Ogaha guryaha IRR (waxa uu yahay iyo sida loo xisaabiyo qiimaha garaaf ahaan aad hore u taqaan), waxaad si fudud u baran kartaa sida loo xisaabiyo saamiga u habaynta gudaha soo laabtay Excel.

Waayo, codsiga isbiriidhshiit this, la dhisay-in MIRR a shaqo gaar ah. Qaado oo dhan isku mid ah, mar hore loo arkaa tusaale u ah. Sida loo xisaabiyo IRR waxaa ku, waxaa horay loo tixgeliyo. MIRR miiska sida soo socota.

	A	B	C	D	E
1	lacagta amaah boqolkiiba		10%
2	heerka reinvestment		12%
3	Period (sanadka) T	kharashka bilowga ah	dakhli lacag caddaan ah	kaashka	kaashka
4	0	200000 p.	- p.	200000 p.	200000 p.
5	1	- p.	50000 p.	30000 on p.	20000 on p.
6	2	- p.	60000 p.	33000 p.	27000 p.
7	3	- p.	45000 p.	28000 p.	1,7000 p.
8	4	- p.	50000 p.	15000 on p.	35000 p.
9	5	- p.	53000 p.	20000 on p.	33000 p.
10	6	- p.	47000 p.	18000 on p.	29000 p.
11	7	- p.	62000 p.	25000 p.	37000 p.
12	8	- p.	70000 p.	30000 on p.	40000 p.
13	9	- p.	64000 p.	33000 p.	31000 p.
14				MIRR	9%

cell waxaa soo bandhigay formula E14 for MIRR «= MIRR (E3: E13, C1, C2)».

Faa'iidada iyo faa'iido darrada isticmaalaya tilmaanta heerka gudaha ee soo laabto

Habka of qiyaasayo rajada mashaariic by xisaabinta IRR iyo qiimaha kharashka magaalada marka la barbar dhigo ma u san yahay. Si kastaba ha ahaatee, waxa aanu u leedahay faa'iidooyin gaar ah. Kuwaas waxaa ka mid ah:

• Awoodda in ay is barbar maalgashi kala duwan marka la eego ay soo jiidashada iyo isticmaalka hufan ee caasimada maal. Tusaale ahaan, waxaa suurtagal ah si ay u fuliyaan is barbardhig la dhalidda in ay dhacdo hantida oo khatar la'aan.
• Awoodda in ay is barbar mashaariic maalgashi kala duwan la kaabiga maalgashi kala duwan.

Ugu isla waqti jilicsanaanta cad ee tilmaamahan. Kuwaas waxaa ka mid ah:

• ku fashilmeen in ay index heerka gudaha ee soo laabto si ay u muujiyaan xaddiga reinvestment ee mashruuca;
• dhib ah ee hillaadiyo lacag naqdi ah, sida ay qiimaha waxaa saameeya arrimo badan oo halista, qiimaynta ujeedada taas oo ka dhib badan tahay,
• ku fashilmeen in ay ka tarjumayaan lacagta buuxda dakhliga (baxa lacag caddaan ah) ee ku saabsan qiimaha maalgelinta.

Waa inaad taxadar! khasaare soo dhaafay waxaa la xalin by hayo tilmaanta MIRR, kaas oo lagu sharaxay si faahfaahsan kor ku xusan.

Sida awoodda si ay u xisaabiso IRR waxay noqon kartaa mid waxtar leh in deynsanaya

Sida laga soo xigtay shuruudaha Bangiga Dhexe Ruush ah, oo qararka oo dhan ka hawlgala dalka reer Ruushka, ayaa tilmaamaya doonaan dulsaarka wax ku ool ah (ESR). Waxaa si madax banaan xisaabi kartaa deyn kasta. Si arrintan loo sameeyo uu yeelan doonaan in ay isticmaalaan isbiriidhshiit, tusaale ahaan, Microsoft Excel, dooro ka dhisay-in HDR. Si aad natiijo this qol isku mid ah si ay u tarmaan by bixinta muddo T (haddii bil kasta, ka dibna T = 12 haddii dhaadheer, ka dibna T = 365) oo aan soo aruurinta.

Haddaba, haddii aad ogtahay waxa heerka gudaha ee soo laabto, sidaa darteed haddii aad ku odhan, "Waayo, mid kasta oo ka mid ah mashaariicda soo socda, xisaabi IRR ah», ma aad yeelan doontaa dhib.