Cartera vencida en Excel: cómo calcularla sin duplicar abonos

La descarga no es un libro XLSX listo para calcular. Incluye dos CSV con los datos ficticios y un archivo de texto con todas las fórmulas e instrucciones. Una vez montados en un mismo libro, puedes guardar tu copia como XLSX y reutilizarla.

1. Importa los CSV como UTF-8, separados por comas. Coloca los datos desde A1 en dos hojas llamadas exactamente Facturas y Pagos.
2. Convierte en fechas las columnas C y D de Facturas, la columna C de Pagos y la celda Facturas!M2. El formato de origen es año-mes-día. Si Excel las dejó como texto, usa Texto en columnas con orden de fecha AMD. Los importes deben ser números.
3. Pega cada fórmula del instructivo en la celda indicada. Copia Facturas!F2:J2 hasta la fila 1000 y Pagos!E2 hasta la fila 1000. El resumen usa M4:M10 y no se copia hacia abajo.
4. Comprueba el ejemplo: 500 vencidos, 1.150 no vencidos, 450 sin vencimiento, subtotal 2.100, una factura y dos pagos por revisar. El estado debe decir «Provisional: conciliar incidencias».
5. Guarda como XLSX. Para usar tus propios datos, reemplaza solo las entradas A:E de Facturas y A:D de Pagos, y cambia el corte en M2. Conserva las fórmulas y no mezcles tus operaciones con los ejemplos.

El instructivo incluye funciones en español con punto y coma y equivalentes en inglés con comas. Usa la versión y el separador que reconozca tu Excel. La plantilla cubre hasta 999 registros por hoja; si amplías ese límite, debes actualizar todos los rangos.

Descargar facturas de ejemplo (CSV) Siete facturas, columnas de cálculo y espacio para el corte y el resumen. Requiere las fórmulas del instructivo.

Descargar pagos de ejemplo (CSV) Ocho movimientos, incluidos un pago posterior al corte y dos incidencias.

Descargar instrucciones y fórmulas (TXT) Fórmulas para montar las dos hojas en Excel, controles de entrada y resultados de referencia.

La columna de control de Pagos marca Incluir solo cuando el movimiento supera sus comprobaciones y su fecha no es posterior al corte. Posterior conserva el pago sin aplicarlo a ese reporte; Revisar señala un dato que debe aclararse.

Para una factura válida, la suma de pagos usa el ID de factura y el estado del movimiento:

=SUMAR.SI.CONJUNTO(Pagos!$D$2:$D$1000;Pagos!$B$2:$B$1000;A2;Pagos!$E$2:$E$1000;"Incluir")

La función suma importes que cumplen ambos criterios. Después, saldo = importe original - pagos incluidos. Las fórmulas completas del instructivo dejan el saldo vacío si la factura tiene incidencias o pagos vinculados sin aclarar; no convierten ese vacío en un saldo cero.

Para un saldo válido y positivo, la clasificación depende del vencimiento: anterior al corte, vencido; igual o posterior, no vencido; ausente, sin vencimiento. Los días de atraso de una factura vencida se calculan como fecha de corte - vencimiento.

Los controles señalan IDs repetidos, referencias inexistentes, campos necesarios vacíos, importes no positivos y algunas contradicciones de fechas. Si los pagos incluidos superan el importe de la factura, el saldo negativo queda visible como Revisar saldo y no entra en el subtotal. No se recorta a cero.

Estos controles no descubren todo: un pago duplicado con otro ID, una fecha numérica pero equivocada o una operación omitida pueden pasar inadvertidos. Contrasta siempre el listado con los comprobantes y el registro contable. «Sin incidencias detectadas» no equivale a conciliación terminada.

F-104 tiene saldo calculable, pero no vencimiento confirmado. Puedes conservar sus USD 450 en «Sin vencimiento»; no puedes decidir cuántos días de atraso tiene ni sumarla al grupo vencido.

F-107 tiene un pago sin fecha. Sin saber si ocurrió antes o después del corte, no puedes elegir entre USD 200 y USD 300 como saldo de septiembre. La fórmula deja el saldo indeterminado hasta resolver la fecha.

P-08 no identifica factura. Sus USD 120 permanecen en el registro de pagos para conciliación. No los repartas entre facturas ni los restes del subtotal por intuición: primero confirma su aplicación.

La plantilla no cubre cuotas con varios vencimientos, notas de crédito, retenciones, anticipos, pagos revertidos, compensaciones, disputas, intereses ni deterioro contable. Si existen, no basta con omitirlos y dar el resto por definitivo: separa el caso para conciliación. El saldo vencido del reporte tampoco demuestra pérdida, imposibilidad de cobro o solvencia del cliente.

En el ejemplo, cambia el importe de P-03 del 2 de octubre de USD 100 a USD 900, sin mover el corte del 30 de septiembre. F-101 debe seguir con saldo de USD 500 y el subtotal provisional con USD 2.100: el movimiento continúa siendo posterior.

Ahora supongamos una factura de USD 200, vencida antes del corte, y un abono de USD 50 cuya fecha falta. ¿Puedes reportar USD 150 vencidos? No todavía. Primero necesitas saber cuándo se pagó. Tener un comprobante de abono no basta para reconstruir el saldo de cualquier fecha.

• Antigüedad de cuentas por cobrar (fecha atrasada) Microsoft Learn · Documenta distintas bases de antigüedad en Business Central. No establece una regla jurídica de mora.
• Función SUMAR.SI.CONJUNTO Soporte de Microsoft · Sintaxis para sumar importes que cumplen varios criterios.

Un reporte útil debe permitir explicar qué se debía en la fecha elegida, qué parte había vencido y qué falta confirmar. Empieza por conciliar facturas y pagos. Solo después usa la clasificación para preparar tu revisión de cartera.