En la pasada entrada (link) te mostraba un Excel para llevar el control de tu rentabilidad, poder compararte con índices, y de paso para tener un mínimo seguimiento de tu cartera de inversión. Este Excel ha tenido una muy buena acogida, especialmente para gente que está empezando, o más veteranos pero que no estaban llevando hasta ahora el control de la rentabilidad (ni con TWR ni MWR). He recibido varias sugerencias de mejora o dudas en los comentarios del blog, por twiter y directamente por email, y esto me motiva a revisar el Excel, ampliar algunos aspectos, y a tratar de explicar mejor o con algo más de detalle cómo se usa este Excel.
En primer lugar presentaré las novedades de este Excel 2.0 vs la versión original (1.0) y luego daré una pequeña guía de uso paso a paso para que no te sientas perdido.
¿Me acompañas en este viaje por el Excel?
Novedades del Excel en la versión 2.0
En esta semana he hecho una serie de mejoras (eso espero), no muy profundas, pero que dan más funcionalidad a aquellos que quieren usar este Excel no solo para analizar rentabilidades sino como plantilla para llevar el control total de la cartera. Estos son los principales cambios:
1-Posibilidad de tener subcarteras. Además de la visión global de tu cartera, podrás categorizar parte de los activos de tu cartera como pertenecientes a una subcartera específica que te interesa tener desagragada para ver cómo funcionan diferentes estrategias. Por ejemplo, puedes tener subcarteras de Acciones, Inversión indexada, Gestión Value, etc.. lo que se te ocurra. Así podrás ver si tu estrategia implementada con la subcartera de acciones, lo hace mejor que tu subcartera de fondos indexados, o qué tal respecto a tus fondos de gestores value. Es algo que uso yo, y a largo plazo quiero saber, cuál de estas tres estrategias (me gustan todas) lo hace finalmente mejor.
Esta posibilidad se consigue añadiendo el nombre de tus subcarteras en la nueva columna «C» de la pestaña «DatosInversiones». Aquí abajo puedes verlo:
La distribución entre subcarteras la tienes en el primer gráfico tipo tarta de la hoja «Resumen». Si no te gusta esta funcionalidad, puedes no usarla, que el resto del excel seguirá funcionando igual.
2-Distribución Renta variable (RV) vs Renta fija (RF). Es muy importante saber cómo distribuyes tu cartera entre este tipo de activos RV (acciones, fondos de RV, etc), y RF (Bonos, fondos de RF, depósitos, etc). Para ello en la pestaña de «DatosInversiones» en la columna «D», se anota el porcentaje de RV de cada activo. Si son acciones pones un 100%, si es un fondo de renta fija pones un 0%, y si es un fondo mixto del 60% en RV y 40% en RF, pones un 60%. El resultado de tu cartera distribuida en RV vs RF lo tienes en el tercer gráfico tipo tarta de la hoja «Resumen».
3-Control de Comisiones. Cuando invertimos es muy importante que no nos frían a comisiones de compra/venta cuando compras directamente acciones o ETF, ni tener muchos gastos de gestión en los fondos que utilizamos. Si compramos acciones en paquetes de unos 1000€, y nos cobran 15€ de comisión de compra, de entrada estamos pagando un 1,5%, y si vendemos y compramos con frecuencia (algo nada recomendable), o si compramos acciones en pequeños paquetes, el porcentaje aumentará. Si hacemos buy&hold puro con acciones, estas comisiones de compra con el paso de los años irán desapareciendo. En el caso de los fondos, no ves un flujo de dinero que te quiten, pero internamente el fondo te va restando sus comisiones internas. Todo esto es muy importante, y puede ser el motivo del mal rendimiento en tus inversiones. Por ello he incluido estas comisiones anualizadas, en la ficha «Resumen» (F7 y G7), para que lo tengas muy presente.
4-Detalles cartera (número acciones, precio medio compra, …). Junto con el listado de los activos en cartera y su gráfico de barras, hemos anotado información importante de interés. Saber, además del valor de cada inversión, el número de acciones o participaciones, el precio medio de compra, el valor actual de ese activo, y si lo tienes en verde o rojo (con ganancias o pérdidas), es necesario.
Además esta tabla es ahora dinámica y contiene la posibilidad de filtrarla, en un desplegable, con lo cual puedes ver la cartera global por defecto, pero si así lo eliges puedes ver solo tu cartera de acciones, o fondos value, o fondos indexados.
Nota: La tabla dinámica solo está implementada, parcialmente ya que parte de las columnas son estáticas. Soy nuevo en tablas dinámicas, y no he sabido cómo incluir los campos precio medio y actual con cálculo preciso en la tabla dinámica. Por ello en esa parte uso celdas estáticas que van bien. Si hay voluntarios se agradecerá, si no así es aceptablemente funcional también.
5-Liquidez. Para aquellos que incluis la liquidez como parte de la cartera global (aquella reservada para invertir cuando haya oportunidades de mercado), decir que es posible gestionarlo. Normalmente esa liquidez la tenéis en una cuenta corriente con algo de rentabilidad (p.ej. Coinc al 0,3%, Pibank 0,5%, etc.). Se consigue gestionarlo en este excel simplemente anotando el dinero en la cuenta como una inversión en la pestaña «DatosInversiones». Hay que usar de ticker «EUR» y de precio unitario «1€», así si tienes 5000€ pones que tienes 5000 participaciones. Si la cuenta da intereses, pues lo anotas como un dividendo en la pestaña «DatosDividendos» con ticker «EUR» y tantas acciones/participaciones como euros en la cuenta. Esos intereses los puedes anotar mensualmente, o para que no sea pesado, creo que vale con una vez al año.
6-Pestaña de rentas por dividendos. Como hay gente que le encantan los dividendos y otros que no los quieren por considerarlos poco eficientes, he dejado la parte de los dividendos en una nueva ficha separada llamada «Rentas». De esta forma también queda más limpia las ficha de «DatosDividendos» conteniendo solo los datos crudos y sin gráficas.
7-Otros. En la ficha de rentabilidades he quitado la fila de rentabilidad MWR ya que esta rentabilidad, al no tener que usar las cotizaciones de tu cartera en el tiempo mes a mes, no es apropiada para medir intervalos tan pequeños. Es una rentabilidad muy buena, pero creo que con verla solo en la ficha «Resumen» es suficiente. Así, la pestaña «Rentabilidad» se centra exclusivamente en rentabilidad TWR.
Guía de uso paso a paso
Bien, ya tienes el Excel en tus manos, lo has echado un vistazo, entiendes un poco por encima su funcionalidad, pero ha llegado la hora de empezar a rellenarlo, quitando los datos del ejemplo y poniendo tus datos de inversión. ¿Por dónde empiezo?, te preguntarás. Yo lo haría siguiendo estos pasos:
1) Modifica las pestañas de Datos (DatosInversiones y DatosDividendos): El sitio natural para empezar a añadir tus datos es en la pestaña de DatosInversiones. Te recomiendo que insertes nuevas filas, y por el momento no borres las del ejemplo. Para añadir tus compras históricas pasadas, o las nuevas que hagas en el futuro, solo tienes que ir insertando nueva filas en blanco a continuación de la última rellena. Tras insertar la fila, deberías concentrarte en anotar las celdas de las columnas con fondo amarillo. El resto de las columnas contienen fórmulas, y por tanto se rellenan solas una vez que añadas tus datos en las celdas amarillas.
El mismo proceso lo repites en la pestaña de Datosdividendos. Si en algún momento alguna fórmula no se replicase al insertar una nueva fila, hazlo tú manualmente (copiando y pegando o mejor arrastrando la esquina derecha inferior de la celda con la fórmula hacia abajo donde están las nuevas filas creadas). Y eso es todo al meter tus datos. Una vez tienes tu fuente de datos en estas dos pestañas ya tienes la mayor parte del trabajo duro hecho.
2) Actualiza las cotizaciones de tus inversiones (una vez a fin de mes). 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» de tu Excel. De esta forma actualizarás el valor de tu cartera en «Resumen» (C7). Dale con el ratón en «Datos>Actualizar todo» para asegurarte que las fórmulas se refrescan.
3) Anota el valor de tu cartera («Resumen», C7) en la última fecha de la hoja de «Rentabilidad». 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 C7 en pestaña «Resumen», y pegas esos euros en la casilla B81, o siguientes, de la pestaña «Rentabilidad». Esta es la única forma de que tengas un historial del valor de tu cartera y puedas calcular la rentabilidad TWR.
Debes hacer lo mismo en las columnas I, M y Q de «Rentabilidad», para poner la cotización de los indices Ibex, S&P500, y MSCI World, que puedes obtener siguiendo los enlaces en esa misma pestaña. Así te podrás comparar, en términos de TWR, con estos índices (u otros que quieras usar). Es como lo hacen los profesionales.
Nota: Si llevas muchos años invertido, y no dispones de este historial del valor de la cartera, no podrás calcular el TWR pasado en el excel, salvo que los regeneres tirando de precios históricos en internet de cada activo que tengas (tarea muy dura). Sin embargo, si tienes los datos completos en DatosInversiones y Datos Dividendos, estás de suerte, ya que podrás calcular la rentabilidad MWR pasada y futura (la que se muestra en la pestaña «Resumen» en H7 e I7).
4) Visualiza tu cartera (verifica y analiza). Una vez has hecho lo anterior, deberás comprobar que no hay errores en fórmulas, si surgiesen deberías revisar que las fórmulas están en los campos adecuados y que no te faltan datos.
Si todo se ve bien, ahora es el momento de analizar cómo vas, cómo es tu diversificación, cómo van tus dividendos crecientes, analizar las comisiones anuales que pagas y si puedes mejorar, ver tus rentabilidades históricas (no importa mucho a corto plazo), pero tras 4 o 5 años invirtiendo ya debes poder sacar conclusiones de si tu cartera lo hace bien o si los índices te baten. Disfruta de este momento, una vez al mes, y de forma un poco más intensa tras finalizar un año.
El enlace al Excel nuevo (2.0) lo tienes en la en la zona de recursos. Y dejo la versión anterior (1.0) por si a alguien le gustaba más la otra.
Update (1/Mayo/2019): Os aviso que hay una nueva versión 3.0 disponible en la sección de recursos. Aquí tenéis la entrada (excel 3.0) que explica los cambios.
Bueno hasta aquí la entrada de hoy, espero que te hayan gustado las novedades. Si te parece útil el Excel, tienes dudas o quieres colaborar en mejorarlo, solo tienes que decirlo.
Promoción: Consigue una tarjeta Revolut gratis gracias a este enlace:
Muchas gracias de nuevo. Lo probaré enseguida.
Un saludo.
Muchas gracias! Tengo la 1 y es muy util. Una duda, ¿como se registrarían aportaciones periódicas a fondos?
Hola Damian, las aportaciones periódicas debes incluirlas una a una en una nueva fila tras la compra. No queda mas remedio pues tienes que anotar el precio y numero de participaciones que finalmente te diga tu broker.
Antonio
Buenas!! Voy a animarme a usar este magnifico excell para hacer un seguimiento de mis inversiones. Quiero rellenarla desde el principios (finales de 2015) y sera una tarea muy pesada por la cantidad de movimientos de compraventa que hice, sobre todo al principio… Mi duda es que si en el futuro decides actualizar el excell con nuevos apartados y funcionalidades, habría alguna forma de exportar todos los datos de golpe del excell antiguo al excell actualizado?
Un saludo y gracias por todo!!
Hola Fran,
Será pesado hacerlo si tienes muchos movimientos, pero si este excel representa mejor el seguimiento respecto a lo que ya tenías merecerá la pena.
Si en el futuro se modificase el excel, solo te afectaría si los cambios son por incluir una nueva columna en la pestaña de datosInversiones o datosDividendos, y eso no sería mucho problema, ya que bastaría con que tu insertes esa nueva columna en tu excel y pongas los datos nuevos necesarios (que igual son solo una fórmula). Pero creo que ya está todo o casi todo recogido actualmente y poco cambio hace falta. Si el cambio es en las pestañas resumen o dividendos, puedes copiar y pegarlas esas nuevas pestañas en tu excel actual, o hacer la actualización a la inversa: coger los datos de tu excel actual y pegarlos al excel nuevo. Parece lioso, pero no es gran dificultad.
Saludos
Hola,
Gracias por tu gran aportación. Tengo una duda al respecto de cómo modificamos aquellos valores (Tikets) que ahora mismo no están en tu lista y por otro lado si conoces la posibilidad de obtener los datos sin que tengamos que entrar el último día del mes para recoger la información de las últimas cotizaciones del mes.
Muchas gracias.
Pedro, Las empresas nuevas se insertan añadiendo una nueva línea en la tabla de googlesheets, y ahí poner el ticker nuevo y resto de campos, y te aseguras que la fórmula de precio esté puesta en la fila, y ya está.
Lo de poder coger las cotizaciones del final de mes, sin hacerlo ese mismo día lo estudiaré y ya os digo.
Antonio
Perfecto Antonio, muchas gracias.
Pedro,
He actualizado el googlesheets «Cotizacionescompleta» (https://docs.google.com/spreadsheets/d/1pSs2mC38gaKbqUYBkmMv7s1sbFWaVJmVJS7GvdXpyDk/edit?usp=sharing)
para que se pueda poner la fecha que quieras (por ejemplo la fecha de fin de un mes pasado), y así saber la cotizacion al cierre de ese día. Era una de tus sugerencias, que realmente es muy útil.
Funciona para acciones, pero para fondos y ETFs, aun no se exactamente cómo hacerlo, pues hay que hacer webscrapping y elegir los valores de precio deseados de una lista no muy regular y dispersa (el primer intento de FT no me ha funcionado). Es más complejo.
Espero que con solo las acciones ya te ayude bastante
Antonio
Gracias Antonio. Aún no he podido estudiarla por falta de tiempo pero los cambios que describes en el artículo son muy interesantes.
Laertes,
Estupendo, cuando puedas, seguro que aportas ideas. Y abuso un poco de ti y te pido más:
Si sabes de webscrapping, estaría bien para el googlesheets poderlo actualizar a cualquier fecha pasada, como le digo a Pedro arriba (en acciones ya va, pero fondos no).
O si sabes como coger las cotizaciones directamente desde Excel, sin requerir el uso del googlesheets, estupendo. yo en su época lo hacía así desde excel, pero no se conectaba bien el excel a las fuentes de datos (web externas), o tardaba mucho, y no me gustaba el resultado. Desde entonces uso la solucion de googlesheets, que va algo mejor, aunque sea manual en parte con el copy pega, y aparezca el «loading» muchas veces. Cualquier cosa que mejore esto será bienvenido.
No se si el plugging de power BI en excel mejora esto de importar datos de cotizaciones y fondos.
A ver si se te ocurre algo. Muchas gracias.
Antonio
Ya he mirado más en profundidad la hoja y me parece una mejora apreciable respecto a la primera versión. En cuanto a tus preguntas ya lo siento pero me temo que mis conocimientos de Excel no llegan a tanto, al menos de momento.
Cambiando de tema, ¿tu libro a partir de qué edad es recomendable? Estoy pensando en regalar uno pero no sé si será apropiado.
Hola,
Bueno, gracias. Ahora estoy auto aprendiendo a hacer webscrapping en excel y espero de sus frutos pronto.
Respecto al libro, a partir de 13 años lo podrian leer, ya depende del niño. A mi me lo revisó una niña de 11 años y pudo con el, pero era especialmente lista. Lo puedes regalar a sobrinos, que luego a los padres si son abiertos al leer cosas tabú de dinero, les suele encantar también .
Gracias por todo
Entonces todavía le faltan unos añitos, pero lo de los padres es buena idea también.
Enhorabuena por el excel, un gran trabajo que me está siendo muy útil, llevaba tiempo buscando algo similar.
Una pena que no se actualicen los precios de los fondos, espero que entre todos podamos corregirlo, porque es realmente útil.
Una duda que tengo, cuando en lugar de cobrar dividendos he cobrado acciones (programa dividendo elección del Santander), supongo que hay que insertar una nueva fila en adquisiciones, no en dividendos, a coste cero, ¿no es así? Es que veo que has puesto el scrip dividendo como dividendo, pero esto solo es en el caso de cobrar derechos, no de recibir acciones, ¿no es así?
Muchísimas gracias, da gusto leer tu blog, para mi el número uno de la comunidad, pocas entradas pero todas de muy alta calidad
Hola Daniel,
Me alegra saber que te gustan los contenidos y que el excel encaje con lo que buscabas. todos hemos pasado por esa fase. Los precios en la hoja de google sheets se actualizan en el día, y los de días pasados también en acciones. Pero falta, como dices, que se actualice en fondos (tarea en stand-by). El motivo, es que he pensado que esa parte de los precios en googlesheets es posible que ya no nos haga falta, pues estoy trabajando en la version 3.0 para poder leer los precios directamente desde excel, sin página de google de apoyo. Ya leo acciones, fondos y ETFs, del día actual y de días pasados. Cuando lo tenga listo ya lo publico, pero hay que depurarlo, pues hay mucho código vba, y debo ver posibles problemas prácticos. Pero si yo logro que funcione todo, será una gozada.
Lo otro que dices de las acciones recibidas en scriptdividend, correcto, si vendes los derechos (tanto a mercado como a la empresa) lo pones en DatosDicidendos, y si coges las acciones lo pones en DatosAcciones en una nueva fila a precio de compra cero.
Si ves alguna funcionalidad que falte, no dudes en decirlo. Vuestros comentarios ayudan, y me dan ideas de mejoras.
Saludos
Buenas!
Pues nada, llevo unos días trasteando con el excel y tengo algunas dudas, a ver si me podéis ayudar…
En la hoja Resumen, como añado y/o modifico las subcarteras disponibles? no consigo hacerlo no se porque.
En la hoja Rentas, el gráfico de evolución de dividendos por mes, que pasos tendría que seguir para añadir mas años?
Mis conocimientos de excel son básicos, de ahí estas dudas.
Un saludo y gracias por todo!
Hola Fran,
Para las subcarteras, en la hoja Resumen, no deberías hacer nada, salvo quizás dar a la tecla F5 para actualizar (o con botón derecho raton sobre la tabla dinamica de activos). Los datos de las subcarpetas se actualizarán una vez que los pongas el nombre d elas subcarpetas que deses en la columna correspondiente de la hoja «Datos Inversiones».
En la hoja de Rentas, simplemente copia y pega una fila de la tabla de meses (desde enero a media mensual) en otra fila libre, y revisa las fórmulas para asegurarte que están bien.
Espero te sirva
Buenos días,
Perfecto, muchas gracias por contestar.
Seguiré operando con ella para ponerla al día con todos mis movimientos.
Un saludo y gracias!
Buenos días,
Muchas gracias por poner estos recursos a disposición.
Mi conocimiento de Excel es básico, pero al empezar y rellenar algunas líneas, no consigo que lo blanco se complete solo.
Tengo un Mac y al abrir el archivo me sale un mensaje y le doy a “Habilitar macros”. Pero no se si este puede ser el problema o en Mac funciona en principio perfectamente.
Tom,
Supongo que estás insertando filas en las tablas en DatosInversiones o DatosDividendos. En este caso si tras seleccionar la fila completa, le das a insertar fila, debe aparecer una fila nueva con las fórmulas ya copiadas. Si por lo que sea no se han copiado las fórmulas, hazlo manualmente arrastrando la fórmula de arriba (por ejemplo como hacen aqui: https://www.youtube.com/watch?v=Z2t_VWF8JVE)
No debería haber problema con el Mac, si usas microsoft excel (si es otra versión de mac compatible con excel no lo se).
Si no dominas excel como para poder insertar, pegar filas y columnas, y entender lo básico de las fórmulas, te recomiendo que veas antes algún tutorial por internet, hasta que cojas un poco de soltura.
Saludos, Antonio
Hola Antonio, muchas gracias por el Excel, está super completo. Voy a coger ideas para mejorar el spreadsheet que tengo para el seguimiento de cartera. Comentarte 2 cosas que te puede interesar:
– Cotización de fondos y ETFs: lo más cómodo es usar el add-on µFunds que te permite actualizar la cotización a partir del ISIN: https://www.mufunds.com/
– Data Studio para dashboard, uso esta herramienta gratuita de Google para montar el dashboard a partir de la información del Excel/Spreadsheet. Quizás es un poco más de lío porque hay que “normalizar” los datos para poder sacar el jugo a la herramienta. Pero bueno es una idea.
Saludos!
Hola Pasape,
Muchas gracias por tus sugerencias de mejora. No las conocía, pero veo que son muy buenas opciones.
Lo del Data Studio, tiene buena pinta, parece seguir un enfoque como el de Power BI de Microsoft, que también me habéis sugerido alguno. Por el momento, no los uso, pues serían una capa final de integración de fuentes de datos y visualización online en un dashboard.
La de mufunds, lo acabo de probar, y ya lo usado en el googlesheets que comparto, pero no veo que se puedan coger cotizaciones del pasado. Esta funcionalidad de precios pasados es la última incorporación del googlesheets que comparto, que ya funciona para acciones y ETFs, pero para fondos no se todavía hacerlo, y mufunds no lo soporta. He dejado un aviso en el github de mufunds para ver si incluyen esas funcionalidad. Si lo ampliasen estupendo.
En cualquier caso, en la version 3.0 del excel ya tengo resuelto el acceso a acciones, fondos, ETS, y divisas, y todo descargable pulsando un botón desde el excel (sin usar googlesheets de apoyo), tanto para precios actuales como pasados. Al final con el excel y código VBA hago lo mismo que muFunds, conectarme a web externas (Yahoo, FT, o investing) y coger los precios de las web, por web scrapping (precio actuales y históricos).
Si alguién tiene más ideas, o formas de mejorar la forma de llevar el control de las acciones, bienvenidas sean. Un aspecto importante, y que no he resuelto, es llevar el control de plusvalias fiscales y de mercado (algo que se necesita si se traspasan fondos).
Un saludo
Antonio
Genial, esperaremos con ganas la versión 3.0!
Otra idea que estaría bien es crear una pestaña donde se «definen» los productos (acciones, fondos o etf) con los campos: nombre, ticker, % RV, tipo, subcartera, moneda, mercado y cotización actual. Con esto, conseguimos no tener que repetir todo esto en los datos de inversiones, ya que simplemente con VLookup se podría coger automáticamente.
Gran trabajo!
Pasape, es buena sugerencia esa, lo estuve pensando poner, pero al final pensé que mejor no poner más pestañas. Al fin y al cabo repetirlo es fácil, pues es solo copiar lo que ya tienes en una compra anterior. Ya veré si me decido a hacerlo.
Gracias
Hola, muchas gracias por compartir tu trabajo! Estoy intentando adaptar la excel a mi cartera modificando alguna cosilla (por ejemplo, la rentabilidad la he puesto semanalmente, 1 mes me parece mucho). De momento me ha surgido una duda con “liquidez” en Datosdeinversiones porq no se exactamente como funciona. Hay que poner la liquidez que tienes al final todos los periodos?? (se me hace engorroso). Seria mas interesante reflejar el ingreso y a partir de ahi, que la excel te indique la liquidez gracias al resto de transacciones.
Gracias de nuevo, con lo que vaya viendo lo compartire aqui.
Edu,
Te deseo que te quede muy bien el excel adaptado, y con TWR por semanas.
Lo de la liquidez, en principio en DatosInversiones, si tienes una liquidez constante, solo tienes que anotarlo una vez, como una compra de liquidez. Los intereses de esa liquidez irían en Datosdividendos (que puedes agrupar en un solo pago a fin de año). Si tu liquidez cambia, y pasas de tener 5.000 a 8.000 euros, tendrías que anotar otra nueva compra de liquidez de 3.000€. Y si baja, tendrías que vender parte. Creo que hacer todo esto es a lo indicas que te parece un lío.¿no? Pero, no hay que hacerlo todas las semanas, solo cuando cambie tu liquidez.
Si se te ocurre otra forma de hacerlo, en tu proceso de adaptación de la plantilla, ya nos cuentas…
Saludos
Antonio
Humm…., si, cuando la tenga hecha la dejo por aqui. La verdad es q esta muy bien y no te creas q voy a innovar mucho, pero claro, he de adaptarlo. Por ejemplo, yo tengo otros brokers y las comisiones las calculo automaticamente segun las tarifas de mi broker. A mi no me dicen el coste bruto para sacar la comision, me dicen las comisiones para calcular el coste bruto. Lo de la liquidez es un poco extraño, para mi eso serian ingresos. Por ejemplo, si yo abro una cuenta y meto 20000€, tengo 20000€ de liquidez, pero segun vaya comprando o vendiendo acciones, la liquidez va variando y tal como esta concebida la hoja, creo que no te calcula la liquidez de cada momento. por eso digo, que lo que tu indicas como «liquidez», para mi serian ingresos. Cuando la termine la cuelgo o la paso.
Aunque aun tendre que hacerte alguna pregunta,çGracias
Edu,
Ya te entiendo. Tú quieres que la liquidez sea el resultado de restar un dinero ingresado de lo que vayas invirtiendo, y que al invertir, esta liquidez se vaya modificando sola. Para ti la liquidez = colchon – aportaciones + reembolsos. La liquidez es para ti lo que aun te queda por invertir. Lo veo bien.
Yo sin embargo lo planteo como que la liquidez es otro activo más, es decir, un dinero que tengo invertido (en un deposito a corto, o en una cuenta remunerada, o en un fondo monetario) y que me da ciertos intereses. Cierto, que tiene la desventaja, que si compro acciones/fondos, entonces debo vender liquidez (una doble operación).
Quedo a la espera de ver como lo implementas. Seguro que queda bien tal y como dices…
gracias
antonio
Bueno, de momento ya tengo todos mis datos metidos, pero me encuentro con un problema estupido q me impide continuar. A ver si alguien me da una idea. En la hoja rentabilidad, pretendo q la columna B me la de automaticamente mediante la formula «=SUMAR.SI(TablaDatosInversiones[Fecha Compra];»<@Fecha]";TablaDatosInversiones[Rendimiento mercado neto (euros)])", pero me da resultado 0. Es curioso, porque si no fuera tabla y pusiera en vez de "<@Fecha]" una fecha concreta, por ejemplo "<12-12-2018", si que me da el resultado correcto.
Edu,
Si lo que quieres es rellenar la columna B completa «Valor de la inversión» (para todos los meses de cada fila) siento decirte que no es posible, ya que para poder hacer esto necesitas tener el historial de las cotizaciones de todos los productos mes a mes, y así obtener el «Valor de la inversion» de tu cartera en cada uno de los meses.
Los datos que tienes en la TablaDatosInversiones son solo para las cotizaciones del día que hayas cargado en la pestaña de cotizaciones. Si no tienes el historial de cotizaciones pasadas, deberás regenerarlas, e irlas poniendo una a una en las casillas de la columna B de «Rentabilidad». Si son acciones, con GoogleSheets se puede hacer relativamente rápido.
¿Es eso lo que querías hacer?
Antonio
si, si que se puede ¿tienes un email y te lo mando?
Hola Edu, sí mándamelo y lo miro. Mi email es olvidatedetucerdito@gmail.com
Buenas tardes, un gran trabajo. Yo he realizado unos cambios por ejemplo en “Resumen” el precio medio de compra he cambiado la formula porque la que estaba no estoy seguro de si está bien o mal.
Yo creo que el precio medio de compra de cada Valor es la de dividir todas las acciones entre el total de compras de ese valor.
Podrías facilitarme la formula de googlefinance para añadir una columna de “capitalización”
Un saludo
Asoma,
Gracias por tu comentario. El precio medio de compra es la suma del coste de todas tus compras dividido por el numero total de acciones que posees (moneda/acciones)
En el excel lo calculo asi, con numerador igual al coste total, y denominador el numero de acciones totales:
=SI.ERROR(SUMAPRODUCTO((TablaDatosInversiones[Nombre]=B43)*1;TablaDatosInversiones[nº unidades compradas (acciones o participaciones)];TablaDatosInversiones[Precio pagado por unidad (moneda)];(TablaDatosInversiones[Operación abierta (cotizando)]=VERDADERO)*1)/SUMAPRODUCTO((TablaDatosInversiones[Nombre]=B43)*1;TablaDatosInversiones[nº unidades compradas (acciones o participaciones)];(TablaDatosInversiones[Operación abierta (cotizando)]=VERDADERO)*1);»»)
Creo que está bien. No le veo el fallo.
Respecto a la capitalizacion de googlefinance se consigue poniendo «marketcap», tal y como se explica aqui:
https://support.google.com/docs/answer/3093281
Saludos
Hola buenas tardes. Lei en algún comentario tuyo que estabas preparando una tercera versión mejorada del excel
Para cuándo prevees que pueda estar listo? Muchas gracias de antemano, un saludo
Hola Daniel,
La versión 3.0 está casi lista, de hecho me gustó tanto la simplicidad de la 2.0 que preparé para vosotros, que estos días he estado pasando mi propia hoja excel, que había crecido demasiado y de una forma más desorganizada, a la nueva versión. Ahora mismo mi propia cartera al completo ya la estoy siguiendo con la versión 3.0. La estoy refinando, incorporando alguna funcionalidad extra que tenía en la antigua, y cuando la deje un poco más a mi gusto, y depure posibles errores que vea en estas semanas próximas, ya la podré medio vaciar de mis operaciones personales y compartir con vosotros.
Espero que os merezca la pena la espera.
Un saludo
Antonio
OK muchas gracias
PD: qué bien nos vendría tener esta nueva versión para hacerlo estas vacaciones de semana santa XDD
Daniel, siento el retraso pero no he podido acabarlo antes.
Afortunadamente la versión 3.0 ya está disponible desde hoy.
Espero te haya merecido la pena la espera
Hola, felicidades por la hoja excel.
En cuento a los script y pago de dividendos mediante acciones, si coges acciones está claro que sería como una compra a precio 0.
Pero si vendes los derechos, en realidad estas bajando el precio de la acciones por lo menos hasta el año 2017 que cambio la tributación. A partir de ese año serían como dividendos.
Yo creo que debería ser recogido en la hoja de DatosInversiones, de forma que el precio medio del valor contemple el valor de los derechos vendidos.
Un saludo
Álvaro
Hola Álvaro,
Cierto todo lo que dices. Si vendes los derechos, y fue antes del 2017, depende de si lo vendías en el mercado o a la empresa. Si la venta era a la empresa, los consideras como dividendos, pero si los vendías al mercado habría que rebajar el precio medio de compra. En un caso lo pones en DatosDividendos, y en el otro en DatosInversiones, sí, cierto como lo dices. Sin embargo ahora (desde el 2017 en adelante), ya la venta de derechos siempre tributa como un dividendos y te retienen el 19%, por tanto va a DatosDividendos.
Para los que necesiten verlo más despacio, aqui lo explica en detalle: https://www.bolsamania.com/declaracion-impuestos-renta/fiscalidad-de-los-dividendos-en-forma-de-acciones/
Un saludo, y gracias por contribuir.
Antonio
Hola
Podrias poner un ejemplo de como hacer esto? No consigo introducir la venta de derechos en el 2016 y hacer que el precio medio de adquisicion baje.
Un saludo
Jose
Hola Jose,
Si es venta de derechos al mercado (no a la empresa) antes de 2017, como ya dices en 2016, entonces hay que minorar el precio de compra de las acciones que te han dado «derecho» (valga la redundancia) a esos derechos vendidos. No era como un dividendo en esa época y se anotaría en DatosInversiones. Yo nunca lo he tenido que hacer, pero si lo hiciera simplemente restaría el precio de cada derecho (ponderado por la relación entre los derechos que te dieron por acción) del precio de compra de las acciones.
Por ejemplo (un caso inventado), si compraste 100 acciones de Repsol a 10€/acción en 2010 (1000€ invertidos), y en 2016 te dieron 1 derecho por cada 4 acciones de Repsol, tendrías 25 derechos, que si los vendiste por 0,2€/derecho, habrías obtenido 5€ (25×0,2). Por tanto son 5€/100 acciones=0,05€/acción tu minoración del precio de compra. Por tanto con este dato de 0,05€ iría a la fila en DatosInversiones donde tienes el precio de compra de tu inversión de 1000€, y pondría «=10-0,05», es decir el precio de compra sería 9,95€, en vez de los 10€/acción que previamente tenías anotado.
Espero que así se entienda.
un saludo
Antonio
Muchisimas gracias!
Hola, lo primero felicitarte por tu gran trabajo y darte las gracias por compartir un Excel que me está ayudando mucho…
Voy a ver si soy capaz de explicarme… cuando añades en la pestaña DatosDividendos un ingreso por Scrip y este es parcial (es decir has vendido una parte de los derechos y otra los has convertido en acciones), tal y como tienes la fórmula de sumaproducto de la columna W de la pestaña DatosInversiones el dato obtenido es incorrecto y superior al real. La función es correcta si has vendido todos los derechos pero no lo es si solamente has vendido una parte de ellos.
Creo que llevo razón así que quizás los ingresos por scrip dividend habría que calcularlos y sumarlos en otra columna, o modificar la fórmula, … no estoy muy seguro, si puedes compruébalo.
Un saludo.
Hola Jose Antonio,
Gracias por el aviso. Es posible que tengas razón. A ver si lo miro en otro momento con más calma. Si se te ocurre una forma de solventarlo, encantado de oir propuestas.
Saludos y gracias por compartir.
Antonio
David555,
Haz la venta parcial duplicando la fila. Tendrás una fila con 180 acciones, por tanto al mes cuando vendas, solo tienes que copiar una nueva fila debajo (dando doble-click) y poner en la primera fila 90 acciones (las que no vendes), y en la fila de abajo las otras 90 (las vendidas), y en este último caso, le pones la fecha de venta. Y ya está, has anotado tu venta parcial.
Antonio
¿como hago para introducir una venta parcial? me refiero a si compro 180 acciones y al mes siguiente vendo 90. Muchas gracias por tu trabajo.
hola buenas pero no se donde descargar la plantilla de excel solo he podido descargar la de cotizaciones
Hola, el excel lo tienes en recursos -> excel
https://olvidatedetucerdito.com/recursos/plantillas-excel/
Buenos días, tengo una duda: ¿cómo resuelves compras y ventas parciales? Porque yo he ido comprando y vendiendo parte de mis posiciones, no el 100% de las mismas. Muchas gracias por tu trabajo.
Hola Rodrigo,
En ese caso, si no vendes todo, tienes que duplicar la linea de tu activo, y repartir la compra entre la parte que permanece, y en la otra linea anotas lo que has vendido. Para inversión a largo plazo buy&hold no es mucho problema, pero si rotas mucho sí. Espero te sirva.
Antonio
Hola, me va todo perfecto excepto la cotizacion de una de mis acciones. Me da error y no me aparece el precio, el ticker esta bien, ICAG (Iberia), pero me sale ERROR 91, linea 7. Sabes como podria solucionarlo?
Hola,
El ticker de Iberia y British en Madrid es IAG.MC
A ver si asi te va
Un saludo
Antonio
Hola,
He visto un pequeño problema cuando se usa mas de 1 broker. Supongamos que tenemos dos brokers: A y B. Compramos 100 acciones de la empresa XXX a 10,00 EUR por accion el dia 1 de Enero en el boker A. Luego compramos 50 acciones de la misma empresa a 7,00 EUR por accion el dia 1 de Marzo en el broker B. Luego vendemos 50 acciones de la misma empresa desde el broker B a 8,00 EUR por accion el dia 1 de Mayo.
Lo que tendriamos seria lo siguiente:
Linea 1 — Compra 100 Acciones 1 Enero Broker A 10,00 EUR
Linea 2 — Compra 50 Acciones 1 Marzo Broker B 7,00 EUR. — Venta 50 Acciones 1 Mayo Broker B 8,00 EUR
Esto reflejaria la realidad de la situacion, ya que seguimos manteniendo 100 acciones en el broker A y ya no tenemos ninguna accion en el broker B. Segun estos apuntes estamos teniendo una ganancia patrimonial de 50 EUR (1,00 EUR de ganancia por accion). Sin embargo, este calculo de la ganancia patrimonial no seria correcto, ya que de acuerdo al criterio FIFO las acciones que se vendan son las primeras en entrar, independientemente del broker donde se compro. Realmente hemos tenido perdida patrimonial, ya que estariamos vendiendo (de cara a Hacienda) las acciones del broker A.
En resumen, segun como esta implementado el excel se presentan dos alternativas:
– Hacer el apunte como he comentado anteriormente. Esta opcion muestra el numero de acciones actuales en cada broker pero no calcula bien la ganancia patrimonial.
– Hacer el apunte de venta en el broker A a pesar de que la venta se hizo desde el broker B. De esta manera nos saldria que tendriamos aun 50 acciones sin vender en broker A y otras 50 en el broker B, lo cual no es real. Sin embargo, el calculo de la ganancia patrimonial se haria correctamente.
A alguien se le ocurre alguna solucion a este problema?
Un saludo y gracias
Jose
Hola Jose,
Gracias por tu comentario tan práctico y real. A mi me ha ocurrido ese caso.
Yo lo hago como dices en tu segunda opción. Y tras esas anotaciones cambio el nombre del broker de las acciones vivas a broker donde están realmente, y así ya coincide todo: las plusvalías y dónde se encuentran las acciones.
¿Te convence?
Saludos
Antonio
Hola Antonio,
Me parece una buena solucion.
Gracias y un saludo
Buenas,
He visto algo raro en la herramienta para aflorar perdidas. He puesto un pantallazo aqui -> https://we.tl/t-NHavFgacOj
Me sale que pago menos impuestos si no hago nada que si afloro perdidas. Esto no puede ser verdad?
Un saludo y gracias por vuestra ayuda
Jose,
Gracias por el aviso. Sí, hay algo mal en la fórmula de la casilla A17. Ya lo revisaré
Lo que te sale en A21 y B21, parece bien calculado, y es básicamente lo mismos que te debería salir en A17 y B17.
En tu caso, no debes aflorar más, ya que has saturado por 2.000€ (que tendrás que compensar con ganancias de los 4 años siguientes). Al salir un número negativo en la casilla amarilla significa que NO hay que aflorar más (o que no puedes beneficiarte este año por aflorar más). Eso tendríamos que modificarlo para que cuando sea negativo ponga «cero» o «no necesario» en vez de un número negativo que es poco intuitivo.
Saludos
Antonio
Hola Antonio,
Esta herramienta esta genial. Lo que había pensado es vender con ganancias por 2000 EUR para cuadrarlo. Tendrían perdidas por 1000 EUR que se compensan con el 25% de los dividendos. Así minimizo factura fiscal no?
Bueno lo de vender por ganancias, solo hazlo porque ya no confíes en esos fondos o acciones, pero no solo por compensar. Date cuenta que los 2000 de pérdidas que te quedan te sirven para ir compensando el 25% de tus dividendos en los dos próximos años. Es solo una opinión
Hola Jose Luis Escobosa,
Ya he detectado los problemas de las fórmulas en la pestaña aflora.
Estas son las nuevas fórmulas que he puesto:
«=SI(H12>0;C12+H12;C12-minimo(B4*C12;-H12))»
«=SI(B16<6000;B16*0,19;6000*0,19+(B16-6000)*0,21)"
"=SI(H12<0;SI(-H12<(B4*C12);0;-H12-B4*C12))" "=SI(C12*B4+H12>0;C12*B4+H12;0)»
«=(100%-B4)*C12»
«=SUMA(B22*0,19;(B22>6000)*(B22-6000)*0,02;(B22>50000)*(B22-50000)*0,02)»
Respectivamente para estos campos (que he renombrado):
Rendimientos imputables (Cajón1+2):
Impuestos a pagar (19%,21% y 23%):
Minusvalías para años siguientes:
Si afloras estas pérdidas adicionales:
Rendimientos imputables (Cajón1+2):
Impuestos a pagar (19%,21% y 23%):
Lo puedes copiar y pegar, pero para que no te líes, puedes bajarte una nueva versión del excel, 3.5 (diponible en un rato), solo para que abras la pestaña aflora y veas cómo queda todo.
Saludos
Antonio