I-Excel yenye yeenkqubo onokuzisebenzisa ukwenza imisebenzi eyahlukeneyo, kubandakanywa nezo zinento yokwenza noqoqosho, utyalo-mali, ucwangciso-mali... Ugxininiso kutyalo-mali, ezimbini zemisebenzi esetyenziswa kakhulu yi-IRR kunye ne-NPV. Kodwa, Ubalwa njani iNPV kunye ne-IRR kwi-Excel?
Yiloo nto esiza kukuchazela yona ngezantsi ukuze wazi ukuba iifomula kufuneka zisetyenziswe njani kwi-Excel ukuze idatha ibonakale ngokufanelekileyo kwaye ichanekile. Masiyenze?
Yintoni i-NPV
Phambi kokukunika iifomula zokubala kunye ne-Excel, kubalulekile ukuba uyazi ukuba sithetha ngantoni ngekota nganye kwaye ngaphezu kwayo yonke into oyiqondayo kakuhle ukuba ithetha ukuthini kunye nokutolikwa okufuneka kunikwe.
I-NPV imele i-Net Present Value, oluyinqobo yotyalo-mali. Sinokuthi esi sixhobo sisinceda sazi ukuba yintoni uqoqosho lweprojekthi.
Inokuqondwa njenge ixabiso elimisela konke ukuhamba kwemali okulungileyo nokubi ukuhlalutya utyalo-mali. Ngamanye amazwi, bathathela ingqalelo ingeniso, iindleko kunye neendleko ezinkulu ukuze bazi ukuba utyalo-mali lunokwenzeka okanye hayi.
Iziphumo ezinokufunyanwa ngokusebenzisa ifomula zithathu:
- Iqinisekile. Xa ibonisa ukuba utyalo-mali ofuna ukulwenza lunokuba nenzuzo kwaye ngoko kulungile ukuqhubela phambili neprojekthi.
- Ukungalungi. Xa iprojekthi yokutyala imali ingeyombono ilungileyo kwaye kuya kuba ngcono ukurhoxisa.
- Unothi. I-NPV enguziro ithetha ukuba utyalo-mali alulunganga kwaye alubi. Kule meko, ezinye iintlobo zamaxabiso ezinokuthi zingalinganisi ibhalansi kwelinye icala okanye kwelinye kuya kufuneka zithathelwe ingqalelo.
Yintoni i-IRR
Ngoku njengoko siphawule ngokubanzi kwaye ngaphandle kokungena kwiinkcukacha, yintoni i-NPV, siza kwenza okufanayo nge-IRR.
I-IRR imele uMyinge waNgaphakathi wokuBuyisa. kwaye, kanye njengoko behamba, isetyenziselwa ukuhlalutya ukusebenza okanye inzuzo yotyalo-mali.
Ichazwa njenge ipesenti yengeniso efunyanwa kutyalo-mali. Oko kukuthi, yintoni oyifumanayo ngokutyala imali. Okanye into elahlekileyo, kufuneka kuthiwe.
Ewe, into ophumeleleyo okanye ophulukene nayo ihlala ihambelana, kuba awuyazi ngenene de kufike loo mzuzu. Kodwa lixabiso elibonisa ukuba uninzi lweengcali zisebenzisa ukwenza izigqibo (ukudibanisa nezinye izixhobo).
Ngokubhekiselele kwisiphumo sayo, njengeNPV, apha uya kuba neziphumo ezithathu:
- I-IRR inkulu kunoziro. Kuya kubonisa ukuba iprojekthi iyasebenza kwaye iyamkeleka kuba inzuzo eyaneleyo iya kuzuzwa (iphezulu inani, ngcono).
- I-IRR ingaphantsi kunoziro. Ngoko ke iprojekthi ayinakwenzeka konke konke. Enyanisweni, iya kugqiba ukumosha ngenxa yokuba awuyi kuyenza imali, kodwa kunokuba ulahlekelwe yiyo.
- Ilingana no-zero. NjengakwiNPV, apha utyalo-mali aluyi kukunika ngeniso okanye ilahleko. Ke ukucofa ibhalansi kwelinye icala okanye kwelinye, amanye amaxabiso okanye izibalo zithathelwa ingqalelo ezinokunceda ukwenza isigqibo.
Ubalwa njani iNPV kunye ne-IRR kwi-Excel
Ngoku, siza kugxila kwiprogram ye-Excel kwaye senze izibalo ze-NPV kunye ne-IRR ngokweefomula ezisebenza kule nkqubo.
Siqala ngomsebenzi we-IRR, oko kukuthi, izinga langaphakathi lokubuyiswa kwemali.
Ukwenza oku, kufuneka ubeke oku kulandelayo kwi-Excel:
=IRR (imatriki equlethe ukuhamba kwemali)
Ukuba akukho xabiso le-IRR eliqikelelwayo elabelweyo, into eyenziwa yinkqubo kukusebenzisa i-10%. Kodwa eneneni ungabeka naliphi na ixabiso olifunayo kuyo. Ngokuqinisekileyo, ifomula iya kutshintsha kwaye iya kuba ngolu hlobo:
=IRR (imatriki equlethe ukuhamba kwemali; ixabiso eliqikelelweyo le-IRR)
Ngokuqinisekileyo, kuya kufuneka udale imatrix equlethe ukuhamba kwemali ngokwakho ukuze inqume ukuba yeyiphi inxalenye yedatha ekufuneka ithathwe kwaye iyahambelana nokuhamba.
Umzekelo, khawufane ucinge ukuba uvula uxwebhu lwe-Excel kwaye kwikholamu A ubeke ixesha. Kuluhlu B umisela ukuhamba kwemali, eya kuba yimali ephumayo.
Ewe, kwifomula awukwazi ukubeka =IRR (i-matrix equlethe ukuhamba kwemali) kodwa kufuneka umisele ukusuka kweliphi ixabiso ukuya kweliphi ixabiso ekufuneka liye kulo.
Kulo mzekelo esikuxelela wona, ukuba ikholamu B ingumqukuqelo, iya kuqala ukusuka ku-B2 (ikholamu B, umgca 2) kwaye iqhubeke kude kube sekupheleni.
Ngoko ke, ifomula iya kuba: =IRR(B2:BX), kunye no-X inani lokugqibela onalo njengokuhamba (B10, B12, B242, B4...).
Sincoma ukuba usungule i-IRR kwikholamu efana ne-C, D, E ... kodwa ikuvumela ukuba wenze utshintsho ekuhambeni okanye enye idatha ngaphandle kokuyicima.
Ngoku ukuqhubela phambili ukubala iNPV, Kuya kufuneka uyazi ukuba kwi-Excel umsebenzi owubalayo ubizwa ngokuba yi-VNA. Njengakwi-IRR, i-Excel yenza umsebenzi ngendlela ethile. Kwaye kuya kuthathela ingqalelo iintlawulo ezizayo, ngoko ke kuqhelekile ukuba ixabiso lokuqala olibonisayo kwimatrix yokuhlawula lihlaziywe kwizinga lenzala.
Ifomula imi ngolu hlobo lulandelayo:
=NAV(ixabiso lesaphulelo; imatrix equlethe ukuhamba kwemali yexesha elizayo)+ utyalo-mali lokuqala
Ngoku, siyitolika njani ngedatha ofuna ukuyinika.
Ngakolunye uhlangothi, kuya kufuneka ufake ixesha kunye nokuhamba kwemali (njengoko kwakunjalo nge-IRR). Kodwa oko kukodwa akwanelanga.
Ukwanalo nexabiso lesaphulelo. Oku kufuneka kunikwe ngokuqhelekileyo kwaye rhoqo, nokuba bakunika i-5%, i-10%, i-20 ... kufuneka ulahlule nge-100 ukuyibeka kwifomula: 0,05, 0,1, 0,2...
Kwelinye icala, unotyalo-mali lokuqala. Unayo le nto kuba iya kuba lixabiso lokuqala lokuhamba kwemali, oko kukuthi, ukuba uyibeka ngendlela efanayo njengoko besitshilo ngaphambili, iya kuba yi-B2.
Ngoku ekubeni uyayazi indlela yokubala i-NPV kunye ne-IRR kwi-Excel, lixesha lokuba uziqhelanise ukuze ubone ukuba uyifumana ngokwenene na ifomula kunye nokuba idatha ichanekile. Ngale ndlela unokwenza izigqibo ezifanelekileyo kwishishini lakho. Ngaba unawo nawaphi na amathandabuzo aseleyo?