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.
Promoción especial para ti:
Consigue un 0,45% de descuento en comisiones el primer año al abrir una cuenta de inversiones en IndexaCapital (en los primeros 10.000€). Por ejemplo: Si inviertes 10.000€ te ahorras 45€, si son 2.000€ te ahorras 9€, etc.
Promoción exclusiva de OlvidatedetuCerdito por contratarlo siguiendo este enlace o pinchando en la imagen superior. Tú te beneficias y de paso ayudas a esta web que recibirá un incentivo.
Promoción: Consigue una tarjeta Revolut gratis gracias a este enlace:
Hola buenas tardes, intento descargar el Excel y no me deja. No se que puede pasar??. Gracias
Deberías poder Eric. Usa otro navegador o vigila que no te bloquee la descarga por ser un zip comprimido
Hola .
Q buena plantilla pero podrias explicar paso a paso por favor como se rellena ?
Es cierto que lo he contado un poco rápido, con la esperanza que una vez el excel en tu mano se vería más fácil de saber qué rellenar o no
Hola .
Podrias explicar paso a paso como se rellena por favor y como se hace lo del google drive?
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.
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?
Pues tienes razón, he debido compartir el enlace con derechos equivocados, y alguién lo ha estado editando, y ha quitado los fondos.
Voy a reestablecerlo en breve, y os doy nuevo enlace
Enlace nuevo: https://docs.google.com/spreadsheets/d/1pSs2mC38gaKbqUYBkmMv7s1sbFWaVJmVJS7GvdXpyDk/edit?usp=sharing
Hola, veo que la hoja de google no da resultados para los fondos (solo para acciones) ¿alguna idea de porque no funciona? no sería mejor usar una búsqueda en mornigstar?
Bueno Carlos, la hoja google sí funciona para fondos, pero con cotización en el día actual, no en fechas pasadas. La búsqueda de fechas pasadas está conseguido en el excel, no en el googlesheets.
Perfecto ahora. Gracias Antonio!
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.
Correcto, así es. Insertar fila, rellenas el nombre, mercado y el ticker, y moneda, y estiras la esquina de la casilla del precio para copiar la fórmula. ello se actualiza solo.
suerte
Hola.
Ante todo, muchas gracias por tu trabajo. ¿Es posible exportar este Excel a GoogleSheets?
Á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
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.
Á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.
De nuevo, muchas gracias por compartir tu excelente trabajo con nosotros. Esperaré expectante el Excel modificado.
Un saludo.
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?
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
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
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.
Gracias, ya sabes que la recibí, me parece un gran aporte, y también te conté esos pequeños detalles que me crean dudas. A ver si tras tus comentarios se resuelven esos inconvenientes.
Perdona, no he podido mirar lo que preguntas. A ver si mañana o el fin de semana saco un rato.
Perfecto, cuando puedas, si está para en finde estupendo. Si no, pues iría actualizando otros aspectos que también me han sugerido.
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?
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
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.
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.
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?
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.
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.
No encuentro el tiker del fondo en Finantial times ¿Donde lo busco?
Si te refieres a la version 3.0, dime qué fondo buscas y te digo el ticker y cómo buscarlo
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»
Por cierto: ¿Valdría también el ISIN de Morning Star?
Gracias por tu respuesta. Ya lohe solucionado.
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…..
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
Buenas
Me parece muy útil y muy trabajada la hoja excel. Estoy empezando a usarla y me ha surgido una duda: ¿como se introducen las primas de emisión o las primas de asistencia?
Gracias
Hola Fernando,
Las primas de asistencia a juntas de accionistas tiene el mismo tratamiento fiscal que los dividendos. Son rendimientos del ahorro ya que cobras un dinero proporcional al número de acciones, y te practican una retención. Por tanto el mejor sitio en el excel es que lo pongas como una nueva fila en DatosDividendos.
Respecto a las primas de emisión, supongo que te refieres a cuando la empresa reparte las primas de emisión que tiene acumuladas en reservas, y lo distribuye a los accionistas. En ese caso la guia del IRPF dice que debes minorar el precio de adquisición (al reducir tu precio de adquisición se retrasa tu pago de impuestos hasta que vendas las acciones). Por tanto simplemente resta al precio de compra la prima dividido por las acciones, y lo puedes hacer directamente en la fila ya existente de esa empresa en DatosInversiones. Si por cobrar mucha prima de emisión, llega un momento en que tu precio de adquisición vale cero, entonces debes tratarlo ya como si fuera un dividendo en DatosDividendos sin retención entiendo.
Espero haberte resuelto la duda
Saludos
Antonio
Buenas tardes,
Muchas gracias por el pedazo de excel que has trabajado con el tiempo, esta muy completo.
Tengo una duda, cuando divido una compra de acciones por que he vendido una parte de la compra total, los dividendos en la tabla de DatosInverison se duplican, como puedo solucionarlo. No se me ocurre nada sencillo.
Muchas gracias y un saludo.
Pablo
Pablo,
Gracias por comentar los problemas que puedan aparecer, pues siempre puede haber errores ocultos. Este me extraña un poco pues creo que me hubiese dado cuenta, ya que es gordo.
Para asegurarme he cogido la plantilla excel 3.5 de la web, la he vaciado y dejado solo Repsol, y he probado a vender la mitad, dejando dos filas de 250 acciones, unas vendidas a 1-ene-2020 y las otras todavía cotizando. Veo los mismos dividendos tanto en DatosInversiones, como en DatosDividendos, que son 1.512€. Con lo cual todo parece ir bien. ¿Estas seguro de haber vendido bien las acciones, poniendo la fecha de venta a mano, y repartiendo el número de acciones entre las dos filas?
Ya nos confirmas
saludos
Antonio
Hola buenas tardes Sr. Antonio, ante todo felicitarle por semejante herramienta que pone a disposición de todos gratuitamente, estoy empezando con ilusión a tener contacto con el mundo de la inversión e intento hacerlo a través de los CFD,S y las acciones.
Me gustaría preguntarle como podria actualizar este producto? (CFD) a través de su herramienta ya que en yahoo finances no encuntro esas cotizaciones,
No tengo ninguna intención de tocar ni ningún otro producto.
Quiza sea porque es el mismo precio los CFD’s que el de las acciones ya que son contratos por diferencias de estas mismas?
Me valdria este mismo precio de las acciones para los CFD’s?
Perdon por tanta pregunta pero soy completamente ignorante en esto de la inversion como ya le he comentado.
Muchisimas gracias por su atención,
Un saludo
Hola Luis,
El excel no está pensado para CFD. Pero supongo que de alguna forma se podrían integrar, y usar la cotización del activo subyacente. Sin embargo, si no eres experto en inversiones de productos derivados, como CDF, futuros, opciones, etc, te recuerdo que es la peor forma de empezar, pues son productos apalancados, que pueden hacer que ganes mucho, pero también que pierdas todo y te cierren la posición. Yo sin duda, me replantearía la estrategia, y ver si no te conviene más pensar en invertir en fondos, acciones, y no tratar de especular con apalancamiento.
Te recuerdo lo que dicen los brokers sobre los CFDs (https://www.selfbank.es/cfds): «Los CFD son instrumentos complejos y están asociados a un riesgo elevado de perder dinero rápidamente debido al apalancamiento. Entre un 74 y un 89 % de los inversores minoristas pierden dinero cuando comercializan con CFD. Debe considerar si comprende el funcionamiento de los CFD y si puede permitirse asumir un riesgo elevado de perder su dinero.»
Infórmate bien, y tómate un tiempo para pensarlo.
Un saludo
Antonio
Hola buenas tardes Sr. Antonio,
Agradezco muchísimo su consejo pero en ningún momento pienso invertir en mas cantidad del capital que tenga en mi cartera, por supuesto de momento hasta que no tenga una amplia experiencia no pienso en apalancarme, aunque me retengan solo la parte parte proporcional de la inversión que pretenda hacer no haré ninguna otra operación si no he cerrado previamente la abierta. Por otra parte estoy esperando que se acabe el confinamiento para encontrar cara a cara un (broker DMA) en el que me aseguren que no me cerraran posiciones siempre que yo así no lo indique.
De todas formas le agradezco enormemente su aporte, aunque no pueda trabajar con el ya que tengo otras necesidades que por otra parte no se como cubrirla ya que tengo muy claro que es lo que necesito para poder llevar un control de mis operaciones.
Enormemente agradecido por su respuesta, intentaré seguir a ver como podría obtener respuesta amis necesidades,
Un saludo,
Hola Antonio,
Me he bajado varias versiones de tu excel para tratar de ponerlo a funcionar con mi cartera de fondos.
que version me recomnedarias solo para fondos y como ponerlo a funcionar.
Hola Alejandro, la última versión es mejor (o eso intento) y está mas depurada que la anterior. Yo siempre uso la última, en realidad uso la última más los retoques extras que meto que estarán disponibles posteriormente.
Saludos
Hola Antonio
Me he descargado tu excell, pero no consigo que me funcione o no lo se hacer, no soy muy amigo de excell, como debo de hacer? borrar todos tus datos de las columnas naranjas y despues poner los mios ? perdona si es obvio.
Gracias
Lo mejor es que trabajes con la version descargada, que funciona, y empieces a añadir tus datos nuevos, dejando los viejos intactos. Cuando hayas metido bastantes tuyos ya puedes quitar los originales directamente borrando lineas completas de datosinversiones. Espero que asi te vaya bien
Saludos
Gracias, me ha funcionado, ahora lo que no acabo de entender es la conversion de dolares a euros, en la pestaña cambio de divisa pongo el valor al que estaba cuando compre, y precio pagado por unidad entiendo que sea en dolares tambien, pero luego no me hace la comversion bien ya que en vez de tener menos euros tengo mas pagados en la casilla total brutos.
Gracias un saludo.
Y si pongo en euros me sale bien en la casilla coste bruto , pero serian dolares, el programa luego sabe diferenciar en esa casilla dolares de euros?
Perdon por ponerlo en 2 veces.
Es posible que tengas que poner el cambio en vez de «X» «=1/X»
Hola Antonio, no sé exactamente a qué te refieres con esto. Tengo el mismo problema que Jose te comenta aquí. Te comento he empezado con Revolut, tengo la transacción aquí:
Precio: 129.10$
Número de acciones: 1.03377226
Coste Total: 133.46$
¿Cómo tendría que ponerlo en el excel? Porque en el coste bruto no me cuadra la información.
Muchas gracias y un saludo!
Hola Daniel,
El el excel en la zona de compra bajo estas cabecera, pones lo que digo:
nº unidades compradas (acciones o participaciones): 1,03377226
Cambio divisa (Moneda/EUR) en compra: =1/1,15 (si el cambio USD EUR esee dái era de 1,15)
Precio pagado por unidad (moneda): 129.10
Coste bruto total de la compra (euros): lo que te de, se calcula solo y está en euros (por tanto no coincidirá con 133.46$)
Espero haberlo aclarado.
Saludos
Antonio
Hola Antonio.
Muchísimas gracias por este trabajo tan útil y meritorio.
Mi problema está en la hoja de Google y en los fondos de inversión. Aparece correctamente el precio de hoy , pero no el precio de la Fecha Pasada (31/12/2019) En su lugar aparece #N/A.
¿Que debo hacer para solucionarlo? Gracias anticipadas
Saludos
Jordi,
Efectivamente, eso es así tal y como dices. No supimos en su momento conseguir eliminar ese error usando una fórmula en la casilla correspondiente. Por eso decidimos abandonar el uso de googlesheets, y creamos los excel 3.0 y siguientes que ya no dependen de estos, y funcionan de forma independiente. Ahora mismo ya sabría cómo hacerlo, pero mediante scripts de google, y eso lleva tiempo. Como no puedo con todo, ahora ya solo me centro en mantener la última versión (la 3.5).
Un saludo
Antonio
Muchas gracias
Por si puede interesar a alguien. Historia de una estafa.
https://www.elmundo.es/economia/2020/07/04/5eff8eee21efa0a8178b458f.html
Realmente al pequeño inversor que usamos brokers regulados, y compramos en bolsa empresas de calidad esto es muy raro que te llegue a ocurrir.
Lo que el articulo confunde es el concepto de mercado secundario. La bolsa es un mercado secundario, y es seguro. Me imagino que se refieren a mercados como el mab en España que está menos regulado, o el mercado primario, cuando tratas directamente con la empresa, o el mercado OTC que tiene riesgo.
Si tu usas ING, Selfbank, etc puedes estar tranquilo.
Saludos
Buenas tardes,
enhorabuena por el enorme trabajo, esfuerzo y dedicación que hay detrás de esta hoja de calculo y muchísimas gracias por compartir dicho trabajo.
He descargado la versión 3.5 y al calcular las cotizaciones, en la pestaña correspondiente, me salen valores muy dispares con relación a las principales bases de datos (yahoo finance, google finance e investing)… ¿por qué esta disparidad? ¿Qué base de datos usa el excel v3.5?.
No me parece estrictamente correcto que haya diferencias entre yahoo finance, google finance o investing, ya que en algunos casos, estas diferencias son altas, y pregunto ¿a qué se deben esas diferencias? y ¿Qué base de datos es mas fiable?
Muchas gracias. Un saludo.
Hola, Jose, las cotizaciones de acciones las lee de yahoofinance. Todas las diferentes fuentes de datos deberian coincidir exactamente al cierre de mercado. Pero durante el mercado abierto pueden diferir pues algunas son en tiempo real y otras retrasadas 15 minutos. Supongo que este sea el motivo de lo que has visto.
Un saludo
Antonio
Muchas gracias por contestar tan rápido,
las discrepencias en las cotizaciones, se me olvidó comentar, son de las cotizaciones pasadas. Te pongo un ejemplo…
– Banco Santander (31 de enero 2014) 6.30 € (yahoo finance); 6.30 € (google finance); 5.80 € (investing) y 4,41 € (excel)
– Banco Santander (29 de enero 2010) 9,98 € (yahoo finance); 10,13 € (google finance); 10,30 € (investing) y 4,69 € (excel)
Un saludo y muchas gracias.
Hola Jose,
Gracias por el detalle de las cotizaciones en diferentes fuentes de información. La diferencia entre ellas, es que algunas tienen los split ajustados, o los dividendos y otras no.
En el caso del Excel, me doy cuenta, gracias a ti, que estoy cargando el «precio de cierre ajustado», por ejemplo la última columna aqui: https://es.finance.yahoo.com/quote/SAN.MC/history?period1=1412121600&period2=1412726400&interval=1d&filter=history&frequency=1d
y no debería hacerlo con el precio ajustado, ya que cuando compramos compramos con el precio actual del momento, no el ajustado. En la próxima revisión, lo actualizo, pues aunque esto no se nota si miras unos pocos días en el pasado, sí se nota y mucho cuando miras varios años como has mostrado.
Gracias por el aporte
Antonio
Gran aporte. Muchas gracias
Gracias David, me alegro que te guste.
Hola, gran aporte, es genial esta hoja de cálculo. Sin embargo, no consigo hacer que los siguientes productos se lean correctamente desde la hoja Resumen:
-Seguro de Vida tipo Unit Linked. Es una especie de Fondo, pero no tiene cotizacion como tal a la que poder enlazar un ticker. Realizo aportaciones mensuales y tengo, por tanto, valor de compra que varia mensualmente y valor de cotizacion que fluctua cada pocos dias.
-Roboadvisor. Realizo aportaciones mensuales, tiene una comision anualizada, y tengo los valores de cotizacion diarios. Parecido al anterior.
-Liquidez para invertir (lo que tengo en mi cuenta disponible para nuevas inversiones)
Alguna idea de como parametrizar cada uno de ellos? Gracias
Hola Aster,
Con la cotización de los roboadvisor y los seguros de ahorro unitlinked, no te se decir cómo poner la cotización de forma automática, ya que como dices no hay ticker. De todas formas, si son solo estos dos casos, como está pensado para fijar valaor de cartera una vez al mes, puedes simplemente en la pestaña de cotizaciones, meter el precio a fin de mes a mano (los tickers te los inventas, cualquiera sea válido o no). Con eso ya tendría que ir bien.
La liquidez la puedes poner como una subcartera si quieres, y entra a formar parte del total de la cartera.
Espero haberte orientado
Saludos
Antonio
Gracias, tiene sentido.
Con respecto a las aportaciones periodicas de ambas cosas, como piensas q seria mejor controlarlas? He pensado en actualizar cada mes en la pestaña Datos Inversiones las columnas Precio Pagado por Unidad y Coste Bruto Compra. De esta manera se obtendria lo que tengo invertido como coste de la compra y en la cotizacion veria el valor del activo, pudiendo entonces establecerse el porcentaje de ganancia o perdida.
Por favor, si puedes indicarme como lo harias tu.
Gracias de nuevo,
Aster.
Aster, Sí, efectivamente, en compras recurrentes vas metiendo fila nueva (copias la fila de la aportacion anterior sililar, con doble click -> No), anotando fecha compra, precio por unidad y numero de participaciones. Y el resto se calcula solo.
Saludos
Antonio
Gracias Antonio, pero no seria demasiado ruido ese metodo? Si las aportaciones las hago mensualmente para 3 productos, eso son 36 entradas adicionales en la pestania de Datos Inversion. Crees q seria erroneo modificar una unica entrada por producto aumentando el valor de compra la cantidad aportada cada mes?
Saludos y gracias de nuevo,
Aster
Aster, yo así no lo haría, pues no recogería bien la fecha de compra, y por tanto no se calcularían bien las rentabilidades, entre otras cosas. No pasa nada por tener 300 o 500 filas en el excel, está preparado para ello. Yo meto más de 5 filas al mes
Saludos
Hola de nuevo Antonio,
Perdona la insistencia, pero no consigo dar con la tecla.
Supongamos q tengo una aportacion inicial de 3000 euros, y luego 50 euros mensuales. Haciendo lo que dices, dado que en Cotizaciones solo introduzco manualmente el valor de cotizacion del Unit Linked actualizado cada mes, esto implica que los datos en DatosInversiones son incorrectos, ya que el apartado Resultado (la zona verde) establece que cada aportacion de 50 euros ahora vale el precio de la cotizacion del fondo, resultando un 5300%, lo cual es erroneo obviamente.
El problema mayor lo veo en esto, como hacer que tanto la aportacion inicial, como las periódicas, como una que realice puntualmente de mas cantidad, queden bien reflejadas a efectos de rentabilidades? Es muy dificil concretar para una entrada de aportacion de 50 euros la rentabilidad concreta de dicha línea.
Saludos,
Aster
Aster, realmente ahora, con lo que me has dicho, si que no entiendo tu dificultad con anotar compras periódicas.
Si quieres mándame un email con un ejemplo de varias compras periódicas, y tu intento de anotarlo en el excel, y te ayudo a retocar ese excel para reflejarlo bien. Ya verás que es muy fácil. Es posible que sea algo que no has entendido de la filosofia de uso del excel.
Saludos
Antonio
Hola Antonio,
Te he mandado un correo a olvidatedetucerdito@gmail.com con el ejemplo atachado.
Gracias por la ayuda nuevamente
Saludos,
Aster
Hola,
Efectivamente, enhorabuena por la hoja, muy conseguida.
Tengo exactamente la misma duda con una inversión en Indexa en la que periódicamente hago aportaciones, y únicamente dispongo del valor total de la cartera. No es como el caso de ING con el S&P 500 que sabes el número de participaciones de cada aportación y el valor de la participación.
En el caso de Indexa, aunque intente meter tantas líneas como aportaciones, cómo hago con el resultado previo? considerarlo como una venta? y dar de alta una nueva línea con el nuevo valor total? No consigo encontrar la clave para esto.
Gracias!!
Pepe, anotar el caso de indexa capital, me parece complicado, pues no sabes qué participaciones te pertenecen de cada fondo, y puedes tener unos 10 fondos cada vez que ingresas, y parte se puede quedar en liquidez, y además ellos hacen rebalanceo interno que no conoces. Con lo cual creo que no merece la pena tener indexa en el excel. Esa parte de tu inversión la puedes supervisar directamente con sus informes. No se si ellos te dan un valor liquidativo, o algo así, de la cartera global. Es algo no no te puedo decir, pues no invierto con indexa, tengo parte de los fondos que usan pero los compro yo individualmente.
Saludos
Antonio
Muchas gracias por la respuesta. es lo que estaba empezando a concluir, pero por si alguien había dado con la clave. Por cierto, donde suscribes los mismos fondos que Indexa (Vanguard)?
Saludos
Pepe, Tienes muchos fondos vanguard e ishares en myinvestor. La cartera de indexa sencilla consta de solo 2 fondos, con lo cual es fácil replicarlo. También tienes un fondo 75/25 de indexa que replica todo. Mira en la seccion de radar de fondos mixtos de esta web, y verás ambas soluciones. Pero no cambies tu inversion solo por el excel, compara comisiones entre las diferentes soluciones que te digo, y su comodidad de uso, y elige la que más te convenga, ya que lo que tienes ahora con indexa es una muy buena opción.
Hola Antonio,
Ante todo gracias por tu fabuloso trabajo tanto en relación al excel cómo a la divulgación y tu aportaciones en tu web.
he leido todas las entradas de comentarios y no he podido resolver alguna dudas que tengo respecto a la hoja de Datosinversiones.
Yo opero con revolut y normalmente hago el cambio de divisas de eu a usd el día que voy a operar. Si ayer compré una acción, anoto el precio del dólar a día de ayer en la columna K. Hasta ahí creo que no lo hago mal. Mi duda surge cuando realizo una venta de acciones ya que la venta la realizo en usd pero en ningún momento las he vuelto a cambiar por eu ya que las dejo en revolut en esa divisa. Entonces debo anotar el precio del cambio de divisa aunque no la haya realizado? ya que si no lo pongo en la casilla me da un valor negativo.
Por otro lado en la pantalla de resumen sólo las dos acciones que he marcado cómo vendidas son las que me aparecen en el % total de acciones, las otras cinco que he introducido me pone que tengo 0% de participaciones en mi cartera. Qué valor crees que he introducido incorrectamente?
Gracias de antemano¡¡¡¡¡
Hola Marcos Led,
Lo que comentas de Revolut (o cualquier broker multidivisa como Interactivebroker, etc) es todo correcto. Y como apuntas, también tienes que poner el cambio de divisa el día de la venta, en la columna R de «DatosInversiones», aunque tú no hayas cambiado moneda ese día y lo dejes en USD. Eso es así, pues el excel tiene como misión controlar tu rentabilidad, pero también servir para saber lo que debes declarar a hacienda por IRPF. Tras vender tus acciones americanas en Revolut, a Hacienda solo te interesa saber cuales han sido tus plusvalías en EUR, y por tanto debes informar sobre la acción que has vendido: el precio pagado en la compra (en EUR) y el previo de la venta (en EUR), ver la diferencia, y restarle los gastos de compra/venta, si lo hubiera. Si tu has usado solo USD a hacienda le da lo mismo, pero ella quiere saber la plusvalía en EUR. Por tanto, hay que poner el cambio de divisa tanto en la compra como en la venta.
Un aspecto importante, y que no gestiona el excel actualmente, pero que debes ser consciente, es que cuando conviertes EUR a USD (compras USD), estás abriendo una posición en divisa USD, y aunque luego tú no compres ninguna acción con esa divisa, si en algún momento la vuelves a convertir en EUR (vendiendo los USD), debes declarar esa plusvalía o minusvalía por el doble cambio de divisa, como si de la venta de una acción se tratase. Para hacerlo bien, habría que llevar también un registro de todas las divisas abiertas (cotizando en FOREX), y declarar cuando la conviertas a EUR de nuevo (cuando las vendas). Eso no está contemplado en el excel, pues es mucho más complejo de lo que parece, ya que cada vez que p.ej. cobres un dividendo, no solo debes anotar el dividendo en «DatosDividendos», sino que se debería abrir otra posición en divisa por un importe igual al dividendo en USD cobrado, etc.. y te aseguro que es un lío, y se generarían inumerables líneas en el excel para llevar registro de todo. Es una tarea que tengo pendiente hacer en el futuro, pero no lo veo inmediato. Por el momento, yo las posiciones en divisa no las añado en el excel, pero para hacer bien la declaración siempre se debe usar el extracto del Broker, y añadir las plusvalías por las divisas cerradas.
Respeto a tu comentario final. Solo las acciones que siguen cotizando (no vendidas) deberían aparecer en la hoja «Resumen». Comprueba que en la casilla «C36», en el botón filtro tienes elegido Cotizando=VERDADERO
Espero haberte aclarado las dudas.
Saludos
Antonio
Buenas Antonio, en primer lugar agradecer tu fenomenal trabajo y agradecer que lo hayas compartido.
Estoy empezando a “trastear” con el y algo debo estar haciendo mal porque al introducir un valor, (por ejemplo el Santander), pero me ocurre con cualquier otro, y poner todos sus datos en Euros, cuando actualizo las cotizaciones me aparece el valor en USD y si el valor no cotiza en USA, me aparece valor 0 moneda USD. Se te ocurre con estos datos que puedo estar haciendo mal? Gracias por todo y saludos.
Jose Luis
Hola Jose luis, pues la verdad es que no se qué puedes haber puesto mal. Mira cómo está en la plantilla original a ver si ves la diferencia. A ver si lo detectas. Suerte
Antonio
Buenas tardes Antonio,
muchas gracias por tú excelente excel y por ponerlo a disposición de todos. Tengo una duda. A la hora de realizar rebalanceos con fondos indexados, y por tanto realizar traspasos internos parciales entre fondos ¿cómo reflejaríamos estas operaciones en la plantilla datos inversiones?
Muchas gracias por todo
Hola Francisco,
El traspaso lo anotas como una venta de ese fondo, y en una nueva fila como una compra del nuevo fondo. En la columna traspaso del fondo de origen, anotas que sí es traspaso, de esa forma no debería computar las plus/minusvalias en IRPF2. A ver que tal.
Saludos
Hola Antonio.
Debo ser de los últimos en descubrir tu Blog, y desde luego de los últimos «de la clase» en este tipo de operativa con hojas de calculo.
Quisiera resolver una duda, de la hoja de Google Sheets que usas de apoyo, en la columna E ( Precio de moneda hoy )
¿los datos que recaba están en euros ?
Podrías explicar como es la formula para la conversión, ( el léxico de la formula).
Aprovecho la ocasión para agradecerte la buena labor del Blog y la calidad didáctica y de contenidos del mismo.
Ansel
Hola Ansel,
La moneda está en la columna de al lado (F).
Si ese precio en la moneda que sea la quieres convertir en otra basta con que la multipliques o dividas por algo similar a esto: «googlefinance(«EURUSD»)»
Espero que te ayude esto.
Y gracias por tus palabras
Antonio
Buen dia, estoy utilizando la planilla y desde hace un tiempo me sale el siguiente error cada vez que abro o cierro el archivo:
«Error al intentar leer posible mensaje dirigido a ti. Error: -214702891 Linea:4»
«Error al gestionar actividad. Error: -214702891 Linea Codigo: 21»
Saben que puede ser o como se puede corregir?
Hola Máximo,
El caso que comentas está comentado en otras secciones del blog. Te lo repito aquí:
La forma más fácil, y así lo estoy haciendo con varios que lo han pedido, es que me mandáis el excel actual y os cambio las macros para poner la versión 3.53 libre de errores.
Si prefieres hacer el copy&paste sobre la nueva plantilla que has descargado, prueba, y si funciona (a veces copias enlaces al anterior excel y genera problemas) estupendo también.
Un saludo
Antonio
Muchas gracias, la probaremos a ver qué tal
Buenas tardes, pregunta de novato.
Entiendo que si no vendes y no cobras beneficios, no hay que declarar a hacienda nada, no?
Mi idea es comprar acciones de dos o 3 empresas y dejarlo ahí quietecito largo tiempo. Voy a probar por primera vez esto. Muchas gracias 🙂
Carlos, efectivamente. Sí no vendes no realizas ni beneficios ni pérdidas, con lo cual no hay que declarar ganancias patrimoniales en el IRPF.
Haces bien, elige empresas de calidad y no las toques en décadas, sí siguen creciendo y teniendo buenos márgenes y retornos de capital. Hay que dejarlas componer.
Saludos
Antonio
Hola Antonio,
muchísimas gracias por compartir tu trabajo.
Tengo una duda. En DatosInversiones en la columna de Coste bruto total de la compra (euros) si la acción la compre en dólares, el resultado me lo da en dólares en vez de Euros. ¿Estoy cometiendo algún error yo? o ¿Cómo lo podría solucionar?
Hols Eduardo,
Metes el precio en tu moneda de compra, poner el cambio de esa moneda respecto al euro, y al final los totales son siempre en euros. Espero te ayude.
Antonio