Formule za EMI, ROI, NPV i IRR u Excelu korak po korak

Savladajte Excel formule za EMI, ROI, NPV i IRR To je jedan od najbržih načina da se od jednostavnog gledanja na brojeve pređe na donošenje informiranih investicijskih odluka. Pomoću nekoliko dobro razumljivih funkcija možete procijeniti kredite, uporediti projekte, analizirati marketinške kampanje ili pregledati profitabilnost svog portfelja, koristeći resurse kao što su... Certificirana obuka za uredski rad.

U sljedećim redovima vidjet ćete, vrlo detaljnoObradit ćemo kako izračunati i praktično primijeniti ključne koncepte poput ROI-a, neto sadašnje vrijednosti (NPV), interne stope povrata (IRR) i njihovih Excel varijanti. Također ćemo povezati ove metrike s konceptima kao što su leveridž, poređenje projekata s različitim vremenskim okvirima i tumačenje rezultata kada stvari ne idu po planu.

NPV / VNA u Excelu: sadašnja vrijednost vaših investicija

Neto sadašnja vrijednost (NSV) To je funkcija koja vam govori trenutnu vrijednost investicije na osnovu diskontne stope i niza budućih novčanih tokova. Drugim riječima, ažurira (diskontuje) buduće prilive i odlive novca kako bi ih izrazila u smislu današnjeg novca.

Sintaksa funkcije VNA u Excelu na španskom Vrlo je jednostavno: NPV(stopa; vrijednost1; ; …)"Stopa" je diskontna stopa po periodu (npr. godišnje), a argumenti "vrijednost1, vrijednost2, ..." su novčani tokovi (negativni ako su plaćanja, pozitivni ako su primici) poredani u vremenu.

Imajte na umu da Excel interpretira vrijednosti redoslijedom kojim ih unosite.Prvi novčani tok na listi odgovara kraju prvog perioda, drugi kraju drugog perioda i tako dalje. Stoga je ključno da poštujete hronološki redoslijed novčanih tokova prilikom konstruisanja formule.

Što se tiče tipa podataka, VNA ignoriše sve što nije validan broj.Ako argumenti sadrže prazne ćelije, logičke vrijednosti, tekst koji se ne može pretvoriti u brojeve ili greške, funkcija ih ignoriše i radi samo s numeričkim vrijednostima referenci ili raspona koje ste naveli, što je čini korisnom. formatiranje podataka u Excelu 365.

Postoji veoma važan detalj o tome kada investicija počinjePo definiciji, izračun NPV-a pretpostavlja da investicija počinje jedan period prije prvog novčanog toka koji uključite u listu i završava se posljednjim novčanim tokom u istoj listi. Ako se prvi novčani tok dogodi na početku prvog perioda (tj. u nultom trenutku), obično ćete morati taj iznos zasebno dodati rezultatu NPV-a, jer funkcija diskontira samo "buduće" novčane tokove na kraju svakog perioda.

Odnos između neto sadašnje vrijednosti (NSV), vrijednosti vrijednosti (VA) i interne stope uspješnosti (IRR).

NPV i PV (sadašnja vrijednost) su usko povezane, ali nisu identičneDok je neto sadašnja vrijednost (NSV) dizajnirana za rad s novčanim tokovima koji mogu varirati u svakom periodu, sadašnja vrijednost (PV) se koristi za anuitete gdje su tokovi konstantni i, osim toga, omogućava vam da odaberete da li se plaćanja/primci vrše na početku ili na kraju svakog perioda.

Veza sa IRR (IRR u Excelu) je još direktnijaIRR je upravo diskontna stopa koja čini neto sadašnju vrijednost jednakom nuli. To jest, ako izračunate IRR niza novčanih tokova, a zatim tu stopu koristite kao "stopu" u neto sadašnjoj vrijednosti, teoretski rezultat bi trebao biti 0. NPV(IRR(…); … ) = 0Ovaj odnos je osnova mnogih investicijskih odluka.

Osnovni primjer VNA u Excelu

Zamislite investiciju s godišnjom diskontnom stopom od 10%.Isplata od -10.000 u roku od jedne godine i naknadni prijemi od 3.000, 4.200 i 6.800 u narednim godinama. U Excel tabeli to možete organizovati na ovaj način (u jednoj koloni):

• A2: 0,10 → godišnja diskontna stopa.
• A3: -10000 → početni trošak koji nastaje za godinu dana.
• A4: 3000 → novčani tok u prvoj godini.
• A5: 4200 → protok 2. godine.
• A6: 6800 → protok 3. godine.

Formula za neto sadašnju vrijednost (NSV) ovih tokova bi bila =VNA(A2;A3;A4;A5;A6)Rezultat, s ovim podacima, je približno 1.188,44 monetarnih jedinica, što ukazuje na to da, diskontiranjem od 10%, investicija generira pozitivnu sadašnju vrijednost i stoga je prihvatljiva prema ovom kriteriju.

Prošireni primjer VNA s rasponom ćelija

Sada pretpostavimo projekat sa početnom investicijom od 40.000, kao u primjer finansijskog plana (negativni novčani tok) i prihode od 8.000, 9.200, 10.000, 12.000 i 14.500 u narednih pet godina, sa diskontnom stopom od 8%. Podatke možete strukturirati na sljedeći način:

• A2: 0,08 → godišnja diskontna stopa (npr. inflacija ili oportunitetni trošak).
• A3: -40000 → početni izdaci.
• A4:A8: 8000; 9200; 10000; 12000; 14500 → godišnje naknade.

Uobičajeni način izračunavanja neto sadašnje vrijednosti (NSV) u ovom slučaju To uključuje primjenu neto sadašnje vrijednosti (NSV) samo na pozitivne buduće novčane tokove, a zatim dodavanje početne investicije: =VNA(A2;A4:A8)+A3Sa naznačenim vrijednostima, dobit ćete pozitivnu neto sadašnju vrijednost (NSV) od oko 1.922,06, što ukazuje na to da se projekat isplati.

Ako se u narednoj godini pojavi dodatni negativni novčani tokNa primjer, dodatni gubitak ili trošak od -9.000 u hipotetičkoj šestoj godini, možete ga uključiti u listu novčanog toka: =VNA(A2;A4:A8;-9000)+A3Rezultat će biti negativna neto sadašnja vrijednost (NSV) (oko -3.749,47), što bi već ukazivalo na to da ovaj dodatni trošak čini projekat manje atraktivnim ili direktno neizvodljivim pod tom diskontnom stopom.

ROI: Kako izmjeriti povrat investicije sa i bez Excela

Povrat ulaganja (ROI) To je vjerovatno najpopularniji pokazatelj profitabilnosti koji se koristi na dnevnoj bazi. Njegova svrha je da izmjeri koliki je profit ostvaren u odnosu na cijenu vaše investicije, obično izražen u procentima.

Koristi se u gotovo svakom kontekstu gdje postoji rashod i ekonomski rezultat.: investicije na berzi, poslovni projekti, transakcije nekretninama, marketinške kampanje, digitalno oglašavanje itd. Upravo zato što je tako intuitivan i jednostavan za izračunavanje, postao je svojevrsni standard za poređenje opcija.

Šta je tačno ROI i zašto je važan?

ROI odgovara na vrlo direktno pitanje"Za svaki euro koji sam investirao, koliko sam dobio ili izgubio?" Ovo vam omogućava da uporedite relativnu efikasnost različite imovine, projekata ili strategija bez potrebe da se od samog početka upuštate u naprednu finansijsku analizu.

Njegova važnost leži u činjenici da vam daje objektivnu brojku na osnovu koje možete donositi odluke.Možete birati između nekoliko projekata, odrediti prioritete reklamnih kampanja i procijeniti isplati li se održati investiciju ili preći na onu s većim potencijalom. To ne zamjenjuje kvalitativnu analizu, ali pruža zajedničku numeričku osnovu.

Međutim, ROI ima jasna ograničenja.Ne uzima u obzir trajanje investicije, zanemaruje rizik, ne govori ništa o volatilnosti i može zavarati ako se tumači izolovano. Stoga se obično kombinuje s drugim pokazateljima (NPV, IRR, Sharpeov omjer itd.) i s iskustvom investitora.

Formula ROI-a i jednostavan primjer

Osnovni izraz za ROI je vrlo jednostavan:

ROI = (Sadašnja vrijednost investicije – Trošak investicije) / Trošak investicije

Ako želite izraziti rezultat kao postotakJednostavno pomnožite sa 100. Na primjer, ako kupite 50 dionica po cijeni od 100 dolara (investicija od 5.000 dolara), a dvije godine kasnije one se trguju po cijeni od 110 dolara, vaša pozicija vrijedi 5.500 dolara. Primjenom formule:

ROI = (5.500 – 5.000) / 5.000 = 0,1 → 10%

Ista formula se primjenjuje na cijele kompanije, pojedinačne projekte ili bilo koju investiciju. čiju trenutnu vrijednost možete procijeniti. Trik je u tome da jasno definirate šta smatrate "troškom" i šta podrazumijevate pod "trenutnom vrijednošću" (uključujući ili ne, poreze, naknade, povezane troškove itd.).

Izračunavanje ROI u Excelu: Struktura tabele

Praćenje povrata ulaganja (ROI) portfelja dionica ili druge imovineExcel (ili Google Sheets) je vrlo praktičan alat. Tipičan način za njegovu organizaciju bio bi:

• Kolona A: ticker ili naziv svake imovine (na primjer, AMZN za Amazon).
• Kolona Bbroj jedinica (dionica, udjela itd.).
• Kolona C: jedinična kupovna cijena.
• Kolona D: početni investicioni trošak, izračunat kao B × C.
• Kolona E: trenutna kotirana cijena.
• Kolona F: trenutna vrijednost pozicije, B × E.
• Kolona GROI u procentima za svaki red.

Na primjer, za red 2, formula za ROI kao postotak mogla bi biti =((F2-D2)/F2)*100 ili alternativno =((F2-D2)/D2)*100 U zavisnosti od toga da li želite da profit povežete sa trenutnom vrijednošću ili početnim troškom (klasična opcija se zasniva na početnom trošku), onda biste jednostavno povukli formulu prema dolje tako da se automatski izračunava za svaku investiciju.

Jedino ručno održavanje u čistoj Excel tabeli To uključuje ažuriranje tržišnih cijena u koloni E. U Google tabelama, ovo možete djelimično automatizirati pomoću funkcija poput Google Finance, koje pružaju kotacije u stvarnom ili gotovo stvarnom vremenu, što znatno pojednostavljuje praćenje povrata ulaganja bez potrebe za ručnim unosom podataka svaki put, ili uz pomoć umjetne inteligencije kao što je Koristite Claude u Excelu.

Jedino ručno održavanje u čistoj Excel tabeli To uključuje ažuriranje tržišnih cijena u koloni E. U Google tabelama to možete djelomično automatizirati pomoću funkcija poput GOOGLEFINANCE, koje pružaju kotacije u stvarnom ili gotovo stvarnom vremenu, što znatno olakšava praćenje povrata ulaganja bez potrebe za ručnim unosom podataka svaki put.

ROI u investicijama s leveridžom

Kada se u obzir uzme leveridž, izračunavanje ROI-a postaje zanimljivije.Levering znači ulaganje samo dijela novca i finansiranje ostatka (na primjer, hipotekom ili derivativnim proizvodom), ali s ostvarivanjem prinosa od punog iznosa.

Ovaj mehanizam umnožava i dobitke i gubitkeTo je tipično za derivate (fjučersi, opcije), a također je vrlo uobičajeno na tržištu nekretnina, gdje kupujete stan s hipotekom doprinoseći samo djelićem vrijednosti.

Zamislite da kupite nekretninu za 100.000 eura Finansirano hipotekom, tako da uplaćujete samo 20.000 eura vlastitog novca. Ako se dodaju troškovi renoviranja i dodatnih 5.000 eura, vaš ukupni izdatak iznosi 25.000 eura. Ako zatim prodate stan za 140.000 eura, bruto kapitalni dobitak iznosi 40.000 eura.

Ako izračunate povrat ulaganja (ROI) na novac koji ste uložiliFormula bi bila otprilike ovakva: (40.000 – 25.000) / 25.000 × 100 = 60%. U praksi biste morali prilagoditi poreze, provizije i ostale troškove, ali primjer služi da pokaže kako leveridž značajno povećava procentualni prinos na vaš vlastiti kapital.

Šta učiniti ako je ROI negativan

Kada je ROI negativan, to jest, izgubili ste novac.Preporučljivo je smireno analizirati uzroke prije donošenja odluke o tome hoćete li održati ili zatvoriti investiciju. Uobičajeni razlozi uključuju:

• Loš izbor imovine: neuspjeh u otkrivanju da kompanija gubi kupce, da se stanje u susjedstvu pogoršava ili da za neki projekat nije postojala stvarna potražnja.
• Nepredviđeni vanjski faktoriekonomske krize, regulatorne promjene, vanredni događaji koji utiču na vrijednost imovine.
• Privremeni gubici povezani s ekonomskim ciklusom: korekcije tržišta, privremeni padovi cijena itd.

Ne postoje univerzalni receptiMeđutim, vrlo općenito govoreći, ako je problem strukturne prirode (loš investicijski izbor ili uporni nepovoljni faktori), možda ima smisla prihvatiti gubitak i preraspodijeliti kapital. Ako se radi o privremenom padu vrijednosti solidne imovine, zadržavanje (ili čak kupovina veće količine) s dugoročnom perspektivom može u konačnici biti najbolji način djelovanja.

IRR u Excelu: stopa koja čini neto sadašnju vrijednost jednakom nuli

Interna stopa povrata (IRR) To je još jedna osnovna metrika profitabilnosti, koja se široko koristi za vrednovanje investicionih projekata. Predstavlja stopu po kojoj se budući novčani tokovi diskontiraju tako da neto sadašnja vrijednost investicije (NSV) postane tačno jednaka nuli.

U praktičnom smislu, IRR odgovara na ovo pitanje"Koliki godišnji procentualni prinos (ili prinos po periodu) generira ova investicija, uzimajući u obzir sve njene prihode i isplate tokom vremena?" Što je IRR veći, to je investicija obično atraktivnija, pod uslovom da je uporedite sa istim vremenskim horizontom i profilom rizika.

Matematički koncept IRR-a

Ako IRR izrazimo u obliku formule, vidjet ćemo da se pojavljuje i u nazivniku i u eksponentu. budućih novčanih tokova, što otežava ručno izračunavanje. Zato se koriste iterativne metode (pokušaji i greške) ili korištenje finansijskih kalkulatora i, prije svega, Excela.

Logika je jednostavna: sabirate buduće novčane tokove diskontirane po određenoj stopiOduzimate početnu investiciju i pitate se po kojoj stopi je ukupan rezultat nula. Ta vrijednost je IRR (Internal Replacement Stopa). Proces pronalaženja stope se vrši iterativno, i upravo tu Excel obavlja sav težak posao za vas.

Kako koristiti funkciju IRR u Excelu

Da biste primijenili IRR u Excelu, prvo morate pripremiti dijagram toka ulaganja., pri čemu se plaćanja (isplate) prikazuju s negativnim predznakom, a prihodi (povrati) s pozitivnim predznakom, hronološkim redom i u redovnim intervalima (na primjer, godišnje ili mjesečno).

Sintaksa funkcije IRR u Excelu je =IRR(vrijednosti; ) ili, u engleskim verzijama, =IRR(vrijednosti, )Ima dva argumenta:

• vrijednosti: raspon koji sadrži sve novčane tokove investicije, počevši od početne investicije (obično negativne) pa sve do primitaka.
• procjena: opcionalna procjena očekivane IRR (na primjer, 0,1 za 10%). Ako je ne unesete, Excel će prema zadanim postavkama pretpostavljati 10%.

U praksi je dovoljno jasno naznačiti raspon novčanih tokova tako da Excel automatski vraća IRR. Ako je investicija "normalna" (jedan početni izdatak nakon kojeg slijede samo pozitivni prinosi), izračun je obično vrlo stabilan i funkcija glatko konvergira.

Ključne tačke i mere predostrožnosti pri korišćenju IIR-a

Postoji nekoliko tehničkih detalja koje ne treba zanemariti. kada koristite funkciju IRR u Excelu:

1. Mora postojati barem jedan negativni tok i jedan pozitivan tok.Ako sve vrijednosti imaju isti znak, Excel ne može izračunati IRR i vratit će grešku.
2. Tokovi moraju biti u redoslijedu kojim se javljajuBudući da funkcija pretpostavlja redovnu periodičnost (mjesec po mjesec, godinu po godinu itd.), ne navodite datume, tako da je redoslijed ćelija jedina vremenska referenca.
3. Funkcija obrađuje samo brojeve. i zanemaruje tekst, prazne ćelije ili vrijednosti koje nisu numeričke unutar raspona.
4. U projektima s novčanim tokovima koji mijenjaju predznak nekoliko puta (Na primjer, investicije koje zahtijevaju dodatne doprinose u kasnijim godinama), može postojati više od jedne moguće IRR. U takvim slučajevima, početna pretpostavka (procjena) može utjecati na rezultat, te može biti poželjnije koristiti NPV i druge metrike.

Sa ekonomskog stanovišta, IRR je veoma koristan za odlučivanje da li projekat prelazi minimalni prag profitabilnosti. (na primjer, trošak kapitala kompanije). Ako je IRR veći od tog troška, ​​projekat stvara vrijednost; ako je niži, uništava je.

Praktični izračun IRR-a s jednostavnim primjerom

Pretpostavimo početnu investiciju od -10.000 dolara i pet godišnjih rata od 3.000 dolara.U Excelu biste mogli smjestiti ove vrijednosti u kolonu, na primjer od B1 do B6:

1. B1: -10000 → početna investicija.
2. B2:B6: 3000 → pet godišnjih plaćanja istog iznosa.
3. U drugoj ćeliji napišite: =TIR(B1:B6) i pritisnete Enter.

Excel će interno ponavljati sve dok ne pronađe stopu prinosa. što neto sadašnju vrijednost (NSV) ovih novčanih tokova čini nulom. Rezultat će biti IRR te investicije, koju možete uporediti sa svojom minimalno prihvatljivom stopom povrata ili sa drugim investicijskim alternativama.

Godišnja IRR i APR: uzmite u obzir periodičnost

Sam IRR ne zna da li su periodi koje koristite mjeseci, kvartali ili godine.Uzima u obzir samo "n" perioda. Stoga, ako upoređujete projekte s različitim periodima (mjesečno vs. dvomjesečno vs. godišnje), morate dovesti njihove IRR-ove na zajedničku godišnju osnovu da bi poređenje bilo validno.

Za pretvaranje periodične IRR u godišnju IRR (APR)Koristi se koncept složene kamate. Na primjer, ako projekat ima IRR od 7,39% svaka dva mjeseca, a postoji šest dvomjesečnih perioda u godini, godišnja kamatna stopa (APR) bi bila:

TAE projekat 1 = (1 + IRR_projekat1)^6 – 1

Ako drugi projekat ima IRR od 7,14% mjesečnoPostoji 12 perioda u godini. APR bi bio:

TAE projekat 2 = (1 + IRR_projekat2)^12 – 1

Kao što vidite, 7,14% mjesečno nije isto što i 7,39% svaka dva mjeseca.Kada se izračuna na godišnjem nivou, razlike postaju vrlo očigledne. Međutim, ako pretpostavimo da su oba projekta mjesečna (na primjer, prvi traje 6 mjeseci s mjesečnim novčanim tokovima), formula za projekat 1 bi također bila (1+IRR)^12 – 1, što bi rezultiralo još višom godišnjom kamatnom stopom (oko 135,28% u opisanom primjeru).

MIRR (TIRM) i XIRR: Napredne verzije IRR-a u Excel-u

Excel se ne zaustavlja na osnovnoj IRR stopi.Uključuje i druge funkcije kao što su MIRR (TIRM na španskom) i XIRR (TIR.NO.PER u nekim verzijama) koje poboljšavaju analizu kada stvarnost ne odgovara jednostavnom modelu periodičnih tokova ili kada želite uzeti u obzir troškove finansiranja i stopu reinvestiranja.

MIRR (modificirana interna stopa povrata) uključuje To uključuje i kamatnu stopu po kojoj pozajmljujete novac i kamatnu stopu po kojoj reinvestirate međuzaradu. Njena sintaksa je:

=MIRR(vrijednosti; finansijska_stopa; stopa_reinvestiranja)

• vrijednostinegativni i pozitivni novčani tokovi.
• finansijska_stopatrošak duga ili novac korišten za finansiranje investicije.
• stopa reinvestiranjaprofitabilnost po kojoj možete reinvestirati novčane tokove koje primate.

MIRR obično pruža realističniji prikaz profitabilnosti projekta u odnosu na klasičnu IRR, posebno kada se trošak kapitala i profitabilnost reinvestiranja značajno razlikuju.

Funkcija XIRR je dizajnirana za novčane tokove na neredovne datumeUmjesto pretpostavke konstantnih perioda, prosljeđujete mu dva raspona: jedan s novčanim tokovima i drugi s odgovarajućim datumima. Njegova sintaksa je:

=XIRR(novčani_tokovi; datumi; )

Na ovaj način, XIRR diskontira svaki tok koristeći tačan broj dana između datuma.Zbog toga je idealan za investicije gdje prihodi i plaćanja ne prate fiksni mjesečni ili godišnji obrazac.

IRR naspram NPV: kako se uklapaju u donošenje odluka

IRR i NPV (NPV/VNA) su dvije strane iste medalje.Ali svaki nudi drugačiju perspektivu. Nisu ekvivalentni, a razumijevanje kako se međusobno nadopunjuju ključno je za donošenje ispravnijih investicijskih odluka.

NPV izračunava sadašnju vrijednost niza novčanih tokova diskontirano po fiksnoj stopi koju odaberete (na primjer, trošak kapitala ili referentna stopa). Rezultat je u novčanim jedinicama i govori vam koliko neto vrijednosti projekat stvara (ili uništava) danas.

S druge strane, IRR je stopa koja tu neto sadašnju vrijednost čini jednakom nuli.Drugim riječima, umjesto da se pitate: "Koliko ovaj projekat danas vrijedi sa 10% popusta?", pitate se: "Koju diskontnu stopu bih morao koristiti da bi prestao stvarati dodatnu vrijednost?" Ta stopa je interna stopa povrata investicije.

Šta je bolje? Zavisi od konteksta.:

• IRR je veoma koristan za poređenje relativne efikasnosti projekata.Zato što se izražava kao postotak i intuitivan je. Često se koristi da se vidi da li investicija prelazi minimalni prag profitabilnosti.
• NPV je nenadmašna za mjerenje apsolutne vrijednosti koju projekat donosi.Zato što vam direktno govori koliko novčanih jedinica sadašnje vrijednosti generira. Da biste maksimizirali bogatstvo, teoretski biste uvijek trebali odabrati projekat s najvećom neto sadašnjom vrijednosti (NSV), ne nužno i s najvećom internom stopom prinosa (IRR).

U praksi, najrazumniji pristup je korištenje oba indikatora zajedno.Provjerite da li IRR premašuje trošak kapitala i istovremeno dajte prioritet projektima s najvećom neto sadašnjom vrijednošću (NSV) unutar ograničenja kapitala, rizika i vremena koja imate.

Dobro integriranje ovih formula u vaše Excel proračunske tablice Transformira analizu investicija od skoka vjere u sistematski proces, omogućavajući vam da uporedite i prilagodite pretpostavke i vidite kako se ROI, NPV i IRR mijenjaju kada modificirate diskontne stope, iznose investicija ili rasporede novčanog toka. Uz malo vježbe, postaje neophodan alat za svaku ozbiljnu finansijsku odluku.