Power Query: Cuadro amortización préstamo francés

Veremos hoy un ejercicio interesante (y un clásico) dentro de la matemáticas financieras, desarrollaremos el plan de amortización de un préstamo francés, pero empleando Power Query / Obtener y transformar.

Para hacer estos cálculos en el entorno del editor de consultas de Power Query necesitamos algunos de los conceptos o cálculos financieros básicos, que son especialmente estas dos fórmulas que nos permiten obtener el importe de la cuota periódica del préstamo francés:
Cuota = C₀ * i / (1-(1+i)^-n)
o para obtener la parte de capital amortizado dentro de una cuota para un periodo concreto k:
A_k = A₁ * (1+i)^(k-1)
siendo
A₁ = C₀ * i / ((1+i)ⁿ-1)

El primer paso será subir los datos de nuestra tabla de condiciones del préstamo al Editor de Power Query (desde la ficha Datos > grupo Obtener y transformar > Desde una tabla)

Una vez subida, con el fin de pasar como parámetros las tres condiciones:
Principal del préstamo
Tipo de interés anual
Plazo en años
En el editor duplicaremos la tabla cargada, aprovechando para cambiarles el nombre por:
principal
interes
plazo
Para duplicar las tablas basta hacer clic derecha sobre ella y presionar Duplicar

A continuación lo convertiremos en parámetros, para esto basta ir tabla por tabla haciendo clic derecho sobre el valor a convertir y Rastrear desagrupando datos:

En el siguiente paso generaremos dos funciones personalizadas dentro editor de Power Query (ver cómo).

Nuestras funciones replican los cálculos financieros comentados más arriba.

Así pues para obtener la cuota mensual de un préstamo francés la función sería (a la que he llamado: 'FxCuotaMensual'):

(Interes, Num_periodos) => let
  calculo1 = Interes*Number.Power(1+Interes, Num_periodos)/(Number.Power(1+Interes, Num_periodos)-1)

in
  calculo1

con dos variables necesarias: el tipo de interés y el número total de periodos en la vida del préstamo.


Por otra parte, la segunda función a definir, nos permitirá conocer de esa cuota, cuál es la cantidad de principal del préstamo amortizado; función a la que he llamado: 'FxPpalMensual':

(Capital, Interes, Num_periodos, Periodo) => let
  calculo1 = (Capital * Interes) / ((Number.Power(1+Interes, Num_periodos)-1)) *  Number.Power(1+Interes, Periodo-1)

in
  calculo1

Para crear nuestra consulta final necesitamos generarla como consulta en blanco (desde el editor, menú Inicio > grupo Nueva consulta > Nuevo origen > Otros orígenes > Consulta en blanco)... llamaré a mi consulta: 'Cuadro_Amortizacion'.
Dentro de la ventana del Editor avanzado escribiremos:

let  
// damos valor a las variables a emplear, 
// tomando los parámetros cargados desde la Tabla de condiciones de la hoja
ppal=principal,  
ti=interes/12,  
nper=plazo*12,  

//Generamos una lista desde 0 hasta el total de periodos (nper)
Mes= Table.RenameColumns(
        Table.FromList(
           List.Generate(  
               ()=>0,  
               each _ <=nper,  
               each _ +1),
           Splitter.SplitByNothing(), null, null, ExtraValues.Error),
       {{"Column1", "Mes"}}),

//Con la función pesonalizada FxCuotaMensual creada recuperamos el valor de la cuota para cada periodo
CuotaMensual = Table.AddColumn(Mes, "Cuota mensual",   each if [Mes] = 0 then 0 else ppal*(FxCuotaMensual(ti,nper)), type number),

//de igual forma, la función pesonalizada FxPpalMensual nos permite saber la parte amortizada en cada periodo
CapitalMensual = Table.AddColumn(CuotaMensual, "Capital mensual", each if [Mes] = 0 then 0 else FxPpalMensual(ppal,ti,nper,[Mes])),

//el cálculo de los intereses en cada periodo es fácil de obtene por diferencias
InteresMensual=Table.AddColumn(CapitalMensual , "Interes mensual",   
     each if [Mes] = 0 then 0 else [Cuota mensual]-[Capital mensual], type number),

//finalmente para conocer la deuda pendiente al final de cada perido (después el pago de la cuota)
//obtenemos un acumulado del principal devuelto, al que le restamos el principal solicitado
DeudaPendiente = Table.AddColumn(InteresMensual, "Deuda pendiente", 
            each if [Mes] = 0 then ppal else ppal-List.Sum(List.Range(InteresMensual[Capital mensual],0,[Mes]+1)))
in
DeudaPendiente

Al aceptar nuestra consulta vemos el resultado esperado...

Queda comentado en cada línea del código de la consulta el sentido de esos cálculos...

Una vez devuelto a la hoja de cálculo es fácil comprobar el correcto funcionamiento de la consulta.