UNIVERSIDAD  DE  LOS  ANDES

FACULTAD DE CIENCIAS ECONOMICAS Y SOCIALES

COMPUTACION  I

EJERCICIO DE  EXCEL

Prof. Israel Ramírez

 

Construir utilizando Microsoft  EXCEL, la siguiente Tabla de Amortización de Préstamos:

 

TABLA DE AMORTIZACION DE PRESTAMOS

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MONTO

Bs 12.450.000,00

 

COBRANZA

Bs 15.000,00

 

 

 

INTERES

32,00%

 

 

 

 

 

 

PLAZO

36

 

 

 

 

 

 

CUOTA CALC.

Bs 542.252,32

 

 

 

 

 

 

CUOTA TOTAL

Bs 557.252,32

 

TOTAL A PAGAR

Bs 20.061.083,46

 

 

 

 

 

 

 

 

 

 

 

SALDO

SALDO

CUOTA

INTERES

AMORTIZACION

INTERES

AMORTIZACION

PERIODO

INICIAL

FINAL

MENSUAL

MENSUAL

MENSUAL

ACUMULADO

ACUMULADA

 

 

 

 

 

 

 

 

1

12.450.000,00

12.239.747,68

557.252,32

332.000,00

210.252,32

332.000,00

210.252,32

2

12.239.747,68

12.023.888,63

557.252,32

326.393,27

215.859,05

658.393,27

426.111,37

3

12.023.888,63

11.802.273,35

557.252,32

320.637,03

221.615,29

979.030,30

647.726,65

4

11.802.273,35

11.574.748,32

557.252,32

314.727,29

227.525,03

1.293.757,59

875.251,68

5

11.574.748,32

11.341.155,95

557.252,32

308.659,96

233.592,36

1.602.417,55

1.108.844,05

6

11.341.155,95

11.101.334,46

557.252,32

302.430,83

239.821,49

1.904.848,37

1.348.665,54

7

11.101.334,46

10.855.117,73

557.252,32

296.035,59

246.216,73

2.200.883,96

1.594.882,27

8

10.855.117,73

10.602.335,22

557.252,32

289.469,81

252.782,51

2.490.353,76

1.847.664,78

9

10.602.335,22

10.342.811,84

557.252,32

282.728,94

259.523,38

2.773.082,70

2.107.188,16

10

10.342.811,84

10.076.367,83

557.252,32

275.808,32

266.444,00

3.048.891,02

2.373.632,17

11

10.076.367,83

9.802.818,66

557.252,32

268.703,14

273.549,18

3.317.594,16

2.647.181,34

12

9.802.818,66

9.521.974,84

557.252,32

261.408,50

280.843,82

3.579.002,66

2.928.025,16

              :

              :

PASOS  A  SEGUIR

1.- Situarse en la celda indicada e ingresar:

      En A1:   TABLA DE AMORTIZACION DE  PRESTAMOS

      En B3:   MONTO

      En B4:   INTERES

      En B5:   PLAZO

      En B6:   CUOTA CALC.

      En B7:   CUOTA TOTAL

      En E3:   COBRANZA

      En E7:   TOTAL A PAGAR

     

      En C3:   12450000

      En C4:   32%

      En C5:   36                                                                               MONTO DE LA CUOTA CALCULADA

      En C6:   =PAGO(C4/12;C5;-C3)   PAGO(TASA;PERIODOS; VALOR ACTUAL;[VALOR FINAL];[TIPO])

      En F3:   15000

      En C7:   =C6+F3                                          (CUOTA TOTAL = CUOTA CALCULADA + GASTOS DE COBRANZA)

      En F7:   =C7*C5                (TOTAL A PAGAR POR EL PRESTAMO = CUOTA TOTAL * PLAZO DEL PRESTAMO)

     

Se ingresan los encabezados que tendrán  las diferentes columnas

      En A10:  PERIODO                           En  D9:     CUOTA                                  En  F10:   MENSUAL

      En B9:     SALDO                              En  D10:   MENSUAL                             En  G9:      INTERES

      En B10:   INICIAL                             En  E9:     INTERES                               En  G10:   ACUMULADO

      En C9:     SALDO                              En  E10:    MENSUAL                            En  H9:     AMORTIZACION

      En C10:   FINAL                                En  F9:     AMORTIZACIÓN                 En  H10:   ACUMULADA                 

2.-  Colocar en la columna A, a partir de la fila 12, los números de período que van desde el 1 hasta el 72

       (Esta tabla debe servir para cualquier plazo desde 1 mes  hasta 72 meses, con las cuotas pagándose al final del mes, es decir, se aplicará el cálculo de las cuotas post pagables).

 

INGRESO DE LAS FORMULAS  PARA  EL  PRIMER  PERIODO

3.-  Hacer el saldo inicial del primer período igual al monto del préstamo.  Situarse en la celda B12 y escribir   

         =C3

4.-  En la celda C12 escribir la fórmula para calcular el saldo al final del primer período.

       (VA es el Valor Actual o presente de una serie de pagos iguales:                 VA(TASA ; PERIODOS ; MONTO PAGOS))         

          =VA(C4/12;(C5-1);-C6)                                                       (C14/12  valor que corresponde a la tasa mensual)

     Con esta fórmula se está calculando el valor que tienen al final del primer período las restantes  (PLAZO-1)  cuotas que faltan por cancelar.

5.-  En la celda D12 ingresar la fórmula para el cálculo de la cuota mensual

      (  CUOTA MENSUAL = MONTO DE LA CUOTA  )

          =C7

6.-  En la celda E12 ingresar la fórmula para el cálculo del interés pagado en el primer período 

       ( INTERES MENSUAL  = CUOTA MENSUAL - AMORTIZACIÓN MENSUAL - GASTOS COBRANZA )

          =D12-(B12-C12)-F3

7.-  En la celda F12 escribir la fórmula para el cálculo de la amortización  en el primer período 

       ( AMORTIZACIÓN PERIODO = SALDO INICIAL DEL PERIODO - SALDO FINAL DEL PERIODO)     

         =B12-C12

8.-  En la celda G12 escribir la fórmula para el cálculo del interés acumulado  en el primer período

         =E12

9.-  En la celda H12 escribir la fórmula para el cálculo de la amortización acumulada  en el primer período 

         =F12

 

INGRESO DE LAS FORMULAS PARA LOS RESTANTES PERIODOS

Como esta hoja debe servir para cualquier número de períodos entre 1 y 72, se debe controlar que solamente se realicen cálculos para los períodos menores o iguales al plazo del préstamo.       =SI( período<=plazo ; asigne ;  no asigne   )

 

10.- En la celda B13 ingresar la fórmula para el cálculo del saldo inicial del segundo período

       ( SALDO INICIAL = SALDO FINAL PERIODO ANTERIOR )

       =SI(A13<=C$5;C12;NOD( ))                                             ( Observe la utilización de la función SI. 

                     SI(Expresión Lógica ; cuando sea VERDADERO asigne ; cuando sea  FALSO  asigne NOD( ))

                     NOD( ) = Función que devuelve el valor de error #N/A, que significa "no hay ningún valor disponible"

11.- En la celda C13 escribir la fórmula para el cálculo del saldo final del segundo período

   =SI(A13<=C$5;VA(C$4/12;(C$5-A13);-C$6);NOD( ))

12.-  En la celda D13 escribir la fórmula para el cálculo de la cuota mensual 

         =SI(A13<=C$5;C$7;NOD( ))

13.-  En la celda E13 ingresar la fórmula para el cálculo del interés pagado en el segundo período

        =SI(A13<=C$5;(D13-(B13-C13)-F$3);NOD( ))          

14.-  En la celda F13 ingresar la fórmula para el cálculo de la amortización mensual en el segundo período   

         =SI(A13<=C$5;(B13-C13);NOD( ))

15.-  En la celda G13 ingresar la fórmula para el cálculo del interés acumulado al final del segundo período 

         ( INTERÉS ACUMULADO = INTERÉS DEL PERÍODO + INTERÉS ACUMULADO HASTA PERÍODO ANTERIOR )

         =SI(A13<=C$5;(E13+G12);NOD( ))

16.-  En la celda H13 ingresar la fórmula para el cálculo de la amortización acumulada al final del segundo período.

        ( AMORTIZACIÓN ACUMULADA = AMORTIZAC PERÍODO + AMORTIZAC  ACUMULADA HASTA PERÍODO ANTERIOR )

         =SI(A13<=C$5;                    ;NOD( ))

17.- Las fórmulas para el cálculo de los valores correspondientes a los restantes períodos ( 3 a 72 ) son semejantes a las  ingresadas para el segundo período; por lo tanto, estas fórmulas pueden y deben ser copiadas a los demás períodos, de ahí la necesidad de utilizar referencias absolutas de celdas en algunas de las fórmulas.

 

18.-  Colocar el ancho adecuado y el formato indicado a cada una de las columnas.

19.-  Realizar un gráfico que presente en el eje X el número del período, y en el eje Y el interés mensual y la amortización mensual. ( Se debe observar la relación inversa que existe entre el pago de intereses y la amortización de capital ).

 

20.  Observar que pasa con la hoja creada cuando se cambian los datos de entrada:  Plazo del préstamo, Monto del préstamo, Tasa de interés anual y Gastos de cobranza.