Después de muchas preguntas, mejoras, correcciones y solicitudes hemos decidido compartir nuestra plantilla de seguimiento de la cartera de valores con todos vosotros. La plantilla permite gestionar 30 valores, 5 brokers, 8 países, 8 monedas y 19 sectores.
Para ello, vamos a explicar su funcionamiento en detalle para que todo el mundo pueda sacarle el mayor rendimiento posible y pueda aprovechar todas las gráficas, controles y análisis que proporciona. ¡Allá vamos!
DESCRIPCIÓN GENERAL
La plantilla está realizada en una hoja de Excel y consta de siete pestañas organizadas de la siguiente manera:
- Resumen Global: En esta página disponemos de los gráficos que consideramos mas importantes/relevantes para el control de nuestra cartera. Todas estas graficas se actualizan automáticamente. Los gráficos que podemos encontrar son:
Estado actual de la cartera.
Evolución de la inversión a lo largo de los años.
Distribución de la cartera según países, moneda, sectores y brokers.
Rentabilidad de la cartera anual y acumulada.
Dividendos netos recibidos a lo largo de los años.
- Cartera Inversión: En esta pestaña nos encontramos con todo lo relacionado con la composición de la cartera y la diversificación de la misma que también tenemos en la página Resumen Global aunque uniendo gráficas y tablas, algunas de las cuales tendremos que cubrir para que automáticamente complete el resto de información.
- Informe General Inversión: En esta parte de la plantilla disponemos de la evolución del capital invertido a lo largo de los años. Ya os iréis familiarizando con el funcionamiento, tendremos que rellenar algunos datos para que automáticamente se dé forma a las gráficas y actualice los enlaces correspondientes.
- Informe General Dividendos: Como su propio nombre indica, aquí tenemos toda la información relativa a los dividendos netos cobrados y la rentabilidad neta proporcionada. Los gráficos relativos a los dividendos que aparecen en el Resumen Global se encuentran en esta hoja.
- RpD: Parte fundamental de cualquier cartera. Se muestran tanto las rentabilidades brutas como netas de cada acción. Además, también se pueden analizar las rentabilidades por sector y país.
- Agencia Tributaria: En esta parte de la plantilla se recogen todos los dividendos brutos recibidos y la retención practicada para que nos sea más fácil a la hora de hacer la declaración de la renta. Aparecen clasificados según moneda, país y bróker.
- Apuntes: Es el corazón de plantilla. Prácticamente el único lugar que tendremos que rellenar y del cual se alimentan el resto de hojas. En el siguiente apartado hablaremos de como introducir los datos.
INTRODUCIENDO DATOS EN LA PLANTILLA
Una vez que ya conocemos las partes de la plantilla vamos a empezar a alimentarla para sacarle el mayor provecho posible y para ello nos situaremos en la hoja Apuntes.
Los primeros campos a cubrir son:
- Fecha operación: Tendremos que rellenar la fecha en la que se ha realizado cualquier tipo de movimiento (cobro dividendo, compra, venta, etc).
- Valor: Colocaremos el nombre de la acción o valor. El que nosotros queramos, por ejemplo, Telefónica, Santander, 3M, etc.
- País: Aquí escribiremos el país al cual pertenece ese valor. Por ejemplo, España, EEUU o Reino Unido.
- Cartera: Esta parte hace referencia al bróker en el que tenemos depositado ese valor. Por ejemplo, ING, Self Bank o Interactive Brokers.
- Divisa: Moneda en la que cotiza dicho valor: USD, GBP, o EUR.
- Sector: Nombre del sector al que pertenece dicho valor. Por ejemplo, Salud, Utilities o REIT.
- Descripción: Elegiremos del desplegable una de las siguientes opciones: Compra acciones, venta acciones, comisión, reparto dividendos o scrip dividend.
- Acción: Se abrirá otro desplegable y ampliaremos la información según lo reportado en el anterior apartado: Compra, venta, comisión o dividendo.
- Tipo de cambio – Anotaremos el tipo de cambio de ese día en cuestión en relación con el Euro, según la divisa que hayamos contemplado en el apartado Divisa.
- Dividendo – No es necesario cubrir nada, se actualizara una vez rellenemos la parte de la tabla relativa al dividendo.
Continuamos con la parte relativa a las operaciones de compra/venta:
- Compra
Precio: Anotaremos el precio al que hemos realizado la compra.
Nº Títulos: Cantidad de acciones que hemos comprado.
Comisión: Gastos de corretaje y comisiones que hemos pagado.
Importe Operación: Se calcularía en automático, coste total de la operación (comisiones incluidas) y a partir del cual calcularía el precio medio de la acción. Recomiendo poner todos los costes en Euros, por lo tanto habrá que multiplicar por el tipo de cambio si fuera necesario. A comprobar con el recibo de la operación que proporciona el bróker para que coincida y los datos sean verídicos.
- Venta
Precio: Anotaremos el precio al que hemos realizado la venta.
Nº Títulos: Cantidad de acciones que hemos vendido.
Comisión: Gastos de corretaje y comisiones que hemos pagado.
Importe Operación: Se calcularía en automático, coste total de la operación (comisiones descontadas) y a partir del cual calcularía el beneficio. Recomiendo poner todos los costes en Euros, por lo tanto habrá que multiplicar por el tipo de cambio si fuera necesario. A comprobar con el recibo de la operación que proporciona el bróker para que coincida y los datos sean verídicos.
- Venta confirmada: Realiza los cálculos en automático cuando corresponden.
- Nº acciones compradas: Realiza los cálculos en automático cuando corresponden.
- Inversión realizada: Realiza los cálculos en automático cuando corresponden.
- Desinversión realizada: Realiza los cálculos en automático cuando corresponden.
- Beneficio: Realiza los cálculos en automático cuando corresponden.
- % Beneficio: Realiza los cálculos en automático cuando corresponden.
Y para acabar vamos con la parte relativa a los dividendos:
- Dividendo bruto: Importe bruto cobrado. De igual manera recomiendo trabajar en Euros, por lo tanto, será necesario multiplicarlo por el tipo de cambio.
- País: Cogerá automáticamente el país introducido previamente.
- Retención origen: Introducir el importe retenido en origen si lo hay.
- % Retención origen: Automáticamente calculara el porcentaje retenido.
- Retención destino: Introducir el importe retenido en destino.
- % Retención destino: Automáticamente calculara el porcentaje retenido.
- Retención total: Automáticamente calculara el importe retenido total.
- % Retención total: Calculara automáticamente el porcentaje retenido total.
- Dividendo neto: Se mostrará el dividendo neto devengado.
COMPLETANDO EL RESTO DE DATOS
Después de haber introducido todas nuestras operaciones en la pestaña Apuntes ya solo nos quedara ir completando el resto de las hojas con algunos datos, de una manera muy rápida y fácil.
En la hoja Cartera Inversión, tendremos que completar la columna Valor con el mismo nombre de la acción que hemos introducido en la columna Valor de la pestaña Apuntes. El resto de datos de la imagen se actualizarán según lo que hayamos introducido previamente.
En el recuadro de Distribución cartera por brokers tendremos que rellenar el nombre del bróker que hayamos introducido en el apartado Cartera en la hoja Apuntes.
En el recuadro de Distribución cartera por países tendremos que introducir el nombre del país que hayamos cubierto en el apartado País en la hoja Apuntes.
En el recuadro de Distribución cartera por moneda tendremos que rellenar el nombre de la divisa que hayamos introducido en el apartado Divisa en la hoja Apuntes.
En el recuadro de Distribución cartera por sector tendremos que introducir el nombre del sector que hayamos introducido en el apartado Sector en la hoja Apuntes.
En la hoja Informe General Inversión, tendremos que completar la columna Valor con el mismo nombre de la acción que hemos introducido en la columna Valor de la pestaña Apuntes. Además, tendremos que indicar el año desde que queremos que la plantilla empiece a gestionar nuestra cartera y completar, de manera consecutiva, el resto de años (total seis). A modo de ejemplo, en la imagen adjunta, se inicia en 2016 y finaliza en 2021.
En la hoja Informe General Dividendos, tendremos que completar la columna Valor con el mismo nombre de la acción que hemos introducido en la columna Valor de la pestaña Apuntes. Además, tendremos que indicar el año desde que queremos que la plantilla empiece a gestionar nuestra cartera y completar, de manera consecutiva, el resto de años (total seis). A modo de ejemplo, en la imagen adjunta, se inicia en 2016 y finaliza en 2021.
En la parte superior se muestran dos porcentajes (2,5% y 4,5%) esos son los límites a partir de los cuales la plantilla identificara condicionalmente los rendimientos de los distintos valores que conforman nuestra cartera: inferior a 2.5% rojo, entre 2.5% y 4.5% amarillo y superior a 4.5% verde. Podréis configurarlos a vuestro gusto cambiando dicho porcentaje.
También se incluye un cuadro en el que podréis ver los dividendos ingresados en un determinado intervalo de tiempo para los valores que introduzcáis en la columna Valor.
En la hoja RpD, tendremos que completar la columna Acción con el mismo nombre de la acción que hemos introducido en la columna Valor de la pestaña Apuntes. El mismo procedimiento lo tendremos que realizar introduciendo el Sector y el País con el que hayamos cubierto la hoja Apuntes.
Todas estas gráficas y datos son configurables para el rango de fechas que queramos.
Al final de esta pestaña podremos comparar mensualmente, trimestralmente y anualmente los dividendos cobrados entre dos años a elección.
Y para terminar llegamos a la hoja de la Agencia Tributaria, aquí tendremos que introducir la moneda, el país o el bróker en la tabla correspondiente, siempre según los datos que hayamos introducido en la pestaña Apuntes.
Podremos seleccionar los dividendos entre el rango de fecha que deseemos.
A modo verificación, hemos introducido en la celda Resto dividendos un marcador Verde/rojo que evidencia si existe algún error en los datos reportados.
Esperamos que os guste y le podáis sacar provecho, como siempre toda sugerencia para mejorarla o automatizarla será bienvenida.
Si realmente os es de ayuda, os agradezco cualquier donación que podáis hacer, servirá para el mantenimiento del blog y su mejora. Podéis hacerlo aquí a través de la página de PayPal o del botón de más abajo.
Aunque solo sea para un café se agradece de corazón.
Libre Inversión es el nuevo blog sobre finanzas, inversión e ingresos pasivos. Con tips prácticos basados en la experiencia de sus editores, descubrirás la mejor manera de alcanzar tu independencia económica.
Buenos días, muchísimas gracias por la plantilla, es de verdad una herramienta estupenda.
Una consulta al respecto: ¿cómo se añadirían las acciones recibidas mediante scrip dividend?
Muchas gracias de nuevo y un saludo, SM
Hola Sandra,
Gracias por el comentario, seguimos trabajando para hacerla mas accesible e incluir nuevas cosas.
En relación a tu consulta, los scrip dividend los contemplamos como compra a 0 euros y la venta de derechos (si la hubiera) como dividendos.
Espero que te haya quedado claro.
Gracias por pasarte y comentar.
Hola Libre Inversión:
Muy claro, ¡muchas gracias!
Entonces, ¿en qué caso se usaría la opción «scrip dividend» en el desplegable de «descripción» en la pestaña «Apuntes», cuando se cobre el scrip dividend en contante?
Muchas gracias a ti de nuevo por tu labor.
Buenas tardes, muchas gracias por vuestro aporte. Una consulta, no tiene la opción de introducir valores actuales de la acción y que calcule la rentabilidad?
Saludos y gracias
Buenos días,
La plantilla esta pensada para calcular rentabilidades brutas y netas de activos de los cuales cobramos dividendos. Puedes simular tu las rentabilidades que obtendrías introduciendo los datos que quieras, aunque te será más rápido en cualquiera de las webs de bolsa.
Gracias por pasarte y comentar.
Buenos días!!
Muchas gracias por la plantilla. Es muy útil.
Me ha surgido una duda al emplearla. ¿Cómo trabajas en la plantilla con dos divisas, por ejemplo euros y dolares? Es que creo que no se aplica el cambio de divisa que se escribe en la tabla, sumando todo como euros a la hora de hacer porcentajes, etc.
Muchísimas gracias!!
Un saludo
Buenos dias Jose Luis,
Toda la plantilla esta pensada para trabajar en Euros. Compras, ventas y dividendos se pasan a euros.
Tienes una columna donde puedes poner el cambio de divisa, aunque no la suelo usar mucho porque con los brokers con los que trabajo mi moneda principal es el euro.
Espero poder haberte aclarado la duda.
Gracias por pasarte y comentar.
Muchísimas gracias por la respuesta!!!
Un saludo
muy buenas parece bastante completa pero me da errores y no me deja usarla, a la hora de elegir descripcion de la plantilla no me deja elegir mas compra de acciones solo en la primera casilla alguna solucion¿? luego una vez tengo todos los datos en los apuntes, en la pestaña de cartera de inversion, pongo el nombre y sale todo pero no pone los precios ni las acciones ni nada de eso, que estoy haciendo mal¿?
Hola Edgar,
La plantillas esta muy automatizada, pero si por error borras alguna formula puede ser que después ya no consigas enlazar los datos.
Envíanos la plantilla con 3 o 4 datos que consideres relevantes (compra, venta, dividendos) para que te los podamos revisar y así veas donde cometes los posibles errores.
Mándanosla a info@libreinversion.com
Un saludo.
Muy buenas,
La plantillas es muy útil y muy bien hecha, a mi me pasa lo mismo que Edgar. Como lo puedo solucionar?
Hola,
El problema de Edgar era que no tenia la plantilla cubierta con el año siguiente al actual (2022) ya que necesita un rango de fechas para calcular los valores.
Gracias por pasarte y comentar.
EN LA HOJA DE APUNTES, NO SE PUEDEN ORDENAR LAS ENTRADAS POR FECHAS O POR SECTORES ES MAS NO SE PUEDEN ORDENAR LOS APUNTES POR NINGUN CONCEPTO.
ESTA FUNCION ESTA BLOQUEADA EN LA HOJA?.
SI ES ASI COMO ACTIVARLA.
Buenos dias Jose,
La idea es ir introduciendo los datos en la hoja de manera cronológica, después colocando un filtro en excel se puede filtrar por cualquiera de las columnas.
Gracias por pasarte y comentar.
Hola, lo primero de todo muchas gracias por el trabajo que supone automatizar todo esto en un excel, yo hacía el seguimiento de mi cartera en una hoja de cálculo pero mucho más sencilla 😀
Una duda, he leído antes en otra duda que los script dividend los pones como compras a 0 €, pero, ¿y si a ese dividendo en derechos le sumas una compra de más derechos para redondear?
¡Gracias, un saludo!
Hola Pablo,
Gracias, en realidad hay un montonero de horas metidas y muchas peleas con el excel.
Si en el Scrip no he desembolsado dinero, el precio medio se diluye, pero tal y como dices he comprado derechos, pues lo simulo como una compra; euros gastados vs acciones adquiridas.
Gracias por pasarte y comentar.
Buenos días. Echo en falta un lugar donde colocar las comisones que el broker te cobra por los dividendos. Gracias.
Buenos dias Rober,
Tanto en las compras como en las ventas tienes una columna para colocar las comisiones.
Gracias por pasarte y comentar.
Buenas tardes, ¿donde se puede ver la valoración de la cartera a fecha corriente con las cotizaciones actuales?
Gracias por el trabajazo! Enhorabuena.
La plantilla no contempla la opción de ver la cotización online, para eso es necesario usar Google Sheets u Office365.
Gracias por pasarte y comentar.
Muchas gracias! Sin duda alguna es un recurso muy útil, y que encima lo pongáis al alcance de todos de forma gratuita indica la importancia que le dais al cliente. Un saludo! 🙂
Gracias, tratamos de aportar nuestro granito de arena.
Buenos días, gracias por la hoja, es muy útil, pero tengo una duda. En la hoja de apuntes, las columnas de inversión y desinversión cada vez que introduzco los datos de los movimientos, por ejemplo, de compras y ventas de Santander, la fórmula busca el total de los movimientos, es decir, mezcla compras con ventas. Estoy haciendo algo mal? Gracias, saludos,
Hola José,
Debería funcionar, tienes que asegurarte que el nombre del valor es exactamente el mismo y que has indicado correctamente las compras y ventas.
De todas maneras, si sigues teniendo dudas mándame tu plantilla con los datos que tengas introducidos y le pego un vistazo.
Gracias por pasarte y comentar.
Hola!
enhorabuena por el blog y gracias por la plantilla. A mí me pasa igual que al compañero, cuando introduzco una venta lo suma todo. ¿Existe alguna solución? Gracias
Hola,
Muchas gracias, me alegra que os sea útil.
Si se utiliza el mismo nombre que en la compra y se registra correctamente la venta no debería sumar nada.
Un saludo.
EXCELENTE TRABAJO!!! Una consulta: como apuntamos el SPLIT?
Muchas gracias Adrian.
Normalmente yo lo apunto como una compra a coste cero, donde nuestro precio medio de compra se ve reducido.
Gracias por pasarte y comentar.
Hola Adrian,
Yo lo apunto como una compra a coste 0 (o el gasto que te haya pasado tu broker), para promediar a la baja el precio medio de compra.
Gracias por pasarte y comentar.
Ahí va una pequeña ayuda por el pedazo de trabajo que habéis realizado y lo bien que me va a venir el excel.
Muchísimas gracias
Muchísimas gracias Raúl.
Seguimos trabajando para mejorarla y poder ofreceros la nueva versión en breve.