PDF de programación - Cuadro de amortización con Visual Basic en Microsoft Excel

Autor: Pere Manel Verdugo Zamora



DESCRIPCIÓN BREVE
Cuadro de amortización con Visual Basic en
Microsoft Excel. Este tutorial te guiará paso a para
para poder realizarlo.

Pere Manel Verdugo Zamora



PROYECTO DE
VISUAL BASIC
PARA EXCEL



Página web: http://www.peremanelv.com/pere3/Sitio_web_2/Bienvenida.html



Este tutorial de guiará paso a paso para la creación de un cuadro de amortización, en este caso
será un préstamo personal.

Abriremos un nuevo libro de Excel y diseñaremos la siguiente hoja de cálculo, intenta dejarlo lo
más parecido al siguiente ejemplo, para que el proyecto funcione correctamente.

Seleccionaremos la pestaña “DESARROLLADOR”.

Seleccionaremos “Visual Basic”.

Del menú “Insertar” seleccionaremos “UserForm”.

Realizaremos el siguiente diseño:



Utilizaremos Etiquetas, cajas de texto, cuadros combinados, imagen y botones.

Seleccionaremos de nuevo la hoja de cálculo y dibujaremos un botón.



El siguiente paso será hacer doble clic sobre el botón y escribiremos el siguiente código:



Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

Esto lo que hace es que al seleccionar dicho botón veremos el siguiente formulario:

Regresamos de nuevo a la hoja de cálculo:



Si la casilla D8 la que está al lado de la etiqueta préstamo es mayor de 0, es decir que tiene una
cantidad que calcule la función “PAGO” que consiste en calcular la cuota fija que tendremos que
pagar del préstamo, su sintaxis es:

=PAGO(Interés/Nº de pagos anuales; Nº de pagos totales; Préstamo)

Regresaremos de nuevo a Visual Basic.

Haremos doble clic sobre el formulario sin seleccionar ningún objeto y escribiremos el siguiente
código:



Private Sub UserForm_Activate()

ComboBox1.Clear

ComboBox1.AddItem ("1")

ComboBox1.AddItem ("2")

ComboBox1.AddItem ("3")

ComboBox1.AddItem ("6")

ComboBox1.AddItem ("12")

End Sub

Con este código lo que hacemos es que cada vez que se activa el formulario asignamos los
valores 1, 2, 3, 6 y 12 al cuadro combinado “ComboBox1”.

El siguiente paso será programar el botón “CALCULAR”, haremos doble clic cobre él.



Private Sub CommandButton1_Click()

Dim interes As Double

Dim amortizacion As Single

Dim pagofijo As Single

Cells(8, 4) = TextBox1.Text

Cells(9, 4) = TextBox2.Text

Cells(10, 4) = TextBox3.Text

Cells(11, 4) = TextBox4.Text

Cells(12, 4) = ComboBox1.Text

Cells(17, 8) = TextBox1.Text

I = Val(TextBox4.Text)

interes = Worksheets("Hoja1").Range("D9")

amortizacion = Worksheets("Hoja1").Range("D12")

pagofijo = Worksheets("Hoja1").Range("D13")

For a = 1 To I

Cells(a + 17, 4).Value = a

Cells(a + 17, 5).Value = ((Cells(a + 16, 8).Value * interes * 30 * amortizacion)) / 360

Cells(a + 17, 6).Value = pagofijo

Cells(a + 17, 7).Value = Cells(a + 17, 6).Value + Cells(a + 17, 5).Value

Cells(a + 17, 8).Value = Cells(a + 16, 8).Value + Cells(a + 17, 7).Value

Next

End Sub



El siguiente paso será programa el botón "LIMPIAR” haciendo doble clic sobre él.

Private Sub CommandButton2_Click()



Range("D17:H1000").Select

Selection.ClearContents

Range("D8:D12").Select

Selection.ClearContents

TextBox1.Text = ""

TextBox2.Text = ""

TextBox3.Text = ""

TextBox4.Text = ""

ComboBox1.Text = ""

End Sub

El siguiente paso será programar el botón “SALIR”.

Private Sub CommandButton3_Click()

UserForm1.Hide

End Sub

Para probar el funcionamiento del proyecto vamos a introducir los siguientes valores:



Daremos al botón “CALCULAR”.

Seguido del botón “SALIR”.



Este tiene que ser el resultado:

Interés

Importe a

pagar

Capital

Amortización



1 75,00  €   - 539,48  €  
2 73,06  €   - 539,48  €  
3 71,12  €   - 539,48  €  
4 69,17  €   - 539,48  €  
5 67,21  €   - 539,48  €  
6 65,24  €   - 539,48  €  
7 63,27  €   - 539,48  €  
8 61,28  €   - 539,48  €  
9 59,29  €   - 539,48  €  
10 57,29  €   - 539,48  €  
11 55,28  €   - 539,48  €  
12 53,26  €   - 539,48  €  
13 51,24  €   - 539,48  €  
14 49,20  €   - 539,48  €  
15 47,16  €   - 539,48  €  
16 45,11  €   - 539,48  €  
17 43,05  €   - 539,48  €  
18 40,98  €   - 539,48  €  
19 38,90  €   - 539,48  €  
20 36,82  €   - 539,48  €  
21 34,72  €   - 539,48  €  
22 32,62  €   - 539,48  €  
23 30,51  €   - 539,48  €  
24 28,39  €   - 539,48  €  
25 26,26  €   - 539,48  €  
26 24,12  €   - 539,48  €  
27 21,97  €   - 539,48  €  
28 19,82  €   - 539,48  €  
29 17,65  €   - 539,48  €  
30 15,48  €   - 539,48  €  
31 13,29  €   - 539,48  €  
32 11,10  €   - 539,48  €  
33 8,90  €   - 539,48  €  
34 6,69  €   - 539,48  €  
35 4,47  €   - 539,48  €  
36 2,24  €   - 539,48  €  



-464,48  €
-466,41  €
-468,35  €
-470,31  €
-472,27  €
-474,23  €
-476,21  €
-478,19  €
-480,19  €
-482,19  €
-484,20  €
-486,21  €
-488,24  €
-490,27  €
-492,32  €
-494,37  €
-496,43  €
-498,50  €
-500,57  €
-502,66  €
-504,75  €
-506,86  €
-508,97  €
-511,09  €
-513,22  €
-515,36  €
-517,50  €
-519,66  €
-521,83  €
-524,00  €
-526,18  €
-528,38  €
-530,58  €
-532,79  €
-535,01  €
-537,24  €

Pendiente
Amortizar

18.000,00  €  
17.535,52  €
17.069,11  €
16.600,76  €
16.130,45  €
15.658,18  €
15.183,95  €
14.707,74  €
14.229,55  €
13.749,36  €
13.267,17  €
12.782,97  €
12.296,76  €
11.808,52  €
11.318,25  €
10.825,93  €
10.331,56  €
9.835,13  €
9.336,63  €
8.836,06  €
8.333,40  €
7.828,65  €
7.321,79  €
6.812,82  €
6.301,73  €
5.788,51  €
5.273,15  €
4.755,65  €
4.235,99  €
3.714,16  €
3.190,16  €
2.663,98  €
2.135,60  €
1.605,02  €
1.072,23  €
537,22  €
-0,02  €

Período



Al final del ejercicio hay un pequeño error de -0,02  €.