Plantilla de inversiones en Excel: cómo armarla y usarla

Una plantilla de inversiones en Excel es un libro ya preparado donde solo tienes que escribir los datos de tus posiciones (qué has comprado, cuánto y a qué precio) y Excel se encarga de calcular el valor actual, la plusvalía y la rentabilidad de cada fila. Sirve para llevar una cartera de acciones, ETFs, fondos, bonos y criptomonedas sin montar las fórmulas desde cero. Al terminar este tutorial sabrás descargar la plantilla, rellenarla con tus posiciones, calcular el precio medio ponderado tras varias compras y ventas, e interpretar la rentabilidad de un proyecto con VAN, TIR y payback. Funciona en Excel 2016 o posterior, en Microsoft 365, y también se puede abrir en Google Sheets y LibreOffice aunque algunos formatos y validaciones se vean distinto.

Qué hace una plantilla de inversiones y qué casos resuelve

Una plantilla de inversiones convierte una idea difusa ("quiero saber si mi cartera va bien") en una tabla ordenada donde cada fila es una posición y cada columna responde a una pregunta: qué compré, cuántas unidades, a qué precio, cuánto vale hoy y cuánto he ganado o perdido.

El caso típico es alguien con cinco, diez o veinte posiciones repartidas entre acciones, ETFs, fondos de inversión, bonos y criptomonedas, que cada mes echa cuentas a mano y se le olvida el precio medio de una compra repetida. La plantilla estandariza esos cálculos: introduce el dato una vez y Excel recalcula todo al cambiar el precio actual.

No hace milagros: si el precio de un activo lo metes a mano cada vez, la plantilla nunca estará al día. Pero mientras esa actualización sea razonable, la plantilla vale para tres cosas concretas: ver la composición de la cartera, saber qué posición gana y cuál pierde, y calcular la rentabilidad de un proyecto aislado (una reforma, un negocio, un alquiler) con sus flujos de caja.

Las hojas que trae y los datos que pide cada una

Una plantilla de inversiones completa suele tener entre seis y ocho hojas. El libro que usamos en este tutorial tiene siete, organizadas para que los datos viajen siempre desde la izquierda: tú escribes en las primeras columnas y Excel devuelve los cálculos en las de la derecha.

Las columnas que escribes tú: activo, nombre, fecha, cantidad, precios, estado

Cada fila de la hoja Cartera representa una posición abierta o cerrada. Los tipos de activo que admite esta plantilla son cinco (Acciones, ETF, Fondos, Bonos y Criptomonedas), y los estados posibles de una posición son tres (En cartera, Vendida y Otra, una categoría abierta para casos atípicos como un traspaso o un cobro en especie).

El dato mínimo para que las fórmulas funcionen es: tipo, nombre, cantidad y precio de compra. El precio actual puede dejarse vacío al crear la fila; mientras esté vacío, Excel mostrará la rentabilidad en blanco, no un error. La fecha es opcional pero recomendable, porque sirve para ordenar movimientos y para el análisis temporal.

Las columnas que calcula Excel: invertido, valor actual, plusvalía y rentabilidad %

Las cuatro columnas calculadas son las que dan sentido a la plantilla. Concretamente, son estas:

=C2*D2
=C2*E2
=F2-G2
=SI.ERROR((F2-G2)/G2;"")

La primera calcula el capital invertido (cantidad por precio de compra); la segunda, el valor actual (cantidad por precio actual); la tercera, la plusvalía (valor actual menos invertido); la cuarta, la rentabilidad porcentual sobre lo invertido, protegida con SI.ERROR por si todavía no hay precio actual.

Descargar la plantilla y abrirla en tu versión de Excel

1. Descarga el archivo .xlsx desde el enlace que acompaña a este tutorial. El archivo está en formato libro de Excel estándar (sin macros), así que se abre en cualquier versión sin avisos de seguridad adicionales.
2. Haz doble clic sobre el archivo descargado para abrirlo con tu versión de Excel. La primera vez, Excel puede pedirte que confirmes el formato: pulsa Sí o Aceptar.
3. Verás siete pestañas. La activa al abrir es Instrucciones; pásate a Cartera para empezar a rellenarla.

Rellenar tu cartera de inversiones fila a fila

La hoja Cartera tiene el encabezado en la fila 1 y espera las posiciones en la fila 2 en adelante. La celda A1 dice "Tipo de activo" y la fórmula de la columna de rentabilidad está en H2, lista para arrastrarse hacia abajo.

Validación del tipo de activo y del estado desde una lista

Para que las filas nuevas no se conviertan en un cajón desastre, las dos primeras columnas y la de estado se rellenan desde una lista desplegable. Los pasos son:

1. Selecciona el rango A2:A1000.
2. Abre Datos > Herramientas de datos > Validación de datos.
3. En Permitir, elige Lista. En Origen, escribe exactamente Acciones;ETF;Fondos;Bonos;Criptomonedas. Pulsa Aceptar.
4. Repite los pasos 1 a 3 para el rango G2:G1000, pero con origen En cartera;Vendida;Otra.

La fórmula de rentabilidad por posición

La rentabilidad por posición mide cuánto has ganado o perdido sobre el dinero que pusiste. La fórmula básica es la diferencia entre el valor actual y lo invertido, dividida por lo invertido:

=(F2*E2-D2*E2)/D2*E2

Espera, hay un error: las cantidades y precios se mezclan. La fórmula correcta sobre la hoja de ejemplo es la diferencia entre valor actual e invertido, dividida por invertido, así:

=SI.ERROR((F2*E2-D2*E2)/(D2*E2);"")

Como cada fila tiene sus propias celdas, la fórmula se arrastra desde H2 hacia abajo con el controlador de relleno. El formato de la columna H debe ser porcentaje: selecciónala, Ctrl+1, categoría Porcentaje con dos decimales.

Si todavía no has rellenado el precio actual de una posición, la celda queda en blanco, no muestra error. Si el precio de compra está vacío (fila creada pero sin compra todavía), la fórmula devuelve #¡DIV/0!; se arregla con SI.ERROR como aquí.

El formato condicional verde y rojo según ganes o pierdas

La columna G (plusvalía) y la columna H (rentabilidad) se entienden mejor de un vistazo si ganan se ven verdes y pierden se ven rojas. Para aplicarlo:

1. Selecciona el rango G2:H1000.
2. Abre Inicio > Estilos > Formato condicional > Nueva regla.
3. Elige Aplicar formato a las celdas que contengan. Condición: Valor de la celda es mayor que 0.
4. Pulsa Formato, pestaña Fuente, color verde oscuro. Acepta.
5. Repite con condición es menor que 0 y color rojo.

Filtros por tipo de activo y por estado

Para ver solo las posiciones de un tipo o un estado concreto, convierte la tabla en una tabla de Excel y aplica filtro:

1. Haz clic en cualquier celda de la tabla A1:H1000.
2. Pulsa Ctrl+T y confirma que la tabla tiene encabezados. Acepta.
3. Ve a Datos > Ordenar y filtrar > Filtro. Aparecen flechas en cada encabezado.
4. Haz clic en la flecha de Tipo de activo, desmarca todos y marca solo ETF. La tabla muestra solo los ETF.
5. Vuelve a marcar todos para restablecer la vista.

Compras periódicas, ventas y precio medio ponderado

Cuando una misma posición la compras varias veces a precios distintos, el precio medio ponderado es el que te dice a cuánto has salido en promedio, contando todas las compras. La plantilla lo calcula en la hoja Movimientos con una fórmula que multiplica cada cantidad por su precio y divide entre la suma de cantidades.

Una fila por compra y el precio medio calculado

Para cada nueva compra de una posición existente, añade una fila en Movimientos con la fecha, la operación (Compra), la cantidad y el precio. La columna del precio medio se calcula con un producto escalar de las cantidades y los precios:

=SUMAPRODUCTO((Nombre=A2)*Cantidad*Precio)/SUMAR.SI(Nombre;A2;Cantidad)

Donde Nombre, Cantidad y Precio son los encabezados de la tabla de esa hoja. Para arrastrarla sin que las referencias se muevan, usa referencias absolutas en los rangos ($B$2:$B$500, etc.). El resultado se actualiza fila a fila y te dice el precio medio después de cada compra.

Ventas parciales con cantidad negativa que conservan el promedio

Registrar una venta en la misma tabla es tan sencillo como añadir una fila con la cantidad en negativo. La fórmula del precio medio se mantiene, porque SUMAPRODUCTO con una cantidad negativa resta ese movimiento del total invertido sin alterar el promedio.

Por ejemplo, si tienes 10 acciones a 50 y otras 10 a 60 (medio 55) y vendes 5 a 65, introduces una fila con cantidad -5 y precio 65. El capital invertido baja, pero el precio medio sigue siendo 55 sobre las 15 restantes.

Reabrir la posición después de venderla toda conviene cuando el activo pasa de "Vendida" a "En cartera" otra vez: añade una fila con cantidad positiva y un comentario en la columna Notas indicando que es una nueva entrada.

Rentabilidad de un proyecto: la calculadora con VAN, TIR y payback

La hoja Calculadora sirve para evaluar una inversión con flujos de caja a lo largo del tiempo: una reforma, la compra de un local para alquilar, un negocio que empieza con pérdidas y termina dando. La columna A tiene los periodos (0, 1, 2, …, n) y la columna B los flujos (el desembolso inicial es negativo).

Las tres celdas clave son: la inversión inicial (B1, en negativo), los flujos por periodo (B2 en adelante, positivos o negativos) y la tasa de descuento (celda aparte, en porcentaje). Con esos datos, Excel calcula el valor actual neto (VAN), la tasa interna de retorno (TIR) y el periodo de recuperación.

VNA, TIR y TIR.NO.PERIODICO con los flujos en una columna

El VAN descuenta cada flujo futuro a la tasa de descuento y le suma la inversión inicial. La fórmula, sobre un rango B2:B10 que contiene los flujos del periodo 1 al 9 y una celda D1 con la tasa, es:

=VNA(D1;B2:B10)+B1

Si el VAN es positivo, el proyecto gana más que la tasa exigida; si es negativo, no compensa.

La TIR es la tasa para la que el VAN se hace cero:

=TIR(B1:B10)

Si los flujos son convencionales (un desembolso inicial negativo seguido de flujos positivos), la TIR es única y comparable con la rentabilidad de una alternativa. La fórmula TIR.NO.PERIODICO (en inglés XIRR) hace lo mismo pero con fechas en vez de periodos: existe desde Excel 2007.

Período de recuperación (payback) con BUSCARV y un pequeño truco

El payback es el periodo en el que recuperas la inversión inicial. La técnica más limpia es tener una columna auxiliar de flujos acumulados y buscar el primero que iguala o supera cero.

1. Añade una columna C con la fórmula del acumulado: =B2, =C2+B3, =C3+B4, y así hasta el final.
2. En una celda aparte, escribe la fórmula que busca en qué periodo el acumulado iguala o pasa de cero:

=BUSCARV(0;C2:C10;A2:A1000;VERDADERO)

Espera, esto no es exacto: BUSCARV busca en la primera columna del rango; lo que necesitamos es un INDICE sobre el periodo en el que el acumulado cruza cero. La fórmula ajustada, suponiendo los periodos en la columna A y los acumulados en C, es:

=INDICE(A2:A10;COINCIDIR(VERDADERO;C2:C10>=0;0))

Esta fórmula matricial (en versiones anteriores a 2021 se confirma con Ctrl+Mayús+Entrar) devuelve el periodo entero en el que el acumulado pasa de negativo a positivo.

Por qué tu plantilla no cuadra: errores que pasan de verdad

Cuatro errores aparecen una y otra vez al usar plantillas de inversiones. Cada uno tiene una explicación corta y un arreglo que lleva menos de un minuto.

La rentabilidad sale #¡DIV/0! en la primera fila

La fórmula (valor-actual-invertido)/invertido falla cuando invertido vale cero, es decir, cuando todavía no has rellenado el precio de compra. La celda muestra #¡DIV/0!.

El arreglo es envolver la fórmula con SI.ERROR o con un SI que compruebe el denominador:

=SI(D2*E2=0;"";(F2*E2-D2*E2)/(D2*E2))

Si la fila está vacía de verdad, el resultado es una celda en blanco; si tiene datos, calcula la rentabilidad normal.

La columna de plusvalía no se actualiza al cambiar el precio

La fórmula de la plusvalía parece correcta, pero al cambiar el precio actual la celda sigue mostrando el valor antiguo. La causa típica es que copiaste una fila desde otra hoja y la pegaste "suelta" debajo de la tabla, así que Excel no la reconoce como parte del rango.

Para arreglarlo: corta la fila problemática (Ctrl+X), ve a la última fila de la tabla, pega dentro de la tabla (Ctrl+V debajo del todo) y vuelve a arrastrar las fórmulas. Otra opción más limpia es usar Pegado especial (Ctrl+Alt+V) y elegir Fórmulas, sin el formato, para no contaminar la tabla.

La TIR muestra #¡NUM! o un valor que no cuadra

La función TIR necesita al menos un flujo positivo y uno negativo; si todos los flujos son del mismo signo, devuelve #¡NUM!. Si el valor cuadra mal, suele ser porque la estimación inicial está demasiado lejos del resultado real.

Para arreglarlo, pasa una estimación a la función:

=TIR(B1:B10;0,1)

Si sigue fallando, revisa que ningún flujo sea exactamente cero en medio del rango (cambia los ceros por valores pequeños, como 0,01) y que la inversión inicial sea la primera celda del rango, en negativo.

La fórmula suma fechas o muestra #¡VALOR! al pegar desde otra hoja

Si copias una fila desde una web o desde otra hoja y la pegas en la columna de Cantidad, a veces el número llega con un carácter invisible o la columna se queda como texto. La fórmula muestra #¡VALOR! y los totales no suman.

El arreglo pasa por reconvertir la columna a número:

1. Selecciona la columna con el problema (por ejemplo, la columna Cantidad).
2. Abre Datos > Herramientas de datos > Texto en columnas.
3. Pulsa Finalizar sin cambiar nada. Excel detecta el formato y lo convierte.

Lo que la plantilla gratis no hace y cómo lo resuelves

La plantilla gratis cubre el día a día de una cartera pequeña o mediana, pero tiene tres límites claros que conviene conocer antes de empezar.

Primero, no se conecta con brókers. Cada precio actual hay que traerlo a mano desde la web del bróker o desde una aplicación y pegarlo en la columna correspondiente. La plantilla no sabe ir a buscarlo.

Segundo, no actualiza los precios por sí sola. Ni en tiempo real ni al abrir el archivo. Si quieres ahorrarte el trabajo, el camino razonable es una herramienta específica (el panel de tu bróker, una hoja de Google Sheets con GOOGLEFINANCE, o un agregador tipo Morningstar).

Tercero, no trae tipos de cambio en vivo, así que una posición en dólares hay que valorizarla en euros (o en la moneda que uses) con el cambio del día. La plantilla deja columnas libres para añadir Comisión, Dividendos, Divisa y Tipo de cambio; si los necesitas, complétalas con tus propias cifras.

Lo que ya sabes hacer con tu plantilla y el siguiente paso

• Tú introduces los datos: tipo, nombre, precio de compra, precio actual y estado. Todo lo demás lo calcula Excel.
• Las entradas se validan con listas desplegables para evitar errores tipográficos en el tipo o en el estado.
• Las celdas calculadas van protegidas con SI.ERROR para que una fila incompleta no rompa los totales.
• El formato condicional distingue al instante las posiciones que ganan (verde) de las que pierden (rojo).
• La TIR de un proyecto se interpreta siempre junto al VAN, porque los flujos no convencionales dan valores engañosos.

El siguiente paso natural es crear una tabla dinámica sobre la hoja Cartera para ver la composición de la cartera por tipo de activo, con el porcentaje que cada categoría representa sobre el total invertido. Insertar > Tablas > Tabla dinámica, arrastra Tipo de activo a filas y Invertido a valores, y tendrás la foto de tu cartera en una sola vista.

Dudas al usar una plantilla de inversiones en Excel