La hoja de cálculo Excel contiene una serie de funciones de cálculo que nos pueden ser útiles para confeccionar pequeños programas para la oficina de farmacia. En esta hoja vamos a confeccionar una hoja de cálculo capaz de simular el coste financiero de un préstamo, así como su cuadro de amortización en función del capital prestado, el tipo de interés aplicado y su duración.
El esquema de la ficha sería el siguiente:
* Diseño de la hoja del coste financiero.
* Estudio de la función pago y similares.
* Diseño de la hoja del cuadro de amortización.
Diseño de la hoja del coste financiero
En primer lugar vamos a rellenar los textos fijos en sus celdas correspondientes:
* A1: CÁLCULO FINANCIERO DE UN PRÉSTAMO
* A2: Capital:
* A3: Interés:
* A4: Plazo (años):
* A5: Número de pagos:
* A6: Cuota mensual:
* A8: Total pagado:
* A9: Coste financiero:
A continuación introduciremos las siguientes fórmulas:
* B5: =12*B4
* B6: =PAGO(B3/12;B4*12;-B2;0;0)
* B8: =B4*12*B6
* B9: =B8-B2
* C5: =12*C4
* C6: =PAGO(C3/12;C4*12;-C2;0;0)
* C8: =C4*12*C6
* C9: =C8-C2
* D5: =12*D4
* D6: =PAGO(D3/12;D4*12;-D2;0;0)
* D8: =D4*12*D6
* D9: =D8-D2
Finalmente, dejaremos en blanco las celdas de introducción de datos y las restantes las colorearemos de amarillo mediante la secuencia «FORMATO», «CELDAS...», pestaña «TRAMAS». Por otro lado, actuaremos sobre la pestaña «BORDES» para dejar la hoja con el aspecto de la figura 1.
Figura 1
Funcionamiento de la hoja
La hoja diseñada nos permite simular simultáneamente tres préstamos en condiciones distintas. En las celdas de la fila 2 introduciremos el capital o importe del préstamo; en las de la fila 3, el tipo de interés, y en la 4, la duración del préstamo expresada en años. En la fila 5 se obtiene el número de plazos; en la 6, la cuota a pagar mensualmente; en la 8, el total pagado en el momento de cancelar el préstamo, y en la 9, el total de los intereses.
Estudio de la función pago y similares
Vamos a aprovechar el manejo de la función «PAGO», que calcula el pago de un préstamo basado en pagos e intereses constantes, para explicarla con más detenimiento.
La pantalla de la función se muestra en la figura 2; en ella deberemos introducir cinco conceptos:
Figura 2
* TASA: es el tipo de interés del préstamo referido al período de pago. Lo más usual es que el pago sea mensual y, por tanto, deberá ir dividido por 12.
* Nper: es el número total de pagos a realizar para cancelar el préstamo referido al mismo período.
* Va: es el principal del préstamo, expresado en negativo.
* Vf: es el valor del principal del préstamo cuando se haya realizado el último pago. Si no consta ningún valor se asumirá que es 0, es decir, que el préstamo estará completamente pagado.
* Tipo: indica si el pago se realiza al principio o al final del plazo. El valor 0 que se asume por defecto indica que los pagos se realizan al final del período, mientras que el valor 1 indica que se realizan al principio.
En las ventanas correspondientes se pueden introducir directamente los valores o, si pulsamos el icono situado a la derecha de la ventana, podremos señalar las celdas donde se encuentran los datos, cosa que da mucho más interactividad a la hoja.
Veamos alguna otra posible aplicación de esta función:
* Cálculo de los pagos de un préstamo del mismo capital, plazo e interés que el que hemos visto, pero con los pagos efectuados al principio del período. En «tipo» colocaremos un 1.
* Cálculo de la cantidad a recibir por un préstamo efectuado por nosotros. En este caso Va se expresará en positivo.
Existen otras fórmulas parecidas que calculan distintas variables. Entre ellas podemos citar las siguientes:
* PAGOINT(tasa;período;Nper;Va;Vf;Tipo). Nos calcula el importe del interés y la utilizaremos más adelante para realizar el cuadro de amortización de un préstamo.
* PAGOPRIN(tasa;período;Nper;va,vf,tipo). Calcula el pago del capital de una inversión.
* TASA(Nper;pago;Va;Vf;Tipo;estimar). Calcula el tipo de interés por período de un préstamo o una anualidad.
Para ver más detalles del funcionamiento de estas fórmulas, es aconsejable consultar la ayuda de Excel.
Diseño de la hoja del cuadro de amortización
Una vez decididas las características del préstamo, podemos, de un modo relativamente fácil, elaborar un cuadro de amortización que constará de 5 columnas y que podemos colocar en la misma hoja a partir de la columna H.
Los encabezamientos de las columnas se colocarán en las siguientes celdas de la primera fila:
* H1: Plazo
* I1: Vencimiento
* J1: Capital amortizado
* K1: Cuota
* L1: Capital pendiente
Las fórmulas de la segunda fila serán:
* H2: 1
* I2: =PAGOINT(B3/12;1;12;-B2)
* J2: =K2-I2
* K2: =$B$6
* L2: =B2-J2
Las de la tercera fila serán:
* H3: =H2+1
* I3: =PAGOINT(B$3/12;1;12;-L2)
* J3: =K3-I3
* K3: =$B$6
* L3: =L2-J3
A continuación copiaremos la tercera fila y la pegaremos en las filas siguientes tantas veces como sea necesario hasta llegar al número total de plazos.
En la fila del último plazo, a la que llamaremos «n», el valor del capital pendiente será lógicamente 0.
En la fila siguiente (n+1) escribiremos lo siguiente:
* Hn+1: Total:
* In+1: =SUMA(I2:In)
* Jn+1: =SUMA(J2:Jn)
* Kn+1: =SUMA(K2:Kn)
Los valores obtenidos se corresponderán con los que hemos calculado en la tabla anterior.
En la figura 3 se muestra el aspecto de la tabla después de darle unos toques de formato. Hay que hacer notar que en la tabla sólo se muestran las primeras y últimas filas, ya que una figura que mostrase los 120 plazos de nuestro ejemplo ocuparía mucho espacio de este artículo.
Figura 3