Cómo llevar el control de préstamos en Excel

Qué necesitas registrar antes de calcular saldos

Para controlar préstamos en Excel, separa lo que estaba programado de lo que realmente se pagó. Una fila con el nombre del cliente y un saldo escrito a mano resulta difícil de revisar cuando hay abonos parciales o más de un préstamo por persona.

Empieza con cuatro hojas: Clientes, Préstamos, Cuotas y Pagos. Asigna códigos únicos como C-001 para un cliente y P-001 para un préstamo. Cada cuota también necesita una clave, por ejemplo P-001-01. No uses nombres como identificadores: pueden repetirse.

La estructura de las cuatro hojas

• Clientes: código, nombre y datos de contacto necesarios.
• Préstamos: código, cliente, fecha de desembolso, capital y condiciones acordadas.
• Cuotas: clave de cuota, préstamo, número, vencimiento, importe programado, pagado y pendiente.
• Pagos: código de movimiento, clave de cuota, fecha, importe, medio de pago y estado.

Guarda importes como números y fechas como fechas de Excel. El formato de moneda debe mostrar S/, pero no escribas «S/ 80» dentro de una celda de importe: convertirías el valor en texto y podrías alterar las sumas.

Un ejemplo con dos cuotas y tres abonos

Este ejemplo usa dos cuotas de S/ 120 previamente acordadas. Solo calcula cuánto se pagó y cuánto sigue pendiente. No calcula intereses, tasas ni cargos, y el pendiente del cronograma no equivale necesariamente al capital pendiente.

Fórmulas para sumar pagos confirmados

Con esa estructura, escribe en E2 de Cuotas la siguiente fórmula y cópiala hacia abajo. Suma el importe de Pagos únicamente cuando coinciden la clave de cuota y el estado «Confirmado».

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

En F2 de Cuotas, resta lo pagado del importe programado:

=D2-E2

En el ejemplo, la primera cuota queda en cero y la segunda tiene S/ 90 pendientes. No fuerces el resultado a cero si es negativo: un abono en exceso necesita revisión para decidir cómo se aplica.

Estas fórmulas usan nombres de funciones en español y punto y coma como separador. Según la configuración de Excel, puede necesitarse una coma. En una instalación en inglés, SUMAR.SI.CONJUNTO se llama SUMIFS. Los rangos del ejemplo llegan hasta la fila 1000: amplíalos juntos cuando tu registro supere ese límite.

Controles para evitar errores

1. Asigna un código diferente a cada movimiento para detectar duplicados.
2. Usa una lista de validación para los estados, con valores consistentes como Confirmado y Pendiente.
3. Verifica que cada pago tenga una clave de cuota que exista. Un pago sin coincidencia no aparecerá en su saldo.
4. Si un pago cubre dos cuotas, reparte su importe en dos registros vinculados a la misma referencia y comprueba que la suma coincida con lo recibido.
5. Conserva respaldo y documenta las correcciones en lugar de borrar el historial.

Cómo revisar las cobranzas del día

Filtra Cuotas por vencimiento y revisa los importes pendientes. En Pagos, filtra por fecha y por estado Confirmado para revisar lo recaudado. Separa efectivo y transferencias: el total cobrado no es siempre el efectivo que el equipo debe entregar.

Evita compartir datos personales o el archivo completo con personas que no necesitan acceder a ellos. Cuando trabajan varios cobradores, define quién actualiza el archivo y cómo resuelven cambios simultáneos.

Cuándo considerar un sistema de préstamos y cobranzas

Una hoja de cálculo requiere mantener fórmulas, revisar referencias y coordinar las actualizaciones. Si varias personas registran operaciones o necesitas consultar vencimientos desde el celular, evalúa una plataforma de gestión.

SOFINA organiza préstamos, cronogramas, cuotas y operaciones por colaborador. Revisa sus funcionalidades y prueba un proceso de ejemplo antes de trasladar tu cartera. Para preparar tus registros, consulta también cómo llevar el control de préstamos y pagos.