Usar Escenarios en Excel y la función PAGO para calcular el pago mensual de un préstamo

En este tutorial veremos un caso práctico en Excel para usar Escenarios, una herramienta que nos ayudará a tener un conjunto de valores lo cuales podrán ser intercambiados para evaluar diferentes escenarios de una situación.

En este ejemplo calcularemos el pago mensual de un préstamo. Haremos el cálculo usando la función PAGO, la cual forma parte de las funciones financieras de Excel.

Con la función PAGO calcularemos la mensualidad a pagar en el préstamo, y con Escenarios evaluaremos diferentes pagos mensuales dependiendo 3 tasas de interés.

Ver video Escenarios en Excel y la función PAGO

Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.

Calcular el pago mensual de un préstamo

Dentro de las funciones Financieras en Excel, tenemos la función PAGO que nos ayudará a determinar la cantidad que deberemos pagar de manera mensual dependiendo una tasa de interés y la cantidad de pagos que nos requiera el banco.

La función PAGO tiene la siguiente sintaxis:

=PAGO(Tasa, Nper, Va, Vf, Tipo)

Donde:

• Tasa. Es la tasa de interés que la institución financiera determina.
• Nper. El total de pagos de un préstamo.
• Va. La cantidad total del préstamo solicitado.
• Vf. Valor futuro que se desea lograr al dar el último pago. Por defecto es 0 (cero).
• Tipo. 1 para pago al comienzo de periodo y 0 para el final del periodo.

En la siguiente imagen vemos que el préstamo solicitado es 5,000, a una tasa del 16.50% anual y con la función PAGO determinamos el pago mensual de 2,460.12.

Escenarios en Excel

Un escenario es Excel es un conjunto de valores que serán guardados y pueden ser cambiados para evaluar diferente situaciones de un mismo caso, es decir, diferentes escenarios dependiendo el cambio de los valores. Un escenario nos permitirá evaluar diferentes resultados cambiando los valores que han sido determinados como cambiantes,

Para nuestro ejemplo del pago de un préstamo los valores cambiantes serán la tasa de interés y el plazo o meses a pagar. Conforme estos dos valores cambien se los resultados serán diferentes para cada Escenario.

Escenarios diferentes cambiando la tasa de interés y pagos

Ahora que definimos los valores cambiantes, vamos a configurar 3 escenarios con la tasa de interés cambiante.

• Cuánto pagamos con el 16.5 mensual.
• Cuánto pagamos con el 38.45 mensual.
• Cuánto pagamos con el 59.4 mensual.

Seguimos los siguientes pasos para tener 3 escenarios:

Pestaña Datos > Análisis e hipótesis > Administrador de escenarios.

• Botón Agregar.
• Damos un nombre al escenario.
• Elegimos las celdas cambiantes que serán la tasa de interés y la cantidad de pagos.
• Botón Aceptar.

• Definimos los valores cambiante para este escenario.
• Botón Agregar para el siguiente escenario.

Una vez que se definen los 3 escenarios y sus valores cambiantes, analicemos qué hace cada botón:

• Agregar. Se agrega un nuevo escenario.
• Eliminar. Se elimina el escenario elegido.
• Modificar. Cambiamos los valores cambiantes.
• Combinar. Si tenemos varios archivos con escenario los podemos combinar.
• Resumen. Se mostrará una nueva hoja donde veremos todos los escenarios y los diferentes resultados que arroja cada uno.

Si presionamos el botón Resumen veremos los 3 escenarios y el resultado de cada uno para analizarlos.

Descarga el archivo de ejemplo

Escenarios para simular un préstamo bancario – EXCELeINFO.zip

Si te gustó este tutorial por favor regístrate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.