Com calcular la TIR a Excel: 4 millors mètodes el 2023

Si voleu prendre decisions informades cap a plans d'inversió sistemàtics (SIP) o invertir en projectes empresarials com a capitalista de risc, heu d'aprendre a calcular la TIR a Excel.

Hi ha moltes maneres de calcular el retorn de la inversió (ROI) de les inversions realitzades en projectes financers o empresarials. Tot i que un càlcul basat en una taxa fixa és bastant fàcil, no sempre és l'eina per al càlcul del ROI en grans empreses o en banca d'inversió.

Suposem que invertiu una suma de diners en un pla d'inversió durant els propers 20 anys. El pla diu que obtindreu un retorn cada trimestre. Ara, si és intel·ligent invertir en aquest pla o no, es pot decidir a partir del valor IRR del SIP. Com més gran sigui la TIR, més lucratiu és el pla d'inversió. El mateix passa amb totes les altres inversions intensives en capital en empreses, comptes d'estalvi bancari, fons d'inversió, 401 (k), etc.

Perquè no confieu en els altres quan es tracta d'invertir diners guanyats amb esforç, us ajudo a aprendre què és la TIR a Excel, les seves diverses fórmules modificades i com calcular la TIR a Excel de diverses maneres sense esforç.

Quina és la taxa interna de rendibilitat (IRR)?

La TIR o taxa interna de rendibilitat és una mètrica comuna utilitzada pels planificadors financers per calcular o predir la rendibilitat futura d'un projecte empresarial potencial o d'altres tipus d'inversions. En termes financers, es coneix popularment com a taxa de rendibilitat econòmica o taxa de retorn de flux d'efectiu descomptat.

La taxa de TIR o la taxa de descompte d'inversió fa que tot el valor actual net de tot tipus de fluxos d'efectiu com les sortides i entrades siguin iguals a zero. Els professionals financers anomenen aquesta taxa interna de rendibilitat perquè aquest càlcul no té en compte altres factors que puguin afectar positivament o negativament el negoci en els propers anys.

Per exemple, la TIR no té en compte el cost del capital, la taxa d'inflació al llarg del temps, els riscos financers, etc., a l'hora de predir la rendibilitat d'un projecte empresarial o d'una inversió financera.

En poques paraules, sempre que decidiu invertir durant un període prolongat en qualsevol projecte comercial, fons d'inversió o pla d'inversió per a la jubilació, esbrineu la TIR d'aquestes opcions d'inversió. A continuació, trieu la que ofereixi la màxima TIR entre totes les opcions.

Llegiu també: Millors plantilles de pressupost d'Excel

Quines són les funcions IRR a Microsoft Excel?

Gràcies a Microsoft, no cal crear diverses fórmules ni passar per càlculs complexos per obtenir la TIR d'un pla d'inversió. Excel té tres funcions internes de càlcul de la taxa de rendiment. Obteniu informació sobre aquestes funcions aquí:

1. TIR per a la taxa interna de rendibilitat simple

La sintaxi IRR a Excel calcula el tipus d'interès d'una inversió en un projecte empresarial o fons d'inversió o fons de planificació de jubilació. La inversió ha d'incloure almenys un valor positiu i un negatiu. A més, el flux d'efectiu al fons s'ha de produir a intervals regulars. Així és com es veu la fórmula:

=IRR(valors o intervals de cel·les, [argument endevinat])

• Els valors són una entrada necessària per a la funció IRR. Pot ser una sèrie de números, una matriu de números o un rang de cel·les de referència format per fluxos d'efectiu.
• L'argument de l'endevina és opcional. Si no proporcioneu un valor de Guess, l'Excel el considera una taxa interna de rendiment de 0,1, 10% o 10 punts bàsics requerida pel pla d'inversió. Si proporcioneu un argument Guess, Excel executarà els càlculs per a un màxim de 20 sessions iteratives per trobar un valor IRR proper al valor Guess.

2. XIRR per a desigualtats en el moment de les devolucions

Si necessiteu tenir en compte el calendari de fluxos d'efectiu, heu d'utilitzar la funció XIRR. La sintaxi és la següent:

=XIRR(valors o intervals de cel·les, dates, [argument d'endevinar])

3. MIRR per a la reinversió dels fons del projecte

MIRR proporciona valors de IRR altament fiables en comparació amb la funció IRR simple. La TIR no té en compte el cost del capital que invertiu. Per tant, el valor final de la TIR no és precís. En lloc d'això, podeu utilitzar la funció MIRR per incloure el cost d'un factor de capital en el càlcul de la TIR. Per tant, la sintaxi esdevé la que es mostra a continuació:

=MIRR(valors o intervals de cel·les, taxa_finançada, taxa_reinversió)

• Valors és l'estat de fluxos d'efectiu que consta d'almenys un fluxos d'efectiu positius i negatius.
• Finance_rate és l'interès que pagueu al prestador amb els diners prestats per a un projecte empresarial.
• Reinvest_rate és el tipus d'interès que rep el vostre negoci o fons d'inversió sobre els fluxos d'efectiu després de la reinversió.

Llegiu també: Com utilitzar la fórmula de resta d'Excel

Com calcular la IRR a Excel: utilitzant la sintaxi IRR

Abans de poder començar a calcular la TIR per a diversos plans o projectes d'inversió, heu d'organitzar les dades a Excel. A continuació es mostra com es fa:

• Obriu un full de treball d'Excel i creeu tres capçaleres de columnes: Període , Descripció i Fluxos d'efectiu .
• Empleneu les dades que voleu que es calculin a les respectives capçaleres de columna.

Utilitzant la fórmula IRR a Excel

• La primera inversió hauria de ser un valor negatiu, ja que esteu pagant a un fons d'inversió o empresari per utilitzar els diners.
• Si obtindreu ingressos regulars després d'un mes o un any, aquests ingressos s'han d'afegir com a valors positius.

Apreneu a calcular la TIR a Excel utilitzant la sintaxi IRR

• Ara, utilitzeu la fórmula següent en una cel·la on voleu que Excel calculi el valor de la TIR:

=IRR(C2:C8)

• Premeu Enter per obtenir el ROI d'una inversió potencial.

Aquest càlcul de la TIR es basa en un argument de Guess predeterminat del 10%. Suposem que esteu buscant un rendiment del 12%. A continuació s'explica com heu d'introduir el valor d'endevinar:

=IRR(C2:C8,30%)

• A la taula anterior, veieu que l'últim flux d'efectiu és negatiu. Per tant, el flux de caixa ha de canviar de positiu a negatiu o de negatiu a positiu.

Mireu Com calcular la TIR a Excel utilitzant el valor de conjectura de la TIR

• En aquest escenari, utilitzeu una Endevinació per trobar el resultat precís, ja que IRR produirà dos resultats.

Com calcular la TIR a Excel quan s'utilitza un valor de Guess negatiu

• L' argument de Guess hauria de ser el ROI de la inversió que espereu.

Com calcular la TIR a Excel: utilitzant la sintaxi XIRR

Quan obteniu rendiments en dates diferents després de la inversió inicial, l'ús de la sintaxi IRR donarà resultats defectuosos. En aquest escenari, heu d'utilitzar la sintaxi XIRR. Aquests són els passos que podeu provar al vostre propi full de treball d'Excel:

• Creeu una capçalera de columna per a la Data de devolució al costat dret de la columna Fluxos d'efectiu .
• Ara, introduïu la data d'inversió a la part superior i, a continuació, introduïu la data esperada dels crèdits d'interès o de retorn.

Descobriu com calcular la TIR a Excel utilitzant la sintaxi XIRR

• Un cop fet, utilitzeu la sintaxi XIRR següent a qualsevol cel·la per obtenir la taxa interna de retorn de la inversió amb un calendari de pagament de retorn irregular:

=XIRR(C2:C8;D2:D8)

Com calcular la TIR a Excel: utilitzant la sintaxi MIRR

Per fer que el valor de la TIR sigui més realista, podeu utilitzar la fórmula MIRR. Aquí podeu incloure dos factors obligatoris: el cost del capital i la taxa de reinversió. A continuació trobareu els passos per utilitzar MIRR a Excel:

• Al full de treball actual, afegiu dues files d'informació tal com es mostra a la imatge.

Ús de la funció MIRR a Excel

• Aquests són el cost del capital i la taxa de reinversió .

Apreneu a calcular la TIR a Excel utilitzant la sintaxi MIRR

• A les seves respectives cel·les, introduïu també els valors.
• Ara, utilitzeu la fórmula següent per obtenir una taxa interna de rendibilitat realista:

=MIRR (C2:C8,G2,G3)

Com calcular la TIR a Excel: utilitzant Goal Seek Excel

L'IRR i les seves fórmules modificades realitzen 20 càlculs iteratius abans de mostrar un resultat. Si necessiteu una precisió més gran que aquesta, podeu utilitzar la funció de recerca d'objectius d'Excel per calcular la TIR. En aquest mètode, Excel realitza fins a 32.000 càlculs iteratius abans de generar un valor de retorn. Aquí teniu les instruccions per utilitzar la cerca d'objectius i calcular la TIR a Excel:

• Estableix IRR a l'11% o qualsevol altra cosa manualment.

Càlcul del VAN a Excel

• Calcula el VAN per sota de la TIR utilitzant aquesta fórmula:

=NPV(C9,C3:C8)+C2

• Ara, feu clic a la pestanya Dades i, a continuació, seleccioneu Anàlisi què passa si al grup d'ordres Previsió al menú de la cinta d'Excel.
• Seleccioneu Cerca d'objectius al menú contextual que apareix.
• Aquí, la cel·la Set hauria de ser el valor NPV. Aleshores, el valor To ha de ser 0.

Realitzeu la cerca d'objectius per calcular la TIR a Excel

• Estableix Canviant la cel·la al valor IRR.
• Només cal que introduïu les referències de cel·la tal com es mostra a la imatge.

Resultats de la cerca d'objectius per al càlcul de la TIR a Excel

• Ara, feu clic a D'acord per calcular la TIR mitjançant la cerca d'objectius.
• Segons el conjunt de dades actual, el VAN serà de 0,00 si la TIR és del 12%.

En el mètode de recerca d'objectius, en realitat no cal calcular la TIR a partir del flux d'efectiu i la quantitat d'inversió. En lloc d'això, establiu una TIR esperada a la cel·la TIR i l'utilitzeu per calcular el NPV mitjançant el flux d'efectiu i la quantitat d'inversió. A continuació, utilitzeu la capacitat de càlcul iteratiu d'Excel per predir quan el NPV es converteix en 0,00 a quin valor de TIR.

Consells per calcular correctament la TIR a Excel

Recordeu aquests consells i fets quan aneu a calcular la TIR a Excel per obtenir percentatges de TIR fiables:

• L'argument de la part Valors ha de contenir uns ingressos (flux d'efectiu positiu) i una despesa (flux d'efectiu negatiu) perquè la funció funcioni amb precisió.
• La funció IRR només processarà els números de l'argument Valors. Ignorarà qualsevol altre element com els valors lògics, les cel·les buides i els textos.
• Hi ha d'haver intervals regulars de fluxos d'efectiu com ara setmanal, mensual, trimestral, anual, etc. No és obligatori que els fluxos d'efectiu siguin iguals en valor en dòlars.
• Assegureu-vos de preparar la taula de fluxos d'efectiu en ordre cronològic. Per exemple, si pagueu 100 dòlars cada mes, organitzeu els valors del flux d'efectiu i les seves dates de gener, febrer, març, etc.
• En els càlculs simples de TIR, no és obligatori introduir un argument Guess.
• Quan s'utilitza un valor Guess, la fórmula IRR mostrarà el valor més proper a l'argument Guess. Si mostra #NUM! Error, canvieu el valor de l'argument Guess.

Quines són les limitacions de la TIR a Excel?

Abans de poder aplicar el valor de la TIR calculat a Excel en situacions del món real, també hauríeu de conèixer les limitacions inherents a la fórmula interna de la taxa de rendibilitat a Excel:

• La TIR és una expressió del ROI en percentatge. No té en compte el valor absolut. Així, pot mostrar un percentatge de rendibilitat més elevat per a un projecte de baix valor en dòlars. Tanmateix, els capitalistes de risc i els inversors individuals busquen projectes de major valor en dòlars, tot i que el percentatge de ROI és menor que els projectes petits.
• La fórmula TIR retorna un valor tenint en compte que el projecte reinvertirà el seu capital o flux d'efectiu al mateix ritme que la TIR calculada. Això no sempre és possible perquè les taxes de retorn fluctuen molt al mercat de capitals.
• Si el pla d'inversió o projecte implica alternar fluxos d'efectiu positius i negatius, és possible que obtingueu més d'una TIR per al mateix projecte.

Conclusió

Ara ja saps com calcular la TIR a Excel utilitzant diverses sintaxis de TIR dedicades. A més, heu après a utilitzar Excel Goal Seek per calcular la TIR a Excel. Si m'he perdut algun altre mètode, no oblideu esmentar-ho al quadre de comentaris a continuació.

A continuació,  com utilitzar la fórmula IF-THEN d'Excel .