Crear filas dinámicas para un cronograma de amortización con Power Query

A veces, en Excel, queremos usar fórmulas para calcular los valores de las filas, pero el número de filas es dinámico y cambia periódicamente. Por ejemplo, digamos que queremos crear un cronograma de amortización y usarlo para una variedad de préstamos. Algunos préstamos se pagan en 36 meses, otros en 120 meses y otros en 360 meses. Entonces, necesitamos entre 36 y 360 filas. O posiblemente menos, o quizás más… simplemente depende del plazo actual del préstamo. El punto aquí es que el número de filas es variable.

Tradicionalmente, una solución común era crear más filas de fórmulas de las que necesitaría y luego usar alguna técnica para ocultar las fórmulas en las filas que no se usan para el programa actual. Por ejemplo, podríamos usar formato condicional, ocultar filas manualmente, usar grupos de esquemas o usar rangos con nombres dinámicos para imprimir.

Sin embargo, Power Query ofrece otra opción. Verá, Power Query puede crear un número dinámico de filas en función de su entrada. Aunque esta técnica se puede utilizar de diversas formas, ilustraré el enfoque con un calendario básico de amortización de préstamos.

Objetivo

Por ejemplo, digamos que ingresamos estos supuestos básicos de préstamo:

Ahora queremos un calendario de amortización que muestre una fila para cada mes. Si el plazo del préstamo fuera de 360 ​​meses, querríamos 360 filas, y así sucesivamente.

Podríamos hacer esto escribiendo fórmulas en el número máximo de filas necesarias para cualquier préstamo. Entonces, si el plazo máximo de la hipoteca fuera de 30 años, escribiríamos fórmulas en 360 filas mensuales. Luego, usaríamos alguna técnica para ocultar el exceso de fórmulas para plazos de préstamo más cortos. El beneficio de este enfoque es que las fórmulas se recalculan automáticamente, por lo que ingresas las suposiciones y terminas con los cálculos. La desventaja es que para plazos de préstamo más cortos, tendrá que administrar un montón de fórmulas adicionales. Es posible que tengas que ocultarlos, eliminarlos, formatearlos condicionalmente o utilizar algún otro método. Y luego, si obtiene un plazo más largo, como una hipoteca a 40 años, entonces tendrá que completar las fórmulas en filas adicionales. Básicamente, está administrando manualmente la cantidad de filas para que coincidan con cada plazo del préstamo.

Alternativamente, podríamos usar Power Query para proporcionar dinámicamente el número correcto de filas según el plazo del préstamo. La ventaja es que proporciona el número exacto de filas necesarias, por lo que no es necesario ocultar filas ni completar fórmulas manualmente. La desventaja es que tienes que hacer clic derecho manualmente Actualizar la tabla de amortización.

Entonces, con esos antecedentes, repasemos los detalles para usar Power Query para generar dinámicamente la cantidad de filas necesarias según el plazo del préstamo.

Detalles

Usaremos estos pasos para lograr nuestro objetivo.

• Recuperar el plazo del préstamo
• Crear una lista dinámica de períodos
• Escribe las fórmulas de Excel.
• Suma la fila total

Hagámoslo.

Nota: Los pasos a continuación se presentan con Excel para Windows 2016. Si está utilizando una versión diferente de Excel, tenga en cuenta que es posible que las funciones presentadas no estén disponibles o que deba descargar e instalar el complemento Power Query.

Recuperar el plazo del préstamo

Primero, necesitamos ingresar el plazo del préstamo en meses en Power Query. Una forma bastante rápida de hacer esto es utilizar un rango con nombre. Básicamente asignamos un nombre definido a la celda de entrada que almacena el plazo del préstamo. Una forma de hacerlo es seleccionar la celda de entrada y luego ingresar el nombre deseado en el cuadro de nombre (el pequeño cuadro a la izquierda de la barra de fórmulas). Nuestro nombre será TermMonths, por lo que seleccionamos la celda que almacena el plazo del préstamo (12, 36, 120, etc.) y luego escribimos el nombre TermMonths en el cuadro de nombre y presionamos Enter .

Podemos confirmar que nuestra celda tiene nombre seleccionando la celda y viendo el cuadro de nombre, como se muestra a continuación.

Para traer el valor de la celda con nombre TermMonths a Power Query, seleccionamos la celda de entrada con nombre y hacemos clic en el botón Datos Desde tabla/rango . El valor de la celda se muestra en el editor. Ahora, cambiaremos el nombre de la consulta utilizando el campo Nombre en el editor de consultas. Simplemente cambiamos el nombre de la consulta por un nombre preferido, como TermMonths. El siguiente paso será un poco más fácil si evitamos los espacios, así que elegí TermMonths sin espacios como se muestra a continuación.

El último paso es profundizar para que podamos hacer referencia fácilmente a este valor en otra consulta. Para ello hacemos clic derecho en el valor 360 y seleccionamos Drill Down, cuyos resultados se muestran a continuación.

Una vez completado esto, ahora podemos seleccionar Cerrar y cargar en y luego seleccionar Solo crear conexión como se muestra a continuación.

En este punto, tenemos una consulta que recupera el término ingresado por el usuario en meses. Hasta aquí todo bien, ahora toca crear la lista dinámica de periodos.

Crear una lista dinámica de períodos

Comenzamos abriendo una consulta en blanco. Para ello, seleccionamos Datos Obtener datos De otras fuentes Consulta en blanco .

Obtenemos una nueva consulta vacía, como se muestra a continuación.

Ahora, suponiendo que hayamos nombrado nuestra consulta anterior TermMonths , definimos el paso Fuente ingresando la siguiente fórmula:

={1..MesesTérmino}

Esto se muestra a continuación.

Ahora llega el momento de la verdad… le damos Enter después de escribir la fórmula y… ¡BAM! Excel crea una lista de números desde 1 hasta el número de meses, como se muestra a continuación.

Lo mejor de esto es que esta lista se actualizará dinámicamente cuando un usuario ingrese un valor diferente en Excel y luego actualice la tabla de resultados de la consulta.

Espera, ¿qué acaba de pasar aquí? Bueno, las llaves { } le dicen a Excel que cree una Lista. Los dos puntos le indican a Excel que complete los valores entre el punto inicial, 1, y el punto final, el valor proporcionado por la consulta TermMonths. Como es de esperar, a medida que cambia el número de TermMonths, también cambia la lista y el número de filas resultante.

Cambiamos el nombre de la consulta a Período como se muestra a continuación.

Enviamos los resultados a Excel seleccionando Inicio Cerrar y cargar en , y seleccionamos Tabla como se muestra a continuación .

Y la lista de períodos aparece en la ubicación especificada, como se muestra a continuación.

Ahora, un usuario puede ingresar un nuevo término y luego hacer clic derecho Actualizar para actualizar la tabla de resultados. Bonito

Ya sólo queda escribir las fórmulas de Excel.

Escribe las fórmulas de Excel.

Dividiremos esta parte en unos pocos pasos. Primero, necesitamos saber el monto del pago mensual.

Calcule el monto del pago mensual

Usaremos la función PAGO de Excel para calcular el monto del pago mensual. Suponiendo que la tasa de interés anual se almacena en C7, el plazo del préstamo se almacena en C8 y el monto del préstamo en C6, la fórmula sería:

=-PAGO(C7/12, C8, C6)

Esto se muestra en la captura de pantalla siguiente.

Nota: si desea obtener más detalles sobre la función PMT, consulte esta publicación de blog.

A continuación, necesitamos crear nuevas columnas en la tabla de resultados.

Agregar nuevos encabezados de columna

Nos gustaría que nuestro calendario de amortización incluyera las siguientes columnas:

• Período
• BegBal
• Interés
• Principal
• Final Bal

Ya tenemos la primera columna, Periodo, creada por Power Query. Entonces, sólo necesitamos agregar los demás. Para hacer esto, escribimos la nueva etiqueta de columna en la celda derecha de la etiqueta existente y la tabla se expande automáticamente para incluirla.

Por ejemplo, ingresamos la siguiente etiqueta de columna BegBal inmediatamente a la derecha de la etiqueta de Período existente y la tabla se expande como se muestra a continuación.

Hacemos esto para todas las columnas deseadas, como se muestra a continuación.

Ahora necesitamos escribir las fórmulas que calculan las cantidades de cada columna.

Escribir fórmulas correspondientes

Hay muchas variaciones de fórmulas que calcularán estas cantidades, por lo que estas fórmulas proporcionan solo una solución. Existen muchos otros.

Ingresamos las siguientes fórmulas en la fila del Período 1 para cada columna. Cuando presionamos Enter después de escribir cada uno, Excel completa automáticamente la fórmula.

• BegBal = SI(ISTEXT(H19),$C$6,H19)
• Pago = $C$12
• Interés = -IPMT($C$7/12,[@Period],TermMonths,$C$6)
• Principal = [@Pago]-[@Interés]
• EndBal = [@BegBal]-[@Principal]

¡Hurra! Ahora tenemos un calendario de amortización básico, como se muestra a continuación.

Una vez completado esto, solo necesitamos calcular algunos totales.

Suma la fila total

Para calcular el interés total y el capital total proporcionados en el programa de amortización, solo necesitamos activar la fila total de la tabla. Hacemos esto seleccionando cualquier celda de la tabla y luego marcando la casilla Herramientas de tabla Fila total .

Luego, en la nueva fila total que aparece en la parte inferior de la tabla, seleccionamos la celda en la columna Interés y usamos el menú desplegable para seleccionar Suma. Hacemos lo mismo para la columna Principal.

Luego, recuperamos los valores en cualquier celda deseada escribiendo = y luego señalando la celda de la fila total correspondiente. Por ejemplo, recuperamos los valores de Principal total e Interés total en las celdas C13 y C14 a continuación.

Ahora, cuando necesitemos realizar una actualización, simplemente ingresamos los tres supuestos de préstamo y hacemos clic derecho Actualizar la tabla de resultados. Tenemos el número exacto de filas necesarias.

Nota: Power Query no funciona como las fórmulas de Excel, por lo que la tabla no expandirá ni reducirá el número de filas hasta que haga clic con el botón derecho en Actualizar.

Mi objetivo aquí era hablar sobre cómo Power Query puede ajustar dinámicamente el número de filas de una tabla. La ilustración que utilicé para demostrar esto fue un calendario de amortización. Si tiene otros trucos divertidos de Power Query, compártalos publicando un comentario a continuación… ¡gracias!

Archivo de muestra:

• PQAmort.xlsx
• Interés diario.xlsx