Antes de tomar el préstamo, sería bueno calcular todos los pagos. Salvará al prestatario en el futuro de diversos problemas inesperados y decepciones cuando resulta que ese sobrepago es demasiado grande. Ayuda en este cálculo puede las herramientas del programa de Excel. Averigüemos cómo calcular los pagos de anualidad en el préstamo en este programa.
Cálculo del pago
En primer lugar, se debe decir que hay dos tipos de pagos de crédito:- Diferenciado;
- Anualidad.
Con un esquema diferenciado, el cliente aporta una proporción mensual de los pagos en el organismo de un préstamo más pagos a los intereses. La magnitud de los pagos de intereses cada mes disminuye, ya que el cuerpo del préstamo se reduce de la cual se calculan. Por lo tanto, el pago mensual general también se reduce.
El esquema de Ann Tía utiliza un enfoque ligeramente diferente. El cliente realiza una cantidad única del pago total mensual, que consiste en pagos en el cuerpo de un préstamo y pago de intereses. Inicialmente, las contribuciones de intereses están numeradas en todo el importe del préstamo, pero a medida que el cuerpo disminuye, el interés se reduce y se reduce a la acumulación. Pero la cantidad total de pago se mantiene sin cambios debido a un aumento mensual en la cantidad de pagos por parte del cuerpo del préstamo. Así, con el tiempo, la proporción de interés en el pago mensual total cae, y el peso de la proporción del cuerpo crece. Al mismo tiempo, el pago mensual general en sí mismo no está cambiando a lo largo del término de crédito.
Justo en el cálculo del pago de la anualidad, nos detendremos. Especialmente, esto es relevante, ya que ahora la mayoría de los bancos utilizan este esquema en particular. Es conveniente para los clientes, porque en este caso la cantidad total de pago no cambia, permanece fijo. Los clientes siempre saben cuánto necesita pagar.
Paso 1: Cálculo de contribución mensual
Para calcular una contribución mensual al usar un circuito de anualidad en Excele, hay una función especial - PPT. Se refiere a la categoría de operadores financieros. La fórmula de esta característica es la siguiente:
= Ppt (tasa; kper; ps; bs; tipo)
Como vemos, la función especificada tiene un número bastante grande de argumentos. Es cierto que los dos últimos de ellos no son obligatorios.
El argumento "Tarifa" indica una tasa porcentual para un período específico. Si, por ejemplo, se usa una tasa anual, pero el pago del préstamo se realiza mensualmente, entonces la tasa anual debe dividirse en 12 y el resultado se usa como un argumento. Si se aplica un tipo de pago trimestral, entonces, en este caso, la apuesta anual debe dividirse en 4, etc.
"CPER" significa el número total de períodos de pagos de préstamos. Es decir, si el préstamo se toma por un año con el pago mensual, entonces el número de períodos se considera 12, si dos años, entonces el número de períodos, 24. Si el préstamo se toma por dos años con el pago trimestral, entonces El número de periodos es 8.
"PS" indica el valor presente en este momento. Hablando con palabras simples, este es el monto total del préstamo al comienzo de los préstamos, es decir, la cantidad que recibe prestado, excluyendo el interés y otros pagos adicionales.
"BS" es un costo futuro. Este valor será un organismo de préstamo en el momento de completar el acuerdo de préstamo. En la mayoría de los casos, este argumento es "0", ya que el prestatario al final del período de crédito debe conformarse completamente con el prestamista. El argumento especificado no es obligatorio. Por lo tanto, si está descendiente, se considera cero.
El argumento "tipo" determina el tiempo de cálculo: al final o al comienzo del período. En el primer caso, toma el valor "0", y en el segundo - "1". La mayoría de las instituciones bancarias utilizan exactamente la opción con el pago al final del período. Este argumento también es opcional, y si se omite, se cree que es cero.
Ahora es el momento de pasar a un ejemplo específico de calcular una contribución mensual utilizando la función PL. Para calcular, usamos una tabla con los datos de origen, donde se indica la tasa de interés en el préstamo (12%), el valor del préstamo (500,000 rublos) y el período del préstamo (24 meses). Al mismo tiempo, el pago se realiza mensualmente al final de cada período.
- Seleccione el elemento en la hoja en la que se mostrará el resultado del resultado y haga clic en el icono "Insertar función", colocado cerca de la fila de fórmulas.
- Se lanzó la ventana del asistente de ventana. En la categoría "Financial" asigne el nombre "PLT" y haga clic en el botón "Aceptar".
- Después de eso, abre la ventana de argumentos del operador de PL.
En el campo "Tarifa", debe ingresar el valor porcentual para el período. Esto se puede hacer manualmente, solo poniendo un porcentaje, pero se indica en una celda separada en la hoja, por lo que le daremos un enlace. Instale el cursor en el campo y luego haga clic en la celda correspondiente. Pero, como recordamos, tenemos la tasa de interés anual en nuestra tabla, y el período de pago es igual al mes. Por lo tanto, dividimos la apuesta anual, y más bien un enlace a la célula en la que está contenida por el número 12, correspondiente a la cantidad de meses en el año. La división se ejecuta directamente en el campo de la ventana de argumento.
En el campo CTER, se establece los préstamos. Él es igual a 24 meses. Puede solicitar el campo manualmente número 24, pero nosotros, como en el caso anterior, especifique un enlace a la ubicación de este indicador en la tabla de origen.
En el campo "PS" indica el valor inicial del préstamo. Es igual a 500,000 rublos. Como en los casos anteriores, especificamos un enlace al elemento de hoja, que contiene este indicador.
En el campo "BS" indica la magnitud del préstamo, después de su pago completo. Como recuerdas, este valor es casi siempre cero. Instale en este campo el número "0". Aunque este argumento generalmente se puede omitir.
En el campo "Tipo", especificamos al principio o al final del mes se realiza el pago. Nosotros, como en la mayoría de los casos, se produce a finales de mes. Por lo tanto, establecemos el número "0". Como en el caso del argumento anterior, es posible ingresar cualquier cosa para este campo, entonces el programa predeterminado asumirá que es cero igual a ella.
Después de ingresar todos los datos, presione el botón "Aceptar".
- Después de eso, en la célula que asignamos en el primer párrafo de este manual, se muestra el resultado del cálculo. Como puede ver, la magnitud del pago general mensual sobre el préstamo es de 23536.74 rublos. Permítanos no confundir el signo "-" antes de esta cantidad. Por lo tanto, el exilio indica que este es el flujo de dinero, es decir, una pérdida.
- Para calcular la cantidad total de pago de todo el período de préstamo, teniendo en cuenta el reembolso del cuerpo de un préstamo y un interés mensual, en lugar de multiplicar el monto del pago mensual (23536.74 rublos) por el número de meses (24 meses ). Como puede ver, la cantidad total de pagos por todo el período de préstamo en nuestro caso fue de 564881.67 rublos.
- Ahora puede calcular la cantidad de sobrepago de préstamos. Para hacer esto, es necesario quitar la cantidad total de pagos en el préstamo, incluido el interés y el organismo de préstamo, el importe inicial reclamado. Pero recordamos que el primero de estos valores ya con el signo "-". Por lo tanto, en específicamente, nuestro caso resulta que deben doblarse. Como vemos, el pago excesivo total del préstamo en todo el período fue de 64881.67 rublos.
Lección: Maestro de funciones en Excel.
Etapa 2: Detalles de pago
Y ahora, con la ayuda de otros operadores de Excel, hacemos un detalle mensual de los pagos para ver cuánto en un mes específico prestamos a través del cuerpo del préstamo, y cuánto es la cantidad de interés. Para estos fines, herrero en la tabla del exilio, que completaremos los datos. Las líneas de esta tabla serán responsables ante el período correspondiente, es decir, el mes. Dado que el período de préstamo es de 24 meses, el número de filas también será apropiado. Las columnas indicaban un organismo de préstamo, los pagos de intereses, un pago mensual total, que es la suma de las dos columnas anteriores, así como la cantidad restante para pagar.
- Para determinar la cantidad de pago por parte del cuerpo del préstamo, use la función OSP, que está pensada para estos fines. Establecemos el cursor en la celda, que se encuentra en la línea "1" y en la columna "Pago por parte del cuerpo del préstamo". Haga clic en el botón "Pegar funcional función".
- Ir al maestro de las funciones. En la categoría "Financiera", notamos el nombre "OSPLT" y haga clic en el botón "Aceptar".
- Los argumentos de los argumentos del operador OSP comienzan. Tiene la siguiente sintaxis:
= Ospult (tasa; Período; KPER; PS; BS)
Como podemos ver, los argumentos de esta característica coinciden casi completamente con los argumentos del operador del PLT, solo en lugar del argumento opcional "Tipo" agregó un argumento obligatorio "período". Indica el número del período de pago, y en nuestro caso particular en el número del mes.
Llene los argumentos de los argumentos de la función OSR que ya están familiarizados con los mismos datos, que se utilizaron para la función PL. Solo dado el hecho de que en el futuro, copiar una fórmula se utilizará a través de un marcador de llenado, debe hacer todos los enlaces en los campos absolutos para que no cambien. Esto requiere poner un signo de dólar antes de cada valor de las coordenadas verticales y horizontales. Pero es más fácil de hacerlo, simplemente seleccionando las coordenadas y haciendo clic en la tecla F4 FUNCTION. El signo de dólar se colocará en los lugares correctos automáticamente. Tampoco olvidamos que la apuesta anual debe dividirse en 12.
- Pero tenemos otro nuevo argumento, que no fue de la función PL. Este argumento "período". En el campo apropiado, establece una referencia a la primera celda de la columna "Período". Este elemento de la hoja contiene el número "1", que denota el número del primer mes de préstamo. Pero a diferencia de los campos anteriores, dejamos el enlace relativo en el campo especificado, y no lo hacen absoluto.
Después de que se introducen todos los datos sobre los que hablamos anteriormente, presione el botón "OK".
- Después de eso, en la célula, que previamente asignamos, aparecerá la cantidad de pago por el organismo del préstamo para el primer mes. Será 18536.74 rublos.
- Luego, como se mencionó anteriormente, debemos copiar esta fórmula a las células columnas restantes utilizando un marcador de llenado. Para hacer esto, coloque el cursor en la esquina inferior derecha de la celda, que contiene la fórmula. El cursor se convierte en una cruz, que se llama un marcador de llenado. Haga clic en el botón izquierdo del ratón y tire de él hasta el final de la tabla.
- Como resultado, todas las columnas celulares se llenan. Ahora tenemos un gráfico de pagar un préstamo mensual. Como se mencionó anteriormente, la cantidad de pago en este artículo aumenta con cada nuevo período.
- Ahora necesitamos hacer un cálculo mensual de pago por intereses. Para estos fines, utilizaremos el operador PRT. Asignamos la primera celda vacía en la columna "Porcentaje de pagos". Haga clic en el botón "Pegar funcional función".
- En las funciones del Maestro de las funciones en la categoría "Financiera", producimos los nombres de la NAMP. Realice un clic en el botón "Aceptar".
- Se inicia la ventana Argumentos de la función TRP. Su sintaxis se ve así:
= PRT (tasa; Período; CPU; PS; BS)
Como podemos ver, los argumentos de esta función son absolutamente idénticos a elementos similares del OPERADOR OF. Por lo tanto, solo ingrese los mismos datos en la ventana que ingresamos en la ventana anterior de los argumentos. No olvidamos que la referencia en el campo "Período" debe ser relativo, y en todos los demás campos, las coordenadas deben llevarse a la forma absoluta. Después de eso, haga clic en el botón "Aceptar".
- Luego, se muestra el resultado de calcular la cantidad de pago por intereses para un préstamo para el primer mes en la celda correspondiente.
- Aplicando el marcador de llenado, haga de la copia de la fórmula en los elementos restantes de la columna, de esta manera que recibe un programa mensual para los porcentajes para el préstamo. Como podemos ver, como se dijo anteriormente, desde el mes hasta el mes, el valor de este tipo de pago disminuye.
- Ahora tenemos que calcular el pago mensual general. Para este cálculo, uno no debe recurrir a ningún operador, ya que puede usar la simple fórmula aritmética. Doble los contenidos de las celdas del primer mes de las columnas "Pago por parte del cuerpo del préstamo" y "Interés completo". Para hacer esto, configure el letrero "=" en la primera celda vacía de la columna "Pago mensual total". Luego haga clic en los dos elementos anteriores configurando el signo "+" entre ellos. Haga clic en la tecla ENTER.
- A continuación, utilizando el marcador de llenado, como en los casos anteriores, complete la columna de datos. Como podemos ver, a lo largo de toda la acción del contrato, el monto del pago mensual total, que incluye el pago por parte del cuerpo del préstamo y el pago de interés, será de 23536.74 rublos. En realidad, ya calculamos este indicador antes de usar PPT. Pero en este caso se presenta con mayor claridad, precisamente como la cantidad de pago por parte del cuerpo del préstamo y el interés.
- Ahora necesita agregar datos a la columna, donde se muestra el saldo del monto del préstamo mensual, lo que aún se requiere para pagar. En la primera celda de la columna "Saldo para pagar", el cálculo será el más fácil. Debemos ser retirados de la magnitud del préstamo inicial, que se especifica en la tabla con datos primarios, el pago por parte del cuerpo del préstamo para el primer mes en la tabla calculada. Pero, dado el hecho de que uno de los números que ya vamos con el signo "-", entonces no deben ser retirados, sino para plegarlo. Lo hacemos y haga clic en el botón ENTER.
- Pero el cálculo del saldo para pagar después del segundo y los meses posteriores será algo más complicado. Para hacer esto, debemos quitarnos del cuerpo del préstamo al comienzo de préstamos el monto total de los pagos por parte del organismo del préstamo por el período anterior. Instale el "=" firme en la segunda célula de la columna "Palacio para pagar". A continuación, especifique un enlace a la celda, que contiene el monto del préstamo inicial. Lo hacemos absoluto, resaltando y presionando la tecla F4. Luego ponemos el letrero "+", ya que tenemos el segundo significado y tan negativo. Después de eso, haga clic en el botón "Insertar función".
- Se inicia la maestría de funciones, en la que debe mudarse a la categoría "Matemáticas". Allí asignemos la inscripción "Sums" y presione el botón "OK".
- La ventana Argumentos inicia los argumentos de la función. El operador especificado sirve para resumir los datos en las celdas que debemos realizar en la columna "Pago por el cuerpo del préstamo". Tiene la siguiente sintaxis:
= Sumas (número 1; número2; ...)
Como argumentos, referencias a las células en las que están contenidos los números. Ponemos el cursor en el campo "Number1". Luego, pónga el botón izquierdo del ratón y seleccione las dos primeras celdas de la columna del cuerpo de crédito en la hoja. En el campo, como vemos, apareció un enlace al rango. Consta de dos partes separadas por el colon: referencias a la primera gama del rango y en el último. Para poder copiar la fórmula especificada en el futuro mediante un marcador de llenado, realizamos el primer enlace al rango absoluto. Lo resaltamos y hagamos clic en la tecla F4 FUNCTION. La segunda parte de la referencia y dejar relativa. Ahora, cuando se usa un marcador de llenado, se solucionará el primer rango del rango, y este último se extenderá a medida que se mueva hacia abajo. Esto es necesario para que cumplamos con los objetivos. A continuación, haga clic en el botón "Aceptar".
- Por lo tanto, el resultado del saldo de la deuda de crédito después del segundo mes se descarga en la célula. Ahora, comenzando con esta celda, hacemos copiar la fórmula en elementos de columna vacía utilizando un marcador de llenado.
- El cálculo mensual de los restos a pagar en el préstamo se realiza para todo el período de crédito. Como debería ser, al final de la fecha límite, esta cantidad es cero.
Por lo tanto, no simplemente calculamos el pago del préstamo, sino que organizamos una especie de calculadora de crédito. Que actuará en un esquema de anualidad. Si en la tabla de origen, por ejemplo, cambiamos el monto del préstamo y la tasa de interés anual, entonces en la tabla final habrá recalculación automática de datos. Por lo tanto, se puede utilizar no solo una vez para un caso particular, sino aplicar en diversas situaciones para calcular las opciones de crédito en un esquema de anualidad.
Lección: Funciones financieras en Excel.
Como puede ver, usando el programa de Excel en casa, puede calcular fácilmente el pago general del préstamo mensual en el esquema de anualidad, utilizando el operador de PL para estos fines. Además, con la ayuda de las funciones OSR y la PRT, es posible calcular la cantidad de pagos por parte del cuerpo del préstamo y los porcentajes para el período especificado. Aplicando todas las funciones de este equipaje juntos, es posible crear una potente calculadora de crédito que se puede usar más de una vez para calcular el pago de la anualidad.