¿Tabla de Excel para calcular los intereses de una cuenta de ahorro que se componen diariamente pero se pagan mensualmente?

Question

Tengo una hoja de Excel que representa una cuenta de ahorros del mundo real cuyos intereses se componen diariamente pero se pagan mensualmente. Se ve así:

Tabla de ejemplos

Para mayor claridad, llamaré a cada fila de la "Tabla de cálculo de intereses" una transacción. La información básica (fecha, importe, fuente) de cada transacción se añade dinámicamente a la hoja de forma ordenada. Como puede ver, la hoja se encarga de llevar un registro del saldo inicial y final de la cuenta después de cada transacción.

La hoja también tiene que actuar como una cuenta de ahorro con intereses en la que éstos se acumulan diariamente pero se pagan mensualmente. Para ello, para cada transacción, calcula el interés compuesto diario que se acumuló entre la transacción anterior y la actual. Añade este valor a cualquier interés calculado previamente para crear una suma mensual móvil de pagos de intereses diarios (la columna de intereses acumulados). Al ver una transacción que contiene la fuente de "Intereses pagados", el recuento de los intereses acumulados se vuelca en el saldo de la cuenta de ahorro y así se cumple el requisito de "intereses calculados diariamente y pagados mensualmente".

La fórmula de la columna de intereses para calcular el interés diario compuesto entre las transacciones anteriores y la transacción actual es Saldo final anterior * (1 + TAE/365) ^ ((365 * Días entre la transacción anterior y la actual) / 365))) - Saldo final anterior.

Utilizando esa fórmula base, un conjunto de sentencias if then en la celda de intereses comprueba si los intereses obtenidos entre la última transacción y la actual inician un nuevo recuento de intereses móviles (los intereses empiezan a acumularse desde 0 al principio del mes) o se añaden al recuento de intereses móviles existente.

Para completar la información, he incluido la fórmula a continuación, así como una descripción de los casos.

Ejemplo de fórmula

1.) Si la transacción actual es una "transacción con pago de intereses" y la transacción anterior es una "transacción con pago de intereses". En este caso, los intereses obtenidos entre la última transacción y la actual forman parte de un nuevo recuento de intereses.

2.) Si la operación actual es una "operación con pago de intereses" y la operación anterior no es una "operación con pago de intereses". En este caso, el interés que se ganó entre la última transacción y la transacción actual es parte del recuento de intereses actual.

3.) Si la transacción actual no es una "transacción con pago de intereses" y la transacción anterior es una "transacción con pago de intereses". En este caso, el interés que se ganó entre la última transacción y la transacción actual es parte de un nuevo recuento de intereses.

4.) Si la transacción actual no es una "transacción con pago de intereses" y la transacción anterior no es una "transacción con pago de intereses". En este caso, el interés que se ganó entre la última transacción y la transacción actual es parte del recuento de intereses actual.

Mi problema es que me estoy encontrando con un problema de compensación en mis cálculos de intereses con respecto a lo que informa el banco.

• El 28/2/2019, el banco informó de un interés pagado de $2.62 where I got $ 2.26.

• El 29/3/2019, el banco informó de un interés pagado de $13.38 where I got $ 12.30.

• El 30/4/2019, el banco declaró un interés pagado de $16.55 where I got $ 17.37.

Sé que habrá una pequeña variación debido a los errores de redondeo bancario, sin embargo no deberían estar tan lejos como estoy obteniendo. Estoy seguro de que mi ecuación de acumulación de intereses diarios está mal, pero no estoy seguro de cuál usar.

Nota: El tipo de interés y el APY se muestran en la tabla.

Answer 1

No puedo decir qué errores has cometido en tu hoja, pero he vuelto a crear cada mes en Excel y coincidir con los resultados del banco. Simplifiqué haciendo sólo una lista de fechas en el mes, y calculando el interés diariamente si había o no transacciones.

Una cosa a tener en cuenta es que los intereses son por mes natural, por lo que los intereses pagados el 29/3 (viernes) incluyen los intereses del 30/3 y 31/3, están pagando por adelantado esos días. Eso explica por qué abril es el único mes en el que tu cálculo es superior al real, porque estás incluyendo 2 días más de intereses.

Tal vez en febrero las múltiples transacciones en algunos días están echando a perder las cosas, simplemente las combiné antes de calcular los intereses para simplificar.

Una cosa que definitivamente sugeriría es mover cualquier sección redundante de código a un campo separado que luego pueda referenciar, por ejemplo (1 + $C$4 / 365)) podría trasladarse a $D$4 para ayudar a limpiar las cosas.

Answer 2

"Compuesto diariamente pero pagado mensualmente" significa lo mismo que "compuesto mensualmente" para el dinero que ha estado en la cuenta durante todo el mes. Sólo significa que los nuevos depósitos obtienen intereses el primer mes en proporción a los días que han estado allí, y lo mismo ocurre con las retiradas durante el mes.

Sus cálculos son de lo que la balanza habría sido después de un mes si los intereses han sido pagado diariamente (y el saldo se mantiene en centavos fraccionados para hacerlo posible) -- pero no es eso lo que significa "compuesto diariamente".

En otras palabras, aunque el interés es calculado cada día, el interés ganado a principios de mes no empieza a suscitar interés propio hasta que se pagado que es sólo mensual.

En cuanto a los ahorros a largo plazo, este prorrateo del primer mes tiene un efecto insignificante, y puede tratar la cuenta como si fuera simplemente "compuesta mensualmente".