Construir una tabla anual de interés compuesto

Una tabla anual convierte una potencia en una narración contable: qué saldo había al empezar, cuánto se añadió, qué crecimiento calculó la tasa y cuánto quedó. Es especialmente útil para enseñar, localizar divergencias y modelar cambios por año.

Usaremos 10.000 € iniciales, 1.200 € al final de cada año, una tasa anual efectiva constante del 5 % y cinco años.

Practica con una hoja completa

Puedes descargar el Kit de proyección Capliz. Su tabla mensual separa saldo inicial, aportación al inicio o al final, interés, comisión, saldo final, total aportado, crecimiento neto y valor real. Las fórmulas quedan visibles para que puedas compararlas con el ejercicio anual de esta página.

Diseña primero las entradas

Reserva B2:B5:

Celda	Dato	Valor
B2	Capital inicial	10.000
B3	Aportación anual	1.200
B4	Tasa anual efectiva	5 %
B5	Años	5

No escribas estos números dentro de las fórmulas. La guía de Microsoft sobre fórmulas explica que las referencias permiten cambiar entradas y actualizar resultados dependientes.

Crea las cinco columnas

Desde A9: Año, Saldo inicial, Aportación final, Crecimiento, Saldo final.

Primera fila:

A10 = 1
B10 = $B$2
C10 = $B$3
D10 = B10*$B$4
E10 = B10+D10+C10

Segunda fila:

A11 = A10+1
B11 = E10
C11 = $B$3
D11 = B11*$B$4
E11 = B11+D11+C11

Copia la segunda fila hasta completar cinco años. Los saldos aproximados son:

Año	Saldo inicial	Aportación	Crecimiento	Saldo final
1	10.000,00 €	1.200,00 €	500,00 €	11.700,00 €
2	11.700,00 €	1.200,00 €	585,00 €	13.485,00 €
3	13.485,00 €	1.200,00 €	674,25 €	15.359,25 €
4	15.359,25 €	1.200,00 €	767,96 €	17.327,21 €
5	17.327,21 €	1.200,00 €	866,36 €	19.393,57 €

Conserva precisión en las celdas aunque muestres dos decimales. Los valores de la tabla pueden diferir un céntimo si se redondea internamente cada año.

Añade conciliaciones

Debajo de la tabla calcula:

Total aportado = $B$2+SUMA(C10:C14)
Crecimiento acumulado = E14-Total_aportado
Suma de crecimiento anual = SUMA(D10:D14)

El total aportado es 16.000 €. Las dos medidas de crecimiento deben coincidir: alrededor de 3.393,57 €. Si no lo hacen, un flujo está duplicado, falta una fila o alguna fórmula dejó de referenciar el saldo anterior.

Comprueba con una fórmula cerrada

En una celda separada:

=$B$2*(1+$B$4)^$B$5+$B$3*((1+$B$4)^$B$5-1)/$B$4

También puedes usar:

=VF($B$4;$B$5;-$B$3;-$B$2;0)

La documentación oficial de VF señala que tipo 0 corresponde a pagos al final. Ambos controles deben devolver el mismo saldo final que E14.

Variante con aportación al principio

Si la aportación entra antes de calcular el crecimiento, cambia la fila:

D10 = (B10+C10)*$B$4
E10 = B10+C10+D10

Y usa tipo 1 en VF. No conserves D10 = B10 × tasa, porque seguiría modelando un pago final aunque la etiqueta dijera «principio».

Tasas y aportaciones variables

Para permitir cambios anuales, añade columnas Tasa y Aportación y escribe cada supuesto en su fila. El crecimiento será saldo inicial × tasa de esa fila. Ya no debes comparar la última cifra con VF de tasa constante; el control correcto será multiplicar o recorrer la misma secuencia de factores.

Una tabla variable aporta flexibilidad, pero también riesgo de edición. Usa validación de datos, bloquea fórmulas y añade una columna Nota para justificar cambios.

Pruebas estructurales

Continuidad. Cada saldo inicial, salvo el primero, debe ser igual al final anterior.

Tasa cero. Todos los crecimientos deben ser cero y el saldo final, 16.000 €.

Aportación cero. La última fila debe coincidir con 10.000 × 1,05^5.

Un año. La tabla debe contener una fila y terminar en 11.700 € con aportación final.

Suma de componentes. Capital inicial + aportaciones + suma de crecimientos = saldo final.

La calculadora de Investor.gov puede servir como tercer control de un escenario regular. Una coincidencia verifica operaciones, no la probabilidad de la tasa. La tabla debe etiquetarse como proyección educativa y declarar costes, inflación o impuestos solo si realmente se calculan.

Añade costes sin ocultar el orden

Si existe una comisión anual efectiva sobre el saldo, añade una columna Coste. Declara si se descuenta después del crecimiento y antes de la aportación. Una fila con ese orden usa:

Crecimiento = Saldo_inicial*Tasa
Coste = (Saldo_inicial+Crecimiento)*Tasa_coste
Saldo_final = Saldo_inicial+Crecimiento-Coste+Aportacion

No restes la comisión de la tasa salvo que hayas demostrado que la equivalencia coincide con la metodología. Mantener columnas separadas permite sumar los costes y verificar el resumen.

Prueba. Con tasa de coste cero, la columna Coste desaparece y la tabla original se recupera.

Añade valor real como salida, no como flujo

En otra columna calcula:

Valor_real = Saldo_final/(1+Inflacion)^Año

No restes inflación dentro del saldo nominal si también quieres conservarlo. Con inflación cero ambas columnas coinciden. La diferencia es unidad de poder adquisitivo, no dinero retirado.

Auditoría de referencias al arrastrar

Después de copiar fórmulas, inspecciona una fila intermedia y la última. Las entradas deben conservar signos de dólar; las referencias a saldo anterior deben moverse una fila.

Usa Mostrar fórmulas para detectar una celda con número pegado. Comprueba que no hay filas ocultas, filtros activos ni totales que excluyan el último año.

Tabla estructurada y crecimiento

Convierte el rango en tabla de Excel. Las fórmulas estructuradas se expanden al añadir años y reducen direcciones rígidas. Aun así, limita la longitud mediante B5: una tabla con veinte filas visibles no debe calcular cuarenta porque quedaron fórmulas antiguas.

Incluye una columna Estado:

=SI(ABS([@Saldo inicial]-celda_final_anterior)<0,01;"OK";"REVISAR")

La primera fila se valida contra el capital inicial. Cualquier ruptura señala el punto exacto donde la secuencia dejó de conciliar.