FORMULAS EN EXCEL

NOCIONES FINANCIERAS

Muchos de nosotros ya tuvimos que pedir una o varios préstamos hipotecarios en el transcurso de nuestra vida. Otros se lo están planteando y otros tendrán que hacerlo en algún momento.

Cuando necesitamos pedir una hipoteca, muchos nos conformamos con visitar varias entidades de préstamo y comparar las cuotas que nos va a tocar pagar mes a mes.

Sabiendo como lo hacen los bancos, podemos llegar a esas oficinas con una idea clara de lo que nos van a proponer.

La idea es la siguiente:

Cuando el banco nos presta dinero, ya sea mediante un crédito personal o una hipoteca, o cualquier otro tipo de producto, lo que espera de nosotros es que se lo devolvamos y además que remuneremos todo el tiempo que ese dinero no ha estado en sus manos, con cierto beneficio. Esa remuneración, es bien sabido que la llamamos interés o tasa.

El interés propuesto por las entidades de crédito son referidas a un año, es decir, si nos prestan un capital a un tanto por ciento, ese tanto por ciento es anual, de modo que si se trata de un préstamo que nos lo entregan hoy, y hay que hacerlo efectivo, amortizarlo, devolverlo dentro de un año, de una vez, tendremos que devolver al banco el capital prestado más el tanto por ciento de capital pactado previamente.

Por ejemplo, nos prestan 10.000 € hoy a devolver al cabo de un año al 10% de interés.

Dentro de un año, tendremos que entregar al banco la siguiente suma:

- 10.000 € que nos habían prestado.- El interés pactado: (10.000 x 10): 100 = 1.000 €

En definitiva, habremos de devolver la cantidad de 11.000 € para saldar nuestra deuda.

Esto que parece tan simple, se complica si decidimos ir amortizando (pagando) nuestra deuda en cómodos plazos mensuales.

¡Que es lo que ocurre ahora que complica los cálculos?

Para comprender bien lo que sucede, vamos a suponer que en un primer momento somos nosotros los que decidimos qué cantidad del principal (importe que nos han prestado) vamos a entregar como pago y que el interés anual es del 10%. Lo recogemos en la tabla siguiente:

Mes 1: 2000 € Mes 4: 500 € Mes 7: 500 € Mes 10: 500 €Mes 2: 1000 € Mes 5: 1000 € Mes 8: 500 € Mes 11: 1250 €Mes 3: 500 € Mes 6: 750 € Mes 9: 500 € Mes 12: 1000 €

El primer mes abonamos 2000 €. Significa que el capital principal a partir del segundo mes, va a ser como máximo 8.000 €, porque 2.000 € ya están amortizados. Esto significa, que el montante total del préstamo: 10.000 € los vamos a tener un único mes: el primero. Pues para ese primer mes, vamos a calcular el interés que hemos de abonar por ese capital.Hemos dicho que la tasa de interés anual era el 10%. Pero esto es en un año, es decir,

i=C i xi . n¿=10.000 x 0,1x112

=83,333 €

Como hemos pactado pagar de principal 2.000 € la cuota el primer mes es 2083,33 €.

Llegado el momento del pago del segundo mes, resulta que hemos mantenido en nuestro poder 10.000 – 2.000) = 8.000 € durante un periodo de un mes.

Volvemos a hacer el cálculo para este segundo pago:

El interés satisfecho será I= 8.000 x (0.1 x 1/12)= 66,66 €

Como hemos pactado pagar 1.000 € de principal, entonces el pago de la segunda cuota será 1066,66 €.

Durante el tercer mes hemos mantenido un principal de 7.000 ~, puesto que ya hemos amortizado 2.000 € el primer mes y 1.000 € el segundo.

El abono de intereses de este tercer periodo es i= 7.000 x (0,1 x1/12) = 58,33 €Luego la cuota de este mes es de 500 € pactados de principal más el valor de los intereses del periodo: 558,33 €Así, de esta manera, realizamos el pago fraccionado en doce cuotas resultando que al cabo de un año el montante total pagado es el que figura en la tabla

Remanente Interes Amortizacion Cuota10000

83,330

8000 66,67 2000 2.083,33

7000 58,33 1000 1.066,67

6500 54,17 500 558,33

6000 50,00 500 554,17

5000 41,67 1000 1.050,00

4250 35,42 750 791,67

3750 31,25 500 535,42

3250 27,08 500 531,25

2750 22,92 500 527,08

2250 18,75 500 522,92

1000 8,33 1250 1.268,75

0 0,00 1000 1.008,33

497,92 10000 10.497,92

Hemos visto que la diferencia entre pagar el principal de una vez al final, comparándolo con realizar el pago mediante cuotas periódicas ha sido importante: de pagar al final 11.000 €, se queda en 10.497,92 € con el sistema de cuotas pactado.

La misma cantidad, pero devuelta mediante cuotas mensuales de igual principal amortizado, da como resultado cuotas crecientes de la siguiente manera:

Remanente Interes Amortizacion Cuota10.000,00 83,33 833,33 916,67

9.166,67 76,39 833,33 909,728.333,33 69,44 833,33 902,787.500,00 62,50 833,33 895,836.666,67 55,56 833,33 888,895.833,33 48,61 833,33 881,945.000,00 41,67 833,33 875,004.166,67 34,72 833,33 868,063.333,33 27,78 833,33 861,112.500,00 20,83 833,33 854,171.666,67 13,89 833,33 847,22

833,33 6,94 833,33 840,28

0,00 0,00 0,00 0,00541,67 10000 10.541,67

Como vemos, dependiendo de cómo pactemos la devolución, así será el resultado final del pago.

En el caso del pago de las hipotecas, el método más extendido por entidades de crédito es la denominada amortización por el método francés.

Consiste en realizar los pagos de las cuotas tal y como hemos visto hasta el momento, pero calculadas de forma que todos los periodos se pague exactamente lo mismo.

Es fácil comprobar que con este método, cambia en cada periodo tanto el interés generado como el capital devuelto del principal. En este caso, y para el ejemplo que estamos tratando, los pagos quedarían de la siguiente manera:

principal 10000años 1 periodos 12interes 10%

periodo cuota intereses ppal amort amortizado remanente

1 879,16 € 83,33 € 795,83 € 795,83 € 9.204,17 €2 879,16 € 76,70 € 802,46 € 1.598,28 € 8.401,72 €3 879,16 € 70,01 € 809,14 € 2.407,43 € 7.592,57 €4 879,16 € 63,27 € 815,89 € 3.223,31 € 6.776,69 €5 879,16 € 56,47 € 822,69 € 4.046,00 € 5.954,00 €6 879,16 € 49,62 € 829,54 € 4.875,54 € 5.124,46 €7 879,16 € 42,70 € 836,46 € 5.712,00 € 4.288,00 €8 879,16 € 35,73 € 843,43 € 6.555,42 € 3.444,58 €9 879,16 € 28,70 € 850,45 € 7.405,88 € 2.594,12 €

10 879,16 € 21,62 € 857,54 € 8.263,42 € 1.736,58 €11 879,16 € 14,47 € 864,69 € 9.128,11 € 871,89 €12 879,16 € 7,27 € 871,89 € 10.000,00 € -0,00 €

10.549,91 € 549,91 € 10.000,00 €¿Que diferencia encontramos entre interés simple y compuesto?Cf

INTERÉS SIMPLE INTERÉS COMPUESTOC f=Ci(1+i . n) 1

n

f iC C i siendo

Donde Cf = Capital final.Ci = Capital inicialI = tasa de interés anual. n = número de años.

Si la capitalización no es anual la fórmula es:

C f=Ci(1+ik . nk )k à Número de períodos de capitalización que se hace al año. Así si la capitalización es:

semestral, k=2cuatrimestral, k=3trimestral, k=4mensual, k=12

Cf à Capital final Ci à Capital inicial Ià Interés anual nà Número de años

Si la capitalización no es anual la fórmula es:

1n k

f i kC C i donde

K à Número de períodos de capitalización que se hace al año. Así si la capitalización es:

semestral, k=2cuatrimestral, k=3trimestral, k=4mensual, k=12

Supongamos que queremos capitalizar un principal inicial de 10.000 € al 10% de interés anual durante un periodo de 5 años.

Si lo hacemos a interés simple, el primer año, nos tienen que devolver el capital inicial, 10.000 € más los intereses generados durante ese año: 10% del principal, es decir, 10.000 x 0.1 = 1000 €. En total recibimos 11.000 €.

En capitalización compuesta, este primer año, recibiremos los mismo, 11.000 €; los 10.000 € de principal más el 10% del mismo.

En capitalización simple, el segundo año volvemos a capitalizar 10.000 €, por lo que transcurrido ese periodo, de nuevo recibiremos 11.000 €.

Pero que pasa ahora con el interés compuesto?. Ahora, lo que capitalizamos durante este segundo año, es absolutamente todo lo recibido tras finalizar el primero. Es decir, capitalizamos 11.000 € que tenemos en vez de 10.000 €.

Ahora, nos devuelven 11.000 € más el 10% de esa cantidad, 11.000 x 0,1 =1.100 €, con lo que, al final del segundo año, recibiremos 12.100 €.

De esta forma, podemos construir una tabla que nos permita diferenciar el monto cada año dependiendo de cada uno de los dos métodos:

INTERES SIMPLE INTERES COMPUESTO

PRINCIPAL % INTERES NUEVO PPAL

PRINCIPAL % INTERES NUEVO PPAL

10.000 € 10% 1.000 € 10.000 € 10.000 € 10% 1.000 € 11.000 €10.000 € 10% 1.000 € 10.000 € 11.000 € 10% 1.100 € 12.100 €10.000 € 10% 1.000 € 10.000 € 12.100 € 10% 1.210 € 13.310 €10.000 € 10% 1.000 € 10.000 € 13.310 € 10% 1.210 € 14.641 €10.000 € 10% 1.000 € 10.000 € 14.641 € 10% 1.464,1 € 16.105,1 €

En definitiva, por capitalización simple, habrá recibido al final de los cinco años, 10.000 € de principal, más 1.000 € cada año. Esto hace un total de 15.000 €

En capitalización compuesta, sin embargo, al final de los cinco años habrá recibido en total 16.105,1 €, resultado de capitalizar cada año, el total del principal de ese periodo más los intereses recibidos en el mismo. La diferencia, como vemos es de 1105,1 €

Sin entrar ya en más detalles, diremos que para hacer este cálculo, nos podemos valer de funciones o fórmulas financieras que permiten conocer el resultado con una sola operación.

La capitalización simple, la hemos descrito más arriba :

C f=Ci (1+i . n ) ;C f=10.000 . (1+0 ,1.5 )=15.000€

Para la capitalización compuesta, utilizamos la siguiente expresión:

C f=Ci (1+i )n;C f=10.000 . (1+0,1 )5=16.105,1€

CÁLCULOS FINANCIEROS CON EXCEL

Entendido como es el funcionamiento de estos sistemas crediticios, vamos a intentar explicar cómo poder calcular los mismos con la hoja de cálculo.

Para ello, vamos a crear un nuevo libro de trabajo con tres hojas.

En la primera de ellas, vamos a crear una tabla para calcular las cuotas, dada una amortización del principal diferente cada periodo, impuesta por el cliente, tal como vimos en el primer ejemplo.

En la segunda hoja, basándonos en la tabla creada en la hoja anterior, calculamos las cuotas resultantes, dada una amortización exactamente igual por periodo.

Por último, y con el mismo formato que las anteriores, crearemos en el último libro la tabla de amortización del préstamo por el método francés.

Las explicaciones que se van a dar servirán para cualquier versión de Excel

Comencemos.

Creamos un nuevo libro de Excel e inicialmente, renombramos las hojas del mismo. Para ello, pulsamos con el botón derecho del ratón sobre el nombre de hoja que queremos modificar y elegimos la opción cambiar nombre. El nombre actual de la pestaña queda seleccionado y podemos reescribir el nuevo nombre encima. Llamaremos a la primera pestaña Cuotas Asimétricas, a la segunda Amortización Constante y a la tercera Método Francés.

Como las tablas que vamos a crear en cada hoja del libro son iguales, mantenemos pulsada la tecla Control del teclado mientras pulsamos con el botón izquierdo del ratón cada una de las pestañas con el nombre de cada hoja. Ahora, cada operación que realicemos en la hoja activa, la estaremos clonando a las otras dos.

La tabla creada tendrá el aspecto de la imagen siguiente:

Tenemos a continuación especial atención en dar formato a las celdas siguientes:

La celda C1 tendrá formato número, con dos decimales y separador de miles.

La celda C3 tendrá formato Porcentaje.

El rango B8:F19 tendrá formato número, dos decimales y separador de miles. Además le daremos formato negrita.

Con esto, que simplemente es a nivel estético, podemos “des-seleccionar las pestañas “Amortización constante” y “Método Francés”. Si pasamos de una hoja a otra pulsando en sus respectivos nombres con el ratón, veremos que las tres contienen exactamente lo mismo.

Centrémonos en la primera hoja.

Los valores del pago de principal los tenemos en este mismo documento, en la página 1. Los transcribimos directamente a la columna ppal amort (principal amortizado). Podemos escribir ya las fórmulas de las columnas siguientes, amortizado y remanente.

Para amortizado, solo tenemos que sumar en cada celda el valor de la celda superior mas la celda a su izquierda. (En el caso de la fila 8, como la fila 7 está vacía, sumará a cero el valor de

la celda a su izquierda. La fórmula quedará “=E7+D8”. La escribimos como referencia relativa porque ahora la vamos

a copiar a todo el resto de la columna y necesitamos que dinámicamente se haga referencia a la celda adecuada. Recordemos esta fórmula se leería “Toma el valor de la celda superior y súmale el valor de la celda situada a la izquierda” en este caso, la superior es la E7 y la de la izquierda la celda D8, pero en cada celda de la columna, a medida que nos movemos por ella, las celdas referenciadas también han variado. Si no lo hemos hecho, copiamos la celda en toda la columna.

Introducimos el valor 10.000 en la celda C1 y pasamos a escribir la fórmula de la primera celda de la columna remanente.

en la celda F8 escribimos la fórmula “=$C$1-E8” . vemos una diferencia

sustancial con la fórmula introducida en la columna anterior. En esta las referencias son mixtas, es decir, contiene referencias absolutas y relativas. Referencia absoluta es la que apunta a la celda C1 y la escribimos con ambos signos de dólar. Esta celda debe permanecer invariante allá donde sea copiada, es decir, en toda la columna F. la fórmula así definida se leerá “Al valor de la celda C1 (10.000) le restas el valor de la celda que se encuentre a la izquierda. En la primera celda de la columna es la celda E8, pero en la segunda será E9 y en la última será E19.

Escrita la fórmula, sino la hemos copiado ya, lo hacemos en todas las celdas de esta columna. Desde la 9 a la 19 inclusive.

El resultado de lo realizado hasta ahora se puede ver en la figura de la derecha.

Nos falta aplicar las fórmulas para el interés de periodo y por último, en la columna cuota, sumar el valor de interés de cada fila con el valor de amortizado de la misma fila.

Vamos con el interés:

Vimos ya anteriormente que el interés anual se iba a aplicar de forma equivalente a un mes en cada periodo. Pues es lo que vamos a hacer.

El interés de primera celda, la celda C8, será el valor obtenido de aplicar el 10% anual en un mes al principal pendiente de amortizar, que es todo.

Comenzamos escribiendo el valor del interés en su celda correspondiente: la celda C3.

A continuación, situándonos en la celda C8 escribimos la fórmula: “=C1*$C$3/12”. Esta fórmula la leeremos así: toma el valor de C1 y lo multiplicas por la doceava parte del valor de la celda C3. Dividir el interés anual entre 12 no es del todo correcto. Lo correcto sería multiplicar el interés anual por la fracción de año que corresponda, que es en este caso 1/12, y multiplicar el resultado por C1.

Esta fórmula solo es aceptable para la primera celda de la columna.

Las restantes celdas no funcionan así. Si copiamos la celda C8 en C9, vemos que el resultado es cero. Esto es así porque al interés equivalente le multiplicamos el valor de la celda C2 que en estos momentos es cero pero que puede contener cualquier valor numérico. Tenemos que modificar la fórmula escrita en C9.

En realidad, solo tenemos que modificar en la fórmula la referencia a la celda C2 por la celda F8. Si copiamos ahora en toda la columna, el valor obtenido es el que veníamos buscando.

Solo nos resta calcular la cuota: suma de interés más amortizado mensual.

En la celda B8 escribimos “=C8+D8”, copiamos al resto de la columna y ya está. Terminada la tabla.

Para terminar el ejercicio, introducimos a modo de información el número de años de este crédito y en la columna E2 escribimos la

sencilla fórmula “=C2*12” que calcula el numero de periodos (meses) en función de los años introducidos en la celda C2.

Este de la figura es el aspecto de la hoja terminada.

Ni que decir tiene que modificando el valor del interés aplicado en la celda C3 o del capital restado en la celda C1 o las dos variables al tiempo, los resultados se modifican dinámicamente.

Pasemos al segundo caso: amortización constante todos los periodos-

En este caso, se trata de devolver en cada periodo la misma cantidad de principal. Para ello, lo que hacemos es dividir el total entre el número de periodos a pagar.

Esta vez vamos a introducir los valores de la cabecera en primer lugar. Introducimos el principal: 10.000 €, el número de años y el tipo de interés anual. Para el cálculo del número de periodos, que lo tenemos calculado en la hoja anterior, lo copiamos. Para ello escribimos en la

celda el signo igual y a continuación con el ratón

seleccionamos la hoja donde se encuentra el dato. Una vez en esa hoja, pulsamos sobre la celda que necesitamos. La fórmula que nos queda es como la que aparece en la figura.

Pasamos a calcular el importe a amortizar cada mes o periodo: seleccionamos la celda D8 y escribimos la fórmula: “=$C$1/$E$2”.

Con ella estamos indicando que en queremos de forma absoluta que se divida el valor de la celda C1 entre el valor de la celda E2 y que esto sea así si se copia en otras celdas.

Esto es justo lo que haremos. La formula nos dice que dividamos el principal entre el numero de periodos.

Al copiar en las celdas de la columna, llenamos esta de celdas con el mismo valor.

Para la columna amortizado, se sumará el valor ppal amort de la columna anterior al valor que figura sobre este (ppal amort. Una fila superior), obteniendo el acumulado en cada periodo. En la primera, se suma la amortización del periodo a cero, pues en la fila superior no hay nada. Al copiarlo, el último valor tiene que coincidir con el total principal.

Para la columna remanente, esta vez vamos a modificar el esquema.

Escribimos en la columna F7 la fórmula “=C1”.

A continuación escribimos en F8 la fórmula “=$F$7-E8” y la copiamos. De esta forma queda más “profesional”.

Nos queda calcular los intereses con la fórmula empleada en la hoja anterior y por último la cuota.

El cálculo del interés ya nos es familiar: en la celda C8 escribimos la fórmula y la copiamos a toda la columna.

Por último, calculamos la cuota. Para este cálculo, sumaremos los intereses y lo amortizado por cada periodo. En la celda B8

escribiremos “=C8+D8”. Por último copiamos la celda en toda la columna y ya tenemos nuestra tabla dinámica terminada.

Entramos de lleno a construir la tabla para la amortización por el método francés.

Una vez en la hoja adecuada, vamos a escribir los datos iniciales: Principal (10.000 €), años (1 año) y tasa de interés aplicable (10%).

Como ya sabemos, copiamos en la celda E2 la misma celda pero de cualquiera de las hojas anteriores, o bien escribimos la fórmula “=C2*12”

¿Qué diferencia hay entre hacer una u otra cosa?

Si copiamos la fórmula de hojas anteriores, por ejemplo de la hoja “Cuotas asimétricas” al modificar la celda “Años” de esta hoja, se va a modificar el numero de periodos de la hoja “Amortización Constante” y de la hoja “Método Francés”. Notemos que en la hoja “Amortización Constante”, si ahora modificamos el número de años en la celda C2, no se modifica el número de periodos de la misma hoja. Esto sucede porque el resultado de esta celda, está relacionado con la celda de la hoja anterior, no con la celda C2 de su propia hoja.

Si escribimos la fórmula relacionada con la C2 de la misma hoja, como es en el caso de la hoja “Método Francés”, relacionamos el número de periodos de la hoja con el número de años introducido en la celda de esa hoja. Este resultado no está vinculado en absoluto con el resultado obtenido en hojas anteriores.

Quizás nos convenga estudiar que pasa al cambiar una de las variables y como afecta a los tres métodos de forma automática.

Lo más eficiente sería escribir las variables una sola vez, y que sirvan para todas las hojas al mismo tiempo. Que las tres hojas queden relacionadas.

Para esto, debemos realizar algunas modificaciones.

Vamos a seleccionar las tres hojas a la vez tal como hicimos al comienzo, cuando diseñábamos el aspecto de las tablas.

Recordemos que se pulsa la tecla Control del teclado y sin dejar de pulsar, se selecciona con el ratón una pestaña tras otra hasta que queden todas seleccionadas.

Nos situamos en la celda E2 y escribimos la fórmula “=C2*12”. En estos momentos hemos escrito la fórmula en las tres celdas a la vez. Pero referida cada una a su propia hoja.

Seleccionamos ahora las dos hojas finales y nos situamos en la celda C2. Escribimos en esta celda la fórmula “='Cuotas Asimétricas'!C2”. para hacer esto de una forma rápida y evitar errores, lo que hacemos es una vez seleccionada la celda, escribimos el signo “=” y con el ratón, pulsamos en la pestaña de la hoja “Cuotas asimétricas” y una vez en ella, pulsamos sobre la celda C2 de esa hoja. Aceptamos la fórmula y el resultado es que la fórmula se ha escrito sola en las dos celdas que pretendíamos.

A partir de este momento, con cambiar el número de años de la primera hoja, habremos cambiado automáticamente los datos de todas las hojas a la vez. Ya están vinculadas y listas para hacer comparaciones.

Volvamos al método francés.

Excel tiene ya función que calcula el valor de una renta periódica, sea cual sea el número de periodos, es decir, de años.

Tenemos funciones para calcular cada una de las columnas implicadas y las vamos a utilizar.

Es importante saber el número de periodos y cual es el periodo que vamos a tratar en cada cálculo. Por eso, teníamos preparada la columna periodo desde un principio.

Calculemos la cuota en la celda B8, que como sabemos es de un valor constante todos los periodos, es decir en todas las celdas de la columna.

Para ello, desde el menú insertar en Excel 2000/2003 pulsamos sobre la entrada “Función”. Desde Excel 2007, pulsamos en el menú funciones y desde allí pulsamos en insertar Función.

En cualquiera de los casos, se abre un cuadro de diálogo asistente de funciones que es similar en todas las versiones. Podemos ver que en la parte

superior se nos permite describir brevemente que deseamos: buscar, sumar, percentil, pago,… y el asistente nos envía a la función que cree que nos interesa. Debajo, una nueva opción es seleccionar una categoría y la escogemos del

desplegable. Nosotros vamos a necesitar la categoría “Financieras” y en el cuadro de lista inferior, elegiremos la apropiada. Par esta columna que vamos a construir, necesitamos la función “Pago”.

Lo seleccionamos y pulsamos el botón Aceptar.

Aparece un nuevo cuadro en el asistente que nos va a permitir introducir todos los datos necesarios para construir la fórmula.

Vemos que podemos introducir el dato directamente o bien utilizar el botón de la derecha del cuadro de texto que nos envía a la propia hoja para seleccionar el dato con el ratón. Utilizaremos este método.

Como nos estaba solicitando la Tasa (interés) y este lo tenemos en la celda C3, arrastramos el cuadro Argumentos de función si nos ha quedado encima de la celda que necesitamos y a

continuación la seleccionamos. Automáticamente se introduce la referencia a la celda en el cuadro. Pulsamos en el botón derecho del mismo y regresamos al asistente. En el cuadro de texto de Tasa aparece ahora C3, que es la celda que contiene el dato. Pero el dato estaba referido al interés anual, y nosotros estamos trabajando en periodos de un mes, luego dividiremos entre 12, quedando la entrada como sigue: “=C2/12”. Además, como sabemos que esta fórmula la vamos a copiar a otras celdas de la columna, y que la tasa va a estar siempre en la misma celda, hacemos de este dato una referencia absoluta. Editamos la entrada pulsando con el ratón sobre “C3” y cuando el cursor se transforma en una barra vertical, pulsamos F4. la entrada quedará por fin “=$C$2/12”.

Pasamos al siguiente dato: Nper se refiere al número de periodos con los que vamos a trabajar. Este dato lo tenemos en la celda E2 y volvemos a hacer lo mismo: se pulsa en el botón derecho del cuadro de texto Nper y cuando nos lleve a la hoja, seleccionamos con el ratón la celda E2. Se vuelve a pulsar el botón de la derecha y ya estamos en el asistente con el dato en su correspondiente lugar. También este dato va a permanecer en esa celda sea donde sea que copiemos la fórmula. Luego pulsamos F4 en el teclado y hemos convertido la referencia en absoluta.

Pasamos al tercer argumento Va (valor actual): se refiere al montante del principal del préstamo. Este valor se encuentra en la celda C1 y permanece invariable durante la vida del préstamo. Realizamos de nuevo la operación hasta conseguir que el valor se encuentre en la casilla correspondiente.

La siguiente casilla Vf es el valor final que por defecto es cero, luego no lo ponemos y tipo hace referencia a

cómo vamos a realizar los pagos, si al principio del periodo o al final. El método francés realiza el pago al final, que es el tipo por defecto y tampoco escribimos nada. Ya tenemos todos los parámetros en su lugar y es el momento de pulsar el botón Aceptar. El valor se ha introducido en la celda de la tabla. Vemos que es un valor negativo. Para hacerlo positivo, editamos la fórmula y ponemos un signo menos delante de la palabra Pago.

Copiamos la celda a toda la columna y vemos el primer resultado en la tabla.

Para hacer la tabla elegante, vamos a copiar el valor de la celda C1 en la celda F7, justo debajo de remanente. Permitirá automatizar mejor la tabla.

Ahora vamos a calcular los intereses. Para ello, como hicimos anteriormente, seleccionamos la celda C8, abrimos el asistente de funciones y buscamos la función PAGOINT; pulsamos Aceptar y continuamos con el siguiente asistente

De nuevo se pide como argumento la Tasa y como antes la introducimos teniendo en cuenta que hay que transformarla en mensual.

A continuación se solicita el periodo. En esta ocasión, a cada pago le corresponde un periodo. A pagos consecutivos, periodos consecutivos. El número de periodo se encuentra en la columna periodo y a esas celdas hacemos referencia. Para la celda C8 en la que nos encontramos, el periodo se encuentra en la celda A8 y es la que introducimos. En este caso, la referencia es relativa. Para la celda C9, el periodo a introducir sería A9, y así sucesivamente…

Tercer argumento Nper, como antes, lo seleccionamos de la celda E2 y con referencia absoluta por lo ya explicado.

Cuarto argumento: Valor actual. En este caso, el valor actual para el cálculo de intereses, corresponde al remanente que va quedando a medida que vamos realizando los pagos. De forma que cada periodo va quedando menos. Para la celda donde nos encontramos, este valor se encuentra en la celda F7 y la referencia será relativa. Finalizada la introducción de parámetros,

pulsamos Aceptar y ya tenemos el valor del interés de ese periodo en su celda.

Como anteriormente, vemos que el valor resultante también es negativo. Para nuestro propósito, colocamos un signo negativo al principio de la formula, delante de PAGOINT.

Antes de copiar la celda en toda la columna, por razones obvias (no tenemos datos en la columna remanente) vamos a fabricar la fórmula para ppal amort.

Los pasos son los mismos. Ahora seleccionamos la celda C8 y buscamos la función PAGOPRIN en el asistente de funciones.

Los argumentos a introducir en esta ocasión son idénticos a los de

PAGOINT… pues hacemos lo mismo.

Ya podemos ir comprobando que la cosa pinta bien. Si sumamos el valor de los intereses del periodo y el de ppal amort, da justamente el valor de la cuota, que es lo que se esperaba.

Pero continuemos con las demás columnas.

Amortizado, como ya sabemos, es la parte del principal que ya hemos pagado. En esta columna, sumamos lo amortizado en este periodo con lo amortizado anteriormente.

Para la celda E8, la formula será “=D8+E7”. Será relativa y nos da

el valor de D8, pues E7 está vacía y es cero.

Por último, calculamos la celda remanente. En este caso, el cálculo es restar del principal total el amortizado acumulado de la columna anterior, es decir: “=$F$7-E8”.

Para terminar la tabla, seleccionamos el rango C8:F8 y arrastrando la selección copiamos todo en las filas inferiores.

Con esto hemos visto tres formas diferentes de hacer frente a un préstamo. Esto no lo vamos a decidir nosotros casi nunca, pero por lo menos, tendremos una idea de lo que nos aplican y de qué manera.

Para hacer distintas comparaciones, podemos modificar como hemos dicho anteriormente el principal y/o en interés en la primera hoja y automáticamente se modifican los valores de las tres, permitiendo así la comparación ante diferentes alternativas.