Fórmulas de EMI, ROI, NPV y IRR en Excel paso a paso

Dominar las fórmulas de Excel para EMI, ROI, NPV y TIR es una de las formas más rápidas de pasar de mirar números sin más a tomar decisiones de inversión con cabeza. Con unas cuantas funciones bien entendidas, puedes evaluar préstamos, comparar proyectos, analizar campañas de marketing o revisar la rentabilidad de tu cartera, aprovechando recursos como la formación Office certificada.

En las siguientes líneas vas a ver, con todo lujo de detalles, cómo se calculan y cómo se usan en la práctica conceptos clave como el ROI, el Valor Neto Actual (VAN/NPV), la Tasa Interna de Retorno (TIR/IRR) y sus variantes en Excel. Además, enlazaremos estas métricas con ideas como el apalancamiento, la comparación de proyectos con distintas periodicidades y la interpretación de los resultados cuando las cosas no salen tan bien como esperabas.

NPV / VNA en Excel: valor actual de tus inversiones

El Valor Neto Actual (VAN o NPV, en Excel en español VNA) es una función que te dice cuánto vale hoy una inversión a partir de una tasa de descuento y una serie de flujos de caja futuros. Es decir, actualiza (descuenta) los cobros y pagos que tendrás en el futuro para expresarlos en términos de dinero de hoy.

La sintaxis de la función VNA en Excel en español es muy sencilla: VNA(tasa; valor1; ; …). La «tasa» es el tipo de descuento por período (por ejemplo, anual) y los argumentos «valor1, valor2, …» son los flujos de caja (negativos si son pagos, positivos si son cobros) ordenados en el tiempo.

Ten en cuenta que Excel interpreta los valores en el orden en que los introduces: el primer flujo de caja de la lista corresponde al final del primer período, el segundo al final del segundo período y así sucesivamente. Por eso es clave que respetes el orden cronológico de los flujos de caja cuando construyas la fórmula.

En cuanto al tipo de datos, VNA ignora todo lo que no sean números válidos. Si en los argumentos hay celdas vacías, valores lógicos, textos que no pueden convertirse a número o errores, la función los pasa por alto y solo trabaja con los valores numéricos de las referencias o rangos que le hayas indicado, por lo que es útil dar formato de datos en Excel 365.

Hay un detalle muy importante sobre el momento en que empieza la inversión: por definición, el cálculo de VNA asume que la inversión arranca un período antes del primer flujo de caja que incluyes en la lista y termina con el último flujo de esa misma lista. Si el primer flujo de caja se produce al principio del primer período (es decir, en el momento cero), normalmente tendrás que sumar ese importe por separado al resultado de la función VNA, porque la función solo descuenta flujos «futuros» al final de cada período.

Relación entre VNA, VA y TIR

VNA y VA (Valor Actual) son parientes cercanos pero no idénticos. Mientras que VNA está pensada para trabajar con flujos de caja que pueden variar en cada período, VA se usa para anualidades donde los flujos son constantes y, además, te deja elegir si los pagos/cobros se hacen al inicio o al final de cada período.

La conexión con la TIR (IRR en Excel) es todavía más directa: la TIR es precisamente la tasa de descuento que hace que el VAN sea igual a cero. Es decir, si calculas la TIR de una serie de flujos de caja y luego usas esa tasa como «tasa» en VNA, el resultado teórico debe ser 0: VNA(TIR(…); … ) = 0. Esta relación es la base de muchas decisiones de inversión.

Ejemplo básico de VNA en Excel

Imagina una inversión con una tasa de descuento anual del 10 %, un desembolso de -10.000 dentro de un año y cobros posteriores de 3.000, 4.200 y 6.800 en los años siguientes. En una hoja de Excel, podrías organizarlo así (en una columna):

• A2: 0,10 → tasa de descuento anual.
• A3: -10000 → coste inicial que se produce un año después de hoy.
• A4: 3000 → flujo de caja del año 1.
• A5: 4200 → flujo del año 2.
• A6: 6800 → flujo del año 3.

La fórmula para el VAN de estos flujos sería =VNA(A2;A3;A4;A5;A6). El resultado, con estos datos, es aproximadamente 1.188,44 unidades monetarias, lo que indica que, descontando al 10 %, la inversión genera un valor actual positivo y, por tanto, es aceptable según este criterio.

Ejemplo ampliado de VNA con rango de celdas

Supón ahora un proyecto con una inversión inicial de 40.000, como en un ejemplo de plan financiero (flujo negativo) y cobros de 8.000, 9.200, 10.000, 12.000 y 14.500 en los cinco años siguientes, con una tasa de descuento del 8 %. Podrías estructurar los datos así:

• A2: 0,08 → tasa anual de descuento (por ejemplo, inflación o coste de oportunidad).
• A3: -40000 → desembolso inicial.
• A4:A8: 8000; 9200; 10000; 12000; 14500 → cobros anuales.

Una forma habitual de calcular el VAN en este caso es aplicar VNA solo a los flujos futuros positivos y luego sumar la inversión inicial: =VNA(A2;A4:A8)+A3. Con los valores indicados, obtendrás un VAN positivo en torno a 1.922,06, lo que sugiere que el proyecto merece la pena.

Si se produce un flujo adicional negativo en un año posterior, por ejemplo una pérdida o gasto extra de -9.000 en un hipotético sexto año, puedes incluirlo en la lista de flujos de caja: =VNA(A2;A4:A8;-9000)+A3. El resultado será un VAN negativo (alrededor de -3.749,47), lo que ya señalaría que ese coste adicional vuelve el proyecto menos atractivo o directamente inviable bajo esa tasa de descuento.

ROI: cómo medir el retorno de una inversión con y sin Excel

El ROI (Return on Investment, o retorno de la inversión) es probablemente el indicador de rentabilidad más popular en el día a día. Su objetivo es medir cuánta ganancia has obtenido en relación con lo que te ha costado la inversión, normalmente expresado en porcentaje.

Se utiliza en prácticamente cualquier contexto donde haya un desembolso y un resultado económico: inversiones en bolsa, proyectos empresariales, operaciones inmobiliarias, campañas de marketing, publicidad digital, etc. Precisamente por ser tan intuitivo y fácil de calcular, se ha convertido en una especie de estándar para comparar opciones.

Qué es exactamente el ROI y por qué importa

El ROI responde a una pregunta muy directa: «por cada euro que he puesto, ¿cuánto he ganado o perdido?». Así, permite comparar la eficacia relativa de distintos activos, proyectos o estrategias sin necesidad de entrar, de primeras, en un análisis financiero avanzado.

Su importancia radica en que te da una cifra objetiva con la que tomar decisiones: puedes elegir entre varios proyectos, priorizar campañas publicitarias, valorar si merece la pena mantener una inversión o rotar hacia otra con mayor potencial. No sustituye al análisis cualitativo, pero sí aporta una base numérica común.

Eso sí, el ROI tiene limitaciones claras: no tiene en cuenta el tiempo que dura la inversión, ignora el riesgo, no dice nada sobre la volatilidad, y puede inducir a errores si se interpreta aisladamente. Por eso suele combinarse con otros indicadores (NPV, TIR, ratio de Sharpe, etc.) y con la experiencia del inversor.

Fórmula del ROI y ejemplo sencillo

La expresión básica del ROI es muy simple:

ROI = (Valor actual de la inversión – Coste de la inversión) / Coste de la inversión

Si quieres expresar el resultado en porcentaje, basta con multiplicar por 100. Por ejemplo, si compras 50 acciones a 100 $ (inversión de 5.000 $) y dos años después cotizan a 110 $, tu posición vale 5.500 $. Aplicando la fórmula:

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

Esta misma fórmula se aplica a empresas completas, proyectos aislados o cualquier inversión cuyo valor actual puedas estimar. El truco está en definir bien qué consideras «coste» y qué entiendes por «valor actual» (incluyendo, o no, impuestos, comisiones, gastos asociados, etc.).

Cálculo del ROI en Excel: estructura de la hoja

Para controlar el ROI de una cartera de acciones u otros activos, Excel (o Google Sheets) es una herramienta muy cómoda. Una forma típica de organizarlo sería:

• Columna A: el ticker o nombre de cada activo (por ejemplo, AMZN para Amazon).
• Columna B: número de unidades (acciones, participaciones, etc.).
• Columna C: precio de compra unitario.
• Columna D: coste inicial de la inversión, calculado como B × C.
• Columna E: precio actual de cotización.
• Columna F: valor actual de la posición, B × E.
• Columna G: ROI en porcentaje para cada fila.

Para la fila 2, por ejemplo, la fórmula del ROI en porcentaje podría ser =((F2-D2)/F2)*100 o, alternativamente, =((F2-D2)/D2)*100 según si quieres relacionar la ganancia con el valor actual o con el coste inicial (la opción clásica es sobre el coste inicial). Después, solo tendrías que arrastrar la fórmula hacia abajo para que se calcule automáticamente para cada inversión.

El único mantenimiento manual en un Excel puro es ir actualizando los precios de mercado en la columna E. En Google Sheets puedes automatizarlo parcialmente con funciones como GOOGLEFINANCE, que traen cotizaciones en tiempo real o casi real, facilitando mucho el seguimiento del ROI sin tener que introducir datos a mano cada vez, o con ayuda de IA como usar Claude en Excel.

El único mantenimiento manual en un Excel puro es ir actualizando los precios de mercado en la columna E. En Google Sheets puedes automatizarlo parcialmente con funciones como GOOGLEFINANCE, que traen cotizaciones en tiempo real o casi real, facilitando mucho el seguimiento del ROI sin tener que introducir datos a mano cada vez.

ROI en inversiones con apalancamiento

Cuando entra en juego el apalancamiento, el cálculo del ROI se vuelve más interesante. Apalancarse significa poner solo una parte del dinero y financiar el resto (por ejemplo, con un préstamo hipotecario o un producto derivado), pero disfrutar de los resultados sobre el importe total.

Este mecanismo multiplica tanto las ganancias como las pérdidas. Es típico en derivados (futuros, opciones) y también muy habitual en el mercado inmobiliario, donde se compra un piso con hipoteca aportando solo una fracción del valor.

Imagina que compras un inmueble por 100.000 € financiado con una hipoteca, de manera que solo pones 20.000 € de tu bolsillo. Sumando reformas y gastos por otros 5.000 €, tu desembolso efectivo es de 25.000 €. Si luego vendes el piso en 140.000 €, la plusvalía bruta es de 40.000 €.

Si calculas el ROI sobre el efectivo que has aportado, la fórmula sería algo así como: (40.000 – 25.000) / 25.000 × 100 = 60 %. En la práctica, habría que ajustar por impuestos, comisiones y demás gastos, pero el ejemplo sirve para ver cómo el apalancamiento dispara el porcentaje de retorno sobre tu capital propio.

Qué hacer si el ROI sale negativo

Cuando el ROI es negativo, es decir, has perdido dinero, conviene analizar con calma las causas antes de decidir si mantener o cerrar la inversión. Entre los motivos habituales están:

• Elección deficiente del activo: no detectar que una empresa está perdiendo clientes, que un barrio se está deteriorando o que un proyecto no tenía una demanda real.
• Factores externos imprevistos: crisis económicas, cambios regulatorios, eventos extraordinarios que afectan al valor del activo.
• Pérdidas temporales ligadas al ciclo económico: correcciones de mercado, bajadas puntuales de cotización, etc.

No hay recetas universales, pero, de forma muy general, si el problema es estructural (mala elección o factores adversos y permanentes), puede tener sentido asumir la pérdida y reorientar el capital. Si se trata de una caída transitoria en un activo sólido, aguantar (o incluso comprar más) con visión de largo plazo puede acabar siendo la mejor decisión.

TIR / IRR en Excel: la tasa que hace que el NPV sea cero

La Tasa Interna de Retorno (TIR, o IRR en Excel en inglés) es otra métrica básica de rentabilidad, muy utilizada para valorar proyectos de inversión. Representa la tasa a la que se descuentan los flujos de caja futuros de manera que el valor actual neto de la inversión (VAN) se hace exactamente igual a cero.

En términos prácticos, la TIR responde a esta pregunta: «¿qué rentabilidad porcentual anual (o por período) genera esta inversión, teniendo en cuenta todos sus cobros y pagos en el tiempo?». Cuanto mayor sea la TIR, más atractiva suele ser la inversión, siempre que la compares con el mismo horizonte temporal y perfil de riesgo.

Concepto matemático de la TIR

Si expresas la TIR en forma de fórmula, verás que aparece en el denominador y en el exponente de los flujos de caja futuros, lo que hace muy difícil despejarla a mano. Por eso se recurre a métodos iterativos (prueba y error) o al uso de calculadoras financieras y, sobre todo, a Excel.

La lógica es simple: sumas los flujos de caja futuros descontados a una cierta tasa, restas la inversión inicial, y te preguntas para qué valor de esa tasa el resultado total es cero. Ese valor es la TIR. El proceso de búsqueda de la tasa se hace por iteraciones, y justo ahí es donde Excel hace el trabajo pesado por ti.

Cómo usar la función TIR en Excel

Para aplicar la TIR en Excel, primero debes preparar el flujograma de la inversión, colocando los pagos (desembolsos) con signo negativo y los cobros (retornos) con signo positivo, en orden cronológico y en intervalos regulares (por ejemplo, anual o mensual).

La sintaxis de la función TIR en Excel es =TIR(valores; ) o, en versiones en inglés, =IRR(values, ). Tiene dos argumentos:

• valores: el rango que contiene todos los flujos de caja de la inversión, empezando por la inversión inicial (normalmente negativa) y siguiendo con los cobros.
• estimar: una conjetura opcional de la TIR esperada (por ejemplo, 0,1 para un 10 %). Si no la pones, Excel asume un 10 % por defecto.

En la práctica, basta con indicar bien el rango de flujos de caja para que Excel devuelva automáticamente la TIR. Si la inversión es «normal» (un único desembolso al principio y luego solo cobros positivos), el cálculo suele ser muy estable y la función converge sin problemas.

Puntos clave y precauciones al usar TIR

Hay varios detalles técnicos que conviene no pasar por alto cuando utilizas la función TIR en Excel:

1. Debe haber al menos un flujo negativo y uno positivo. Si todos los valores son del mismo signo, Excel no puede calcular una TIR y devolverá un error.
2. Los flujos deben estar en el orden en que se producen, ya que la función asume periodicidad regular (mes a mes, año a año, etc.). No le indicas las fechas, así que el orden de las celdas es la única referencia temporal.
3. La función solo procesa números y pasa por alto textos, celdas vacías o valores no numéricos dentro del rango.
4. En proyectos con flujos de signo cambiante varias veces (por ejemplo, inversiones que requieren aportes adicionales en años posteriores), puede haber más de una TIR posible. En esos casos, la conjetura inicial (estimar) puede afectar al resultado, y puede ser preferible usar el VAN y otras métricas.

Desde el punto de vista económico, la TIR es muy útil para decidir si un proyecto supera un umbral mínimo de rentabilidad (por ejemplo, el coste de capital de la empresa). Si la TIR es mayor que ese coste, el proyecto crea valor; si es inferior, lo destruye.

Cálculo práctico de la TIR con un ejemplo sencillo

Supón una inversión inicial de -10.000 $ y cinco cobros anuales de 3.000 $. En Excel, podrías colocar estos valores en una columna, por ejemplo de B1 a B6:

1. B1: -10000 → inversión inicial.
2. B2:B6: 3000 → cinco cobros anuales de la misma cuantía.
3. En otra celda, escribes: =TIR(B1:B6) y presionas Intro.

Excel iterará internamente hasta encontrar la tasa de rentabilidad que hace que el VAN de estos flujos sea cero. El resultado será la TIR de esa inversión, que podrás comparar con tu tasa mínima aceptable o con otras alternativas de inversión.

TIR anualizada y TAE: tener en cuenta la periodicidad

La TIR en sí no sabe si los períodos que estás usando son meses, trimestres o años. Solo ve «n» períodos. Por eso, si comparas proyectos con distinta periodicidad (mensual vs bimestral vs anual), debes llevar sus TIR a una base común anualizada para que la comparación sea válida.

Para pasar una TIR de períodos a una TIR anual (TAE), se utiliza la idea de capitalización compuesta. Por ejemplo, si un proyecto tiene una TIR del 7,39 % bimestral (cada 2 meses), en un año hay 6 períodos de dos meses, así que la TAE sería:

TAE proyecto 1 = (1 + TIR_proyecto1)^6 – 1

Si otro proyecto tiene una TIR del 7,14 % mensual, en un año hay 12 períodos. La TAE sería:

TAE proyecto 2 = (1 + TIR_proyecto2)^12 – 1

Como ves, no es lo mismo un 7,14 % mensual que un 7,39 % bimestral; al anualizar, las diferencias se hacen muy evidentes. En cambio, si asumimos que ambos proyectos son mensuales (por ejemplo, el primero dura 6 meses con flujos mensuales), la fórmula para el proyecto 1 sería también (1+TIR)^12 – 1, dando lugar a una TAE aún mayor (en torno al 135,28 % en el ejemplo descrito).

MIRR (TIRM) y XIRR: versiones avanzadas de la TIR en Excel

Excel no se queda en la TIR básica. Incluye otras funciones como MIRR (TIRM en español) y XIRR (TIR.NO.PER en algunas versiones) que mejoran el análisis cuando la realidad no encaja en el modelo simple de flujos periódicos o cuando quieres tener en cuenta el coste de financiación y la tasa de reinversión.

La MIRR (Tasa Interna de Rentabilidad Modificada) incorpora tanto el tipo de interés al que te financias como el tipo de interés al que reinviertes los cobros intermedios. Su sintaxis es:

=MIRR(valores; tasa_financiera; tasa_reinversión)

• valores: los flujos de caja negativos y positivos.
• tasa_financiera: el coste de la deuda o del dinero utilizado para financiar la inversión.
• tasa_reinversión: la rentabilidad a la que puedes reinvertir los flujos de caja que vas recibiendo.

La MIRR suele proporcionar una visión más realista de la rentabilidad de un proyecto que la TIR clásica, especialmente cuando el coste del capital y la rentabilidad de las reinversiones difieren significativamente.

La función XIRR está pensada para flujos de caja en fechas irregulares. En lugar de asumir períodos constantes, le pasas dos rangos: uno con los flujos de caja y otro con las fechas correspondientes. Su sintaxis es:

=XIRR(flujos_de_efectivo; fechas; )

De este modo, XIRR descuenta cada flujo utilizando exactamente el número de días entre fechas, lo que la hace ideal para inversiones donde los cobros y pagos no siguen un patrón fijo mensual o anual.

TIR frente a VAN: cómo encajan en la toma de decisiones

La TIR y el VAN (NPV/VNA) son dos caras de la misma moneda, pero cada una aporta una perspectiva diferente. No son equivalentes, y entender cómo se complementan es clave para tomar decisiones de inversión más sólidas.

El VAN calcula el valor presente de una serie de flujos de caja descontados a una tasa fija que tú eliges (por ejemplo, el coste de capital o una tasa de referencia). El resultado está en unidades monetarias y te dice cuánto valor neto crea (o destruye) el proyecto hoy.

La TIR, en cambio, es la tasa que hace que ese VAN sea cero. Es decir, en lugar de preguntarte «¿cuánto vale hoy este proyecto al 10 % de descuento?», te preguntas «¿a qué tasa tendría que descontar para que deje de crear valor extra?». Esa tasa es la rentabilidad interna de la inversión.

¿Cuál es mejor? Depende del contexto:

• La TIR es muy útil para comparar la eficiencia relativa de proyectos, porque se expresa como porcentaje y es intuitiva. Suele emplearse para ver si una inversión supera un umbral mínimo de rentabilidad.
• El VAN es imbatible para medir el valor absoluto que aporta un proyecto, porque te dice directamente cuántas unidades monetarias de valor actual genera. Para maximizar riqueza, en teoría deberías elegir siempre el proyecto con mayor VAN, no necesariamente con mayor TIR.

En la práctica, lo más razonable es usar ambos indicadores en conjunto: comprobar que la TIR supera el coste de capital y, al mismo tiempo, priorizar aquellos proyectos con VAN más elevado dentro de las restricciones de capital, riesgo y plazo que tengas.

Tener estas fórmulas bien integradas en tus hojas de cálculo de Excel hace que analizar inversiones deje de ser un acto de fe y pase a ser un proceso sistemático, donde puedes comparar, ajustar supuestos y ver cómo cambian ROI, VAN y TIR al modificar tasas de descuento, montos invertidos o calendario de flujos de caja. Con un poco de práctica, se convierte en una herramienta de cabecera para cualquier decisión financiera mínimamente seria.