Excel nos permite realizar cálculos de todo tipo, es uno de los software más potentes y completos que podemos tener. Una de sus principales atractivos es que puedes activar la pestaña Programador con la que multiplicarás las posibilidades de este programa.
A través de las Macros podemos automatizar tareas simples y complejas que sean repetitivas. Esto nos permitirá ahorrar una gran cantidad de tiempo. Es cierto que tendrás que saber cómo crear una macro en Excel, pero hoy en día puedes crearlas prácticamente sin saber nada de VBA, que es el lenguaje en el cual se crean.
Puedes simplemente conocer algunos aspectos básicos de VBA para aplicarlos a tus macros. Por ejemplo, con saber como se seleccionan filas, columnas y como funcionan los bucles, ya podrías empezar a crear macros realmente complejas. Esto es debido a que el resto de parámetros o modificaciones los puedes aprender o copiar de las macros que grabes.
Por ejemplo, si quieres saber cómo se formatea una celda (color, formato de número, etc.) podrás grabar una macro y observar qué código realiza esa tarea y usarlo en la macro que quieras. Además Excel cuenta con una amplia comunidad de personas y desarrolladores que comparten sus macros y están dispuestos a ayudarte.
Tabla de contenido
Calcular préstamos a través de una Macro
Una tarea como calcular un préstamo también se puede realizar a través de una macro. Nosotros hemos desarrollado una macro que calcula préstamos simples. Su funcionamiento es bastante sencillo.
Simplemente tendremos que introducir la cantidad, el número de meses que dura el préstamo y el tipo de interés anual al que está sujeto. Luego, se genera un cuadro de amortización automático en el que podremos ver como se articula el préstamo. Además, podremos volver a pulsar en el botón para calcular un nuevo crédito. Es una macro bastante útil si en tu día a día tienes que andar realizando este tipo de cálculos.
Para que veas mejor como funciona, te dejamos un vídeo para que te hagas una idea.
Lo que realmente hace la macro es lo siguiente:
- Se borra el contenido y el formato de las celdas que intervienen en el proceso. Esto lo he hecho para que cada vez que se presione el botón se borre todo lo que había antes.
- Luego creamos 3 cajas para introducir texto (InputBox), que recogerán la cuantía del préstamo, el los meses y el tipo de interés. A estas cajas le he puesto condiciones (tienen que ser números mayores que cero y no pueden ser otros caracteres que no sean números). Y se ha puesto un pequeño en negrita el texto que saldrá en la celda contigua.
- A la cuantía y al tipo de interés le damos formato (moneda y porcentaje)
- Empezamos a crear la tabla de amortización. Cada cabecera de las columnas es formateada con el mismo color de letra y fondo.
- Tratamos a cada columna por separado introduciendo las fórmulas oportunas y creando bucles (For) para que rellenen cada celda.
- Para finalizar, la macro autoajustará las columnas automáticamente para adaptarse a la amplitud de su contenido. También le pasamos la tecla ESC.
En resumidas cuentas, este es el proceso general que se realiza cada vez que se pulsa el botón “Calcular préstamo”. Antes de poner el archivo para descargar y el código de la macro tengo que hacer algunas aclaraciones:
A tener en cuenta…
Hay parte del código como los formatos que podrían ser más breves, ahorrándonos código. Pero hemos decidido introducirlo así para que aquellos que tengan curiosidad y empiecen a crear sus propias macros, sepan y entiendan más o menos como funciona todo de una manera más clara. Obviamente si tienes un mayor grado de conocimiento, podrás reducir estas partes de código y hacer que funcione de manera más eficiente.
La segunda aclaración que quisiera hacer es que al crear una macro hay que tener cuidado con las fórmulas, porque las que se usan en VBA son las inglesas y no las que tengas en tu Excel. Por ejemplo, la fórmula para calcular la cuota del préstamo en Excel sería PAGO(……..) la equivalente en inglés es PMT(……..), su uso es el mismo, pero cambia la nomenclatura. Lo mismo sucede con el resto de fórmulas, así que si te interesa crear tus propias macros ten a mano una tabla con las equivalencias entre unas funciones y otras.
En el siguiente enlace podrás descargar el archivo de Excel con la macro para calcular préstamos y el código utilizado. Si no sabes como ver la macro en un Excel puedes consultar nuestra entrada sobre cómo crear una macro.
Código VBA para calcular préstamos en Excel
A continuación podrás copiar y pegar el código en tu Excel. Podrás utilizarlo tal como está, modificarlo, etc.
Sub prestamo()
Sheets("Hoja1").Select
Range("A1").Select
Range("B6:G1000").Select
Selection.ClearContents
Selection.ClearFormats
Range("B1:B3").Select
Selection.ClearContents
Range("A1").Select
'Creamos un cuado de texto para introducir la cuantía del préstamo
'si introducimos un importe menor que 0 o que no es numérico
'volverá a pedirnos la cuantía
cuantia:
Mensaje = "Introduce la cuantía del préstamo"
Título = "Importe del préstamo"
estandar = 1000
importe = InputBox(Mensaje, titulo, estandar)
If importe <= 0 Then GoTo cuantia
If IsNumeric(importe) = False Then GoTo cuantia
'Ponemos en A1 el texto del campo
Range("A1").Select
Selection.Font.Bold = True
ActiveCell = "Cantidad"
Range("B1").Select
Selection = importe
'Hacemos lo mismo con los meses, creamos un cuadro de texto
'para introducir la cantidad de meses que durará el préstamo
'si el valor es menor que 0 o no es numérico nos mandará introducirlo de nuevo
meses:
Mensaje = "Introduce el número de meses"
Título = "Número de meses"
estandar = 12
meses = InputBox(Mensaje, titulo, estandar)
If meses <= 0 Then GoTo meses
If IsNumeric(meses) = False Then GoTo meses
'Creamos el nombre del campo
Range("A2").Select
Selection.Font.Bold = True
ActiveCell = "Nº de meses"
Range("B2").Select
Selection = meses
'Volvemos a hacer lo mismo con los intereses, creamos un cuadro de texto
'para introducir el tipo de interés que tendrá el préstamo
'si el valor es menor que 0 o no es numérico nos mandará introducirlo de nuevo
interes:
Mensaje = "Introduce el tipo de interés anual"
Título = "Tipo de interés"
estandar = 3
interes = InputBox(Mensaje, titulo, estandar)
If interes <= 0 Then GoTo interes
If IsNumeric(interes) = False Then GoTo interes
'Introducimos el nombre del campo correspondiente
Range("A3").Select
Selection.Font.Bold = True
ActiveCell = "Tipo de interés anual"
Range("B3").Select
Selection = interes / 100
'Damos formato a algunas celdas, concretamete a la del importe y el tipo de interés
formato:
Range("B1").Select
Selection.NumberFormat = "#,##0.00 $"
Range("B3").Select
Selection.NumberFormat = "0.00%"
'Creamos la tabla del préstamo. Lo primero que creamos son los textos de las columnas
'luego creamos los bucles con los cálculos pertinentes
tabla:
'texto columnas
Range("B6").Select
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell = "Mes"
Range("C6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell = "Cuota mensual"
Range("D6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell = "Intereses"
Range("E6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell = "Amortización"
Range("F6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell = "Capital Vivo"
Range("G6").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.Font.Bold = True
ActiveCell = "Capital Amortizado"
'Nº de meses
Range("B7").Select
For contador = 0 To meses
Cells(contador + 7, 2).Select
Set actual = Application.ActiveCell
actual.Value = contador
Next contador
'Cálculo cuota
Range("C8").Select
For contador = 1 To meses
ActiveCell.Formula = "=PMT($B$3/12,$B$2,-$B$1)"
Selection.Copy
Cells(contador + 7, 3).Select
Selection.PasteSpecial
Next contador
'Cálculo intereses
Range("D8").Select
ActiveCell.Formula = "=IPMT($B$3/12,B8,$B$2,-$B$1)"
Selection.Copy
For contador = 1 To meses
Cells(contador + 7, 4).Select
Selection.PasteSpecial
Next contador
'Cálculo de la amortización
Range("E8").Select
ActiveCell.Formula = "=C8-D8"
Selection.Copy
For contador = 1 To meses
Cells(contador + 7, 5).Select
Selection.PasteSpecial
Next contador
'Cálculo del capital vivo
Range("F7").Select
Selection = "=B1"
Range("F8").Select
ActiveCell.Formula = "=F7-E8"
Selection.Copy
For contador = 1 To meses
Cells(contador + 7, 6).Select
Selection.PasteSpecial
Next contador
'Cálculo del capital amortizado
Range("G8").Select
ActiveCell.Formula = "=G7+E8"
Selection.Copy
For contador = 1 To meses
Cells(contador + 7, 7).Select
Selection.PasteSpecial
Next contador
'Autoajuste de columnas
Worksheets("Hoja1").Columns("A:G").AutoFit
'Tecla de escape
SendKeys "{ESC}"
End Sub
Crear el botón que lance la macro
Simplemente copiando el código anterior, tendremos la macro lista para hacerla funcionar seleccionándola desde el botón “Macros” de la pestaña “Programador”, pero no tendremos un botón como el que que se ve en el vídeo para lanzarla. Para crearlo es muy sencillo, haremos lo siguiente:
1. Le damos a Insertar.
2. Tenemos dos opciones. Insertar un control de formulario o un control Activex. Podemos usar ambos pero nos decantaremos por el primero.
3. Una vez seleccionado procederemos a colocarlo sobre nuestra hoja de Excel y se abrirá la siguiente ventana.
4. En esta ventana nos aparecerán las macros que tenemos y seleccionamos aquella que queremos vincular con el botón, en nuestro caso “Préstamo”. Una vez hecho esto, ya podremos lanzar la macro pulsándolo.
Por último, si encuentras algún error en la macro o tienes alguna pregunta no dudes en comentarla. También puedes aportar nuevas sugerencias.
