PRÉSTAMOS FRANCÉS EN EXCEL: CUADRO DE RENTAS.
Una vez vista las distintas Funciones para un Préstamo Frances en capítulos anteriores, vamos a aplicar dichos conocimientos para el desarrollo de un cuadro de rentas para un préstamo hipotecario.
Para su desarrollo partiremos de los siguientes datos iniciales e iremos explicando paso a paso las funciones a colocar en cada una de las filas de nuestra tabla.
El cuadro de Rentas que obtendremos será:
Columna A Número de Cuotas
Utilizaremos la función SI que devuelve un valor si la condición especificada es Verdadero y otro valor si dicho argumento es falso. Su sintaxis es SI (prueba_logica;valor_si_verdadero;valor_si_falso). Se puede emplear hasta siete funciones SI como argumentos.
En la primera celda el argumento sería, si 1 que es el valor del primer período, es menor o igual al producto de la Periodicidad por el Plazo en años (30*12), y este argumento es verdadero, colocar 1, en caso de falso, colocar 0;
En la celda A150 escribiríamos A15=SI(1<=($B$142*$B$147);1;0). Las celdas B142 y B147 correspondientes a los Plazos y Periodicidad han de tener referencia absoluta para que no cambien la referencia a la celda, por eso el signo $.
En la segunda celda el argumento sería, A151=SI(A150=0;0;SI((A150+1)<=(($B$142*$B$147);(A150+1);0)). El valor de la celda A150 puede ser cero o uno. Si es cero, el valor verdadero de la celda A151 sería cero. En caso contrario, el valor falso, sería el valor A150=1. Para este caso, se evalúa con otra función SI, es decir, si la suma de A150+1=2, es menor o igual al producto del Plazo por Periodicidad. Si es menor, valor verdadero, sería A150+1=2. Si no lo es, valor falso, tendríamos como valor en A151=0.
Para las siguiente filas arrastramos la formula. Para cuando no haya valores y no aparezca en las celdas el valor cero, indicaremos en Preferencias que no muestro los valores cero, desmarcamos la casilla.
Columna B Fecha de Vencimientos de las cuotas.
Utilizaremos ahora además de la función SI ya vista, la función FECHA.MES, que devuelve el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada. La sintaxis es FECHA.MES(fecha_inicial;meses). Si meses es valor positivo, corresponde a fecha futura y si es negativo a fecha pasada. Si el argumento fecha_inicial no es valido devolvera el error #¡NUM!.
En la primera celda, B150, haremos referencia absoluta a la fecha de vencimiento de la primera cuota indicada en los datos iniciales, F139. B150=SI($F$139=" ";" ";$F$159). Estamos diciendo que si no hemos indicado fecha en F139 (" " vacío), en B150 no ponemos nada, " ", y en caso contrario, valor falso, es decir que se indique fecha, F139.
Para la segunda celda, B151. La celda B150 puede estar vacía, " ", o tener el valor fecha de vencimiento de F139. Por ello el argumento sería, B151=SI(B150=" "; " ";(FECHA.MES(B150;1)). Es decir, si B150 esta vacía, B151 vacía. En caso contrario, a la fecha de B150 le suma un mes. Para el resto de filas arrastramos esta formula. Pero nos daremos cuenta que salvo que B150 este vacía tendremos valores en todas las celdas incluso más allá del Plazo establecido. Por ello, hay que introducir otro argumento. Este sería que evalúe si en la celda contigua de número de cuotas es cero o no. Me quedaría la formula, B151=SI(A151=0;0;SI(B150=" "; " ";(FECHA.MES(B150;1))). Es decir, si A151 verdadero sería B151 " " vacío, y en caso contrario, la formula que ya habíamos visto. Veremos ahora que más allá del plazo establecido no se indica ningún valor en la celda.
Columna C Cuota Principal óTérmino amortizativo Ak.
En este caso será necesario utilizar la función ESERR cuya sintaxis es ESERR (valor). Es decir, evalúa si el valor es cualquier error salvo #NA (valor no disponible). Por ejemplo, si dividimos por cero, en la celda indicará el error #¡DIV/0!, y por tanto, la función aplicará a la celda el valor vacío, " ".
El argumento a utilizar en la celda C150 sería en primer lugar evaluar si en la celda A150, número de cuotas hay o no valor. Si no lo hay, en la función PAGOPRIN=(Tasa;período;nper;va;vf;tipo) tendré un error, y por ello he de utilizar ESERR para que indique valor vacío. En caso de que no se produzca el error porque en la celda A150 se indique el número de período, en este caso, 1, entonces, se aplica la función PAGOPRIN. La función sería:
C150=SI(A150="";"";SI(ESERR(PAGOPRIN($B$141/$B$147;A150;$B$142*$B$147;$B$139-$B$140;$B$146;$B$145));0;(PAGOPRIN($B$141/$B$147;A150;$B$142*$B$147;$B$139-$B$140;$B$146;$B$145)))).
Para el resto de la columna arrastramos la celda C15.
Columna D Intereses
El argumento sería el mismo que el anterior y lo único que se modifica es la función ya que ahora utilizaremos PAGOINT=(Tasa;período;nper;va;vf;tipo).
Celda D150 valor:
D150=SI(A150="";"";SI(ESERR(PAGOINT($B$141/$B$147;A150;$B$142*$B$147;$B$139-$B$140;$B$146;$B$145));0;(PAGOINT($B$141/$B$147;A150;$B$142*$B$147;$B$139-$B$140;$B$146;$B$145)))).
Para el resto de la columna, arrastramos la celda D150.
Columna E Cuota o término de la renta a.
Lo primero sería evaluar si la celda A150, número de cuota, es cero o no. Si lo es, valor verdadero, cero. En caso contrario, valor falso, utilizamos la función PAGO(Tasa;nper;va;vf;tipo). Por tanto quedaría la formula:
E150=SI(A150="";"";PAGO($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;$B$146;$B$145)).
Arrastramos para el resto de celdas. Identico resultado hubiéramos obtenido sumando C150+D150, capital más intereses.
Columna F Principal Pendiente ó Capital Vivo Ck
Calcularemos el capital pendiente de amortizar. Por tanto, compararemos el importe del préstamo, B139-B140 (55.000-10.000) con la cuota amortizada de la columna C. Si solo existe un período la suma del importe del préstamo (valor positivo) con la cuota amortizada (valor negativo) sería cero, por tanto en F150 iría valor vacío. En caso contrario, el valor sería el resultado del Principal menos lo amortizado. La formula sería:
F150=SI(A150="";"";SI(($B$139-$B$140)+SUMA($C$150:C150)<=0;"";($B$139-$B$140)+SUMA($C$150:C150)))
Indicamos SUMA($C$150:C150) para ir acumulando las cuotas que restaremos al Importe del Préstamo. Para verlo más claro, en F151 iría SUMA($C$150:C151), En F152 SUMA($C$150:C152)....etc.
Arrastramos para el resto de celdas. Puede surgir diferencias pequeñas de decimales si tenéis marcada en Preferencias la Precisión de Pantalla para el calculo de décimales. Utilizaremos para el cálculo los 15 decimales de excel y desmarcamos la opción de Precisión de Pantalla.
Columna G Comisión de Cancelación
La comisión se calculará en base al Capital Pendiente y por tanto tenemos que hacer referencia a la Columna F.
El argumento de la función a introducir en la celda G15 sería G15=SI(F15=" ";" ";F15*$B$6). Arrastramos al resto de celdas para tener el calculo.
Columna H Coste de Cancelación
Si la celda F Capital pendiente es vacía (" ") , la G, Comisión de cancelación también lo será, con lo que su suma dará el valor de error #!VALOR!. Si este se produce, es decir, es verdadero, habrá que dejar la celda H vacía. Para ello utilizamos la función ESERROR, que devuelve como VERDADERO si Valor es cualquier valor de error ( #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #NULO).
El argumento en la celda G150= SI(F150=" ";" ";F150*$B$144)). Para el resto de celdas,arrastramos G150.
Con todo lo comentado ya tendremos nuestra plantilla construida y que nos permitirá cambiando los datos iniciales hacer distintos supuestos.
Os adjunto el archivo creado con las indicaciones realizadas. Se ha creado como Excel 2004-2007 para que no haya problemas al abrirlo con las versiones:
Plantilla Préstamo Hipotecario
INFORME ADICIONAL
Obtenida la plantilla vamos a obtener para los períodos inicial, intermedio y final los intereses y capital amortizado acumulado de acuerdo a la siguiente tabla:
Se ha utilizado las funciones:
- PAGO.PRINC.ENTRE(Tasa;nper;va;periodo_inicial;periodo_final;tipo)
- PAGO.INT.ENTRE(Tasa;nper;va;periodo_inicial;periodo_final;tipo)
- INT.EFECTIVO(Int_nominal;num_per_año)
- TASA (nper;pago;va;vf;tipo;estimar)
- B207=PAGO.PRINC.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;B205;B206;$B$145)
- C207=PAGO.PRINC.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;C205;C206;$B$145)
- D207=PAGO.PRINC.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;D205;D206;$B$145)
- B208=PAGO.INT.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;B205;B206;$B$145)
- C208=PAGO.INT.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;C205;C206;$B$145)
- D208=PAGO.INT.ENTRE($B$141/$B$147;$B$142*$B$147;$B$139-$B$140;D205;D206;$B$145)
- B209=TASA(B142*B147;E150;B139-B140;B146;B145)
- B210=INT.EFECTIVO(B141;B147)
- B211=(B139-B140)*B143
En la práctica, la Tasa de Interés esta referenciada a un indicador, Euribor por regla general, con un diferencial, con revisiones anuales normalmente, aunque también es habitual utilizar la revisión semestral e incluso trimestral. Por tanto, vamos a elaborar esta misma plantilla en los próximos capítulos pero teniendo en cuenta que anualmente va a ver un cambio en la tasa de interés.
Imprimir artículo
Si te gustó el artículo puedes agregar mi blog en tu iGoogle o Yahoo, Gracias.
Comparte esta entrada
Inicio
Autor
Excel
Gestión
Operaciones Financieras
Presupuestos
Costes
Cuadros de Mando
Análisis Económico-Financiero
Reporting
Planes de Negocios
Físcal
Contactar
Contactar
Suscripción
Artículos
Escribe tu comentario
Publicar un comentario
Tu comentario es IMPORTANTE, son bienvenidos y serán todos respondidos:
1.-Por favor, expresate lo más claro y correcto que puedas, así será todo mucho más fácil. Recuerda puedes contactar conmigo para tratar cualquier duda.
2.-No se permiten descalificaciones y ni faltas de respeto a ningún usuario. Estos comentarios serán automáticamente borrados.
Gracias por vuestra comprensión.