Calcular la TIR y el VAN

No hace muchas fechas, expliqué el uso de solver, para calcular la TIR (Tasa Interna de Rentabilidad, o Tasa Interna de Retorno) de una operación financiera. Lo hice de la forma más complicada, con la única excusa de mostrar como funciona solver. Quienes hayan estudiado programación lineal, sabrán que esta ulitidad que incorpora excel, nos sirve para optimizar (maximizar o minimizar), utilizando restricciones, sin tener que hacer complejos cálculos matemáticos. Solver es algo así como una miniaplicación, dentro de otra aplicación, en este caso, excel.

Yo soy más dado a utilizar las fórmulas de matemáticas financieras, para determinar la rentabilidad o el coste de una operación, en lugar de utilizar las fórmulas que excel incorpora y que permiten ahorrarnos ese trabajo (aparte de que también nos permiten olvidarnos de las complicadas matemáticas financieras).

Hoy vamos a hacer uso de esas formulitas, para determinar la TIR y el VAN de una operación financiera. Para saber de qué hablamos, cuando digo eso de TIR y VAN, daré unas pinceladas sobre el concepto de esas siglas, sin entrar en tecnicismos.

La TIR es el tipo de interés efectivo de una operación, y se define como el tipo de interés que hace que una serie de flujos monetarios futuros (en diferentes momentos del tiempo) tanto positivos como negativos, hace que el Valor Actual Neto (VAN) sea cero.

"Uffffffff, qué complicado" estarás pensando… ¡Que va!, no lo es en absoluto. Vamos a ilustrar el concepto de TIR con un ejemplo sencillo. Supongamos que os ofrecen un negocio en el que tenéis que invertir hoy 8.000 euros, y que cobraréis las siguientes cantidades:

Al final del primer año: 1.000 euros
Al final del segundo año: 3.000 euros
Al final del tercer año: 5.000 euros

Así a bote pronto, pensaréis que es un negocio rentable, pues pagaremos 8.000 euros, y cobraremos 9.000 euros (la suma de 1.000 + 3.000 + 5.000), pero tened en cuenta que esos cobros se realizan en momentos futuros y no hoy. La cuestión es determinar la rentabilidad de la operación, es decir, determinar el tipo de interés efectivo de la operación por el cual invirtiendo 8.000 euros, obtenemos esos 9.000 euros. La TIR será la "i" de la siguiente ecuación:



No le deis muchas vueltas al denominador de la ecuación anterior, pero si sois muy curiosos, es el término que tiene que ver con la actualización de cada cuota, es decir, con "traer a hoy" esas cuotas futuras que se cobran.

La ecuación anterior, se puede reformular cambiando lo 8.000 euros de lado de la ecuación, y obtendremos esto:



Que no es más que el Valor Actual Neto igual a cero. Es decir, la TIR es el tipo de interés que hace que el VAN sea cero.

Para resolver la TIR, utilizaremos la siguiente fórmula de excel:

=TIR(flujos monetarios;tipo de interés estimado)

Flujos momentarios es el conjunto de valores, tanto positivos como negativos. Si nos fijamos en la última ecuación del ejemplo anterior (la que hace que el VAN = 0), veremos que los 8.000 euros del cobro, aparecen en negativo, y los pagos en positivo. Pues bien, aquí en la ecuación de Excel, deberá aparecer de la misma forma: cobros en negativo, y pagos en positivo.

Tipo de interés estimado es un tipo de interés aproximado, …vamos, un tipo de interés calculado a ojo, aunque puede omitirse, por lo que se estimará que es 0,10, es decir un 10%. Este tipo de interés es el que toma Excel como punto de partida para iniciar los cálculos de la TIR, es decir, un valor sobre el que empezará a pivotar Excel, para calcular la Tasa Interna de Rentabilidad.

Mirad en la barra de fórmulas, de la siguiente imagen, y mirad también como están colocados los valores en la columna D:



Como veis, es muy sencillo calcular la TIR de una operación financiera. Es obvio que todos los periodos (en nuestro ejemplo eran 3 años), deben ser plazos idénticos. No podemos mezclar meses con trimestres, y con años, por ejemplo, porque no obtendremos una TIR real (ni siquiera aproximada).

Podemos calcular el VAN con la siguiente fórmula:

=VNA(TIR;flujos monetarios futuros) + flujos monetarios en el momento actual

La función VNA de excel, solo nos devuelve el valor actual, de una serie de flujos futuros, por eso, si añadimos la inversión actual dentro de los parámetros de la función, estaremos cometiendo un error (como lo he cometido yo, aunque he rectificado, gracias a los comentarios de unos lectores del blog), pues se interpretaría que la inversión efectuada en el momento cero, no es realmente una inversión en ese momento cero, sino en el momento 1 (al finalizar el momento cero).

Mirad la barra de fórmulas de la siguiente imagen (donde veremos que el VAN es cero):



Yo incorrectamente había puesto antes la función VNA creyendo que me devolvería el valor actual neto, de la siguiente forma (es incorrecto poner la función, tal y como se muestra en la barra de fórmulas de la siguiente imagen).



¿Véis ahora por qué soy más dado a utilizar las funciones de matemáticas financieras, que las funciones propias de excel?. Pues simplemente por eso, porque las funciones de excel, a veces no funcionan como creemos que deberían hacerlo (y todo por no leernos la documentación de excel, a través de su ayuda).

Ahora imaginemos que con el mismo ejemplo, el tipo de interés de mercado (o el tipo de interés de una inversión), es del 4%, y deseamos saber qué cantidad deberíamos invertir para obtener esos 1.000 euros dentro de 1 año, los 3.000 euros dentro de 2 años, y los 5.000 euros dentro de 3 años. Pues nada, eso es tan sencillo como hacer esto (mirad la barra de fórmulas de la siguiente imagen):



Como veis, al ser un tipo de interés inferior (un 4% en lugar de un 4,9602%), necesitaremos invertir una cantidad mayor, para obtener esos mismos ingresos de 1.000, 3.000, y 5.000 euros. Eso quiere decir que los 9.000 euros (1.000 + 3.000 + 5.000), que cobraremos en un FUTURO, son 8.180,19 euros a día de HOY (teniendo en cuenta que el tipo de interés es del 4%). Es decir, estaremos obteniendo el Valor Actual de la inversión.

Esto último lo podemos comprobar de la siguiente forma (fijaos en la barra de fórmulas de la siguiente imagen, donde veremos que en E9, figura el tercer término de la primera ecuación que he puesto en este artículo):



Si os fijáis, si sumamos los valores que hay en la columna E, es decir, 961,54 (valor HOY de 1.000 euros a cobrar dentro de 1 AÑO), 2.773,67 (valor HOY de 2.000 euros a cobrar dentro de 2 AÑOS), y 4.444,98 (valor HOY de 5.000 euros a cobrar dentro de 3 AÑOS), obtenemos el valor actual neto (valor hoy de esos flujos futuros), es decir, los 8.180,19 euros.

Si los periodos para los que quieres calcular la TIR, no son uniformes, pásate por este artículo, para saber como calcular la TIR de una inversión, para periodos irregulares.

Calcular la TIR de una inversión, para periodos irregulares

Cuando calculamos la TIR de una inversión, lo normal es que los flujos de caja estén todos referidos a unos mismos periodos de tiempo, es decir, que todos los momentos donde se producen los flujos guardan siempre la misma periodicidad. Por ejemplo, cuando calculamos la TIR de una inversión, lo normal es que los flujos sean siempre iguales, y si se trata de años, los flujos serán anuales, si se trata de meses, los flujos serán, y así sucesivamente.

En el ejemplo siguiente podéis ver que hay una serie de periodos, y una serie de flujos. El primer periodo, corresponde al momento cero, es decir, al momento actual, o momento presente, y corresponde al desembolso inicial (como es la inversión inicial, es decir, un pago, debemos ponerlo en negativo, para distinguirlo de los cobros periódicos que recibiremos). Podemos ver que no nos interesan los periodos de tiempo (no sabemos a la vista de ese pantallazo, si se trata de meses, años, semestres, o cualquier otro periodo), porque se da por hecho que son todos iguales. En la fórmula que podéis ver en el pantallazo siguiente, podéis observar que hemos puesto un 5% como tipo de interés estimado, a partir del cual Excel obtendrá la TIR correcta, aunque si se omite, Excel parte de un tipo de interés del 10%, como interés inicial a partir del cual hará las estimaciones. La TIR obtenida es del 6,7075%, pero no decimos si se trata de una TIR anual, semestral, mensual, etc. ¿Por qué no lo decimos?. Pues porque esa TIR estará expresada en función del periodo. Es decir, si esos periodos 0, 1, 2, 3, 4, 5, y 6, son meses, es decir, pagos y cobros mensuales, la TIR será mensual, y si se trata de años (como es lo más habitual), pues la TIR será anual:


Suponiendo que estamos hablando de la TIR anual, la imagen anterior, podemos hacerla más legible, de la siguiente forma, donde hemos añadido las fechas. Aparte, podéis observar que en la fórmula de la TIR, hemos omitido el tipo de interés inicial a partir del cual Excel hará la estimación de la TIR:


Todo esto, junto con el cálculo del valor actual neto, ya lo habíamos explicado en un artículo donde precisamente hablábamos de cómo calcular la TIR y el VAN. También habíamos visto como calcular la TIR con Solver (una potente herramienta de Excel, para optimización).

Ahora la cuestión es la siguiente: ¿Qué pasaría si en lugar de tener los periodos uniformes, se tratase de periodos con diferente periodicidad?. Vamos a suponer que en lugar de recibir esos flujos de cada el día 10 de cada año (ver imagen anterior), se reciben en las siguientes fechas:


¿Sería correcto utilizar la función TIR, para evaluar la tasa interna de rentabilidad de esta inversión, teniendo en cuenta que los periodos no son homogéneos?. Efectivamente, no. Para ello, deberemos utilizar la función TIR.NO.PER, de la siguiente forma:

=TIR.NO.PER(flujos de caja; fechas; tipo de interés estimado)

Como ocurre con la función TIR, podemos omitir el último argumento, donde se nos solitita el tipo de interés a partir del cual Excel hará las estimaciones. Si lo omitimos, Excel presupone que es el 10%.

En nuestro ejemplo anterior, donde hay periodos que no guardan la misma periodicidad, deberemos introducir esta fórmula para calcular la TIR:

=TIR.NO.PER(D6:D12;C6:C12;5%)

El resultado sería que nuestra TIR es de 10,8779%, como podéis ver en la siguiente imagen:


Si os fijáis en los dos ejemplos que ilustran este artículo, veréis que los flujos son los mismos (idénticas cantidades, tanto en pagos como en cobros), pero no son los mismos los periodos donde se producen. Es por ello, que utilizar correctamente las funciones para calcular la TIR, es básico para hacer correctamente las cosas. Solo hay que controlar si los periodos son uniformes o no, para elegir la función TIR o la función TIR.NO.PER.

Calcular el VAN, para periodos irregulares

En un artículo anterior, ya explicamos como calcular la TIR de una inversión, para periodos de tiempo irregulares o no constantes. Ahora le toca el turno a la VAN (Valor Actual Neto), en el caso de encontrarnos con periodos también irregulares.

Lo más habitual es que nos encontremos con periodos de tiempo siempre constantes, como por ejemplo, cuando invertimos una cantidad X hoy, y cobramos otra cantidad Y cada final de año, durante una cantidad de años determinada. En ese caso, con utilizar la función TIR, tenemos resuelto el tema cómo calcular la tasa interna de rendimiento. Lo mismo nos pasará con el VAN, si utilizamos la función VNA, para calcular el valor actual neto, en el caso de que queramos saber que cantidad deberíamos invertir (supondremos en un principio, que la inversión inicial es cero, para que el valor actual neto sea precisamente el valor actual, es decir, la inversión que tenemos que hacer), cobrando esas cantidades Y cada año, a medida que vamos jugando y probando con diferentes tipos de interés.

Ahora vamos a ver como calcular el valor actual de una inversión, para periodos que no siempre son constantes, sabiendo los cobros futuros, y el tipo de interés, pero sin determinar el valor inicial de la inversión (para obtener ese valor actual, utilizaremos la función VNA.NO.PER). Para ello, utilizaremos un ejemplo, que es la mejor forma de explicar el funcionamiento de una función en Excel. Vamos a determinar cual debería ser la cantidad a invertir hoy (suponemos que hoy es 21 de abril de 2010), en el caso de que recibamos los siguientes cobros, en las fechas en las que se indica en la siguiente tabla, y suponiendo que el tipo de interés efectivo de mercado, sea del 4,50%:



Si nos fijamos bien, lo que queremos determinar es el valor de la celda C4, que es la inversión inicial que tenemos que hacer, para cobrar todos esos importes en las fechas dadas, sabiendo que el tipo de interés es del 4,50%. Si observamos la tabla anterior con detenimiento, veremos que en la columna de la fecha, los periodos de tiempo no son constantes, por lo que para calcular el valor actual, lo apropiado es utilizar la función VNA.NO.PER de la siguiente forma:

=VNA.NO.PER(tipo de interés; flujos de caja; fechas)

Eso sí, antes de utilizar esta función aplicada a nuestro ejemplo, deberemos introducir en la celda C4, un cero, pues no debe quedar en blanco, ya que sino, la función siguiente nos daría error:

=VNA.NO.PER(C12;C4:C9;B4:B9)

De tal forma que obtendríamos algo como esto que nos aparece en la siguiente imagen:



Si nos fijamos lo que acabamos de obtener es el valor actual, es decir, el importe que debemos invertir hoy 21 de abril de 2010, sabiendo que el tipo de interés efectivo es del 4,50%, y sabiendo también que cobraremos o recuperaremos las cantidades indicadas en la tabla y en las fechas indicadas.

Si en lugar de utilizar la función como os ponía en el ejemplo, la cambiamos y ponemos esto (fijaos que cambiamos el rango, empezando desde la fila 5, en lugar de la fila 4), estaríamos haciendo las cosas mal:

=VNA.NO.PER(C12;C5:C9;B5:B9)

Lo que obtendríamos sería el valor actual, peroooooooo a la fecha del 14 de Julio de 2010, y no al 21 de abril de 2010, que es lo que nos interesa evaluar. De ahí que debamos incluir la fecha en la que queremos obtener el valor actual, poniendo cero como fujo de caja (ni cobro, ni pago).

Podemos hacer una comprobación de que necesitaremos invertir esos 35.043,98 euros (pesos, soles, dólares, o la moneda que queráis), al 4,50% de interés efectivo anual, sabiendo que cobraremos en las fechas indicadas, las cantidades que aparecen en las tablas anteriores. Para ello, utilizaremos la función TIR.NO.PER que vimos en otro artículo. Solo tendremos que construir la tabla de la siguiente forma:



Si os fijáis bien, como siempre a la hora de calcular la TIR, hemos incluido la inversión inicial en negativo, para diferenciarla de los cobros (es como si pagáramos la inversión inicial, para cobrarla posteriormente poco a poco, en los plazos indicados).

Solver: cálculo de la TIR

Solver es un complemento de excel, que nos permite resolver diversos problemas matemáticos, como es el caso de la optimización con restricciones, o problemas que requieren de muchas iteraciones, por poner dos ejemplos.

Un ejemplo básico de optimización con restricciones, es por ejemplo calcular las horas óptimas de producción en una empresa, teniendo en cuenta que el tramo horario se tarifica por parte de la compañía eléctrica a diferentes precios (restricciones horarias), y teniendo en cuenta que el número de horas trabajadas por cada empleado, no puede exceder de 8 horas diarias (restricción legal). Podríamos incluir muchas más restricciones, como por ejemplo, las relativas a mantener un nivel de stock máximo, o la de producir como máximo un número X de unidades. Todo esto podríamos resolverlo con excel, a través de solver, un complemento que nos permite ahorrar tiempo y dolores de cabeza.

Un ejemplo relativo al tema de iteraciones, y que nos puede solucionar solver, es por ejemplo cuando tenemos una fórmula en la que cambiando determinada cifra que tenemos en otra celda, obtenemos un resultado, y a base de cambiar esa celda, vamos obteniendo un resultado cada vez más cercano a lo que nosotros buscamos. Podríamos resolverlo introduciendo diferentes valores, e ir ajustando cada vez más, esa cifra, hasta dar con una que nos permita obtener el resultado que buscábamos, o bien podemos utilizar solver, para economizar tiempo, y obtener resultados más fiables.

Vamos a ver el uso de solver con un tema de financiación al consumo, en el que pensé el otro día viendo la publicidad de una cadena de electrodomésticos. Seguramente os habréis fijado que casi todas las cadenas de tiendas, hipermercados, y grandes almacenes nos ofrecen la posibilidad de financiar nuestras compras, en determinados productos. Seguramente habréis visto también, que se ha puesto de moda en algunos sitios, financiar una compra que vale X euros, en 11 pagos de 1 décima parte el valor de X. Vamos a verlo con un ejemplo:

Supongamos que la cadena de tiendas "Electrodomésticos buenos, bonitos y baratos" (una cadena inexistente), nos ofrece un televisor de 42" de plasma, de una reconocidísima marca, y de ultimísima tecnología, a un precio de 800 euros. Imaginemos también que debido a la crisis, nuestros bolsillos carecen de liquidez, y que esa cadena de tiendas nos ofrece pagar el televisor durante 11 meses, a razón de 80 euros mensuales (1/10 parte del precio inicial). Nosotros, ingenuos a más no poder, pensaremos "bueno, no está mal, podemos pagarlo casi en un año, en unas módicas mensualidades". Pero al llegar a casa, hemos decidido que lo mejor será coger nuestro excel, y ver que tipo de interés implícito lleva inherente esa operación, antes de aceptar la propuesta de financiación del vendedor (aunque su obligación es informarnos es todo esto, incluida la TAE, es decir, la Tasa Anual Equivalente, o en palabras que todo el mundo entienda, el tipo de interés efectivo, o el tipo de interés real de la operación).

Vamos a introducir para ello, otro concepto básico: el valor actual (VA). El valor actual, es la suma de diferentes flujos, realizados en distintos periodos de tiempo, pero valorados en un momento dado (normalmente a fecha de hoy). Dicho en palabras más sencillas, y aplicado a nuestro ejemplo, el valor actual neto (VAN), será la suma de esos 80 euros mensuales, que pagamos al cabo del primer mes, en el segundo mes, en el tercer mes, y así, hasta el mes undécimo, pero valorados a día de hoy. Todos sabemos que no es lo mismo cobrar 100 euros hoy, que cobrar esos mismos 100 euros dentro de 20 años. ¿Por qué?. Pues porque esos 100 euros que cobremos dentro de 20 años, no tienen el mismo valor que hoy, …de hecho, esos 100 euros a cobrar dentro de 20 años, pueden ser hoy el equivalente a 5, 8, o 24 euros (no lo he calculado, porque habría que estimar una inflación media para esos 20 años), pero en ningún caso, esos 100 euros dentro de 20 años serían iguales que los 100 euros hoy. Siguiendo con nuestro ejemplo, la cuota nº 11 que pagaremos al final, tenemos que "traerla" al momento presente, es decir, ahora. Lo mismo haremos con el resto de las cuotas.

Sabemos que el valor actual se rige por esta fórmula:


Como sabemos que hoy, el valor del televisor es de 800 euros (valor hoy = valor al contado), el plazo es de 11 mensualidades, y que cada una de ellas es de 80 euros, entonces podemos escribirla así:


Como todos los pagos son iguales (80 euros mensuales), podemos escribir la función genérica, de la siguiente forma:


Y para nuestro ejemplo, y dado que las cuotas no son anuales, sino mensuales, el tipo de interés tendremos que pasarlo de "tipo de interés anual", a "tipo de interés mensual". Quedará así:


Si cambiamos el valor de 800 de lado, obtendremos el Valor Actual Neto (VAN) igual a cero, y es en ese momento donde i se convierte en la TIR (tasa interna de rentabilidad), aunque para nuestro caso, la llamaremos TAE (tasa anual equivalente). Por tanto, la TIR será el tipo de interés implícito de la operación, que hace que el VAN sea cero:


Para resolver esa ecuación, tenemos dos opciones:

a) Probar diferentes valores para "i", y así saber que tipo de interés nos cobra la cadena de tiendas que nos está financiando la compra.

b) Ejecutar solver, introduciendo los parámetros necesarios.

Vamos a ver como solucionar esto con solver. Para ello, en el menú Herramientas, miraremos si nos aparece Solver... por algún lado. Si no fuera así, desde el menú Herramientas, seleccionaremos Complementos..., y marcaremos con una muesca el complemento de solver. A partir de ahora, ya tendremos solver visible desde el menú Herramientas.

Supongamos que tenemos los siguientes datos, en las siguientes celdas:

F4 = 800
F5 = F4/10
F6 = 11
E7 = -F4+((F4/10)*((1+F7/12)^11 - 1)/((F7/12)*(1+(F7/12))^11))
F7 = X

El significado de cada celda es el siguiente:

F4 = Corresponde al valor al contado.
F5 = La cuota que pagaremos: 1/10 parte de la cantidad que aparece en F4.
F6 = El número de meses a financiar la operación.
E7 = En esta celda hemos introducido la fórmula que hace que el VAN sea cero, por tanto, en esta celda deberemos obtener cero.
F7 = Este es el valor a determinar, en nuestro caso, el tipo de interés implícito, es decir, la "i". Para que todo funcione como debe funcionar, lo recomendable es introducir un valor cualquiera, para que a partir de él, Solver ejecute los cálculos. Podemos introducir un 1, un 2, o la cifra que queramos.

Ahora nos vamos a Herramientas, y seleccionamos Solver.... Deberemos introducir en "Celda objetivo:" $E$7 (si lo preferimos, podemos omitir los símbolos del dólar). El valor de la celda objetivo, debe ser cero (VAN = 0), por tanto, en "Valores de:" pondremos 0. Finalmente en "Cambiando las celdas", pondremos $F$7 (aunque podemos omitir los símbolos del dólar).


Ya solo nos quedará darle al botón "Resolver", y se nos presentará esta otra pantalla:


Pulsamos sobre el botón aceptar, una vez hemos seleccionado "Utilizar solución de Solver", y veremos con en la celda F7 nos aparece el tipo de interés anual real, que nos cobrará la cadena de tiendas, por financiarnos el televisor de plasma (un 19,4776%). Veremos algo como lo que muestra la siguiente imagen:


Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo. Debéis tener presente que con independencia del importe que introduzcáis, si el plazo es de 11 mensualidades, y la cuota es 1/10 parte del importe al contado, el tipo de interés efectivo de la operación siempre será el mismo (el 19,4776%), así que ya sabéis, desde el punto de vista del consumidor, una compra financiada en esas condiciones, no es una buena opción, por el elevado coste que supone. En cambio, si tu eres quien vende ese televisor, para ti será una excelente inversión.

33 Utilidades para Microsoft Excel

Hoy os presento un manual en pdf, de lo que creo que podrían ser, las 33 mejores utilidades para Microsoft Excel que he publicado en el blog. Quizás algunos de vosotros no estéis de acuerdo, y penséis que hay otros artículos en el blog que deberían incluirse en el manual. Muy probablemente tengáis razón, pero he escogido esas 33 utilidades, después de hacer muchos descartes.

Este manual con 33 utilidades para Microsoft Excel, no pretende ser un manual de cabecera, pero si un manual de consulta, especialmente ideado para aquellos lectores que quieran aprender las posibilidades de las macros en Excel. No obstante, este manual no solo incluye macros, sino que también podréis encontrar en él, funciones propias de Excel, como la TIR, y el VAN, por poner solo dos ejemplos.

El manual, como todo lo que encontrarás en este humilde blog de Excel, es gratuito y de libre distribución, por lo que puedes imprimirlo, enviárselo a tus amigos, compartirlo, y en definitiva, hacer lo que quieras con el :-)

En muchos de los artículos, podréis comprobar que al final de los mismos, hay un enlace para descargar un fichero con todo lo explicado, para que el usuario no tenga que partir de cero escribiendo el código fuente en Excel. Asimismo, se incluye un enlace a la entrada original de este blog, por si en algún momento el lector quiere acercarse hasta aquí, para ver si he realizado algún cambio o modificación en algún artículo del blog, como ha ocurrido recientemente por ejemplo, en el que explico como obtener datos de una página web.

Estas son las utilidades que he incluido en el pdf:

1. Obtener el nombre del archivo.
2. Obtener el nombre de la hoja.
3. Obtener la ruta, el nombre del fichero, y la hoja.
4. Mi primer macro en Excel.
5. Mi primer UserForm.
6. Introducir datos utilizando un formulario.
7. Modificar datos utilizando un formulario.
8. Mi primer ComboBox.
9. Sacándoles provecho a los ComboBox.
10. Macro al abrir o cerrar un libro.
11. Desproteger una hoja de cálculo.
12. Crear carpetas (o directorios), desde Excel.
13. Poner la hora en una celda.
14. Crear hojas con un clic.
15. Buscar hojas ocultas.
16. Mostrar y ocultar hojas, utilizando macros.
17. Leer una base de datos Access.
18. Simultanear filas de colores.
19. Validación con datos en otra hoja.
20. Validación de listas dependientes.
21. Control horario: horas normales y horas extras.
22. Números aleatorios no repetidos.
23. Préstamos y cálculo de hipotecas.
24. Préstamos según el método americano.
25. Préstamos con amortización de capital constante.
26. Calcular la TAE.
27. Calcular la TIR y el VAN.
28. Evolución de un capital a interés simple e interés compuesto.
29. Calcular la letra del NIF/DNI.
30. Controlar vencimientos de facturas y recibos.
31. Calcular vencimientos.
32. Obtener datos de una página web.
33. Calendarios para imprimir.

Aquí os dejo una imagen de una vista a 4 páginas, para que os hagáis una idea de lo que podéis encontrar en el pdf que podéis descargar más abajo:


Ya no os hago esperar más. Aquí tenéis el manual con las 33 utilidades para Microsoft Excel (cliquead en la imagen para descargar el manual en pdf):

Si te ha gustado este manual en pdf, te agradecería que dejases un comentario.

Calcular la TAE

La TAE es la tasa anual equivalente, o también llamada, tasa anual efectiva. Lo que realmente nos interesa, es saber que significa eso, y como se calcula.

Antes de entrar en materia, comentaros que os dejo una mini aplicación que os permitirá calcular la TAE de una operación financiera, con intereses pagaderos al vencimiento. Tan solo tendréis que informar el tipo de interés nominal, y el plazo de pago de los intereses:



En un artículo anterior ya hablamos sobre el cálculo de la TIR con Solver, así que gran parte de lo que aquí explicaremos, lo podéis ampliar con la información contenida allí, aunque en principio no será necesario. También podéis ver el uso de la TAE en una operación financiera como es el caso de un contrato de préstamo, pues en el artículo donde colgué la plantilla para el cálculo de préstamos, está incorporada esta función.

Vamos a explicar qué es la TAE con un sencillo ejemplo. Imaginemos que disponemos de 5.000 euros, y queremos invertirlos en un depósito a plazo fijo a un año, pero tenemos dos opciones:

• El Banco A nos ofrece un tipo de interés nominal del 7% anual, pagadero al vencimiento.


• El Banco B nos ofrece un tipo de interés nominal del 6,95% anual, pagadero mensualmente.

Así a bote pronto, podemos pensar que la opción que nos plantea el Banco A, es más interesante, porque nos da 350 euros al cabo de ese año (5.000 x 0,07), mientras que el Banco B nos da solamente 347,50 euros (5.000 x 0,0695). Es decir, el Banco A nos da 2,50 euros más de intereses al año (350 – 347,50).

Desde un punto de vista financiero, nuestro objetivo es siempre maximizar el beneficio procedente de una inversión, por lo que hay que analizar la rentabilidad efectiva de ambas operaciones que nos plantean los bancos, antes de decidirnos. Vamos a ver como analizar la rentabilidad efectiva, es decir, obtener la TAE de ambas operaciones.

Si el Banco A nos paga los intereses al vencimiento, lo que está claro, es que solo podremos disponer de ellos a la finalización del contrato que hemos firmado con el banco, es decir, al vencimiento del depósito (al cabo de ese año). El Banco B en cambio, nos paga cada mes los intereses, por lo que podemos disponer de ellos mensualmente. Esto implica que podemos retirar esas cantidades (los intereses mensuales) para gastarlos en lo que queramos, o mejor aún, para invertirlos nuevamente. Es aquí donde entra en juego el concepto de la TAE, pues el propio concepto de la TAE para lleva implícito que vamos a reinvertir esos intereses.

La opción del Banco A es muy clara, pues al cabo de un año, podremos retirar el capital inicialmente invertido (5.000 euros), más los intereses (350 euros). Es decir, al cabo de 1 año, dispondremos de 5.350 euros.

En cambio la opción del Banco B tenemos que analizarla con más detenimiento, pues nos pagan intereses cada mes. Para saber que intereses mensuales obtendremos, tenemos dos opciones:

• Podemos coger el tipo de interés nominal anual, dividirlo entre los 12 meses, y aplicar ese tipo de interés a los 5.000 euros: 0,0695 / 12 x 5.000 = 28,9583 euros de intereses mensuales.


• O también podemos calcularlo, aplicando a los 5.000 euros, el tipo de interés que nos da el Banco B, y dividiendo esos intereses totales, por 12, porque los cobraremos mensualmente: 5.000 x 0,0695 / 12 = 28,9583 euros mensuales.

Como habéis visto, ambas opciones para calcular los intereses mensuales son idénticas, porque matemáticamente son lo mismo.

Pero... ¿qué pasaría si esos intereses de 28,9583 que cobraríamos también al cabo de un mes, y los invirtiéramos al mismo tipo de interés del 6,95%, durante los 11 meses que nos quedan hasta finalizar el contrato del depósito a plazo fijo?. Esta pregunta es básica, y es la que tendríamos que hacernos siempre, para comprender el auténtico significado de la TAE.

Vamos a ver en una tabla de Excel, que pasaría, si los intereses que cobramos mensualmente con la opción del Banco B, los invertimos por el tiempo que reste hasta finalizar ese año, en el que recuperaríamos la inversión inicial de los 5.000 euros.


La columna bajo el título de "Capital invertido" incluye los 5.000 euros invertidos inicialmente, más los intereses que nos van pagando cada mes, y que también se reinvierten por el periodo que resta hasta finalizar la imposición. Por ejemplo, la recuperación del capital, más los intereses que nos pagan el primer mes, los podemos reinvertir durante 11 meses. La recuperación del capital, más los intereses que nos pagan al cabo del segundo mes, los podemos reinvertir durante 10 meses, y así sucesivamente, hasta que al final, cuando cobramos los intereses del mes nº 12, ya no podemos reinvertir nada nuevamente, porque ha finalizado el plazo de tiempo por el que abrimos el depósito a plazo fijo.

También podemos ilustrarlo con este otro ejemplo, donde más claramente se puede observar como reinvertimos los intereses:


Como veis, hemos conseguido 347,50 euros de intereses con la opción del Banco B, y reinvirtiéndolos, nos darían 11,29 euros adicionales, es decir, obtendríamos un total de 358,79 euros, si optimizásemos la inversión (reinvirtiendo los intereses). También hemos calculado la TAE de la operación, que no es más que dividir los intereses totales que obtendríamos (358,79 euros), entre el capital invertido (5.000 euros), porque es a un año, lo que nos da una TAE del 7,1757%.

Llegados a este punto, ya estamos en condiciones de saber que inversión es más rentable desde un punto de vista financiero (optimizando la inversión). El Banco A nos da unos intereses de 350 euros al año, mientras que con la opción del Banco B podemos obtener 358,79 euros.

En el caso del Banco A, el tipo de interés nominal del 7%, es también la TAE, porque no podemos reinvertir los intereses, ya que los obtenemos al final, cuando finaliza el depósito a plazo fijo. En el caso del Banco B, el tipo de interés nominal es del 6,95%, pero la TAE es del 7,1757%, lo cual desde un punto de vista financiero, nos deja las cosas muy claras: el Banco B es la mejor opción.

Como habéis visto, aunque aparentemente el Banco A inicialmente era la mejor opción, hemos demostrado que no es así, y que el Banco B nos ofrecía desde un punto de vista financiero, la mejor opción para maximizar nuestros beneficios (nuestros intereses).

Para calcular la TAE de una operación financiera, os dejo una hoja de cálculo para descargar, que os simplificará mucho la tarea, pues tan solo tenéis que introducir el tipo de interés nominal anual, y los periodos de pago al año (pagos semestrales, pagos trimestrales, pagos mensuales, etc.). Es la plantilla de Excel cuyas imágenes podéis ver al principio de este artículo.

Desde aquí podéis descargar los dos ficheros de Excel, comprimidos en formato zip, con los ejemplos que hemos visto en este artículo. En uno de ellos tenéis la tabla con el estudio de rentabilidad de la opción ofrecida por el Banco B (la tabla de la última imagen de este artículo), y en el otro fichero Excel tenéis la calculadora de la TAE.

Préstamos y cálculo de hipotecas

Antes de entrar en materia, anticiparos que este artículo que estáis comenzando a leer, ocupa ni más ni menos que quince páginas en DIN A-4 (este primer párrafo lo he redactado, una vez tenía escrito todo lo demás), así que espero que tengáis paciencia, tiempo, y un poco de voluntad.

Haré una mínima introducción, para comentaros que ha pasado algo más de un mes desde la última entrada que publiqué en el blog de Excel, y ya era hora de ofreceros a todos los usuarios que seguís fielmente estos artículos, una nueva entrega. En esta ocasión, tocaremos un tema de carácter económico y financiero, que no solo va a serle útil a quien se dedique a estos temas, sino que va a serle útil a todo el mundo. ¿Quién no tiene una hipoteca hoy en día?. ¿Quien no paga un préstamo bancario?. ¿Quién no tiene una deuda porque ha comprado algo a plazos?. Casi todos nos encontramos o nos podemos encontrar en cualquier momento de nuestra vida, en una situación así, ¿verdad?. Pues para todos vosotros, está especialmente indicado este artículo.

A aquellos usuarios a los que no les interesen los macros, y quieran descargarse el libro de Excel para calcular préstamos, e hipotecas, o simplemente quieran hacer simulaciones de préstamos (esta aplicación que os presento, también es un simulador de préstamos, o lo que es lo mismo, una calculadora de préstamos avanzada), pueden saltarse todo lo que explicaré a continuación, e ir directamente al final del artículo, donde encontrarán un enlace para descargar el simulador de préstamos, es decir, el fichero de Excel, con todo lo que veremos aquí. Y a aquellos usuarios que copian y pegan los artículos de este blog, en sus webs o blogs, sin mencionar la fuente, recordarles que la fuente original es http://www.hojasdecalculoexcel.com

Antes de seguir, quiero comentaros que la metodología que se utiliza para el cálculo de préstamos, sigue el método francés. Los que no sepan que es esto del método francés, simplemente daré un par de pinceladas. El cálculo de préstamos según el método francés, se caracteriza por lo siguiente:

• Los intereses se devengan al vencimiento de cada cuota.


• El capital que se amortiza va creciendo en cada cuota, es decir, el principal del préstamo que se va pagando, es cada vez más alto, a medida que va transcurriendo el tiempo, y a medida que vamos liquidando las cuotas.


• Los intereses por el contrario, van disminuyendo y son menores en cada cuota.


• Las cuotas totales que se pagan, son todas del mismo importe. El capital que se va pagando aumenta, y los intereses disminuyen, pero las cuotas son siempre iguales.

Es importante reseñar que no todas las operaciones financieras se rigen por el método francés, como por ejemplo las operaciones de arrendamiento financiero o leasing, que siguen otra metodología distinta, pero a pesar de eso, también es importante indicar que el método francés es el más extendido para el cálculo de la mayoría de operaciones de financiación.

Ahora sí, vamos a entrar en materia. Para calcular préstamos con esta aplicación en Excel, utilizaremos un formulario para la entrada de datos. Antes de eso, crearemos otro formulario donde informaremos de las características del préstamo francés.

Los dos formularios que utilizaremos serán estos:




Aquí os dejo un pantallazo, con un ejemplo de lo que obtendremos con esta aplicación en Excel.



Entrando ya en los macros, veréis que tenemos cuatro. Uno para acceder al menú principal (desde la hoja donde calcularemos el préstamo), otro macro para imprimir, otro macro para hacer una presentación preliminar (como si utilizáramos la lupa), y otro para cargar el formulario con información sobre el préstamo francés (el formulario que vemos en la primera de las imágenes anteriores).

Vamos a ver el código de los cuatro macros, y que tendremos que copiar en un módulo:

Sub menu_principal() 'Si hay errores que continúe On Error Resume Next 'ocultamos el procedimiento Application.ScreenUpdating = False 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos desde la fila 6 hasta el máximo 'que podemos tener, y que ocupa hasta la 'fila número 3021 Rows("6:3021").Select Selection.Delete 'ponemos el ancho estandar de 12,14 en la columna E Columns("E:E").Select Selection.ColumnWidth = 12.14 'ponemos el ancho estandar de 11 en 'las columnas desde la F a la J Columns("F:J").Select Selection.ColumnWidth = 11 'nos situamos en la celda B2 Range("B2").Select 'protegemos la hoja ActiveSheet.Protect 'vamos a la primera hoja Hoja1.Select Range("B10").Select 'mostramos el procedimiento Application.ScreenUpdating = True End Sub

Sub imprimir() 'Si hay errores que continúe On Error Resume Next 'imprimimos la hoja activa ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Sub presentacion_preliminar() 'Si hay errores que continúe On Error Resume Next 'presentación preliminar de la hoja activa ActiveWindow.SelectedSheets.PrintPreview End Sub

Sub prestamo_frances() 'Lanzamos el formulario con info sobre 'el préstamo según el método francés InfoPrestamoFrances.Show End Sub

Ahora dentro del formulario con la información sobre el cálculo de préstamos mediante el método francés, colocaremos los siguientes códigos, uno para cuando cliqueemos en el botón "Si", y otro para cuando cliqueemos en el botón "No" (así precisamente se llaman los CommandButton):

Private Sub Si_Click() 'Si hay errores, que continúe On Error Resume Next 'descargamos el formulario de memoria Unload Me 'llamamos al formulario del préstamo francés 'para rellenar los datos PrestamoFrances.Show End Sub

Private Sub No_Click() 'Si hay errores, que continúe On Error Resume Next 'descargamos el formulario de memoria Unload Me End Sub

A los TextBox y botones del segundo formulario, es decir, del formulario donde rellenaremos los datos del préstamo, les he puesto nombres bien descriptivos. En lugar de llamarlos TextBox1, TextBox2, TextBox3, etc., los he llamado Principal, InteresPrestamo, CuotasAmortizacion, etc., pues así nos será más sencillo saber de qué estamos hablando, cuando leamos el código fuente del formulario.

Este es el segundo formulario que veremos, cuando cliqueemos en el botón "Si", del formulario anterior:


Y todo que viene a continuación, esto será el código que nos encontraremos dentro del formulario (aparte de una pequeña reseña informando que el código es de libre distribución, que está prohibida su venta y su explotación con fines comerciales, y que ha sido obtenido del blog http://www.hojasdecalculoexcel.com). No hace falta que comente para que sirve cada cosa, porque está todo debidamente comentado, y los procedimientos son muy claros. Comenzaremos con el código que nos permitirá controlar los datos introducidos en el formulario:

Private Sub UserForm_Activate() 'Si hay errores, que continúe On Error Resume Next 'al activarse sl formulario, añadimos 'las opciones del desplegable relativos 'a la carencia del préstamo (SI/NO) Carencia.AddItem "SI" Carencia.AddItem "NO" 'bloqueamos por defecto, las opciones de la 'carencia (interés y cuotas), para que no se 'pueda escribir, si no se ha seleccionado en 'el desplegable de carencia (SI/NO) InteresCarencia.Enabled = False CuotasCarencia.Enabled = False End Sub

Private Sub Carencia_Change() 'Si hay errores, que continúe On Error Resume Next 'activamos o desactivamos los TextBox 'relacionados con la carencia del préstamo If Carencia.ListIndex = 0 Then 'si se elige Carencia=SI (el primer valor es cero), 'activamos los restantes TextBox InteresCarencia.Enabled = True CuotasCarencia.Enabled = True Else 'en caso contrario, si se elige Carencia=NO, 'desactivamos los restantes TextBox InteresCarencia = "" CuotasCarencia = "" InteresCarencia.Enabled = False CuotasCarencia.Enabled = False End If End Sub

Private Sub Principal_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor introducido en el principal 'del préstamo es numérico... If IsNumeric(Principal) Then 'y además de ser numérico es menor 'o igual que cero... If Principal <= 0 Then 'eliminamos el dato introducido Principal = Empty Else 'en caso contrario, que le de formato con 'separador de miles y dos decimales Principal = Format(Principal, "#,##0.00") End If 'si no es numérico... Else 'eliminamos el dato introducido Principal = Empty End If End Sub

Private Sub InteresPrestamo_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor introducido en el interés 'del préstamo es numérico... If IsNumeric(InteresPrestamo) Then 'y además de ser numérico es menor o igual 'que 100, y mayor que cero... If InteresPrestamo <= 100 And InteresPrestamo > 0 Then 'que divida el valor entre 100 (para que sea %), y 'que le de formato decimal y con cuatro decimales InteresPrestamo = Format(InteresPrestamo / 100, "##0.0000%") Else 'en caso contrario, eliminamos 'el dato introducido InteresPrestamo = Empty End If 'si no es numérico... Else 'eliminamos el dato introducido InteresPrestamo = Empty End If End Sub

Private Sub CuotasAmortizacion_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor es numérico... If IsNumeric(CuotasAmortizacion) Then 'y además de ser numérico 'es menor o igual que cero... If CuotasAmortizacion <= 0 Then 'eliminamos la entrada CuotasAmortizacion = Empty Else 'en caso contrario, que le de formato con 'separador de miles, siempre y cuando 'sea menor que 1500 If CuotasAmortizacion <= 1500 Then 'si es menor o igual que 1500, le 'damos el formato con separador de miles CuotasAmortizacion = Format(CuotasAmortizacion, "#,##0") Else 'si es mayor que 1500, eliminamos 'el dato introducido CuotasAmortizacion = Empty End If End If 'si no es numérico... Else 'eliminamos el dato introducido CuotasAmortizacion = Empty End If End Sub

Private Sub CuotasAnio_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es numérico, o es menor 'o igual que cero, o mayor que 52... If Not IsNumeric(CuotasAnio) Or CuotasAnio <= 0 Or CuotasAnio > 52 Then 'eliminamos el dato introducido CuotasAnio = Empty End If End Sub

Private Sub Fecha_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es una fecha... If Not IsDate(Fecha) Then 'eliminamos el dato introducido Fecha = Empty 'si es una fecha, que le de formato de fecha Else Fecha = Format(Fecha, "dd-mm-yyyy") 'si la fecha es menor que el 01-01-1900, o mayor 'que el 31-12-3000, borramos el dato introducido '(hay que ponerlo con formato mes-día-año) If Fecha < #1/1/1900# Or Fecha > #12/31/3000# Then 'eliminamos el dato introducido Fecha = Empty End If End If End Sub

Private Sub InteresCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor es numérico... If IsNumeric(InteresCarencia) Then 'y además de ser numérico es menor o 'igual que 100, y mayor que cero... If InteresCarencia <= 100 And InteresCarencia > 0 Then 'que divida el valor entre 100, y 'que le de formato con cuatro decimales InteresCarencia = Format(InteresCarencia / 100, "##0.0000%") Else 'en caso contrario, eliminamos 'el dato introducido InteresCarencia = Empty End If 'si no es numérico Else 'eliminamos el dato introducido InteresCarencia = Empty End If End Sub

Private Sub CuotasCarencia_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) 'Si hay errores, que continúe On Error Resume Next 'si el valor no es numérico, o es menor 'o igual que cero, o mayor que 1500... If Not IsNumeric(CuotasCarencia) Or CuotasCarencia <= 0 Or CuotasCarencia > 1500 Then 'eliminamos el dato introducido CuotasCarencia = Empty Else 'si es menor o igual que 1500, le damos formato CuotasCarencia = Format(CuotasCarencia, "#,##0") End If End Sub

Sub QueEsLaCarencia_Click() 'Si hay errores, que continúe On Error Resume Next 'mostramos un mensaje, informando 'de lo que es la carencia MsgBox (Chr(13) & " La carencia es el periodo de tiempo durante " _ & Chr(13) & " el cual no se amortiza nada del principal del " _ & Chr(13) & " préstamo, pero en cambio, sí que se deven- " _ & Chr(13) & " gan y amortizan intereses. " _ & Chr(13) & Chr(13)), vbOKOnly, " ¿Qué es la carencia?" End Sub

Y ahora el código que se ejecutará cuando cliqueemos en los dos botones del formulario, empezando por el código del botón que nos hará los cálculos, y cuyo código es más extenso, y a continuación con el otro botón cuyo código es muy sencillo, y que nos permite cerrar el formulario:

Private Sub Calcular_Click() 'Si hay errores, que continúe On Error Resume Next 'si hay algún campo vacío, o si se ha seleccionado SI 'en la Carencia, pero faltan el interes y/o las cuotas 'de carencia, que muestre un mensaje If Principal = Empty Or InteresPrestamo = Empty Or CuotasAmortizacion = Empty Or _ CuotasAnio = Empty Or Fecha = Empty Or Carencia.ListIndex = -1 Or _ (Carencia.ListIndex = 0 And (InteresCarencia = Empty Or CuotasCarencia = Empty)) Then 'mostramos el mensaje MsgBox (Chr(13) & " Por favor, revisa el formulario. " _ & Chr(13) & Chr(13) & " Debes completar los datos necesarios, para " _ & Chr(13) & " poder llevar a cabo el análisis del préstamo. " _ & Chr(13) & Chr(13)), vbOKOnly, " Datos incompletos" 'en caso contrario, si todos los datos están completos... Else 'informamos que estamos efectuando 'los cálculos, en el label llamado "Informacion" Informacion = "Calculando..." DoEvents 'ocultamos el proceso Application.ScreenUpdating = False 'seleccionamos la Hoja2 (hoja del préstamo francés) Hoja2.Select 'desprotegemos la hoja ActiveSheet.Unprotect 'eliminamos desde la fila 6 hasta el máximo 'que podemos tener, y que ocupa hasta la 'fila número 3021, por si acaso no hemos 'vuelto al menú principal usando los botones Rows("6:3021").Select Selection.Delete 'escribimos en las celdas, lo que nos 'interesa, en negrita, y de color granate Range("B6").Select ActiveCell = "CÁLCULO DE PRÉSTAMOS (método francés)" Selection.Font.Bold = True Selection.Font.ColorIndex = 9 'ponemos una doble línea Range("B6:F6").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With 'escribimos los títulos del cuadro 'resumen que colocaremos en la parte 'superior de la página Range("B7") = "Principal del préstamo:" Range("F7") = Principal Range("B8") = "Tipo de interés durante la amortización del préstamo:" Range("F8") = InteresPrestamo Range("B9") = "Número de cuotas de amortización:" Range("F9") = CuotasAmortizacion Range("B10") = "Número de cuotas de amortización, al año:" Range("F10") = CuotasAnio Range("B11") = "Número de años hasta la amortización del préstamo:" Range("F11") = Format(CuotasAmortizacion / CuotasAnio, "#,##0.00") Range("B12") = "Fecha del primer pago:" Range("F12") = Fecha 'ponemos la TAE de la amortización, 'alineando el dato a la derecha, pero antes 'miraremos si hay carencia o no, para elegir 'donde escribimos el dato de la TAE. If Carencia.ListIndex = 0 Then Range("J14").Select Else Range("J15").Select End If With Selection .HorizontalAlignment = xlRight End With 'ponemos la TAE de la operación TaePrestamo = (((1 + (CDec(Replace(InteresPrestamo, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100 ActiveCell = "TAE: " & Format(TaePrestamo / 100, "##0.0000%") 'seguimos escribiendo, dependiendo de si 'tenemos o no carencia en el préstamo If Carencia.ListIndex = 0 Then Range("B13") = "Tipo de interés durante la carencia:" Range("F13") = InteresCarencia Range("B14") = "Número de cuotas de carencia:" Range("F14") = CuotasCarencia Range("B15") = "Número de años de carencia:" Range("F15") = Format(CuotasCarencia / CuotasAnio, "#,##0.00") 'ponemos la TAE de la carencia TaeCarencia = (((1 + (CDec(Replace(InteresCarencia, "%", "") / 100) / CuotasAnio)) ^ CuotasAnio) - 1) * 100 Range("J15") = "TAE carencia: " & Format(TaeCarencia / 100, "##0.0000%") With Selection .HorizontalAlignment = xlRight End With End If 'ponemos una doble línea, 'dependiendo de si hay carencia o no If Range("B13") = Empty Then 'si no hay carencia, ponemos la doble línea 'debajo de la fila 12 Range("B12:F12").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With Else 'si hay carencia, ponemos la doble línea 'debajo de la fila 15 Range("B15:F15").Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble End With End If 'alineamos los datos numéricos a la derecha Range("F7:F15").Select With Selection .HorizontalAlignment = xlRight End With 'seguimos escribiendo los encabezados de la tabla Range("B17") = "Cuota nº" Range("C17") = "Concepto" Range("D17") = "Fecha" Range("E17") = "Capital vivo antes del pago de la cuota" Range("F17") = "Capital amortizado" Range("G17") = "Intereses a pagar" Range("H17") = "Capital amortizado acumulado" Range("I17") = "Intereses acumulados" Range("J17") = "Cuota total" 'alineamos los textos básicos a la izquierda (puesto que se 'centran por defecto) al estar toda la columna centrada Range("B6:B15").Select With Selection .HorizontalAlignment = xlGeneral End With 'alineamos los encabezados, vertical y horizontalmente, 'los ajustamos a su celda, y los ponemos en negrita Range("B17:J17").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Font.Bold = True End With 'ponemos valores y fórmulas, empezando 'por numerar las cuotas del préstamo Range("B18").Select 'si no hay carencia (si está vacía), ponemos 'que el nº de cuotas de carencia es cero If CuotasCarencia = "" Then CuotasCarencia = 0 'le quitaremos el separador de miles al nº de 'cuotas de carencia y de amortización del préstamo, 'pues en los textbox aparecen con el separador. 'Como no en todos los países se usa el punto, sino que 'se utiliza la coma, tendremos en cuenta esta circunstancia CuotasCarencia = Replace(CuotasCarencia, ",", "") CuotasCarencia = Replace(CuotasCarencia, ".", "") CuotasAmortizacion = Replace(CuotasAmortizacion, ",", "") CuotasAmortizacion = Replace(CuotasAmortizacion, ".", "") 'pasamos el nº total de cuotas de amortización 'y de carencia a una variable CuotasTotales = CInt(CuotasAmortizacion) + CInt(CuotasCarencia) 'ponemos el nº de las cuotas de amortización y de 'carencia, siempre que CuotasAmortizacion + CuotasCarencia 'sea mayor o igual que 1 If CuotasTotales >= 1 Then For i = 1 To CuotasTotales 'ponemos el nº de la cuota ActiveCell = i 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next End If 'seguimos poniendo los conceptos Range("C18").Select 'si no hay carencia... If CuotasCarencia = 0 Then 'ponemos como concepto "Amortización" 'y debajo, comillas dobles ActiveCell = "Amortización" For i = 1 To CInt(CuotasAmortizacion) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'si hay carencia... Else 'ponemos como concepto "Carencia" ActiveCell = "Carencia" 'ponemos comillas dobles, si las cuotas 'de carencia son mayores que 1 For i = 1 To CInt(CuotasCarencia) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'ponemos debajo como concepto "Amortización" ActiveCell.Offset(1, 0).Select ActiveCell = "Amortización" 'ponemos comillas dobles, si las cuotas 'de amortización son mayores que 1 For i = 1 To CInt(CuotasAmortizacion) - 1 'ponemos el nº de la cuota ActiveCell.Offset(1, 0) = """" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next End If 'recuerda que todo esto ha salido del blog 'http://www.hojasdecalculoexcel.com 'seguimos poniendo las fechas Range("D18").Select 'pasamos los primera fecha a una variable FechaDelPrimerPago = CDate(Range("F12")) Range("D18") = FechaDelPrimerPago 'si las CuotasAmortizacion + CuotasCarencia son 'mayores que 1, seguimos poniendo las fechas If CuotasTotales > 1 Then 'bajamos una fila ActiveCell.Offset(1, 0).Select For i = 1 To CInt(CuotasTotales) - 1 'miramos el nº de cuotas anuales para 'poner la fecha dependiendo de eso Select Case CuotasAnio 'cuotas semanales Case 52 'sumamos 7 días al dato de la celda anterior ActiveCell.Formula = "=R[-1]C+7" 'cuotas mensuales, bimensuales, trimestrales, 'cuatrimestrales, semestrales, o anuales Case 12, 6, 4, 3, 2, 1 'que coincida el día exacto (si es primer 'pago es el día 12, por ejemplo, que cada 'pago coincida con el día 12) ActiveCell.Formula = "=IF(DATE(YEAR(R18C),MONTH(R18C),DAY(R18C))" & _ "=DATE(YEAR(R18C),MONTH(R18C)+1,),DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2])+1,)" & _ ",DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C[2]),MIN(DAY(R18C4),DAY(DATE(YEAR(R[-1]C)," & _ "MONTH(R[-1]C)+(12/R10C[2])+1,)))))" 'si es otro tipo de cuota Case Else ActiveCell.Formula = "=IF(R10C[2]=12,DATE(YEAR(R[-1]C),MONTH(R[-1]C)+(12/R10C6)," & _ "IF(R10C6=12,DAY(R[-1]C))),R[-1]C+INT(365/R10C[2]))" End Select 'bajamos una fila ActiveCell.Offset(1, 0).Select Next End If 'Seguimos poniendo el capital vivo 'antes del pago de la 1ª cuota Range("E18").Select ActiveCell.Formula = "=IF(RC[-3]<R14C6+1,R7C6,R7C6)" 'Seguimos poniendo el capital amortizado 'en la primera cuota Range("F18").Select ActiveCell.Formula = "=IF(RC[-4]<(R14C6+1),0,IF(RC[-4]<=(R9C6+R14C6),RC[4]-RC[1],0))" 'seguimos poniendo los intereses pagados Range("G18").Select ActiveCell.Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]<=(R9C6+R14C6)," & _ "RC[-2]*R8C6/R10C6,0))" 'seguimos poniendo el capital amortizado acumulado Range("H18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo los intereses acumulados Range("I18").Select ActiveCell.Formula = "=IF(RC[-2]<>0,RC[-2],0)" 'seguimos poniendo la cuota total Range("J18").Select ActiveCell.Formula = "=IF(RC[-8]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-(1+(R8C6/R10C6))^-R9C6))" 'seguimos poniendo el resto de datos, es decir 'el capital vivo antes del pago de cada cuota, 'el capital amortizado, los intereses, el capital 'amortizado acumulado, los intereses acumulados, 'y el importe de las cuotas Range("E18").Select For i = 1 To CuotasTotales - 1 'el capital vivo ActiveCell.Offset(1, 0).Formula = "=IF(RC[-3]<=R14C6+1,R7C6,IF(RC[-3]<=R7C6,R[-1]C-R[-1]C[1],0))" 'el capital amortizado ActiveCell.Offset(1, 1).Formula = "=IF(RC[-4]<(R14C6+1),0,IF(RC[-4]<=(R9C6+R14C6),RC[4]-RC[1],0))" 'los intereses pagados ActiveCell.Offset(1, 2).Formula = "=IF(RC[-5]<(R14C6+1),RC[-2]*R13C6/R10C6,IF(RC[-5]" & _ "<=(R9C6+R14C6),RC[-2]*R8C6/R10C6,0))" 'el capital amortizado acumulado ActiveCell.Offset(1, 3).Formula = "=IF(RC[-6]<>0,R[-1]C+RC[-2],0)" 'los intereses acumulados ActiveCell.Offset(1, 4).Formula = "=IF(RC[-7]<>0,R[-1]C+RC[-2],0)" 'la cuota total ActiveCell.Offset(1, 5).Formula = "=IF(RC[-8]<(R14C6+1),RC[-4]+RC[-3],R7C6*(R8C6/R10C6)/(1-" & _ "(1+(R8C6/R10C6))^-R9C6))" 'bajamos a la fila siguiente 'y seguimos con el bucle ActiveCell.Offset(1, 0).Select Next 'ponemos las sumas totales, lo ponemos en negrita 'y le ponemos un nombre a la celda Range("F17").End(xlDown).Offset(1, 0).Select ActiveCell.Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Name = "SumaDelCapitalAmortizado" ActiveCell.Font.Bold = True 'sumamos los intereses a pagar, y ponemos 'el valor de la celda en negrita ActiveCell.Offset(0, 1).Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Offset(0, 1).Font.Bold = True 'sumamos las cuotas totales, y ponemos 'el valor de la celda en negrita ActiveCell.Offset(0, 4).Formula = "=SUM(R[-1]C:R18C)" ActiveCell.Offset(0, 4).Font.Bold = True 'ponemos las tramas alternas, es decir, celdas 'sombreadas y blancas desde B17 hasta el final Range("B17", Range("B17").End(xlDown).End(xlToRight)).Select 'borramos el formato que tengan Selection.FormatConditions.Delete 'añadimos los formatos condicionales 'a los datos de la tabla Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" 'hacemos lo mismo con los totales Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" Range("J17").End(xlDown).Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();2)=0;VERDADERO;FALSO)" Selection.FormatConditions(1).Interior.ColorIndex = 15 Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=SI(RESIDUO(FILA();1)=0;VERDADERO;FALSO)" 'ponemos bordes alrededor de los conceptos Range("B17:J17").Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'seguimos poniendo bordes desde B18 hasta el final 'si solo hay 1 cuota ponemos la fila 19 con bordes If CuotasTotales = 1 Then Range("B18:J18").Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Else 'si hay más de una cuota, ponemos 'todos los datos con bordes Range("B18", Range("B18").End(xlDown).End(xlToRight)).Select With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End If 'le seguimos poniendo bordes a los totales 'del capital amortizado, e intereses a pagar Range(Range("F17").End(xlDown), Range("G17").End(xlDown)).Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'hacemos lo mismo para la suma 'de las cuotas totales Range("J17").End(xlDown).Select With Selection.Borders .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 'configuramos página y ponemos la fila 17 fija, 'por si hay más de una página a imprimir, para 'que nos salgan los encabezados correctamente With ActiveSheet.PageSetup .PrintTitleRows = "$1:$17" .PrintTitleColumns = "" End With 'si la SumaDelCapitalAmortizado no cuadra exactamente hasta 'el segundo decimal, con el principal del préstamo, ponemos 'un mensaje al final de la tabla If Format(Range("SumaDelCapitalAmortizado"), "#,##0.00") <> Format(Range("E18"), "#,##0.00") Then Range("B17").End(xlDown).Offset(3, 0).Select ActiveCell = "Excel provoca un error en el cálculo, a nivel decimal, en la suma total del capital amortizado." 'lo alineamos dándole formato general With Selection .HorizontalAlignment = xlGeneral End With End If 'borramos el nombre de la suma 'total del capital amortizado ActiveWorkbook.Names("SumaDelCapitalAmortizado").Delete 'liberamos memoria Principal = Empty InteresPrestamo = Empty CuotasAmortizacion = Empty CuotasAnio = Empty Fecha = Empty Carencia = Empty InteresCarencia = Empty CuotasCarencia = Empty Informacion = Empty Unload Me 'autoajustamos desde la columna E a la J Columns("E:J").Select Selection.Columns.AutoFit 'nos situamos en la celda B2 Range("B2").Select 'protegemos la hoja ActiveSheet.Protect 'mostramos el proceso Application.ScreenUpdating = True End If End Sub

Private Sub cerrar_Click() 'Descargamos el formulario de memoria Unload Me End Sub

Si os habéis fijado bien (y no os habéis cansado leyendo tanto código fuente), he utilizado fórmulas de matemáticas financieras, omitiendo las funciones propias de Excel, como por ejemplo la función PAGO. Personalmente me gusta más utilizar las fórmulas matemáticas, que estas funciones que lo encapsulan todo, y en las que no se sabe exactamente que es lo que está haciendo la aplicación (bueno, sí se sabe, porque sabemos para que sirven esas funciones, pero el control sobre lo que estamos haciendo, no es el mismo). También os habréis fijado, que he utilizado las fórmulas como si las estuviéramos escribiendo directamente en las celdas de Excel, entrecomillándolas dentro del código, y escribiéndolas en inglés. El secreto de esto último, no es otro que crearlas utilizando la grabadora de macros, ...así no nos equivocaremos.

Otra cuestión que me gustaría remarcar, es que si en el formulario donde entraremos los datos, escogemos que las cuotas del préstamo sean mensuales, bimensuales, trimestrales, cuatrimestrales, semestrales, o anuales, los cálculos se realizarán escogiendo el mismo día de pago para todas las cuotas. Vamos a explicar esto con un ejemplo sencillo. Imaginad que escogemos amortizar el préstamo de forma mensual (12 cuotas al año). Si el primer pago lo realizamos por ejemplo el día 20-01-2010, si le sumamos 30 días, las cuotas siguientes se pagarían en estas fechas:

20/01/2010
19/02/2010
21/03/2010
20/04/2010
...

Como vemos, no coinciden todas el día 20, que es lo normal (lo que nos aplicará la entidad financiera). En el código está contemplado todo esto, y si escogemos alguno de los periodos que os he indicado antes, el día de pago siempre será fijo (en el caso del ejemplo anterior, todos los pagos se realizarían el día 20 de cada mes).

Como veis, ya son varios los artículos sobre temas financieros, los que he ido publicando en el blog. Espero que este tenga tan buena aceptación, como la ha tenido el artículo donde explicaba como calcular la tir y el van. A partir de ahora, ya no tienes excusa, para calcular las cuotas de la hipoteca, la letra del coche, o cualquier otro préstamo, porque esta aplicación se constituye como un excelente simulador de préstamos.

Desde aquí podéis descargar el fichero de Excel, con el ejemplo que hemos visto en este artículo.