Sólo tienes que utilizar la fórmula del interés compuesto:
Principle * (1 + Rate / Time) ^ Time
Para la celda C2 quieres esta fórmula
=B2*(((1+(D$1/360))^(C$1-$A2))-1)
- La columna A es la fecha del depósito
- La columna B es el importe del depósito
- La celda C1 es la fecha de hoy
- La celda D1 es el tipo de interés anual
La mayoría de las cuentas de ahorro que conozco componen los intereses diariamente y acreditan los intereses ganados mensualmente , por lo que, siendo realistas, la fórmula anterior será exacta a la fecha de hoy, aunque todavía no se le hayan acreditado algunos de los intereses.
También puede omitir la fórmula de interés compuesto real y sólo utilizar la fórmula de valor futuro incorporada:
=FV(D$1/360,C$1-$A2,0,-B2)-B2
Para profundizar más en la capitalización mensual, tiene que empezar a jugar con el ajuste de las fechas…
Puedes empezar tomando las dos fechas y calculando el número de meses que han transcurrido usando la función DATEDIF() así:
=DATEDIF(A2,C$1,"M")
Pero tendrás que ajustar las dos fechas porque un simple DATEDIFF entre la fecha de hoy y las celdas A3 y A4 devolverán ambas 2, lo que no es realmente correcto. Podrías tomar el primer día del siguiente mes del depósito utilizando
=EOMONTH(A2,0)+1
Y puedes tomar el primero del mes del mes actual con
=DATE(YEAR(C1),MONTH(C1),1)
Lo que hace que tu fórmula:
=B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1)
Pero esto no es realmente correcto porque no empieza a acumular intereses hasta el primero del mes siguiente al depósito. También podrías obtener un número aproximado de meses restando las dos fechas y dividiendo por 30 días.
Puedes complicar esto para calcular un número de días para el primer mes + el interés mensual completo más allá de eso, pero hace que la fórmula sea mucho más larga porque tendrás
First month in days interest + monthly interest beyond that
Para obtener los días que quedan en un mes harías algo como:
=EOMONTH($A2,0) - $A2
Entonces para obtener la proporción del resto del mes haces (días ocurridos en el mes divididos por el número de días en el mes):
=(EOMONTH($A2,0) - $A2) / DAY(EOMONTH($A2,0))
Luego multiplicas lo anterior por la tasa de interés mensual por el principio para obtener el mes parcial, luego sumas el interés mensual anterior.
=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(B2*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))
Pero recuerde que su cantidad principal en el interés mensual es ahora su principio + el interés que se acreditó en el primer mes por lo que su fórmula debe ser en realidad:
=($B2*(((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12))))+(($B2*1+((((EOMONTH($A2,0)-$A2)/DAY(EOMONTH($A2,0))*($D$1/12)))))*(((1+(D$1/12))^(DATEDIF(EOMONTH($A2,0)+1,DATE(YEAR(C$1),MONTH(C$1),1),"M")))-1))
En este punto estás realmente dividiendo los pelos porque es la diferencia de 1,74327 dólares de interés frente a 1,74331 dólares cuando se incluyen los intereses del primer mes en el principal de los meses restantes. Esto difiere de los 1,85 $ de la celda C2 anterior porque aún no se le han abonado los primeros 10 días de agosto. En muchos casos, las diferencias mínimas en la capitalización sólo importarán en los números grandes, e incluso entonces …. Si tuvieras 10.000.000 de dólares en principio, la diferencia de capitalización cambiaría de 0,00004 a 4 dólares. Para la mayoría de los propósitos, la primera fórmula de ahí arriba es más que suficiente (y probablemente la que yo utilizaría en todos los casos porque la diferencia práctica de capitalización diaria frente a la mensual no es significativa).
