Tabla de amortización de préstamos con Power Query

Hace varios días que venía dándole vueltas a la idea de mostrar cómo crear una tabla de amortización de préstamos, como el modelo que publiqué en el pasado, pero usando Power Query en lugar de las funciones de Excel.
A diferencia de los ejemplos anteriores de Power Query que he mostrado en este blog, en este caso no partimos de una fuente de datos, de la hoja o remota, que cargamos a una consulta en forma de tabla. De hecho, tenemos que crear la tabla de amortización "de la nada". Es decir, una vez definidos los parámetros iniciales (monto del préstamo, tasa de interés, número de cuotas) tenemos que crear una tabla en Power Query que luego cargaremos a una hoja de Excel.
Para crear la tabla "de la nada" tendremos que usar funciones del lenguaje M (el lenguaje del Power Query). En este post no entraré en los detalles técnicos de M (objetos, funciones, etc.) y me limitaré a mostrar cómo fue construido el modelo.
Investigando las distintas funciones de Power Query llegué rápidamente a la conclusión que la función indicada era List.Generate. Lamentablemente la ayuda en la hoja de funciones de Microsoft es oscura (por lo menos para el usuario promedio). En mi ayuda vino este post publicado por Andrew Todd en PowerPivot(Pro).

Y ahora, manos a la obra! Empezamos por definir los tres parámetros iniciales: monto del préstamo (Capital), tasa de interés anual (Interes) y el número de pagos o cuotas (Pagos). Los ingresamos en celdas de una hoja y creamos nombres definidos que se refieran a esas celdas. La forma más rapida de hacerlo es seleccionando el rango y usando Fórmulas-Nombres definidos-Crear desde la selección:

Ahora creamos una consulta para cada parámetro. Como hemos creado nombres definido Power Query no carga el rango A1:B3 a la consulta, sino sólo la celda del nombre definido

Power Query cambia el tipo de datos s Entero automáticamente. Con un clic del botón derecho abrimos el menú contextual y usamos "Rastrear desagrupando datos" (Drill down)

Vamos a Inicio, cerramos y cargamos como "sólo conexión"
Hacemos lo mismo con los otros dos parámetros

Para crear la tabla de amortización empezamos por crear una consulta en blanco (Desde otras fuentes-Consulta en blanco). En la venta del editor pegamos este código, que paso a explicar

 let  
 P=Capital,  
 i=Interes,  
 n=Pagos,  

 Mes=Table.ExpandRecordColumn(  
     Table.FromList(  
           List.Generate(  
           ()=>[Counter=1],  
           each [Counter]<=n,  
           each [Counter=[Counter]+1]  
           ),  
     Splitter.SplitByNothing(), {"Balance Inicial"},   
    null, ExtraValues.Error),"Balance Inicial", {"Counter"}, {"Mes"}),  

   BalanceInicial = Table.AddColumn(Mes, "Balance Inicial",   
     each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1),   
     type number),  

   PagoMensual = Table.AddColumn(BalanceInicial, "Pago Mensual",   
     each P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1), type number),  

   InteresMensual=Table.AddColumn(PagoMensual , "Interes Mensual",   
     each (i/12)*[Balance Inicial], type number),  

   CapitalMensual = Table.AddColumn(InteresMensual, "Capital Mensual",   
     each [Pago Mensual]-[Interes Mensual], type number),  

   BalanceFinal = Table.AddColumn(CapitalMensual, "Balance Final",   
     each [Balance Inicial]-[Capital Mensual], type number)  
 in  
   BalanceFinal

Las primeras tres líneas crean las variables que reciben el valor de las consultas de los parámetros. Luego creamos la columna "Mes" (el número de cuota) para lo cual anidamos tres funciones de M; de adentro hacia afuera:

List.Genetate: crea una lista (columna) con los números de mes; empieza con [Counter] = 1 y va agregando 1 hasta que el valor sea igual a n (Pagos);
Table.FromList: convierta la lista que acabamos de generar en Tabla de manera que podamos usarla en Power Query;
Table.ExpandRecordColumn: expandimos la tabla

A esta altura de los acontecimientos nuestra consulta ser verá así

En Power Query no disponemos de las funciones para calcular el pago fijo, el interés y la parte del capital en cada pago como tenemos en Excel (PAGO, PAGO.INT, PAGOPRIN), así que tendremos que hacer los cálculos con operadores. Por ejemplo, para calcular el balance inicial de cada período usamos

P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1)

Para crear la columna podemos usar el menú en la interfaz del usuario (Agregar Columna-Columna Personalizada) o pegar el código en el editor. Lo mismo para las demás columnas. Al final de proceso tenemos la tabla

La descargamos en la hoja donde pusimos los parámetros y nuestro modelo está listo

Este modelo es absolutamente dinámico. Todo lo que hay que hacer es cambiar los parámetros y apretar Datos-Actualizar todo como puede verse en este video