Excel Avanzado

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

Excel Avanzado

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

| 4 comentarios

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.

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

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

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

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:

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

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

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

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

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:

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

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:

FORMULA A LA MACRO1 1 300x30 UDF para el cálculo del valor futuro según pagos con Gradiente Geométrico

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

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:

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

5)      Se formula la macro:

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

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

-          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:

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

6)      Se crea la macro:

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

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

-          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

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

-          Click en aceptar:

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

Finalmente se adjunta el archivo correspondiente

UDF MACRO GRADIENTE

Gracias

 

Jesús Zárate

4 comentarios

  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 una respuesta

Los campos requeridos estan marcados con *.