jueves, 27 de noviembre de 2008

Cuadro de Amortización Automático

Descargar el fichero autocuadro.xlsm

Vamos a automatizar la generación de un Cuadro de Amortización de un Préstamo. Al cambiar el principal o el tipo de interés las celdas del cuadro se actualizan, como es lógico al tratarse de una hoja de cálculo. Pero lo que no se actualiza de forma automática es el número de filas del cuadro al variar la duración del préstamo. Para este caso se ha de utilizar una macro que cambie el número de filas del cuadro al cambiar la duración de la operación financiera.


El ejemplo se desarrolla en varias hojas:
  1. anual: Préstamo francés con pago anual constante
  2. mensual: Préstamo francés con pago mensual constante
  3. carencia: Préstamo francés con meses de carencia
  4. italiano: Préstamo de cuota de amortización constante y carencia
La macro que recalcula el cuadro de amortización es la siguiente:


Código:

Sub mCuadro()
Call mLimpia
Call mPeriodos
Call mCopia
Range("A1").Select
End Sub
Sub mLimpia()
Range("B12").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Clear
Range("A1").Select
End Sub
Sub mPeriodos()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("B10:B11").Select
Selection.AutoFill Destination:=Range("B10:B" & fin)
Range("B10:B" & fin).Select
Range("A1").Select
End Sub
Sub mCopia()
Dim fin As Integer
fin = Range("G4").Value + 10
Range("C11:G11").Select
Selection.AutoFill Destination:=Range("C11:G" & fin)
End Sub

En las dos últimas hojas (carencia, italiano) la macro no se lanza pulsando con el ratón sobre el botón que se ha creado para lanzar la macro. En estos casos no existe botón, y la macro se lanza simplemente escribiendo el número de años del préstamo (celda D6). Inmediatamente despues de validar la celda la macro se ejecuta y se actualiza el cuadro de amortización.

La macro que permite esta ejecución automática no se programa en un Módulo sino que se ha de escribir en el apartado 'Microsoft Excel Objetos' y en la hoja de la que se trate. Es una macro que se lanza al producirse un evento. Concretamente el evento es un Worksheet_Change que permite lanzar un proceso al producirse un cambio en la hoja. Ese cambio en nuestro caso es un cambio en el Target que es la celda D6.



Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$6" Then
Call mCuadro
End If
End Sub


5 comentarios:

  1. Muy bueno. Hacía tiempo que pensaba cómo se podría hacer esto, pero de VBA estoy algo pegado.
    Gracias.

    ResponderEliminar
  2. Hola. Solo quería agradecer la ayuda encontrada en la página de Excel avanzado. El método de validación usando la función INDIRECTO para relacionar dos listas, me ha sido de gran utilidad. Gracias por tu esfuerzo.

    ResponderEliminar
  3. hola, te mando un excel, si es posible, revisalo, se trata de que la gente conozca las verdaderas comisiones bancarias, el escel es muy sencillo, sirve mas bien de paso para presentar un metodo nuevo de amortizacion.

    te agradezco si lo divulgas, o me comentas tu opinion te llevara 4 minutos, un saludo.

    http://cid-7d6a20ac04530f3c.skydrive.live.com/self.aspx/P%c3%bablico/comisionreal.xls

    www.dondenaceeldinero.wordpress.com

    ResponderEliminar
  4. BUENAS TARDES AMIGO,
    FELICITACIONES ,MUY BUENA SU APORTACIÓN.
    TENDRAS POR CASULAIDAD PARA HACER EL FLUJO DE CAJA?

    ResponderEliminar
  5. Buenos días y enhorabuena por esta web.
    En relación a lo que hace la macro expuesta, quería indicar que se puede hacer lo mismo con la función SI, indicando que cuando no cumpla la condición establecida la celda quede en blanco.
    La condición se puede establecer en relación al número de periodos, y al sobrepasar este, la celda queda en blanco.
    Por ejemplo =SI($B7<DATOS!$D$7;-PAGOINT(DATOS!$D$9;B8;DATOS!$D$7;DATOS!$D$4;0);"")
    Dónde:
    $B7<DATOS!$D$7 es la condición a cumplir, en este caso en relación al periodo correspondiente dentro del total de periodos
    -PAGOINT(DATOS!$D$9;B8;DATOS!$D$7;DATOS!$D$4;0) fórmula si cumple la condición, en este caso son los intereses de un periodo.
    "" la celda queda en blanco si no cumple la condición.

    saludos

    ResponderEliminar