![]() ![]() Construimos ahora un cuadro con las siguientes columnas: - Año............: para señalar los datos referidos al final de cada año- Cuota...........: la misma para todos los años (constante)- Intereses........: se calcula sobre el capital pendiente del año anterior- Amortización.... Capital pendiente..: saldo del préstamo al final de cada año. Fórmulas para calcular un préstamo. En anteriores entradas hemos visto como calcular un préstamo en Excel de manera sencilla y hemos puesto a vuestra disposición algunas plantillas ya hechas para calcularlo directamente sin tener que confeccionarlas desde cero. También incluimos la posibilidad de añadirle la opción de realizar una amortización anticipada, así como poder establecerle un período de carencia (total y parcial). Ahora en esta entrada vamos a ver la equivalencia entre las fórmulas matemáticas, que están detrás del cálculo de un préstamo por el sistema francés o también conocido como sistema de termino amortizativo constante y que es el más extendido (existen otros sistemas), y las funciones de Excel. Lo primero que tendremos que hacer es definir las variables que intervienen en el cálculo. Para realizar un cuadro de amortización tendremos que crear un cuadro con 6 columnas (período, cuota o término amortizativo, cuota de interés, cuota de amortización, capital vivo y capital amortizado). Ahora veremos qué cálculos hay que seguir para calcular los valores de las variables en cada período. Cuestiones previas. Antes de nada tenemos que tener en cuenta que tanto la cuota de intereses como los períodos deben ir en consonancia, es decir, si calculamos cuotas anuales el tipo de interés debe ser anual, si la cuota es mensual el tipo de interés debe ser mensual. Para calcular el número de períodos es sencillo, solo tendremos que multiplicar: Si las cuotas las tenemos que pagar mensualmente, multiplicaremos el número de años por 1. Si las cuotas las tenemos que pagar trimestralmente, multiplicaremos el número de años por 4. Y así para cualquier período de pago, solo hay que subdividir el año en períodos iguales y multiplicarlo por el número de años que vaya a durar el préstamo.[box style=’warning’]Ojo con el tipo de interés que usamos[/box]Para poner en consonancia el tipo de interés que aplicamos con la frecuencia de pago tendremos que hacer algún cálculo más: Si disponemos del tipo de interés anual efectivo, que denotaremos por “i”, la fórmula que usaremos para calcular el tipo de interés efectivo para el período en cuestión “im”(suponemos capitalización compuesta de los intereses, la mayoría de entidades financieras lo calculan simplemente dividiendo el tipo de interés por “m”, el resultado puede resultar similar pero no es equivalente) de la siguiente manera. Donde “m” se corresponderá con el número de partes en las que hemos subdividido el año o número de pagos que haremos cada año. Esto quedará más claro en la siguiente tabla: m. Período. 1Anual. 2Semestral. Cuatrimestral. 4Trimestral. Bimestral. 12. Mensual. Este será el tipo de interés que tendremos que utilizar si nuestro préstamo lo pagamos en cuotas mensuales, bimestrales, trimestrales, etc. Una vez aclarado esto, calcularemos cada una de las columnas del cuadro de amortización de un préstamo. Cuota o término amortizativo. El término amortizativo en un sistema de préstamo francés es constante, es decir, en cada período pagaremos lo mismo hasta el final del préstamo. La fórmula que emplearemos para calcular la cuota, que denotaremos por “a” es la siguiente: Las variables de esta fórmula son: C0: Capital inicial (el capital que pedimos prestado)i: Tipo de interés efectivo (tendremos que acordarnos de ponerlo en consonancia a la frecuencia de pago como hemos explicado en las cuestiones previas). Corresponde con el número de períodos de pago (tanto tipo de interés como períodos de pago deben ir en consonancia). Capital vivo. El capital vivo es el capital que todavía nos queda por amortizar. Lo denotaremos por “Ck”. Ck=a*(1- (1/(1+i))(n- k))/i)Las variables que intervienen en esta fórmula ya las hemos visto salvo “k”. Esta variable indica el período en el que estamos calculando el capital vivo. Para verlo más claro podemos suponer que tenemos un préstamo a 1. Si queremos calcular el capital vivo después de realizar el 5º pago o de haber pagado la 5ª mensualidad la expresión (n- k) de la fórmula será, (1. Cuota de interés. Este cálculo es bastante sencillo. Se corresponde con el producto del tipo de interés efectivo de cada período por el capital vivo a principio de dicho período. Denotaremos a la cuota de interés como “Ik”. Cuota de amortización. Las cuotas de amortización se corresponden con la diferencia existente entre el capital vivo a principio de un período y el capital vivo al final del mismo. Se puede calcular de esta manera o podemos optar por otra fórmula más sencilla como: Ak+1=Ak(1+i)Ak=A1*(1+i)(k- 1)Donde A1 = a – C0*i. Ak: Cuota de amortización del período “k”. Capital amortizado. ![]() Construir el cuadro de amortización de un préstamo de 300.000 euros, al 10% de interés anual. del fraccionamiento del importe del préstamo en pagos. Los individuos solicitan prestamos a. Cuadro de Amortización. del 18 % anual. Para la amortización y pago de intereses. El capital amortizado hasta un período “k” es la suma de todas las cuotas de amortización hasta ese mismo período “k”. El capital amortizado en el último período tiene que ser igual al capital prestado al inicio del préstamo. Denotamos al capital amortizado con “Mk”, entonces la fórmula será la siguiente: Mk=A1*((1+i)k)* (1- (1/(1+i))k)/i)Una aclaración es que “k” es el número de período para el cual queremos calcular el capital amortizado. Fórmulas para Excel. Si somos de los que queremos hacer las cosas más fáciles en una hoja de cálculo las formulas a emplear podrían ser las mismas que hemos explicado anteriormente, solo tendríamos que introducirlas en las celdas y ya estaría. Por suerte, Excel dispone de funciones financieras que evitan que tengamos que introducir largas fórmulas a la hora de calcular un préstamo. Ahora explicaremos que funciones son equivalentes a las fórmulas anteriores en Excel. Cuota de amortización en Excel. Para realizar este cálculo Excel dispone de la función PAGO, esta función admite una serie de parámetros. Tabla de amortización en Excel utilizando las funciones PAGO, PAGOINT y PAGOPRIN así. Generalmente encontraremos especificada la tasa de interés de forma anual. Si los pagos son anuales. Haz click aquí para acceder una hoja de cálculo en la que podrás confeccionar cuadros de amortización cambiando los datos. Contabilización de la formalización de un préstamo por el coste amortizado mediante un ejemplo. A continuación escribiremos su estructura: PAGO(tasa; nper; va; [vf]; [tipo])Donde: Tasa: Se corresponde con el tipo de interés efectivo de cada período (tendremos que calcularlo en una celda a parte como hemos explicado anteriormente)Nper: Es el número de períodos o pagos a los que tenemos que hacer frente (podemos calcularlo en una celda aparte)VA: Tendremos que introducir el capital inicial, es decir, el capital que nos han prestado. Le pondremos signo negativo para no tener números negativos.[vf]: Lo dejaremos en blanco[tipo]: Generalmente tampoco se especifica pero hace referencia a cuando se realiza el pago, a principio de período o al final del período. Si se omite o se le da un valor igual a 0 estaremos calculando los pagos a final de período y si se le da un valor igual 1 serán al comienzo.[box style=’help’] Como las cuotas son constantes en el sistema francés, podremos establecer dólares para fijar los valores y arrastrar hacia abajo. Esto nos permitirá agilizar los cálculos.[/box]Lo habitual es crear un cuadro de variables al lado del cuadro de amortización en el cual pondremos: el capital, la frecuencia de pago, tipo de interés anual, tipo de interés efectivo (tendremos que calcularlo a partir del anual con las fórmulas que hemos explicado al principio), la duración en años y la duración en períodos según la frecuencia de pago (tendremos que multiplicar como hemos dicho anteriormente). Cuota de interés en Excel. Cuadro Amortización Préstamo ExcelAquí utilizaremos la misma fórmula que hemos visto antes. Es decir, si queremos calcular los intereses que pagamos en el período 3, tendremos que coger el capital vivo del período 2 (3- 1) y multiplicarlo por el tipo de interés del período. Una vez establecidos los dólares podremos arrastrar. ![]() Cuota de amortización en Excel. Este cálculo es muy sencillo. Si en la cuota que pagamos mensualmente se recogen los intereses más el capital que amortizamos, si tenemos los intereses de un período y la cuota o pago mensual, sacar la cuota de amortización es una tarea fácil, solo tendremos que realizar una resta. La variable “a” no lleva el subíndice “k” debido a que siempre tiene el mismo valor, es constante. Si queremos calcular la cuota de amortización del período 5, tendremos que restar a la cuota mensual los intereses del período 5. Capital Vivo en Excel. Al igual que el anterior, este valor se obtiene por diferencia. Para calcular el capital vivo de un período “k”, tendremos que echar mano del capital vivo del período “k- 1” y restarle la cuota de amortización del período “k”. Capital amortizado en Excel. Si en las anteriores columnas de nuestro cuadro de amortización teníamos que restar, aquí tendremos que sumar. Para calcular el capital amortizado en un periodo “k”, tendremos que sumarle al capital amortizado en “k- 1” la cuota de amortización del período “k”. Con esto ya tendríamos confeccionado nuestro cuadro de amortización, lo único que podemos añadir es que hay que tener cuidado con los dólares a la hora de fijar ciertas cantidades. Al realizar el cuadro observaremos que la cuota o pago mensual es constante, la cuota de interés es decreciente, la cuota de amortización es creciente, el capital vivo decreciente (en el último período debe ser 0) y el capital amortizado es creciente (en el último período el capital amortizado debe ser igual al capital que nos han préstamo).
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |