AYUDA con la fórmula de Intereses por cada día de atraso.

[droplon]

Hola Amigos, nuevamente acudo al saber popular ya que agoté mis recursos y a esta altura tengo un "bug" mental con tantas pruebas!

Poniéndolos en contexto, paso a contarles:
Estoy creando una planilla donde pueda llevar toda la gestión de alquileres y expensas.
El tema es que estoy empantanado con la fórmula de los intereses diarios el cual del 1% por día. En algunos casos la fecha de vencimiento es el día 10 de cada mes, en otros es el día 15, 20 o 25. Dependiendo cada caso.


imagen 1

Aclaración*
La Celda B1 la estoy utilizando como simulador de la fecha actual HOY(), después que logre hacer funcionar bien la fórmula, debo reemplazar todas las referencia a B1 por HOY() para que el cálculo sea automático.

Esta es una imagen resumida de mi planilla (es una versión minimizada sólo con la información necesario a estos efectos. De más está decir que es mucho mas extensa)

imagen 2



LO QUE NECESITO DE LA FÓRMULA:

• * La fórmula de intereses debería activarse o calcular el 1% sobre el saldo pendiente a pagar sólo si la cuota del mes está vencida.
  * Si antes de la fecha de vencimiento, existiera un pago a cuenta, los intereses deberían calcularse sobre el saldo pendiente de pago. (Ej: antes del día 10 debía pagar $20500, pero solo pagó $500. Aún debe $20000. Si paga después del día 10, los intereses, se deben calcularse sobre el saldo de $20000)
  * Si hay un saldo a favor del inquilino, no debería calcular los intereses sobre el saldo negativo.(Ej: antes del día 10 debía pagar $20500, pero pagó $21000. El saldo será -$500, porque pagó $500 de más y en estos casos, los intereses, NO se deben calcularse)
  * Debería quedar registro del total de los intereses pagados, para lo cual no sé si necesito crear una columna adicional.
  Ej. Antes del día 10 debía pagar $20500, pero recién día 15 se acerca a pagar la deuda+intereses x 5 días de atraso. La deuda total al día 15 sería $21525 ($20500+$1025 de intereses). Son $205 de intereses diarios (1% de 20500) x 5 días = $1025). El tema es.... que pasaría si el día 15 en lugar de cancelar toda la deuda con sus respectivos intereses ($21.525) solo hace un pago parcial de $20.000??? Aún quedaría un saldo a pagar de $1525 sobre los cuales se debería seguir calculando intereses diarios contando desde el día de vencimiento. Cómo podría resolver esta situación?? Debería crear otra columna para tener un registro sobre los intereses sobre la deuda y otra para los intereses sobre los saldos, que sería mas o menos lo mismo, no?

[/list]


Adjunto les dejo el archivo para que puedan ayudarme
Saludos!

[Neftali R]

Wow amigo, la verdad es que sí lo veo complicado, una cosa que creo que hay que aplicar sí o sí es activar las referencias iterativas, pero tódo lo demás, la verdad no sé, voy a ver si encuentro alguna manera de hacerlo, te aviso cuando encuentre una solución, pero tengo la sensación de que esta es una tarea más adecuada para ser hecha en Base.

[Neftali R]

Okey, hice algo que creo puede ser funcional.

No hace falta activar las referencias iterativas.

En el archivo hay 3 hojas, “Original”, “Modificado” y “Consulta”.

El Original como lo indica el nombre, es el original, Modificado es la versión de tu tabla con pequeñas modificaciones para adaptarse a lo que necesito y Consultar es donde está el filtro que consulta los saldos a pagar.

En la hoja "Modificada":

Modifiqué la fecha en “Prído” para que ahí esté la fecha de vencimiento.

Añadí una columna “Saldo Atrasado” a la derecha de las columnas “Intereses” y “Días de atraso”. Las columnas “Saldo Atrasado” y “Días de Atraso” deben llenarse manualmente, después los intereses se calculan en base a los días de atraso y al saldo atrasado.

Modifiqué la formula de “Total a Pagar” para que tuviera en cuenta a la columna “INTERESES”.

Agregué una segunda tabla de pagos para registrar ahí cuando el inquilino pague la deuda pendiente más los intereses.

Modifiqué la formula en “TOTAL PAGOS” para que tomara en cuenta esa segunda tabla con pagos.

Eso es todo en la hoja “Modificado”, cabe señalar que coloreé en amarillo suave todas las columnas en las que modifiqué algo.

Ahora, la hoja “Consulta”.

Arriba, en la celda “B3”, hay una lista desplegable que tendrá los nombres de todos los inquilinos, el origen de los datos de esta lista desplegable son las celdas “$Consulta.$J$6:$J$44”, es decir, en ese rango tienes que colocar el nombre de todos los inquilinos tal cual como aparecen en tu lista original en la columna “Apellido y Nombre”.

Hay una tabla de B6:E31 en la que van a aparecer los datos filtrados en base al nombre seleccionado en la celda B3, con las columnas “Fecha Vencimiento”, con la fecha de vencimiento, “Saldo” con el saldo pendiente por pagar, “Días de Atraso” que calcula los días de atraso solo en caso de haber días de atraso y de que el saldo sea menor que 1, “Interés” que se va a calcular en base a los días de atraso y el Saldo pendiente.

También a la derecha, hay una tabla que dice “OCULTAS”, esta tabla contiene información necesaria para hacer los cálculos, pero no es necesario para el usuario visualizarlas, son columnas auxiliares. Las columnas “Mora” y “Filas Coinciden”, tienes que extender las formulas hasta que tengan la misma cantidad o más de filas que tu tabla original, y la tabla “Lista Inquilinos” es para que coloques el nombre y apellido de todos tus inquilinos, tal cual aparecen en la columna “Apellido y Nombre”, no deberías repetir los nombre.

Espero haber sido lo suficientemente claro y que el archivo te pueda servir.