Lo que buscamos es crear una especie de calculadora en una hoja de Excel tal que si ingresamos el monto adeudado y las fechas, nos haga el cálculo del interés a pagar. Nuestra hoja se vería así (el archivo se puede descargar aquí)
Nuestro enfoque será resolver el modelo con fórmulas. Empecemos por señalar que tanto los datos en las celdas B2 y B3 como en el rango A6:A17 son fechas y no texto. Es decir, estamos trabajando con fechas que son números, como ya hemos explicado en el pasado.
Por ejemplo, si seleccionamos la celda A6, donde vemos la palabra "enero", veremos en la barra de fórmulas que en realidad es la fecha 01/01/2008
Para calcular los días por mes de la fecha de iniciación hasta la fecha de pago usamos una "mega-fórmula". Baste con ver la fórmula que usamos para calcular, dinámicamente, los días del mes de febrero que caen entre las fechas de principio y fin del intervalo para entender por qué la llamamos "mega"-fórmula
Para usar la función EOMONTH (o FIN.MES, de acuerdo a las definiciones del sistema) hay que tener instalado el complemento Analysis Toolpak.
La técnica que propongo para construir esta mega-fórmula consiste en crear las fórmulas parciales en columnas auxiliares y luego unirlas en una única fórmula.
La primer columna auxiliar es C donde calculamos la cantidad de días que caen dentro del mes de la fecha inicial con esta fórmula
=SI(MES($B$2)=MES(A6),$B$2-A6,0)
La función MES nos permite ver si el mes de la línea es el mes de la primer fecha. En caso afirmativo calculamos la diferencia de días entre la fecha de inicio (B2) y el primer día del mes (A6). En caso de coincidir el mes el resultado es 0.
En la columna D hacemos lo mismo pero en relación a la fecha de pago efectivo (B3), con la fórmula
=SI(MES($B$3)=MES(A6),$B$3-A6,0)
En la columna E calculamos los días de los meses que caen entre ambas fechas con la fórmula
La función EOMONTH (FIN.MES) nos permite calcular con exactitud los días de cada mes, también en años bisiestos.
En la columna F calculamos el total de días de cada mes con una resta sencilla
=E6-C6
A esta altura del partido tenemos calculados los todos los días por mes, excepto para el último mes del período. Para esto u Samos la columna auxiliar G con esta fórmula
Ahora podemos combinar todas estas fórmulas intermedias en una única mega-fórmula. Primero nos proponemos la fórmula en palabras, lo que nos ayuda a crear la lógica. Nos fijamos en las columnas auxiliares y decimos:
1 - si el mes de la fila no es el de la fecha de pago, calculamos los días restando la columna C de E (E-C);
2 - si el mes es el de la fecha de pago tomamos el resultado de la columna D y le sumamos 1.
En la columna H empezamos a crear la mega-fórmula
=SI(MES(A6)
Necesitamos que toda la fórmula esté expresada en términos de celdas en la columna A y B para lo cual empezaremos reemplazando F6 y G6 por sus equivalentes, las fórmulas que hemos puesto en las columnas auxiliares. Al reemplazar F6 y G6 nos queda
=SI(MES(A6)
Ahora tenemos que ocuparnos de E6, C6 y D6, que al reemplazarlas por las fórmulas de las columnas auxiliares nos dan la mega-fórmula que estamos buscando.
Es de notar que esta fórmula supone que las fechas de principio y final caen en el mismo año.
Para ver cómo construir un modelo real para el cálculo de interese por mora, consulten esta nota.
Quien esté interesado en recibir el archivo con el ejemplo y las fórmulas, debe dirigir el pedido a mi mail.
Technorati Tags: MS Excel
