Cronograma de Amortización con Rangos de Derrame

Esta es la segunda publicación de una serie sobre fórmulas de matriz dinámica y rangos de derrame. En esta publicación, crearemos un cronograma de amortización dinámico… ¡y en realidad es muy divertido! Antes de que tuviéramos rangos de derrame, era complicado actualizar un cronograma de amortización cuando cambiaba el número de períodos. Es decir, es posible que quieras verlo para un préstamo con 360 meses, y luego actualizarlo para un préstamo con 180 meses, y así sucesivamente. A medida que cambia el número de períodos, también cambia el número de líneas que queremos mostrar. Entonces, tendríamos que juguetear con las filas de la fórmula o usar alguna técnica inteligente como formato condicional o rangos de impresión dinámicos. Pero ahora podemos usar fórmulas de matriz dinámica y rangos de derrame.

Objetivo

En resumen, nos gustaría ingresar los términos clave del préstamo en algunas celdas, como esta:

Dado que el plazo del préstamo es de 6 meses, queremos mostrar 6 períodos en nuestro calendario de amortización, así:

Pero, si cambiamos el número de meses para decir 12, queremos que se muestren 12 períodos en nuestro calendario de amortización, así:

Y NO queremos tener que editar ninguna fórmula, ocultar o mostrar ninguna fila, ni completar fórmulas. Esto es exactamente lo que logran las fórmulas de matriz dinámica y los rangos de derrames

He creado un video y una narrativa para seguir los pasos.

Video

Narrativo

Después de almacenar los términos clave del préstamo en algunas celdas, necesitaremos escribir una fórmula para cada columna. Los escribiremos en el siguiente orden:

• Período
• Interés
• Principal
• Balón final
• suplicar bal

Hagámoslo.

Nota: dependiendo de su versión de Excel, es posible que no se le muestren las funciones de matriz dinámica. Al momento de escribir este artículo, está disponible solo mediante suscripción a O365 y solo en el canal de actualización Insiders Fast. Si aún no tienes acceso a ellos, sin duda son algo que esperas con ansias

Período

La columna Período simplemente debe comenzar en 1 y terminar en la cantidad de meses ingresados. Podemos usar la función SECUENCIA para esto. Suponiendo que la cantidad de meses (6) está almacenada en C8, escribimos la siguiente fórmula en B14:

=SECUENCIA(C8)

Nota: la función SECUENCIA admite argumentos adicionales, incluido el número de columnas, el valor inicial y el valor del paso.

Excel ingresa 1 en B14 (la celda de fórmula) y, dado que la función SECUENCIA devuelve múltiples valores, los valores restantes se extienden a las celdas adyacentes como se muestra a continuación.

A medida que el usuario cambie la cantidad de meses en C8, Excel actualizará dinámicamente la columna Período en consecuencia.

Interés

Para calcular el interés de cada período, usaremos la función IPMT. La función IPMT existe en Excel desde hace décadas. No es una función de matriz dinámica, sin embargo, ¿adivinen qué sucede cuando le pasamos una referencia de rango de derrame como B14#? Sí… ¡también se derrama! (Impresionante, ¿verdad?)

Suponiendo que la fórmula del período dinámico está en B14, la tasa de interés anual se almacena en C7, el monto del préstamo en C6 y el número de meses en C8, la siguiente fórmula funcionaría.

=-IPMT(C7/12,B14#,C8,C6)

Notas:

• C7/12 toma la tasa de interés anual y la divide por 12 para determinar la tasa de interés mensual. Esto es necesario ya que estamos creando filas mensuales.
• B14# es la referencia del derrame. B14 hace referencia al período (calculado mediante la función SECUENCIA anterior) y el hash # le indica a Excel que haga referencia a todo el rango de derrame (todas las filas devueltas por la fórmula Período). Esto hace que la función IPMT devuelva múltiples resultados y también se derrame.
• C8 es el número de meses
• C6 es el monto del préstamo
• Cambiamos el signo de un valor negativo predeterminado a un valor positivo con el operador de resta principal ( – )

Aquí podemos ver que IPMT también se derrama:

Principal

Escribir el monto de capital para cada período es muy similar al cálculo de intereses, excepto que usamos la función PPMT. Escribimos la siguiente fórmula en E14, de la siguiente manera.

=-PPMT(C7/12,B14#,C8,C6)

Los mismos argumentos básicos que la fórmula de interés.

Balón final

Para calcular el saldo final de cualquier período determinado, usaremos la función CUMPRINC que calcula el capital acumulado pagado durante cualquier período. Esta función ha estado en Excel durante décadas y no es una función de matriz dinámica. Sin embargo, cuando usamos la función SECUENCIA como argumento… ¿adivinen qué? Sí, devuelve múltiples valores y se derrama.

Comencemos calculando el capital acumulado pagado en un período determinado. Podemos usar la siguiente fórmula en F14:

=CUMPRINC(C7/12,C8,C6,1,SECUENCIA(C8),0)

Pero, para la columna Saldo final, no queremos mostrar el capital acumulado pagado, queremos mostrar el saldo restante. Entonces, solo necesitamos mostrar la diferencia entre el monto total del préstamo (C6) y el capital pagado (cálculo CUMPRINC) actualizando la fórmula de la siguiente manera:

=C6+CUMPRINC(C7/12,C8,C6,1,SECUENCIA(C8),0)

Nos vemos bien y solo necesitamos calcular la columna de saldo inicial.

suplicar bal

Guardamos este para el final ya que se refiere al monto del saldo final.

Solo necesitamos agregar el saldo final al monto principal del período. Usaremos referencias de derrames en nuestra fórmula para que esta fórmula también se derrame:

=E14#+F14#

Una vez completado, disponemos de un calendario de amortización dinámico que se adapta al número de periodos introducidos.

Si desea ver todos estos pasos funcionando juntos, no dude en descargar el archivo de muestra a continuación. O, si aún no tiene estas funciones en su versión de Excel, mire el video de arriba.

Si tiene otras técnicas divertidas sobre el rango de derrames o consejos sobre el calendario de amortización, compártalas publicando un comentario a continuación… ¡gracias!

Archivo de muestra: AmorizationSpill.xlsx