PDF de programación - Solución caso Préstamos I

SOLUCIÓN CASO PRÉSTAMOS I



Antes de empezar con la resolución de este caso vamos a analizar las funciones

financieras que vamos a utilizar.

Excel incorpora varias funciones que permiten efectuar cálculos financieros sin
necesidad de construir modelos complicados o tener que utilizar clásicas fórmulas financieras. En
este caso que vamos a resolver utilizaremos una serie de funciones financieras que permiten
calcular los distintos elementos asociados con un préstamo de tipo francés, como por ejemplo el
pago a realizar en cada periodo, el número de periodos en que se amortiza el préstamo, o el tipo
de interés nominal aplicado, entre otros.



Funciones para calcular un préstamo

Las funciones financieras que incorpora Excel para el cálculo de los
distintos elementos de un préstamo, utilizan una serie de argumentos
comunes a todas ellas que definen los distintos elementos asociados a
dicha operación financiera. Estos argumentos son:

• Tasa: es el tipo de interés nominal por periodo.

• Nper: es el número total de periodos de pago.

• Pago: es el importe que se paga en cada periodo. Contiene una parte
correspondiente al principal y otra a intereses.

• Va: es el valor actual que se percibe (o se desembolsa) al principio de la
operación. Se introduce en las fórmulas con signo negativo cuando se
trata de un préstamo.

• Vf: es el valor final o valor futuro que se percibe (o se desembolsa) al
final de la operación.

• Modelo: indica si la operación es postpagable, en cuyo caso Tipo=0, o
si es prepagable, en cuyo caso Tipo=1.

Además, estos argumentos siguen siempre el mismo orden dentro de las
funciones, que es precisamente el que hemos seguido para su definición.

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 1

En el primer apartado, tenemos que calcular el pago mensual a realizar para amortizar un
préstamo hipotecario, mientras que en el segundo apartado se nos pide calcular el pago a realizar
para amortizar el mismo préstamo en el caso de que los pagos se realicen trimestralmente.

Con esto pretendemos poner de manifiesto la diferencia financiera existente entre tres
pagos mensuales y un pago trimestral, diferencia que Excel nos va a explicitar perfectamente si
aplicamos las funciones de forma correcta. Por ello, al introducir funciones financieras en un
modelo, hay que tener mucho cuidado a la hora de seleccionar los argumentos.



1) En este caso vamos a utilizar las funciones PAGO, PAGOPRIN y PAGOINT.


PAGO

=PAGO(Tasa;Nper;Va;Vf;Tipo)

PAGOPRIN y PAGOINT

Estas funciones son una variante de la función PAGO, cuyos
argumento son los mismos que en el caso anterior, pero incluyen, antes
del argumento Nper, el argumento Per que hace relación al periodo
concreto de que se trata, en el primer periodo Per=1, en el segundo
periodo Per=2, etc.

=PAGOPRIN(Tasa;Per;Nper;Va;Vf;Tipo)

=PAGOINT(Tasa;Per;Nper;Va;Vf;Tipo)



El pago mensual será:



Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 2



Para obtener la fórmula de la función PAGO, tenemos dos alternativas, teclearla, o bien
acudir al menú INSERTAR/FUNCIÓN, con lo que aparecerá la pantalla correspondiente al
Asistente para funciones donde seleccionaremos la categoría de funciones financieras y dentro de
éstas la función PAGO:

(En Excel 2007 se accede desde la FICHA: Fórmulas/GRUPO: Biblioteca de

funciones/OPCIÓN: Insertar función)



Podemos ver que Excel muestra los argumentos, con lo cual si no los recordamos los
tenemos aquí. Si pulsamos la opción «Aceptar» pasamos a la segunda pantalla del asistente para
funciones, desde el cuál nos irá pidiendo las celdas para cada uno de los argumentos a la vez que
nos va proporcionando información sobre cada uno de ellos:



A pesar de que la función PAGO tiene cinco argumentos, podemos observar que en este
caso los dos últimos son cero, dado que el valor final de un préstamo siempre será cero y si el
modelo es postpagable el argumento Tipo=0. En cualquiera de estas funciones, cuando alguno de

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 3

los argumentos es cero, puede obviarse de la fórmula, si bien, en el caso de no ser el último habrá
que respetar el orden de los mismos, con lo que habrá que colocar los signos de separación
correspondientes.

Por otra parte, el argumento correspondiente al Valor actual se pondrá con signo
negativo, dado que el valor actual representa lo que tenemos en este momento y en el caso de un
préstamo lo que tenemos es una deuda por el importe concedido.

La función puede quedar:

=PAGO(D6;D8;-D4;0;0)

=PAGO(D6;D8;-D4;;)

=PAGO(D6;D8;-D4)

Dado que son equivalentes elegiremos la tercera ya que es la más breve. En caso de que

el modelo fuese prepagable, las dos opciones válidas serían las siguientes:

=PAGO(D6;D8;-D4;0;1)

=PAGO(D6;D8;-D4;;1)



El modelo en fórmulas quedará (observar que en todas las fórmulas se hace referencia a

celdas):



En cuanto a la tabla de amortización, empezaremos poniendo en una columna los
números correspondientes a los periodos, dado que se trata de pagos mensuales el primer año
estará compuesto por doce periodos, con lo cual, en la columna de periodos (en nuestro caso será
la columna B) tendremos los números 1 a 12. A continuación, en la celda C15 escribiremos la
función PAGO, en la celda D15 la función PAGOPRIN y en la E15 la función PAGOINT:

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 4

=PAGO(D6;D8;-D4)

=PAGOPRIN(D6;C15;D8;-D4)

=PAGOINT(D6;C15;D8;-D4)

Con lo que nos quedará una tabla como esta:



Los valores de las funciones para los restantes periodos, los obtendremos copiando las
funciones del primer periodo, pero hay que tener en cuenta que es lo que va a variar de unos
periodos a otros y que es lo que va a permanecer constante. Así, Tasa, Nper y Va, coincidirán en
todos los periodos, lo que quiere decir que las funciones deben hacer referencia a las mismas
celdas en todos ellos. Por el contrario el argumento Per es variable por propia definición. Por
tanto, en la función PAGO, todos los argumentos deberán presentar referencias absolutas antes de
llenar hacia abajo para copiar la función en los restantes periodos, mientras que en las funciones
PAGOPRIN y PAGOINT, el argumento Per deberá quedar libre de modo que al llenar hacia
abajo, vaya cambiando la referencia y calculándose por tanto, en función del periodo de que se
trate.

Las funciones quedarán:



=PAGO($D$6;$D$8;-$D$4)

=PAGOPRIN($D$6;C15;$D$8;-$D$4)

=PAGOINT($D$6;C15;$D$8;-$D$4)

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 5

Calcularemos además los totales de cada columna para conocer el importe amortizado al

final del año, así como los intereses totales pagados. La tabla quedará:



2) Este caso es muy similar al anterior, con la diferencia de que se trata de pagos

trimestrales. El modelo en fórmulas quedará:



Los resultados:



Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 6

La tabla de amortización para el primer año constará en este caso de cuatro pagos, dado
que estos son trimestrales. Podemos observar que los totales anuales no coinciden exactamente
con los totales obtenidos en el caso de pagos mensuales.



3) En este caso utilizaremos la función NPER.

NPER:



=NPER(Tasa;Pago;Va;Vf;Tipo)



La función Nper nos dará en nuestro caso el número de meses que tardaremos en

amortizar el préstamo. Teniendo en cuenta que el modelo es prepagable será:



Los valores:



***********************************

Margarita Labrador Barrafón – Sistemas Informativos Contables

SOLUCIÓN CASO PRÉSTAMOS I - 7