miércoles, octubre 29, 2014

Calcular días por períodos

Supongamos que queremos calcular el interés a cobrar por una deuda (por ejemplo, un pago atrasado). Durante el lapso transcurrido hay períodos con distintas tasas de interés.
El problema consiste en calcular cuantos días del lapso de la deuda caen en cada período de interés.
Consideremos esta ejemplo (el cuaderno es interactivo y puede descargarse)





El lapso de la deuda corre del 15/02/2014 al 27/06/2014, 133 días. Durante este lapso hay tres períodos de interes, tal como aparece en el rango B6:E8.

Nuestra tarea es asignar los 133 días a los distintos períodos de interés, tal como aparece en el rango F6:F8. La celda F6 contiene la fórmula

=SUMAPRODUCTO((((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))>=$C$3)*(((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3))

En esta fórmula usamos la técnica que ya mostré en la nota "Calcular días por años entre dos fechas". El funcionamiento es el siguiente:

  • la expresión (C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1)) al estar dentro de la función SUMAPRODUCTO crea un vector de fechas , en nuestro caso {01/01/2014,02/01/2014,03/01/2014...,31/03/2014}
  • al comparar los miembros de este vector con la expresión >=$C$3 creamos un vector con los valores VERDADERO o FALSO (según se cumpla la condición o no)
  • la segunda expresión ((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3) funciona de la misma manera para la fecha del fin del lapso
  • SUMAPRODUCTO multipllica ambos vectores resultando 1 cuando se multiplican dos valores VERDADERO y 0 para los restantes casos. La suma interna del vector da como resultado el número de días comprendido dentro del período de interés.

3 comentarios:

  1. Está muy buena la explicación, pero lo que me gustó más fue la hoja de Excel incrustada, no tenía idea que se podía hacer...

    Muchas gracias!

    ResponderBorrar
  2. Hola Jorge,

    Se me ha ocurrido una manera más sencilla de calcular los días. La idea es generar dos vectores con las fechas de inicio y fin correspondientes y luego calcular la intersección. La fórmula sería esta:

    =FILAS(INDIRECTO($C$3&":"&$D$3) INDIRECTO(C6&":"&D6))

    Enhorabuena por tu blog

    ResponderBorrar
  3. Excelente Daniel! Gracias por compartir.
    Sobre el operador espacio que calcula la intersección entre dos rangos pueden leer esta nota en el blog.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.