Excel para controlar tu rentabilidad y tu cartera de inversión

En la pasada entrada te mostraba cómo calcular la rentabilidad de tu cartera de inversión (link). Vimos varias formas de hacerlo, y que no eran demasiado difíciles, pero tampoco algo inmediato. Es por ello que para muchos será de gran ayuda tener una plantilla excel de ejemplo, pero totalmente operativa.

En esta entrada de hoy os doy el enlace para que os descarguéis esta plantilla que he creado, y también os explicaré cómo se debe usar para que no te veas perdido. La buena noticia, es que no solo te va a servir para saber la rentabilidad, sino que te va a servir como base para crear tu excel de seguimiento. Se incluyen por tanto, hojas para anotar tus compra/ventas (de acciones, fondos o ETFs), así como hojas para que registres todos los dividendos que vayas cobrando. El excel usa las cotizaciones disponibles en una hoja googlesheets que también os proporciono.

Si no llevabas un control de tu cartera, esta es tu oportunidad de hacerlo con rigor y detalle. Empezar es lo que cuesta, pero luego es ya solo adaptarlo a tu gusto, y dedicarte a anotar nuevas operaciones y dividendos. ¿Empezamos a ver el excel?

Empecemos diciendo que el excel que te he preparado, es una versión simplificada del propio excel que yo uso, pero contiene casi todo su potencial y filosofía de registro de las operaciones, dividendos y medida de la rentabilidad. Esta versión contiene 5 pestañas, que son las mínimas para poder hacer algo funcional:

  • Resumen
  • Rentabilidad
  • DatosInversiones
  • DatosDividendos
  • Cotizaciones

En la pestaña «Resumen» tienes una visión global de tu cartera, con tus rentabilidades, rendimiento y capital. Así como una visión de tus activos y cómo están distribuidos por activos, y por moneda. Este es el aspecto de la parte superior de esta pestaña resumen:

La pestaña de «Rentabilidades«, es una hoja donde se anotan los valores de tu inversión mes a mes, así como de los índices, para que puedas saber exactamente lo bien que lo haces mes a mes, año a año, o desde que empezaste a invertir. Este es su aspecto en su parte superior:

Es en esta pestaña donde tenemos implementado todo el cálculo de las rentabilidades TWR y MWR, siguiendo las explicaciones y fórmulas mostradas en la pasada entrada. Para la parte de la rentabilidad MWR, en vez de usar la fórmula TIR.NO.PER, he usado una función propia (Rentabilidad_acumulada_fechas) que he programado, pues me permite más flexibilidad, y no tener que colocar los datos en otra tabla tal y como le gusta a la función TIR.NO.PER. Es por eso que el excel, en vez de ser de tipo xlsx, termina en xlsm (contiene la «m» de macro) para almacenar la macro o función que calcula el MWR. No te preocupes, que no tienes que hacer nada con ello, se ejecutará solo.

Pero para poder calcular todo esto, no nos queda más remedio de decirle al excel todas las operaciones de compra y venta que hemos hecho a lo largo de nuestra etapa de inversión. Eso lo hacemos en la pestaña «DatosInversiones«. Aquí vamos anotando en cada fila las compras que hagamos del activo elegido, sea este acciones, fondos de inversión o ETFs. En la misma fila se incluye información de la fecha de venta de este activo, si es que lo has vendido, y si no, le dejas la fecha de hoy (tal y como viene por defecto). La fila calcula de forma automática el rendimiento de mercado y de dividendos, así como los años en cartera.  Este es el aspecto de esta ficha:

El tema de las anotaciones de los dividendos, se hace en una pestaña diferente denominada «DatosDividendos«, puesto que no queremos mezclar estos datos con las compra/ventas. Los dividendos tienen campos específicos, que queremos registrar, como las retenciones en origen (impuestos en un país extranjero de tus acciones), retenciones de destino (19% de retenciones en España), o el exceso de retenciones que puedes recuperar en el IRPF español cuando hagas la declaración. Esta pestaña, tiene este aspecto:

Para los que adoran los dividendos, esta misma ficha contiene abajo una gráficas y tabla resumen, para que veas su evolución, y cómo crecen los dividendos con el tiempo.

Por último si queremos saber cuánto vale nuestra cartera en un momento dado, debemos acceder a internet para averiguar la cotización de nuestras acciones, fondos o ETFs. Yo lo hago usando una hoja externa en googlesheets, que copio y pego dentro de mi excel de seguimiento. Este es el resultado tras pegarlo en mi hoja «Cotizaciones«:

Si decides usar el excel para tu seguimiento de cartera, debes copiarte la hoja de cálculo de  GoogleSheets que te doy en el enlace siguiente. Así podrás tener las cotizaciones de acciones, fondos y ETFs, actualizados: https://docs.google.com/spreadsheets/d/1pSs2mC38gaKbqUYBkmMv7s1sbFWaVJmVJS7GvdXpyDk/edit?usp=sharing  .

Ahora que tienes delante el GoogleSheets (Hoja de cálculo de Google), hazte una copia local pulsando en «Archivo»>»Crear una copia…», y así generas un nuevo fichero local en tu propia cuenta de Google Drive. Una vez la tienes bajo tu control, luego puedes adaptarla añadiendo el nombre de tus nuevas empresas, ETFs o fondos que no estén ya en ella, y dejarla a tu gusto. En este GoogleDrive tuyo, las cotizaciones se actualizan solas cada vez que entres (p.ej. a fin de mes), y por tanto basta con copiar esas cotizaciones y pegarlas en la columna «Precio actual (moneda)» de la pestaña «Cotizaciones» del Excel.

El Excel lo puedes descargar en la zona de recursos.

En los comentarios de abajo explico un poco más los pasos a seguir para editar el excel en el orden correcto, sabiendo qué debes tocar y qué no. Y en esta entrada (posterior a la actual) te doy una nueva versión con novedades.


Bueno esto es todo. En esta entrada he ido un poco ligero explicando el excel, pues creo que la mejor forma de entenderlo es usándola y viendo todo su contenido. Te recomiendo que la descargues, la uses para entenderla más o menos bien, y cuando lo tengas claro, la vayas vaciando de mis datos, y los renueves con los datos de tu cartera. Es la mejor forma de aprender y ver qué dudas pueden surgir.

Espero que te haya resultado útil la entrada y la plantilla. Si es así, te agradecería que lo comentases, digas si te surge algún problema, y que dieses ideas de mejora a la comunidad. Buen provecho.

38 respuestas a «Excel para controlar tu rentabilidad y tu cartera de inversión»

  1. Pasos:

    1) El sitio natural para empezar a añadir tus datos es en la pestaña de DatosInversiones. Cada vez que tengas una nueva compra añades una nueva fila y rellenas todos los campos con fondo amarillo. El resto se supone que llevan una fórmula, y por tanto se calculan solos y no tienes que poner nada a mano.

    2) Si cobras dividendos haces lo mismo en la ficha DatosDividendos.

    3) A fin de mes debes ir a la página de googledrive y copiar la columna de cotizaciones y luego pegarla en la columna E de la pestaña de Cotizaciones.

    4) Una vez refrescadas las cotizaciones, ya tienes la inversión de tu cartera actualizada. Por tanto a fin de mes, debes coger el valor de tus inversiones : casilla B7 en pestaña Resumen, y pegas esos euros en la casilla B82 o siguientes, de la pestaña Rentabilidades. Solo con eso, casi todo lo tienes ya hecho.

    5) Puedes hacer lo mismo en las columnas I M y Q de Rentabilidades, para poner la cotizacion de los indices SP500, ibex y World, que puedes obtener siguiendo los enlaces en esa misma pestaña.

    Resumen, si tiene fórmula no lo toques. Si no tiene fórmula es que hay que rellenarlo.

    Espero que se vea más claro ahora.

  2. Hola,
    En la googlesheet que dejas en el enlace no encuentro fondos de inversión, que si que esta en la pestaña de cotizaciones del excel.
    Podrías explicar como se añaden las cotizaciones de fondos?

  3. Buenos días Antonio, gracias por la plantilla.

    Para añadir una nueva Acción en googlesheet, como se haría, por ejemplo BBVA ???
    Se inserta una nueva fila en el tipo Acciones y se rellena tal cual, y el solo se va actualizando o como.
    Gracias de antemano, saludos.

    1. ÁlvaroLP, Creo que sí, pero es posible que alguna fórmula de excel no te funcione en googlesheets. No lo he probado, ya que prefiero tenerlo en local en excel, pues googlesheets va un poco lento.
      Saludos

      1. He seguido analizando tu excel, me parece un curro formidable. Pero tengo un par de dudas:
        – ¿No llevas control de cuantas acciones o participaciones mantienes en cartera?
        – ¿Cómo controlas el número de acciones obtenidas mediante scrip y lo generado de la venta de los derechos sobrantes?

        Un saludo.

        1. Álvaro, te respondo a tus dos preguntas:
          – En este excel, no lo he puesto, pero en mi cartera sí. Si te parece lo incluyo el finde, junto con otras sugerencias de mejora que veo por ahí, y actualizo el excel para todos.
          -Las acciones por script puedes ponerlas como compra a coste cero, y la venta de derechos como dividendos cobrados.

          1. De nuevo, muchas gracias por compartir tu excelente trabajo con nosotros. Esperaré expectante el Excel modificado.
            Un saludo.

  4. Muchas gracias por la tabla, Antonio. Me parece muy útil y creo que la voy a empezar a usar pero tengo unas dudas y comentarios:

    • En la pestaña resumen a partir de la línea 13 hay varias tablas de diversificación. ¿Por qué no las has hecho con tablas dinámicas? Es mucho más sencillo y rápido que con fórmulas ¿no crees? Mismo comentario en la pestaña datosdividendos para las tablas a partir de la línea 65.

    • En la pestaña resumen casilla A7 indica que el capital aportado es de 18.487,45 €, pero en la pestaña rentabilidad si restamos las aportaciones menos los reembolsos (C93-D93) nos da 18.391,62€. Entiendo que esto ocurre porque los reembolsos no se hacen al mismo precio que las compras y además hay que restarles las comisiones de venta ¿no? ¿No es más correcto por lo tanto usar el valor de 18.391,62 en lugar del 18.487,45 que es simplemente la suma de las aportaciones que se hicieron a los valores que aún están en cartera?

    • En la pestaña datosinversiones, están en la misma línea la compra y la venta, si vendes una cantidad de títulos menor a la comprada ¿cómo lo reflejas?

    1. Laertes,
      Muchas gracias por tus sugerencias de mejora.

      Lo de las tablas dinámicas no lo controlo, y por eso lo he hecho así. Las he usado un par de veces para tablas enormes pero no debo haber captado su utilidad en estos casos.
      Me encantaría que si puedes, las modifiques añadiendo esas tablas dinámicos, como piensas, y así podemos mejorarlo.

      La segunda pregunta luego te digo…

      Y la tercera, si vendo menos de lo comprado, divido la linea inicial en dos compras en la misma fecha, y solo una de ellas la marco como vendida.

      Si te animas con modificar el excel, y me lo mandas a mi correo, sería estupendo. Así lo mejoramos todo.
      un saludo

    2. Laertes,Hola de nuevo.

      Muy buena pregunta sobre la pestaña resumen casilla A7. Es algo que estaba dudando realmente, pero al final dejé los 18.487,45 que es la suma de las aportaciones que se hicieron al comprar los valores y que aún están en cartera.

      Si pongo, lo otro, al tener ganancias en las ventas, me sale una cifra menor, y por tanto parece que he invertido menos capital, pero en realidad prefiero mostrar el coste de mis inversiones actuales. No se si te convence de esta forma, pero otras maneras pueden ser razonables…

      Por cierto, he cambiado un poco el excel «casilla C7 Resumen», que ahora tiene los rendimientos «netos» y columna S de DatosInversiones, para poner el rendimiento neto (le resto los gastos de compra y venta), que creo es más apropiado.

      Algunos otros han sugerido algún cambio menor, espero poder compilarlo todo el finde, y poder subir una nueva versión. Lo siento por los que ya la estais usando, pero los cambios son para mejorar…

      Gracias Laertes por tu gran aporte.

      Antonio

      1. Gracias a ti por currarte el blog, la hoja, por tus respuestas y sobre todo por aceptar las «críticas» jaja, es muy fácil criticar el trabajo de otros.
        Te he mandado la hoja con las tablas dinámicas a ver que te parece. Yo creo que ahorran mucho tiempo no sólo al hacerlas sino cuando hay cientos o miles de líneas de datos.

  5. hola Antonio, primero que nada agradecer tu gran trabajo con la planilla y esfuerzo y en segundo lugar agradecer que lo compartas. Tengo dos puntos a comentar:
    1 – rentabilidad otras divisas. en mi caso, invierto en activos denominados en otras divisas, y me gustaría poder registrar la rentabilidad de dichos activos en dicha divisa, y no como tu lo has calculado convirtiendo las otras divisas a tu divisa principal, el euro. ¿crees que se podrá modificar la hoja de calculo para poder registrar activos en otras divisas junto a los denominados en euros y poder registrar ambas rentabilidades por separado? pienso en registrar las inversiones y los dividendos en las pestañas existentes y registrar el Resumen las rentabilidades de los activos según su divisa
    2 – a la hora de calcular el TWR de mi cartera, veo que debemos ingresar la valoración de mi cartera en columna B pestaña Rentabilidad, para todas las fechas históricas. En caso de no tener dichas valoraciones, ¿que sugieres para poder tener una aproximación? Entiendo que es necesario el valor histórico de cartera para un correcto calculo de la TWR. ¿cierto?

    1. Hola German,
      Gracias ante todo. Respecto a la primera pregunta, entiendo que quieres tener como dos o más carteras separadas, por su moneda de origen, y analizar la rentabilidad de cada una, sin el efecto del cambio a euros. No me lo había planteado, ya que el cambio moneda lo considero como una volatilidad más de los activos, y lo veo todo en euros. Se podría hacer, pero no lo veo claro, pues si tienes muchas monedas, USD, CAD, GBP, etc.. quedaría todo muy distribuido, y la visión global de la rentabilidad del conjunto de la cartera quizás se pierda. No se. Pero a nivel de la pestaña DatosInversiones sería fácil añadir una columna más y poner la rentabilidad en «moneda», la que sea, y no en euros. Pero luego al ponerlo en el TWR, ¿que hacemos? ponemos más columnas, tantas como diferentes monedas? De entrada me parece un lio. Si alguien lo ve claro, por favor que lo diga.
      Respecto a la segunda pregunta, sí, hay que poner el valor histórico de tu cartera al final de cada mes, y si no lo anotaste en su momento, no hay forma de saberlo, salvo que te bajes todos los históricos de precios de los activos que tengas, los multipliques por las acciones que tuvieses en cada fecha y recalcules el valor de tu cartera. No veo otra forma.
      Espero haber ayudado.
      Saludos
      Antonio

  6. Gracias Antonio. Comparto que no es fácil trabajar con varias divisas, yo tengo tres (euro incl.) pero si es muy cierto lo que mencionas a la hora de calcular toda la rentabilidad global de cartera. Creo que por ahora le haré una pequeña trampa trabajando con tipo de cambio 1 a 1 contra el euro.

    Si me permites algún comentario, yo le cambiaría, o agregaría, los valores del tipo activo (Fondo, Acciones) por RV, RF para conocer los pesos de RF, RV en la composición de la cartera (hay ETFs de RF). Y agregar grafico correspondiente.

    ¿como gestionaríamos el traspaso de fondos? ¿cerrando la posición del fondo origen y abriendo una nueva linea con el fondo de destino?

    Avisa por favor si has subido o subirás una nueva versión de la planilla con los cambios. Seguimos conversando.

  7. hola Antonio,

    he estado revisando la formula utilizada para el calculo de TWR, y veo que dicha formula la calculas como (Vf – Vi + Aportaciones – Reembolsos ) / Vi. Sin embargo entiendo desde el punto de vista conceptual que el calculo de la TWR es (Vf – Vi – Aportaciones + Reembolsos ) / Vi; resta aportaciones y suma reembolsos para anular dicho efectos. La formula utilizada en la planilla hace justamente lo contrario, suma aportaciones y resta reembolsos, por lo que el calculo de la TWR no sería el correcto. Si me ayudas a despejar esta duda sería genial, no se si me estaré perdiendo algo. Muchas gracias y disculpa la molestia.

  8. Gracias por el comentario. Es una buena y acertada observación. Veo que hay nivel entre los lectores.

    En realidad en el excel se calcula como (Vf – (Vi + Aportaciones – Reembolsos) ) / (Vi+ Aportaciones – Reembolsos). La otra forma que propones es también correcta: (Vf – Vi – Aportaciones + Reembolsos ) / Vi. ambas solo se diferencian en el denominador. Pero al ser distinto este van a dar resultados diferentes TWR.

    Esto es así, ya que estamos simplificando y calculando el TWR mensualmente (lo ideal sería día a día). En la primera fórmula que uso se asume que las aportaciones o reembolsos se hacen a primeros de mes, y la inversión es cte a lo largo del mes. En la segunda fórmula, se asume que todas las aportaciones y reembolsos se hacen a fin de mes, y que no hay movimientos durante el resto del mes.

    Solución ¿qué fórmula usar?, pues cualquiera de las dos son aproximaciones, ya que no operamos siempre ni a principios ni a final de mes. Por tanto si asumes el error de esta aproximación cualquiera de las dos es una opción decente. Si no te vale la aproximación, entonces tienes que hacer el TWR diario, y anotar el valor de tu cartera todos los días. ¿Merece la pena? pues no se, me parece demasiado pesado.

    Yo creo que con el TWR mesual, y el MWR acumulado y anualizado, es suficiente paara hacer el control ¿cómo lo veis?

  9. hola Antonio, gracias por tu respuesta y aclaración. Me quedan claras las diferencias entre ambas formulas, y si estamos de acuerdo que hacer el seguimiento mensualmente esta bien para mi como tu indicas.

    Otra pregunta/duda, ¿como gestionaríamos el traspaso de fondos? ¿cerrando la posición del fondo origen y abriendo una nueva posición/linea con el fondo de destino? Muchas gracias y buen fin de semana.

    1. Exacto, al fin y al cabo el traspaso es eso vender y comprar otro.
      El problema de la trazabilidad fiscal de los multiples traspasos cuando vendas definitivamente, y saber las plusvalias por metodo FIFO se lo dejamos a los brokers, que hacen el tracking doble de la plusvalía fiscal y de mercado. Eso es dificil registrarlo en un excel, o yo no he sabido.

    1. Por ejemplo si buscas el fondo «Cobas Seleccion», pues pones en el buscador de google: «Financial times cobas selección», y pinchas en el primer resultado y ahi ves ya el ISIN del fondo: ES0124037005:EUR donde sobra la última parte «:EUR»

  10. Invitado a café. Y no me des las gracias. Soy yo el que te las tiene que dar. Irás a Valencia a las Segundas Jornadas Financieras? Por conocerte…..

    1. Hola Francisco,
      Pues es inevitable no darte las gracias por el donativo. Lo que valoro no es el importe en sí, sino que el hecho de que os molestéis en meter vuestros datos en el formulario de Paypal, indica que estáis agradecidos por el trabajo, y eso es lo realmente valioso; vuestro reconocimiento.
      Gracias de paso a todos los donantes anónimos, y al primero de ellos que quería hacerlo incluso sin tener aun puesto el botón para ello.
      A las jornadas no puedo ir este año, ya que estos en la misma fecha en Pamplona de finde, pero me gustará mucho conoceros en otra ocasión.
      Un saludo
      Antonio

Deja un comentario