Excel Avanzado

Macros, Vba en Excel y muchos ejemplos de nuestro Curso de Excel Avanzado

Excel Avanzado
SOL EJEMPLO

UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico

| 4 Comments

Esta macro fue creada con el fin de calcular el valor futuro del dinero según pagos con gradiente geométrico. Los cálculos realizados en esta macro se realizaron según la teoría desarrollada en el curso de INGENIERÍA ECONÓMICA sobre los FACTORES DE SERIE DE PAGOS CON GRADIENTE GEOMÉTRICO. Las variables utilizadas para esta macro fueron las siguientes:

–          Monto constante de las cuotas: Es el monto mensual constante que depositara el cliente en la su cuenta del banco

–          Tasa de interés: Es la rentabilidad que le ofrece la entidad bancaria producto del depósito mensual de las cuotas

–          Tasa de incremento: Es el incremento porcentual  que el usuario tendrá que incrementar por cada cuota, esto es una de las condiciones del banco para que el usuario obtenga la rentabilidad deseada.

–          Periodo: Es la cantidad de cuotas, una por cada mes y al final de los mismos, que tendrá que depositar el usuario.

La fórmula, según la teoría del curso mencionado, es la siguiente:

A = El monto constante de las cuotas

i = Tasa de interés

g = Tasa de incremento

n = Periodo

El valor hallado aquí es el valor presente del dinero depositado, sin embargo lo que se busca es el valor futuro, por lo que se tendrá que actualizar el valor presente del dinero:

La fórmula aplicada al VBA funciona de la siguiente manera:

1)      Se crea un botón que permita acceder a un interfaz:

2)      Al darle click se activa un interfaz que fue creado con las siguientes características:

–          Se creó un interfaz, con la finalidad de que sea más amigable con el usuario, con las características mostradas:

3)      Se le asignan los valores de cada TextBox las variables siguientes:

–          C = A

–          Ti = i

–          Incremento = g

–          n = n

Por lo que la fórmula aplicada al VBA queda de esta manera:

4)      Se convierten tanto la tasa de interés como la de incremento en fracciones, ya que en el interfaz están expresadas en términos de porcentaje:

5)      Se formula la macro:

–          Se crea el factor mostrado que cambia su valor “n” según lo asignado en TexBox4

–          Si el valor es 6, dicho valor se ira elevando de de 1 a 6, en dicho orden, pero dichos valores se irán sumando consecutivamente con el siguiente principio:

6)      Se crea la macro:

–          Se crea la macro como se muestra a continuación:

–          Se utilizó el UDF Round para redondear el número a dos decimales

–          Una vez ejecutada la macro se activa un MsgBox con el resultado.

–          Se utilizó el procedimiento “Unload”, para que borre el contenido de los TextBox cada vez que se active el interfaz.

Ejemplo:

Un usuario de una cuenta de un banco desea crear un fondo para invertirlo en un negocio a corto plazo. El usurario plantea depositar 1000 soles mensuales por un periodo de 12 meses. Para ello el banco lo propuso lo siguiente, con el fin de que el usuario logre su propósito:

–          Tasa de interés: 5 %

–          Tasa de incremento: 0.2%

Calcular cuánto será su dinero al final del periodo de depósitos:

Solución:

Usando la macro:

–          Le damos click en el botón “Consultar”

–          Se llenan los TexBox con las variables mencionadas

–          Click en aceptar:

Finalmente se adjunta el archivo correspondiente

UDF MACRO GRADIENTE

Gracias

 

Jesús Zárate

4 Comments

  1. una consulta, será posible la funcion para un periodo infinito?

    • Se trata de hallar el Valor Futuro de una serie de pagos de gradiente geométrico, es decir hallar el valor equivalente de los montos en un punto exacto del futuro. No tendría sentido hallar el monto equivalente en un periodo infinito.

    • Si te refieres a hacer crear una formula para traer a valor presente una perpetuidad. Por ejemplo, en el caso de un ingreso de dinero que se considera perpetuo. En este caso sí es posible crear una función que te ayude a realizar esta operación de forma más rápida. La formula es la siguiente: VP=C/i , donde “C” es el valor de las cuotas e “i” la tasa de interés. Entonces solo tendrías que ingresar 2 argumentos, uno para la cuota y otro para la tasa de interés.

  2. En base a lo expuesto, para que sea una UDF el código podría ser el siguiente:

    Function ValorFuturoGradienteGeometrico(Monto As Single, Ti As Single, TIncGeom As Single, periodos As Integer) As Single
    Dim Num, Suma, Numero As Single
    Dim cont As Integer

    Ti = Ti / 100 ‘Tasa de Interes
    TIncGeom = TIncGeom / 100 ‘Tasa de Incremento Geometrico
    Num = 1 ‘Valor inicial de Num
    Suma = 0

    For cont = 1 To periodos ‘Contador
    Num = Num * ((1 + TIncGeom) / (1 + Ti))
    Numero = (Monto / (1 + TIncGeom)) * Num
    Suma = Suma + Numero ‘Acumula los valores obtenidos Numero
    Next
    cont = 0
    For cont = 1 To periodos ‘Contador1
    Suma = Suma * (1 + Ti)
    Next

    ValorFuturoGradienteGeometrico = Round(Suma, 2)

    End Function

Deja un comentario

Required fields are marked *.


Excel Avanzado located at , Lima, Perú . Reviewed by usuarios rated: 4.7 / 5