Generar un Calendario Automático de Pagos

Descargar el Archivo

Hoy vamos a ver cómo podemos generar un calendario automático que nos indique, por ejemplo, cuándo debemos cobrar una venta a plazos. Partiremos de una fecha inicial, que será la fecha pactada con el cliente, además del número de años al que se va a realizar la operación y el número de pagos al año que el cliente debe atender. Con estos tres datos generaremos un calendario automático con las correspondientes fechas de pago.

Para desarrollar este modelo utilizaremos, entre otras, varias funciones de la categoría denominada Fecha y Hora. A saber: FECHA, FIN.MES, AÑO, MES, DIA. Nuestra entrada de datos será la siguiente:

Lo que queremos conseguir es que el calendario que se muestra en la siguiente imagen se rellene automáticamente con el número de pago (o cobro) a realizar y la fecha de vencimiento, en base a los datos introducidos en B2:B4. Más concretamente queremos que si la fecha de inicio coincide con el último día del mes los siguientes vencimientos también lo hagan. Por otro lado, si la fecha es cualquier otro día del mes queremos que mantenga dicho día para el resto de la operación.

Para resolver este "problema", sin hacer la demasiado habitual chapucilla (y por favor que no se me moleste nadie) de sumar 30 días al mes anterior o soluciones similares, es necesario manejar algunas funciones de la categoría Fecha y Hora que paso a describir:

FIN.MES(Fecha_inicial;Meses)   Devuelve la fecha (como número de serie) del último día del mes anterior o posterior, respecto al argumento Fecha_Inicial, en función de que el argumento Meses sea positivo o negativo.

AÑO(num_de_serie)   Devuelve el año correspondiente a una fecha.

MES(num_de_serie)   Devuelve el mes de una fecha.

DIA(num_de_serie)     Devuelve el día de una fecha.

FECHA(Año;Mes;Dia)  Tal y como indica la ayuda de Excel, devuelve el número de serie secuencial que representa una fecha determinada. Si el formato de celda era General antes de escribir la función, el resultado tendrá formato de fecha. Dentro de los argumentos de esta función es importante destacar dos cuestiones que nos indica la ayuda de Excel: Mes es un número que representa el mes del año. Si el mes es superior a 12, el mes agrega ese número de meses al primer mes del año especificado. Por ejemplo, FECHA(2008;14;2) devuelve el número de serie que representa la fecha 2 de febrero de 2009. Día es un número que representa el día del mes. Si el día es superior al número de días del mes especificado, día agrega ese número de días al primer día del mes. Por ejemplo, FECHA(2008;1;35) devuelve el número de serie que representa la fecha 4 de febrero de 2008.

Empezamos por generar el número de Pago:

1. Nos situamos en la celda A9 (en la celda A8 hemos dejado como dato fijo un uno porque como mínimo existirá un pago) y escribimos la siguiente fórmula:

=SI(A8<$B$3*$B$4;A8+1;"")

Fíjese que lo que estamos comprobando en A9 es si A8 (la celda inmediata superior) es menor que $B$*$B$4, es decir, que el número total de pagos a realizar. Si es menor le pedimos que sume uno. Si no es menor entonces es que la celda inmediata superior (en este caso A8) ya es igual que el número total de cuotas, en cuyo caso le ordenamos que no escriba nada en la celda (el doble par de comillas).

2. Copie la fórmula de A9 hasta la celda, por ejemplo, A43. La celda hasta la que debe copiar dicha fórmula dependerá de las opciones que usted maneje. En nuestro ejemplo he supuesto que el plazo máximo en años es de tres y el número máximo de pagos al año es de doce (3*12=36, que son las celdas en las que hemos copiado dicha fórmula). Con esta sencilla solución habremos conseguido que el número de pagos aparezca automáticamente en función de los datos que contemplemos en nuestra entrada de datos.

3. Nos situamos en la celda B8 y escribimos la fórmula =B2  para recuperar la fecha inicial de nuestra entrada de datos.

4. En la celda B9 escriba la siguiente fórmula:

=SI(A9="";"";SI(B8=FIN.MES(B8;0);FIN.MES(B8;12/$B$4);

FECHA(AÑO(B8);MES(B8)+12/$B$4;DIA(B8))))

Con el primer condicional comprobamos si en la celda de la izquierda, respecto a la que contiene esta fórmula) hay algo o no. En caso de que no haya nada (doble par de comillas) que no escriba nada. En caso contrario, es decir, que sí haya algo (un número de pago), comprobamos si la fecha inicial (que ya hemos trasladado a B8) es una fecha de fin de mes (31 de enero, 28 ó 29 de febrero, 31 de marzo, etcétera) o no. Si lo es, entonces le pedimos que genere la siguiente fecha sumándole a la fecha inicial el cociente entre 12/$B$4 ¿Qué significa esto? Significa que estamos calculando el número de meses de un año dividido por el número de pagos al año. Por ejemplo,  para pagos trimestrales (4 al año) la celda B4 contendrá un 4, con lo que el cálculo resultante será: 12/4=3  que es el número de meses que le sumará cada vez a la fecha inicial.

Finalmente (si la celda de la izquierda no está en blanco ni es una de las fechas de fin de mes del año) entonces le pedimos que  para componer la FECHA correcta utilice el AÑO de la celda anterior (inmediata superior); el MES de la celda anterior más 12/$B$4; y el DIA de la celda anterior. 

5. Copiamos esta fórmula y la pegamos hasta B43.

De esta manera, y como puede apreciar en las siguientes figuras, tanto el número de pago como el calendario de pagos aparecerán automáticamente en función de los datos que introduzcamos en nuestra entrada de datos:

Sugerencias:

* Como práctica puede intentar introducir Validación de datos en las entradas (rango B2:B4).

* También puede utilizar el artículo anterior si desea sombrear las filas pares.