1. INTRODUCCIÓN
En este ejercicio crearemos la plantilla de préstamo vista en el vídeo. En líneas generales seguiremos los pasos descritos en el vídeo.
2. EJERCICIOS GUIADOS
Paso 1) En primer lugar crearemos la estructura de la tabla de cálculos generales:
• En A1 indicaremos el título: “CÁLCULO DE PAGOS E INTERESES DE UN PRÉSTAMO”. Esta celda la fusionaremos con las 4 siguientes para tener espacio para todo el texto.
• A partir de A3 crearemos la tabla horizontal de datos del préstamo (como se representa en el vídeo):
• Introduciremos los datos de ejemplo para el capital, la tasa anual y la duración.
• La tasa por periodo se calcula a partir de la tasa anual → =B5/12 =B5/12
• El número de periodos se calcula a partir de la duración en años → =B5/12 =B7*12
Paso 2) En la tabla inferior realizaremos los cálculos generales:
• Pago por periodo → =B5/12 =PAGO( B6; B8; B4)
• Pago total, multiplicando por el número de periodos → =B5/12 =B11*B8
• Intereses totales, restando del pago total el capital principal (fíjese que los pagos aparecen en negativo) → =B5/12 =B4+B12
EJERCICIO DE PLANTILLAS FINANCIERAS 2
Paso 3) A continuación crearemos la estructura para un préstamo con posible interés variable.
• Las columnas serán: “Periodo”, “Tasa”, “Pago Intereses”, “Pago Principal”, “Pago Mes” y “Capital Pendiente”.
• En la columna de “Periodo” indicaremos los distintos periodos: 1, 2, 3, etc. Arrastrando el último valor excel generará los números. Haremos, al menos, hasta el 24.
• Sobre el resto de columnas aplicaremos un formato de moneda.
• La tabla será similar a la vista en el vídeo:
Paso 4) En la columna de “Tasa” indicaremos la tasa anual aplicada en dicho mes. Partiendo de que inicialmente no se conoce la variación del interés tendremos:
• En la primera fila aplicaremos el interés inicial =B5.
• En la segunda fila suponemos que tenemos el anterior =E5
• Copiaremos la fórmula anterior en el resto de filas. De esta forma, cada fila tiene el mismo interés que la fila anterior.
Paso 5) Calcularemos el pago de intereses con la fórmula general para el primer periodo.
• En la fila 5 PAGOINT($→ =B5/12 E5/12;1;B8;B4)
Paso 6) Igualmente el pago de capital principal:
• En la fila 5 → =B5/12 PAGOPRIN($E5/12;1;B8;B4)
Paso 7) El pago total es la suma:
• En la fila 5 → =B5/12 =F5+G5
Paso 8) Y finalmente el interés pendiente tras el periodo será la diferencia con el inicial:
• En la fila 5 → =B5/12 =B4+G5
Paso 9) Para el segundo periodo supondremos que volvemos a empezar con el capital que quedó pendiente. Por lo que sigue siendo el periodo 1 pero tendremos un número de periodos menor.
EJERCICIO DE PLANTILLAS FINANCIERAS 2
• Para el cálculo de intereses PAGOINT( → =B5/12 E6/12; 1; $B$8-D6+1; I5)
• Para el cálculo del capital principal → =B5/12 PAGOPRIN( E6/12; 1; $B$8-D6+1; I5)
• El pago total y el pendiente serían como en la fila anterior.
Paso 10) El resto de periodos se calcularán como el segundo, por lo que las fórmulas podemos copiarlas sin más.
3. PRÁCTICA
Paso 11) A partir de la segunda fila añadiremos una guarda que compruebe que el periodo actual está dentro de los límites del préstamo.
• Cada fórmula pasará a ser del tipo (para la fila 5) =SI(D5<=$B$8; <fórmula anterior>; “”)
Paso 12) En la columna del número de periodo dejaremos el primero con el valor 1. Pero los siguientes los calcularemos a partir del anterior. Para la fila 5 tendríamos =D4+1
Paso 13) Copiaremos la segunda fila hacia abajo creando cuantas filas estimemos oportunas. Teniendo en cuenta que para un préstamo a 20 años serían 360 periodos.
• Ahora nuestra tabla estará preparada para indicar el número de años y se ajustará según las necesidades.