Fórmula excel para calcular plusvalías FIFO
FIFO capital gains calculation with a single excel formula
El cálculo de plusvalías/minusvalías mediante el método FIFO (First-In, First-Out) es un requisito fiscal común. No es difícil pero nunca lo he visto resuelto de forma sencilla en una sola celda. Normalmente requiere el uso de varias tablas / columnas auxiliares, scripts o software específico. La hoja de cálculo termina volviéndose compleja y difícil de mantener.
Este post presenta una fórmula excel / google sheets que calcula la plusvalía o minusvalía FIFO correspondiente a cada operación de venta directamente en la celda de esa fila, eliminando la necesidad de pasos intermedios.
Estructura y Fórmula
Se requiere una hoja de cálculo con la siguiente estructura mínima:
Columna A: Fecha de la operación (formato fecha).
Columna B: Ticker / Identificador del activo.
Columna C: Número de títulos (positivo compras, negativo ventas).
Columna D: Importe de la operación (signo indiferente).
Columna E: Celda donde se insertará la fórmula para calcular la plusvalía/minusvalía de las ventas.
Insertar la siguiente fórmula en la celda E2 (asumiendo que los datos comienzan en la fila 2) y arrastrar hacia abajo para aplicarla al resto de filas:
=IF(C2>0;"-"; LET( operation_date;A2; operation_ticker;B2; operation_shares;C2; operation_amount;D2; dates;$A$2:$A; tickers;$B$2:$B; shares;$C$2:$C; amounts;$D$2:$D; previous_sales; -SUMPRODUCT((dates<operation_date)*(tickers=operation_ticker)*(shares<0)*shares); data; SORT(FILTER(HSTACK(dates; tickers; shares; amounts); (dates<operation_date)*(tickers=operation_ticker)*(shares>0)); 1; 1); accumulated_shares; SCAN(0; INDEX(data;;3); LAMBDA(x; y; x+y)); sales_shares; MAP(accumulated_shares; INDEX(data;;3); LAMBDA(k; i; MAX(0; MIN(previous_sales-operation_shares; k) - MAX(previous_sales; k - i)))); ABS(operation_amount) - SUMPRODUCT(sales_shares; ArrayFormula(ABS(INDEX(data;;4))/INDEX(data;;3))) ))
Nota: Ten en cuenta la configuración regional de tu hoja de cálculo (separador coma (,) vs semicolon (;), idioma de las fórmulas…).
Desglose de la Fórmula
=if(C2>0;"-"; ...): Comprueba si la operación es una compra (C2 > 0). Si es así, devuelve "-", ya que las compras no generan plusvalía/minusvalía en sí mismas. El cálculo principal solo se ejecuta si C2 es negativo (venta).
LET(...): Define nombres para valores y rangos:
operation_date, operation_ticker, operation_shares, operation_amount: Datos de la fila actual (la venta).
dates, tickers, shares, amounts: Rangos completos de las columnas de datos. Se usan referencias absolutas de columna ($A$2:$A, etc.) para incluir todos los datos.
previous_sales; -SUMPRODUCT(...): Calcula el total de títulos del mismo activo (tickers=operation_ticker) vendidos (shares<0) antes de la fecha de la operación actual (dates<operation_date). Es necesario para saber qué lotes de compra ya fueron consumidos por ventas anteriores.
data; SORT(FILTER(HSTACK(...))): Prepara los datos de compra relevantes para FIFO.
HSTACK: Combina las columnas fecha, ticker, títulos y coste en una matriz temporal.
FILTER: Selecciona solo las filas de compra (shares>0) del mismo activo (tickers=operation_ticker) que ocurrieron antes de la fecha de la venta actual (dates<operation_date).
SORT: Ordena las compras filtradas por fecha (1; 1), de la más antigua a la más reciente, implementando el criterio FIFO.
accumulated_shares; SCAN(...): Calcula la suma acumulada de los títulos comprados (INDEX(data;;3)). Proporciona el total de títulos poseídos después de cada compra FIFO.
sales_shares; MAP(...): Determina cuántos títulos de cada lote de compra FIFO específico se están utilizando en la venta actual. MAP aplica una LAMBDA a cada lote. La LAMBDA compara los títulos acumulados hasta ese lote (k), los títulos del lote específico (i), las ventas previas (previous_sales) y la cantidad vendida en la operación actual (operation_shares) para asignar la porción correcta de la venta a ese lote. MAX y MIN aseguran que la asignación sea correcta y no negativa.
Por ejemplo, si después de cierta operación de compra de 10 shares tendríamos (sin considerar ventas intermedias) 100 shares acumuladas, eso significa que hemos comprado en esa operación desde nuestra share número 90 hasta nuestra share número 100. Por tanto habría que comparar usando el min-max ese intervalo [90, 100] con el intervalo [previous_sales, prevoius_sales + operation_shares] y ver cuánto se solapan.
ABS(operation_amount) - SUMPRODUCT(...): Realiza el cálculo final de la plusvalía/minusvalía.
ABS(operation_amount): Es el importe de la venta (D2) en valor absoluto.
SUMPRODUCT(sales_shares; ArrayFormula(ABS(INDEX(data;;4))/INDEX(data;;3))): Calcula el coste total de adquisición FIFO de los títulos vendidos.
ABS(INDEX(data;;4)): Extrae los costes como valores absolutos de cada lote de compra de data.
/INDEX(data;;3): Divide el coste de cada lote por el número de títulos de ese lote para obtener el coste unitario.
ArrayFormula: Garantiza la operación elemento a elemento (necesaria en algunas versiones/contextos de Sheets para la división de arrays).
SUMPRODUCT: Multiplica la cantidad de títulos vendidos de cada lote (sales_shares) por su respectivo coste unitario y suma los resultados. Esto da el coste total FIFO.
Resultado: Ingreso Venta - Coste Adquisición FIFO = Plusvalía / Minusvalía.
Comentarios adicionales
Revisa la fórmula con tus datos y la configuración regional de tu google sheets (; vs , como separadores).
Según la configuración regional las funciones las puedes tener que introducir en un idioma u otro: SUMAPRODUCTO vs SUMPRODUCT, APILARH vs HSTACK, VERDADERO vs TRUE…
Confirma que la lógica de signos (positivo/negativo) en tu columna C (shares) coincide con lo asumido en la fórmula. Los signos de la columna D (amount) son irrelevantes (se toma el valor absoluto) ya que es el signo de shares el que define si una operación es compra o venta.
Ya que todas las funciones empleadas tienen su equivalente en excel (excepto ArrayFormula que no es necesaria) puedes traducir la fórmula simplemente teniendo en cuenta tu configuración regional:
Si quieres aplicar la fórmula de forma matricial (que calcule las plusvalías de toda la columna sin tener que arrastrar la fórmula) puedes encapsularla en un MAP + LAMBDA.
No lo he probado pero algo me dice que si cambias el criterio de ordenación del SORT de ascendente a descendente la fórmula pasa a aplicar criterio LIFO (Last In, First Out).
La fórmula actual no gestiona casos raros como ventas cortas u operaciones con la misma fecha. Si quieres controlar estos casos deberás añadir alguna lógica o comprobación adicional.
Pd0. De nada.
Pd00. Y gracias también a @Marcos_Luque_ que ha sido el promotor de todo esto.
Pd1. Puedes ver todos mis posts agrupados por temática en el Índice.
Pd2. Posts relacionados: Cómo ahorrar impuestos al rescatar fondos de inversión, Retribución flexible: la forma más fácil de ahorrar impuestos.
Cómo siempre, 100% valor añadido. Lo he anexado a mi Sheets de seguimiento de acciones. No hay dinero para pagarte.