Notas de lectura de Excel 29 - Tabla de gestión de préstamos - Ejemplos de gestión automatizada de consultas y cálculo de intereses de préstamos
Esta sección toma la situación crediticia de Yifan Company como ejemplo para analizar cómo utilizar Excel para diseñar un conjunto de "tablas estadísticas de gestión de préstamos" altamente informativas (consulte el archivo de muestra "Tabla 5-4 Gestión de préstamos"). Tablas estadísticas" ).
1. Marco básico y visualización de funciones
Las "estadísticas de gestión de préstamos" constan de cuentas de préstamos (consulte la Figura 5-71) y cuentas de préstamos (consulte la Figura 5-72).
Figura 5-71 Cuenta de crédito
Figura 5-72 Libro mayor de préstamos
El libro mayor de préstamos será responsable de registrar la información de cada préstamo y proporcionar información sobre Consultas futuras y alertas tempranas sobre el monto total a pagar.
Nuestro objetivo es: solo necesitamos ingresar manualmente el número de contrato de crédito, la institución financiera, el monto total del crédito y el período de crédito en el libro mayor de crédito, e ingresar manualmente el número de contrato de crédito, el número de contrato de préstamo y El préstamo en el libro mayor de préstamos incluye información básica como capital, fecha de inicio del préstamo, plazo, tasa de interés anual, período de liquidación de intereses, etc.
1. Funciones de información principal
(1) Muestra automáticamente el límite de crédito utilizado actualmente, el límite de crédito disponible y el límite de crédito total disponible de todos los créditos.
(2) Muestra automáticamente el monto total actual del préstamo, los intereses a pagar y el capital e intereses totales a pagar para cada (y todos) los préstamos de este mes.
2. Función de información auxiliar
Después de que el usuario ingresa un período personalizado (que no exceda los 30 días), automáticamente le indicará el capital, los intereses y el capital total y los intereses adeudados en el próximo. unos días e identificar detalles específicos.
2. Premisas y supuestos básicos
1. El plazo de crédito y financiación es de un mes.
Generalmente, el período de crédito y el período de financiación proporcionados por las instituciones financieras a las empresas se calculan mensualmente (o se pueden convertir a un mes completo). Por ejemplo, medio año (6 meses) y un año (12 meses). Pero también hay algunos casos en los que el cálculo se basa en días (no se pueden convertir a meses completos). Por ejemplo, 100 días. Esta sección sólo considera los casos en los que el período relevante puede calcularse mensualmente.
2. El principal del préstamo debe reembolsarse en un solo pago en la fecha de vencimiento.
Cuando se trata de pagar el principal del préstamo, la mayoría de las empresas e instituciones financieras todavía están acostumbradas a utilizar un método de pago único en la fecha de vencimiento. Por tanto, este apartado no considera situaciones no convencionales como la amortización anticipada, la amortización a plazos, la amortización aplazada, la imposibilidad de amortizar o incluso los préstamos fraudulentos.
3. Sólo se consideran los ciclos de liquidación de intereses mensuales y trimestrales, y el período del préstamo es mayor que el ciclo de liquidación de intereses.
La liquidación de intereses mensuales y la liquidación de intereses trimestrales son actualmente los métodos de liquidación de intereses más utilizados. Para evitar discutir temas demasiado complejos, esta sección solo considera los dos métodos de liquidación de intereses anteriores. Cuando el plazo del préstamo es inferior a 3 meses, los intereses sólo se pueden liquidar mensualmente.
4. Fecha de vencimiento del préstamo y reglas de liquidación de intereses
Cabe señalar que las siguientes reglas de liquidación de intereses son las reglas generales vigentes de las instituciones financieras de mi país y no son hipotéticas. Si hay cambios de política, relegémoslos a suposiciones.
(1) Fecha de vencimiento del préstamo (crédito).
La fecha de vencimiento de un préstamo (crédito) es diferente a la fecha de vencimiento de un depósito a plazo. La fecha de vencimiento del depósito es generalmente el año (mes) de la contraparte, y la fecha de vencimiento del préstamo generalmente es el día anterior al año (mes) de la contraparte. Por ejemplo, la fecha de inicio también es octubre de 2013 y el período es de un año. La fecha de vencimiento de los depósitos a plazo es el 14/12/2014 y la fecha de vencimiento del préstamo generalmente es el 12/2013.
(2) Fecha de liquidación de intereses.
1) Liquidación y pago de intereses mensuales: El día 20 de cada mes es el día de liquidación de intereses, pagándose los intereses al día siguiente.
Es decir, los intereses del préstamo del día 21 del mes anterior al 20 de este mes se pagan el día 21 de este mes, y así sucesivamente. Por supuesto, la premisa es que la fecha de inicio del préstamo es anterior al 21 del mes anterior y la fecha de vencimiento del préstamo es posterior al 20 de este mes.
2) Los intereses se liquidan trimestralmente, siendo el día 20 del último mes de cada trimestre el día de liquidación de intereses y los intereses se pagan al día siguiente.
Es decir, los intereses del préstamo del día 21 del trimestre anterior al día 20 de este trimestre se pagarán el día 21 de este trimestre. Por supuesto, la premisa es que la fecha de inicio del préstamo es anterior al día 21 del trimestre anterior y la fecha de vencimiento del préstamo es posterior al día 20 del trimestre anterior.
3) En la fecha de vencimiento del préstamo, liquidar y pagar los intereses impagos.
(3) Al calcular el interés diario, el año completo se calcula como 360 días.
Los lectores con experiencia en administración de dinero deberían haber descubierto un fenómeno: la cantidad total de intereses realmente pagados por un préstamo es siempre ligeramente mayor que la cantidad total de intereses calculada en función de la tasa de interés del préstamo. Esto se debe a que el interés diario utilizado para cada liquidación de intereses durante el período del préstamo se calcula en base a 360 días en un año, pero hay 365 (o 366) días en el año. De esta manera, el interés anual real pagado es igual al principal del préstamo × tasa de interés anual × (365÷360).
(4) Fechas de inicio y finalización del devengo de intereses.
Se puede ver en las reglas de vencimiento del préstamo que el interés del préstamo se calcula en función del número de días que los fondos están realmente ocupados (en lugar de los intereses de los depósitos a plazo calculados en función del recuento pero no de la cola). modelo), por lo que la fecha de valor Hay dos principios para el cálculo: la fecha de valor es la fecha de inicio del préstamo más tarde y la fecha de liquidación de intereses anterior es la fecha límite de acumulación de intereses más temprana entre la fecha de liquidación de intereses actual y la fecha de vencimiento del préstamo; .
En concreto, cuando la fecha de inicio del préstamo es anterior al día siguiente a la fecha de liquidación de intereses anterior, y si la fecha de vencimiento del préstamo es posterior a la fecha de liquidación de intereses actual, las fechas de inicio y finalización del interés actual será la fecha de liquidación de intereses anterior al día posterior a la fecha de liquidación de intereses hasta la fecha de liquidación de intereses actual, si la fecha de vencimiento del préstamo es inferior a la fecha de liquidación de intereses actual, la fecha de inicio y finalización del devengo de intereses para el período actual es la fecha de liquidación de intereses anterior. día siguiente a la fecha de liquidación de intereses del período anterior a la fecha de vencimiento del préstamo.
Por ejemplo, actualmente es 11 de julio de 2014 y un préstamo se paga el 14 de octubre, por un año, con intereses mensuales. Entonces la fecha de inicio del préstamo (octubre de 2014) es anterior al día siguiente a la fecha de liquidación de intereses anterior (214 de junio de 2014) y a la fecha de vencimiento del préstamo (octubre de 2015).
Si el préstamo anterior es por un período de medio año y la fecha de vencimiento del préstamo (2014, 14) es anterior a la fecha de valor actual (2014, 20 de julio), la fecha de valor actual es 2014, 21 ~ Julio de 2014.
Si la fecha de inicio del préstamo es posterior al día posterior a la fecha de liquidación de intereses del período anterior, y la fecha de vencimiento del préstamo es posterior a la fecha de liquidación de intereses del período actual, la fecha de inicio de devengo de intereses para el período actual es desde la fecha de inicio del préstamo hasta la fecha de liquidación de intereses del período actual, si la fecha de vencimiento del préstamo es menor que la fecha de liquidación de intereses del período actual, la fecha de inicio y finalización del período de intereses actual es desde la originación del préstamo. fecha hasta la fecha de vencimiento del préstamo. Esta última situación sólo ocurrirá cuando el plazo del préstamo sea inferior a un mes, por lo que es un evento de pequeña probabilidad.
Por ejemplo, la fecha actual es 2011, julio de 2014, y se registra un préstamo el 28 de junio de 2014, a un año, con liquidación de intereses mensual. Entonces, la fecha de inicio del préstamo (28 de junio de 2014) es posterior al día siguiente a la última fecha de liquidación de intereses (20 de junio de 2014), y la fecha de vencimiento del préstamo (27 de junio de 2015) es posterior a la fecha de liquidación de intereses actual (julio de 2014). 20, 2014).
3. Notas
Para facilitar la expansión y consulta posterior, se debe prestar atención a mantener los nombres unificados al ingresar manualmente los parámetros relevantes (por ejemplo, cuando el nombre de un banco). está involucrado en "institución financiera", es necesario unificar las especificaciones del nombre y evitar múltiples nombres para la misma institución financiera).
En la primera sección de este capítulo, mencionamos el uso de configuraciones de orden en la validación de datos para evitar que ocurra la situación "mismo nombre y mismo apellido". Por ejemplo, este método se puede utilizar para especificar el período de liquidación de intereses en el libro mayor del préstamo como "mes" y "trimestre". Pero para las instituciones financieras, este método no es muy fácil de usar, porque el alcance del rango de parámetros de las instituciones financieras no es muy limitado y no se puede predecir si surgirán nuevas instituciones, por lo que no se pueden diseñar alternativas. Por lo tanto, sólo podemos enfatizar las especificaciones al completar información aquí.
4. Equipo de puntos de conocimiento
Antes de leer el siguiente contenido de esta sección, confirme si los puntos de conocimiento relevantes en la Figura 5-73 se han equipado básicamente en su cerebro.
Antes de comenzar a explicar el caso en esta sección, insertamos un punto de conocimiento que utilizaremos próximamente: la definición y uso de nombres.
En términos generales, el nombre de la definición es similar a "let X = alguna información" comúnmente usado en matemáticas, donde "alguna información" puede ser una constante, un rango de celdas o una fórmula.
Una vez completada la configuración, podemos usar "X" para implementar la función "alguna información" que se le ha asignado. El uso de nombres puede simplificar fórmulas complejas, haciéndolas más fáciles de entender y mantener. A continuación, ponemos un ejemplo para explicarlo brevemente.
Puntos de conocimiento relacionados en la Figura 5-73
En el orden de la Figura 5-74, usamos el nombre de la definición para establecer la fórmula para el monto total en "Importe total". Los pasos de la operación son los siguientes: Pestaña Fórmula → Definir grupo de nombres → Definir nombre (consulte la Figura 5-74).
Figura 5-74 Uso de nombres definidos
En este momento, podemos usar los nombres definidos para establecer la fórmula del monto total (ver Figura 5-75).
La fórmula para la unidad de área D3:D5 es: =Cantidad total.
Figura 5-75 Reemplazar fórmulas tradicionales con nombres definidos
Usar nombres definidos es similar a configurar parámetros auxiliares. Puede simplificar gradualmente fórmulas compuestas largas, mejorando así la comprensión de las fórmulas compuestas. sexo. Habrá aplicaciones específicas a continuación.
V. Método de diseño de la fórmula de información principal
Después de tener una comprensión general del marco, las funciones y las precauciones relacionadas de la "Tabla de estadísticas de gestión de préstamos", usemos Yifan Company Loan. un caso para discutir el diseño de la “Tabla de Estadísticas de Gestión de Préstamos”.
Caso 5-4 Al 11 de julio, Yifan Company ha otorgado 2.065.438 créditos a instituciones financieras como se muestra en la Figura 5-76, y ha emitido préstamos como se muestra en la Figura 5-77, todos en RMB.
Figura 5-76 Tabla de información crediticia de Yifan Company
Figura 5-77 Tabla de información de préstamos de Yifan Company
Estructura combinada con la "Tabla de estadísticas de gestión de préstamos", la Los métodos de diseño relevantes son los siguientes.
1. Método de diseño de la fórmula de la cuenta de crédito (1)
(1) Visualización de la fecha en tiempo real (celda D2).
Para que la tabla siempre muestre la fecha "de hoy", sólo necesitamos usar una fórmula de fecha simple mientras nos aseguramos de que la fecha del sistema informático sea correcta (consulte la Figura 5-78).
La fórmula en la celda D2 es: =Hoy()
Figura 5-78 La fórmula para establecer "Hoy" para siempre
(2) Fecha de vencimiento del crédito (Área unitaria H4:H8).
La fórmula de este parámetro también es relativamente simple. Hemos visto una situación similar en la tercera sección de este capítulo. Es solo que la fecha de vencimiento en esta tabla debe ser exacta a una fecha específica, no solo a un mes. Cabe señalar que, dado que la fecha de vencimiento del préstamo no se calcula en años (meses), es necesario restar 1 del parámetro de día.
La fórmula para las celdas H4 es:
=SI (o (F4=" ", G4=" " "), FECHA (año (G4), mes (G4) F4 , (G4)-1))
Después de completar las columnas, puede completar la configuración de la fórmula del período de crédito (ver Figura 5-79).
Actualmente, existen. tres en el libro mayor de crédito, a saber, límite de crédito utilizado (D4: área de unidad D8), límite de crédito disponible (E4: área de unidad E8) y límite de crédito actualmente disponible (unidad G2), estos parámetros aún no se han diseñado con fórmulas porque. Estos tres datos solo se pueden utilizar en préstamos. Su efecto solo puede reflejarse después de que se realice el negocio. Volveremos a analizarlos más adelante.
2.
(1) Visualización de fecha en tiempo real (celda A3).
No hay nada que decir al respecto. La fórmula en la celda A3 es: =HOY()
Figura. 5-79 Fórmula de vencimiento del crédito
(2) Instituciones financieras (C7:C16 área de unidad).
Dado que la institución financiera tiene datos existentes en el libro de crédito, no es necesario. ingréselo repetidamente. Al mismo tiempo, debemos usar la función BUSCARV para encontrar la referencia. Al mismo tiempo, también debemos considerar cuando se ingresa accidentalmente Cuando no existe un contrato de crédito (no hay información financiera coincidente). institución en este momento), la fórmula debe aparecer de inmediato
La fórmula en la celda C7 es:
= if (a7 = " "," ", if error (vlookup (a7 , cuenta de crédito! $A$4: $B$8, 2, 0), "El número de contrato de crédito no existe").
Después de completar las columnas, se puede completar el cotejo de la información de la institución financiera (ver Figura 5-80).
Figura 5-80 La función BUSCARV coincide automáticamente con información relevante
Si el usuario también necesita verificar la fecha de vencimiento del crédito, el monto total del crédito y otra información según el contrato de crédito correspondiente en el préstamo libro mayor, también se debe utilizar el mismo método y no se repetirá aquí.
(3) Fecha de vencimiento del préstamo (G7: G16 unidad de superficie).
La idea es la misma que la fórmula de vencimiento en un libro de crédito.
La fórmula de la celda G7 es:
=SI (o (E7=" ", F7=" " "), FECHA (año (F7), mes (F7) E7 , día (F7)-1))
Después de completar, se puede completar el cálculo de la fecha de vencimiento del préstamo (ver Figura 5-81)
Figura 5-81 Llegada del préstamo Fórmula de fecha
(4) Fecha de inicio de acumulación de intereses para este mes (área de celda T7: T16)
A continuación, ingrese el capital y los intereses a pagar este mes, los intereses a pagar este mes y. otros datos Diseño de fórmula Sabemos que un requisito previo importante para calcular el interés es determinar el número de días para el cálculo del interés ya que la fecha límite para el cálculo del interés para este período es clara (la fecha de liquidación de intereses de este mes o la fecha de vencimiento del préstamo). , debemos mirar el inicio del cálculo de intereses para este mes. ¿Cómo calcular el día?
La fecha valor de este mes puede entenderse como el día posterior a la última fecha de liquidación de intereses antes de este mes (. denominada "última fecha de liquidación de intereses"), porque es solo un parámetro auxiliar, para evitar parámetros auxiliares Para influir en la lectura clara de información importante, lo colocamos en el área fuera de la tabla principal (columna T)
De las reglas de cálculo de intereses, sabemos que según las fechas de inicio y finalización del préstamo y el período de liquidación de intereses, este mes la fecha de interés será diferente, como se muestra en la Figura 5-82.
La Figura 5-82 en realidad nos proporciona las ideas y la lógica del diseño de fórmulas. Para evitar que la fórmula final sea demasiado larga, antes de diseñar la fórmula, podemos establecer los nombres de definición de las fórmulas para varios indicadores de uso frecuente (. ver Figura 5-83)
Figura 5-82 Varias situaciones de la fecha de inicio de acumulación de intereses de este mes
Figura 5-83 Parámetros (1) que definen nombres en esta sección
Paso uno: Limpiar el medio ambiente
Como se puede ver en la Figura 5-81, siempre que falte uno de los tres parámetros de plazo, fecha de inicio del préstamo y período de liquidación de intereses. , la fecha de interés de este mes no se puede calcular, por lo que llegamos a la conclusión:
El primer paso de las celdas T7 La fórmula es:
=IF(o (E7=. " ", F7=" ", I7=" " ", ingrese el segundo paso)
Paso 2: Determinar este mes El préstamo se ha cancelado antes.
Desde que asumimos Para que todos los préstamos se liquiden a la vez en la fecha de vencimiento, la clave para el segundo paso es determinar si el préstamo se canceló antes de este mes.
Si la fecha de vencimiento del préstamo no es posterior al. último día del mes anterior (G7
La fórmula del segundo paso de la celda T7 es:
= IF(G7 lt; =EOMONTH(final de este mes) Fecha de interés, -1 ), "Establecido", ingrese al tercer paso)
Tenga en cuenta que esto involucra la celda ubicada en la tercera fila y debe establecer una referencia absoluta para garantizar que las columnas siguientes se completen correctamente. El siguiente diseño de fórmula también debe seguir este principio.
Paso 3: Determinar el método de liquidación de intereses mensuales
Debido a que el pago mensual es el día posterior a la última fecha de pago de intereses. diferencia entre la modalidad de liquidación de intereses y la modalidad de liquidación de intereses trimestral. En la modalidad de liquidación de intereses mensual, el día posterior a la última fecha de liquidación de intereses es el día 21 del mes anterior. En la modalidad de liquidación de intereses trimestral, el día posterior a la última liquidación de intereses. la fecha es el mes anterior 21. Por lo tanto, después de entrar en el tercer paso, sólo podemos discutirlo paso a paso junto con el ciclo de liquidación de intereses.
La fórmula del tercer paso de las celdas T7:
=IF(I7= "mensual", si (F7
El cuarto paso: Determinar la liquidación de intereses trimestral Liquidación de intereses bajo la modalidad de liquidación de intereses trimestral
De los pasos anteriores, ingresar al cuarto paso significa préstamos impagos bajo la modalidad de liquidación de intereses trimestral
Liquidación de intereses trimestral y liquidación mensual. La relación entre la fecha de inicio del préstamo y la fecha final de liquidación de intereses sigue siendo la misma que la tasa de interés.
Sin embargo, la fecha final de liquidación de intereses del modelo de liquidación de intereses trimestral es un poco más problemática. Debe ser la fecha de liquidación de intereses del último trimestre.
Entonces, ¿cómo determinamos el final del trimestre? Encuentre una regla lógica: el número de meses al final del trimestre debe ser un múltiplo entero de 3.
Entonces, podemos ver este patrón:
El número de meses al final del trimestre se divide por 3 y el resto es 0.
El número del primer mes después del final del trimestre se divide por 3 y el resto es 1.
El segundo mes después del final del trimestre se divide entre 3 y el resto es 2.
Haremos lo contrario de esta ley:
El primer mes después del final del trimestre menos 1 es el número de meses al final del trimestre anterior.
El número de meses después del final del trimestre menos 2 es el número de meses al final del trimestre anterior.
El número de meses al final del trimestre menos 3 es el número de meses al final del trimestre anterior.
El último mes del trimestre anterior se puede calcular restando el resto del mes actual dividido por 3 del mes actual. Pero hay una excepción. Cuando el resto es 0, debemos restar 3. Por lo tanto, necesitamos usar la función MOD para calcular el resto.
La fórmula del cuarto paso de las celdas T7 es:
= IF(F7 lt; =FECHA(año (fecha de liquidación de intereses de este mes), MES (fecha de liquidación de intereses de este mes )- if (mod (fecha de liquidación de intereses de este mes), 3) = 0, 3, mod (fecha de liquidación de intereses de este mes), 3), día (fecha de liquidación de intereses de este mes), DATE (año (fecha de liquidación de intereses de este mes) )).
La fórmula que combina los pasos anteriores
La fórmula molecular completa de las células T7 es:
=IF(o (E7= " ", F7 = " ", I7. = " " ", IF (G7 lt; = EOMES (fecha de liquidación de intereses de este mes, -1), "liquidado", IF (I7 = "mensual", if (F7
<) p>Después de completar la columna, puede obtener la fecha de inicio de acumulación de intereses de cada préstamo de este mes (consulte la Figura 5-84, la Figura 5-84a es un ejemplo en junio, la Figura 5-84b es un ejemplo en julio)Figura 5- 84 Fórmula de la fecha de cálculo de intereses para este mes
(5) Intereses pagaderos este mes (K7: K16 unidad de área)
Los intereses pagaderos este mes se refieren al interés realmente pagado este mes La fórmula de cálculo es:
Interés a pagar este mes = (principal del préstamo × tasa de interés anual ÷ 360) × número de días en el período de acumulación de intereses = interés diario × número de días en. el período de acumulación de intereses
Con base en esto, según los supuestos básicos de la sección y la información de la sección de premisas, sabemos que el período de acumulación de intereses será diferente según las fechas de inicio y finalización del préstamo. como se muestra en la Figura 5-85
Figura 5-85 Diversas situaciones durante el período de cálculo de intereses mensuales
Además, también debemos considerar:
1) El interés pagadero este mes incluye el pago de intereses en la fecha de liquidación de este mes y el pago de intereses en la fecha de vencimiento.
El pago de intereses en la fecha de liquidación de intereses de este mes se refiere al interés pagado sobre los intereses de este mes fecha de liquidación, y el pago de intereses en la fecha de vencimiento de este mes se refiere al interés pagado en la fecha de vencimiento del préstamo cuando el préstamo vence este mes. Cuando la fecha de liquidación de intereses resulta ser ese mes, asumimos que se pagan todos los intereses sobre el interés. fecha de liquidación.
2) Para los préstamos con modo de liquidación de intereses trimestral, los intereses se pagan solo en la fecha de liquidación de intereses al final del trimestre.
En consecuencia, nuestros pasos de diseño son los siguientes. siguiente
Paso 1: Limpiar el medio ambiente
Como se puede ver en la información relevante en la Figura 5-85, cuando el préstamo vence y el período de liquidación de intereses está vacío. y la fecha de vencimiento del préstamo es anterior a este mes (en este momento, la fecha de pago de intereses de este mes se muestra como "pagada
El interés a pagar este mes es 0. La fórmula de un paso"). es:
=SI (o (G7= ", I7= ", T7= ", 0, ingrese el segundo paso)
Segundo paso: Préstamo este mes El interés a pagar es vence este mes
Para determinar si la fecha de vencimiento del préstamo es este mes, también es cuestión de comparar si la fecha de vencimiento del préstamo y la fecha de liquidación de intereses de este mes son el mismo mes y año, y los intereses. pagadero este mes El período de acumulación de intereses es desde el día siguiente a la fecha de valor de los intereses de este mes hasta la fecha de vencimiento del préstamo.
Cabe señalar que los intereses se devengan tanto en la fecha de inicio como en la de finalización. fecha del período de acumulación de intereses, se calcula la acumulación de intereses. Al contar los días, se suma 1 después de restar las dos fechas.
Por ejemplo, la fecha de inicio del cálculo de intereses es 2014 14 1 y la fecha de finalización del cálculo de intereses es 2014 14 3, ** 3 días, pero la diferencia entre las dos fechas es 2 días, por lo que Es necesario agregar 1.
La fórmula del segundo paso de las celdas K7 es:
= Si (12 * año (G7) mes (G7) = 12 * año (fecha de liquidación de intereses de este mes) mes ( este mes) Fecha de pago de intereses mensual), Ronda(D7 * H7 *(G7-T7 1)/360.
Paso 3: Intereses a pagar sobre el préstamo que vence este mes.
Si el préstamo vence después de este mes, los intereses pagaderos este mes deben distinguirse según el modo del ciclo de liquidación de intereses, y este mes no es el final del trimestre (AND(I7= "trimestre", mod (mes (a3 ), 3)
La fórmula del tercer paso de las celdas K7 es:
=IF(AND(I7= "trimestre", MOD(mes (fecha de liquidación de intereses de este mes), 3)
La fórmula que combina los pasos anteriores
La fórmula completa para las celdas K7 es:
=IF(OR(G7= ", I7= ", T7= ", liquidado), 0, si (12 * año (G7) mes (G7) = 12 * año (fecha de liquidación de intereses de este mes) mes (fecha de liquidación de intereses de este mes) gt0), 0, ROUND (D7*H7) * (este mes Fecha de liquidación de intereses mensuales - T7 1)/360, 2)).
Después de completar, puede obtener el interés a pagar por cada préstamo este mes (consulte la Figura 5-86). > A juzgar únicamente por la extensión, la fórmula para los intereses pagaderos este mes no parece complicada. Sin embargo, cabe señalar que si no hay una fecha de inicio del cálculo de intereses este mes (celda T7), entonces la fórmula para los intereses pagaderos este mes es. relacionado con la columna T. Todos los parámetros de la celda deben reemplazarse con la fórmula completa para la fecha de inicio de este mes.
De esa manera no escalará mucho, como escalar el Monte Everest, donde necesitamos hacerlo. Configure algunos campos base en el camino. Cuando encontramos que la configuración de una determinada fórmula tiene muchos niveles y ramas lógicas, debemos habilitar inmediatamente alguna información intermedia (por ejemplo, al calcular el interés a pagar este mes, la fecha de inicio). de acumulación de intereses este mes pertenece a una información intermedia) como campo base El papel de simplificar gradualmente el diseño de la fórmula Para evitar daño cerebral en el proceso de conquistar la cima de la fórmula, definir el nombre también puede considerarse como un. forma de establecer un campamento base.
Figura 5-86 La fórmula de intereses a pagar para este mes
(6) El interés se paga en la fecha de liquidación de este mes (L7: L16 área unitaria) y la fecha de vencimiento de este mes (M7: M16 área unitaria)
La razón es que debe pagarse de acuerdo con su tiempo de pago. El motivo para distinguir el área de intereses a pagar de. este mes es que generalmente se pagará una gran cantidad de intereses en la fecha de liquidación de intereses del mes. Por lo tanto, es necesario que prestemos mucha atención al pago de intereses en la fecha de liquidación y estamos implementando la función de consulta. el capital y los intereses a pagar en los próximos días. En este momento, también debemos distinguir el momento del pago de intereses.
Con base en la relación anterior, podemos saber:
. Pago de intereses en la fecha de liquidación de intereses de este mes, pago de intereses en la fecha de vencimiento de este mes = interés a pagar este mes
Entonces, después de haber calculado el interés a pagar este mes, solo necesitamos saber uno de los dos. parámetros, a saber, el pago de intereses en la fecha de liquidación y el pago de intereses en la fecha de vencimiento, y el otro se puede resolver mediante el algoritmo inverso. En este caso, analizaremos y discutiremos la fórmula para pagar intereses en la fecha de liquidación.
Se puede ver en las reglas de cálculo de intereses que los factores relevantes que influyen en el pago de intereses en la fecha de liquidación de intereses se muestran en la Figura 5-87.
Figura 5-87 La relación entre la fecha de vencimiento y el período de liquidación de intereses en la fecha de vencimiento
Aunque hay seis situaciones en la Figura 5-87, la fecha de liquidación de intereses de este mes En realidad, solo hay tres posibilidades: sin intereses, el monto del pago de intereses es igual al interés pagadero este mes y el monto del pago de intereses es igual al interés pagadero desde el día posterior a la fecha de liquidación de intereses anterior hasta la fecha de liquidación de intereses de este mes. Con base en la lógica anterior, nuestros pasos de diseño son los siguientes:
Paso uno: No se calcularán intereses en la fecha de liquidación de intereses de este mes.
Cuando se dé una de las siguientes situaciones, no se pagarán intereses en la fecha de liquidación de intereses de este mes:
1) El interés a pagar este mes es 0.
2) La fecha de vencimiento del préstamo es anterior a la fecha de liquidación de intereses de este mes.
3) Bajo la modalidad de liquidación de intereses trimestral, este mes no es el final del trimestre.
La fórmula del primer paso para las celdas L7 es:
=Si (o (K7=0, G7 lt es la fecha de liquidación de intereses de este mes, y (I7= "Trimestre" , MOD( mes (la fecha de liquidación de intereses de este mes), 3)
Paso 2: El interés pagado en la fecha de liquidación de intereses de este mes es igual al interés pagadero este mes
.Si la fecha de vencimiento del préstamo es posterior a este mes, significa que no habrá pago de intereses este mes. Por lo tanto, el interés pagado en la fecha de liquidación de intereses de este mes es igual al interés pagadero este mes. De lo contrario, significa que la fecha de vencimiento del préstamo es posterior a la fecha de liquidación de intereses de este mes y antes del mes siguiente. En este momento, el monto del pago de intereses en la fecha de liquidación de intereses de este mes es igual al interés pagadero desde el día siguiente al anterior. fecha de liquidación de intereses a la fecha de liquidación de intereses de este mes
La fórmula del segundo paso en la celda L7 es:
= IF(G7 gt; EOMONTH (fecha de liquidación de intereses de este mes, 0), K7, ROUND (D7*H7* (fecha de liquidación de intereses de este mes - T7 1)/360, 2))
Combina la fórmula anterior
La fórmula completa de la celda L7:
=If (o (K7=0, G7 lt fecha de liquidación de intereses de este mes, y (I7= "Trimestre", MOD (mes (fecha de liquidación de intereses de este mes), 3)
En este momento, calcularemos y estableceremos la fórmula de pago de intereses en la fecha de vencimiento.
La fórmula para las celdas M7 es =K7-L7
Después de completar. , puede obtener el pago de intereses en la fecha de liquidación y el pago de intereses en la fecha de vencimiento (consulte la Figura 5-88). Figura 5-88 Fórmula de pago de intereses en la fecha de liquidación. pago de intereses en la fecha de vencimiento
(7) Principal e intereses pagaderos este mes (J7: J16 unidad de área)
El principal e intereses pagaderos este mes es la suma del principal del préstamo y interés pagadero este mes El interés mensual pagadero ya aparece en K, por lo que la clave del problema es el capital pagadero este mes
Si el capital del préstamo debe pagarse este mes, significa que el. El préstamo vencerá este mes, lo que a su vez se utiliza para determinar si el año de vencimiento del préstamo es igual al año actual. Si es igual, el capital y los intereses pagaderos este mes son iguales al capital del préstamo más los intereses pagaderos este mes. De lo contrario, solo se considera el interés a pagar este mes
Celda N7, la fórmula de ), D7 K7))
Después de completar las columnas, puede obtener el capital y los intereses. pagadero este mes (ver Figura 5-89)
Figura 5-89 Fórmula de capital e intereses pagaderos este mes
p>(8) Monto total actual del préstamo (unidad B3)
El monto total actual del préstamo es la suma del principal del préstamo que aún no ha vencido. La lógica de cálculo es, naturalmente, determinar si la fecha de vencimiento del préstamo es posterior a "Hoy". función (ver Figura 5-90).
La fórmula para la celda B3 es = sumif (G7: G16, " > " ampA3, D7: D16)
Figura 5-90 Actual fórmula del monto del préstamo
(9) Intereses pagaderos este mes (celda C3) y capital e intereses pagaderos este mes (celda D3). Esto va directamente a la función de suma.
La fórmula en la celda C3 es =SUM(K7:K16).
La fórmula en la celda D3 es =SUM(J7:J16).
Por supuesto, también puedes configurar fórmulas como el interés a pagar en la fecha de liquidación de intereses de este mes para satisfacer las necesidades de gestión, por lo que no entraré en detalles aquí.
En este punto se han configurado las principales fórmulas de información del libro de préstamos.
A continuación, regresaremos y completaremos los elementos inacabados en el libro de crédito.
3. Método de diseño de la fórmula de la cuenta de crédito (Parte 2)
(1) Límite de crédito utilizado (comunidad D4: D8).
El límite utilizado se refiere al importe del préstamo que se ha obtenido en el contrato de crédito correspondiente y aún no ha sido reembolsado. Esta definición contiene dos significados. En primer lugar, debe identificar los préstamos en virtud de su propio contrato y no se le permite contar los préstamos de otros contratos a su nombre. En segundo lugar, debe aclarar si los préstamos correspondientes todavía están en uso, porque los préstamos que se han reembolsado en su fecha de vencimiento; ya no ocupa el límite de crédito.
De esta forma sabemos que la cantidad utilizada es un problema de suma de múltiples condiciones.
Fórmula de celda D4:
=SUMIFS (¡libro mayor de préstamos! $D$7: $D$16, libro mayor de préstamos! $A$7: $A$16, A4, libro mayor de préstamos! $G $7: $G$16, " > amp$D$2)
Después de completar la columna, puede obtener el monto utilizado actualmente de cada contrato de crédito (consulte la Figura 5-91).
Figura 5-91 Fórmula de límite de crédito utilizada
(2) Líneas disponibles (E4:E8 unidad de área)
La primera impresión que nos da el límite de crédito disponible es naturalmente el total El límite de crédito menos el límite de crédito utilizado, sin embargo, este solo puede ser el primer sentimiento, y nuestro segundo sentimiento debería ser que la premisa de esta lógica es que el contrato de crédito no ha expirado.
La fórmula para la celda unitaria E4 es =IF(H4 gt; $2, C4-D4, $0)
Después de realizar el llenado de la columna, se puede obtener la cuota disponible (ver. Figura 5-92).
Figura 5-92 Fórmula de límite de crédito disponible
(3) Límite de crédito disponible actual (celda G2)
Este parámetro. se utiliza principalmente para resumir la cuota disponible
La fórmula en la celda G2 es =SUM(E4:E8)
En este punto, la fórmula de información principal del libro de crédito ha sido. completamente completado (Ver Figura 5-93)
Figura 5-93 Información principal de la cuenta de crédito