Hojas de cálculo en Excel - página principal

Evolución de un capital a interés simple y a interés compuesto

Si trabajas en el ámbito financiero, seguramente te habrás preguntado alguna vez, como evoluciona un capital durante X años, si éste se retribuye a interés simple, o si se retribuye a interés compuesto. La diferencia entre ambas opciones, reside en que en la capitalización compuesta, se retribuye no solo el capital, sino también los intereses generados.

Vamos a verlo con un sencillo ejemplo. Supongamos que tenemos un depósito con 10.000 euros, y el banco A nos lo retribuye al 5% anual, a interés simple, y el banco B nos lo retribuye también al 5%, pero a interés compuesto anual. Supongamos también que esta imposición la mantendremos durante 8 años. ¿Qué opción es económicamente más rentable?.

El banco A nos da anualmente el 5% de 10.000 euros, a interés simple, es decir:

Año 1: 10.000 x 5% = 500 euros
Año 2: 10.000 x 5% = 500 euros
Año 3: 10.000 x 5% = 500 euros
Año 4: 10.000 x 5% = 500 euros
Año 5: 10.000 x 5% = 500 euros
Año 6: 10.000 x 5% = 500 euros
Año 7: 10.000 x 5% = 500 euros
Año 8: 10.000 x 5% = 500 euros

Al finalizar el octavo año, recuperaremos el capital, más los intereses, es decir:

10.000 + 500 + 500 + 500 + 500 + 500 + 500 + 500 + 500 = 14.000 euros

El banco B nos da anualmente el 5% con capitalización compuesta, es decir, cada año se calcularán los intereses, sobre el capital inicial, más los intereses devengados hasta la fecha:

Año 1: 10.000 x 5% = 500 euros
Año 2: (10.000 + 500) x 5% = 525 euros
Año 3: (10.000 + 500 + 525) x 5% = 551,25 euros
Año 4: (10.000 + 500 + 525 + 551,25) x 5% = 578,81 euros
Año 5: (10.000 + 500 + 525 + 551,25 + 578,81) x 5% = 607,75 euros
Año 6: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75) x 5% = 638,14 euros
Año 7: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14) x 5% = 670,05 euros
Año 8: (10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14 + 670,05) x 5% = 703,55 euros

Al finalizar el octavo año, recuperaremos el capital, más los intereses, es decir:

10.000 + 500 + 525 + 551,25 + 578,81 + 607,75 + 638,14 + 670,05 + 703,55 = 14.774,55 euros

Es decir, una inversión en el banco B (14.774,55 euros), es más rentable que en el banco A (14.000 euros). De hecho, si la inversión en el banco A permaneciera un año más (9 años, en lugar de 8 años), todavía seguiría siendo una inversión menos rentable que la del Banco B, a 8 años, pues obtendríamos 500 euros más de intereses por ese noveno año, pero aún así, no llegaríamos a los 14.774,55, ya que 14.000 + 500 = 14.500 euros.

Las fórmulas que necesitaremos para los cálculos a interés simple, serán las siguientes:


Intereses anuales = capital · tipo de interés
Intereses acumulados = capital · tipo de interés · nº de años
Capital + intereses acumulados = capital · [1 + (tipo de interés · nº de años)]

Las fórmulas que necesitaremos para los cálculos a interés compuesto, serán las siguientes:

Intereses anuales = [capital · (1 + tipo de interés)i] – [capital · (1 + tipo de interés)(i - 1)]
Intereses acumulados = [capital · (1 + tipo de interés)i] – capital
Capital + intereses acumulados = capital · (1 + tipo de interés)i

Vamos a hacer una tabla en excel, donde calcularemos la evolución de dos capitales, y sus correspondientes intereses, en función de esas dos modalidades: con capitalización simple, y con capitalización compuesta. Podríamos hacer una tabla con fórmulas, pero no lo haremos así, porque si queremos calcular la evolución de ese capital en 100 años, tendríamos que poner las fórmulas en 100 filas, y si quisiéramos hacerlo con 1000 años, tendríamos que poner las fórmulas en 1000 filas. Lo haremos a través de un macro, y así nos quedará más elegante, pues solo ocuparemos las celdas con datos, estando el resto libres de fórmulas.

Esta será la plantilla que utilizaremos para el cálculo de los capitales:


Y este es el macro que tendremos que incluir en un módulo de VBA:

Sub interes_simple_y_compuesto()
'Ocultamos el procedimiento
Application.ScreenUpdating = False
'Si hay errores, que continúe
On Error Resume Next
'fichamos la celda donde estamos,
'para volver a ella posteriormente

celda_actual = ActiveCell.Address
'Pasamos los datos a variables
capital = Range("D3")
anios = Range("D4")
tipo_de_interes = Range("D5")
'Comenzaremos evaluando si los datos son correctos
If capital <= 0 Or Not IsNumeric(capital) Then datos_incorrectos = True
If anios <= 0 Or Not IsNumeric(anios) Then datos_incorrectos = True
If tipo_de_interes <= 0 Or Not IsNumeric(tipo_de_interes) Then datos_incorrectos = True
If datos_incorrectos = True Then
'Mostramos un mensaje
respuesta = MsgBox("Existen datos incorrectos, por lo que no se puede continuar:" & _
Chr(10) + Chr(10) + "1.- El capital debe ser > 0" & _
Chr(10) + "2.- El nº de años debe ser > 0" & _
Chr(10) + "3.- El tipo de interés anual debe ser > 0", vbExclamation, "Datos incorrectos")
'finalizamos el macro
Exit Sub
End If
'Eliminamos todos los datos que hubiese
'para lo cual nos situamos en B9

Range("B9").Select
'seleccionamos el rango contínuo de datos que encuentre
Range(Selection, Selection.End(xlDown)).Select
'eliminamos las filas
Selection.EntireRow.Delete
'Si los datos introducidos son correctos,
'comenzaremos poniendo los datos

Range("B9").Select
For i = 1 To anios
'ponemos el año
ActiveCell = i
'ponemos el interés simple anual
ActiveCell.Offset(0, 1) = capital * tipo_de_interes
'ponemos el interés simple acumulado
ActiveCell.Offset(0, 2) = capital * tipo_de_interes * i
'ponemos el total acumulado a interés simple
ActiveCell.Offset(0, 3) = capital * (1 + (tipo_de_interes * i))
'ponemos ese dato en negrita
ActiveCell.Offset(0, 3).Font.Bold = True
'ponemos el interés compuesto
ActiveCell.Offset(0, 4) = (capital * (1 + tipo_de_interes) ^ i) - (capital * (1 + tipo_de_interes) ^ (i - 1))
'ponemos el interés compuesto acumulado
ActiveCell.Offset(0, 5) = (capital * (1 + tipo_de_interes) ^ i) - capital
'ponemos el total acumulado a compuesto acumulado
ActiveCell.Offset(0, 6) = (capital * (1 + tipo_de_interes) ^ i)
'ponemos ese dato en negrita
ActiveCell.Offset(0, 6).Font.Bold = True
'ponemos la diferencia entre los tipo_de_intereses anuales
'por utilizar los dos métodos (simple y compuesto)

ActiveCell.Offset(0, 7) = ActiveCell.Offset(0, 6) - ActiveCell.Offset(0, 3)
'bajamos una fila
ActiveCell.Offset(1, 0).Select
Next
'volvemos a la celda donde estábamos al principio
Range(celda_actual).Select
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Si tenéis curiosidad, mirad la evolución de un capital de 10.000 euros a 100 años, tanto a interés simple, como a interés compuesto. El resultado os va a sorprender, ya lo veréis.

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo.



4 comentarios:

Dr. Hugo Ortiz Quiroga dijo...

Te felicito por tu Blog ...si quieres te mando el juego del gato en excel... es muy sencillo pero sirve para la comunidad....mi mail es hortiz.quiroga@gmail.com

Anónimo dijo...

Hola Javier, tengo una consulta. Como puedo desde una hoja de excel a la cual le llame buscador, buscar otra hoja(cada hoja es un cliente distinto). Los datos para buscar dicho cliente seriam codigo, nombre, telefono, direccion o email. Qual quiera de los datos seria una manera distinta de poder buscar la hoja del cliente. Ey dado mucha vueltas a la web para solucionar mi problema e tu blog a sido el mas compreencible. Te puedo facilitar el archivo en question se te puede aclarar mejor. Mi email es datortek@hotmail.com Te agradesco de antemano. Carlos gomes

silvia dijo...

En primer lugar los felicito y agradezco por esta pagina.
Agradeceria su rpta. a esta consulta: cuales serian las modificaciones que deberia hacer para que partiendo del mismo ejemplo por uds ofrecido, tubiera la evolucion mensual a una tasa mensual, sobre la planilla de excel que acompañan a su ejemplo.
muchas gracias

Javier Marco dijo...

Siguiendo el ejemplo que se incluye en el fichero de Excel, en la celda D5, en lugar de poner el interés nominal anual del 5%, tendrías que poner el tipo de interés nominal mensual, es decir el 0,4166% mensual (=5%/12 meses).

Luego solo tendrías que cambiar los rótulos de la fila 8, para que donde pone "año" o "anual", ponga "mes" o "mensual". Evidentemente, deberías cambiar también el texto de las celdas B4 y B5, para poner "Número de meses", en lugar de "Número de años", y "Tipo de interés mensual", en lugar de "Tipo de interés anual".

Saludos.