Create Power Query Column Recursively

Question

EMI Calculation:

Number.Round((rate/12*pv)/(1-Number.Power(1+rate/12,-nper)),0)

where PV is initial loan amount Rate is the initial rate of interest at the beginning and nper is 180 = Tenure in months

here the EMI = 107485

Keeping EMI Fixed, now i need to compute monthly interest and principal

The interest calculated formula will be Previous balance * Rate of interest/12 * no. of days / no of days in the previous month So if there is no rate change the interest will be prev bal * rate of interest /12 else it will be for the no of days new interest rate applicable.

Principal = EMI-Interest paid
New Balance = Previous Balance - Principal
Amount

What i am not able to achieve is :

Every time Interest is calculated, i want to refer to the previous balance which is not available.

I have tried creating Recursive functions to compute Interest, principal and balance columns:

Below function computes EMI value at the beginning

PMT

    (pv,rate,nper)=>
    let
    payment = Number.Round((rate/12*pv)/(1-Number.Power(1+rate/12,-nper)),0)
    in
    payment

Below function computes Interest amount (Index Value is the row number / payment no)

Get_InterestValue

    (indexValue,table,rate,no_of_days,no_of_days_in_Month)=>
    let
    prev_idx=indexValue-1,
    previousbal = get_previous_balance(indexValue,table),
    interest=previousbal*rate/12/no_of_days_in_Month*no_of_days
    in
    interest

Below function computes Principal amount paid

GetPrincipalPaid
    (indexvalue,table,EMI_amount,Interest_amount)=>
    let
    principal_amount= EMI_amount-Interest_amount
    in
    principal_amount

Below function computes Previous balance

get_previous_balance

    (index_value,table)=>
    let
    prev_idx=index_value-1,
    prev_bal=if prev_idx=0 then fParameter("ParameterTable","Initial_Loan_Amount") else Table.SelectRows(table, each ([Index] = prev_idx)){0}[Bal]
    in 
    prev_bal

fParameter function reads the intial values table

The columns gives errors

Answer 1

I have found a solution as in following code:

Now I am facing performance issue. Below solution works good with 10 Months or smaller. once u go for a higher Tenure, the calculations are very slow and doesnt generate any output for long time or probably crashes before calculations are completed, is there any way i can speed up the query processing?

PMT Function:

(pv,rate,nper)=>
let
    payment = Number.Round((rate/12*pv)/(1-Number.Power(1+rate/12,-nper)),0)
in
    payment

fParameter Function:

let Parameter=(TableName,ParameterLabel) =>
let
    Source=Excel.CurrentWorkbook(){[Name=TableName]}[Content],
    value=Source{[Parameter=ParameterLabel]}[Value]
in
    value
in Parameter

Payment Schedule Query:

let
    Custom1 = List.Generate(()=>Date.Month(fParameter("ParameterTable","StartDate")),each _<=fParameter("ParameterTable","Tenure_in_Months")*1.5+Date.Month(fParameter("ParameterTable","StartDate"))-1,each _+1),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Month", each if Number.Mod([Column1],12)=0 then 12 else Number.Mod([Column1],12)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Year1", each Date.Year(fParameter("ParameterTable","StartDate"))+Number.RoundDown([Column1]/12)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Year", each if [Month]=12 then [Year1]-1 else [Year1]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom2",{{"Column1", "PaymentMonth"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Payment Date", each #date([Year],[Month],Date.Day(fParameter("ParameterTable","StartDate")))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Payment Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Payment Date", type date}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type", "EMI", each EMIValue),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom4",{"Payment Date"},Loan_Rates,{"Date"},"Loan_Rates",JoinKind.FullOuter),
    #"Expanded Loan_Rates" = Table.ExpandTableColumn(#"Merged Queries", "Loan_Rates", {"Date", "Loan Rate"}, {"Date", "Loan Rate"}),
    #"Added Custom5" = Table.AddColumn(#"Expanded Loan_Rates", "New_Date", each if [Date] = null then [Payment Date] else [Date]),
    #"Sorted Rows" = Table.Sort(#"Added Custom5",{{"New_Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Loan Rate"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Payment Date", "Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New_Date", "Payment Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Payment Date", "EMI", "Loan Rate"}),
    #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Added Index1",JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries1", "Added Index1", {"Payment Date"}, {"Payment Date.1"}),
    #"Added Custom7" = Table.AddColumn(#"Expanded Added Index1", "Payment.Date.2", each if [Index.1]=1 then fParameter("ParameterTable","Disbursement Date") else [Payment Date.1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom7",{{"Payment.Date.2", type date}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"Payment Date.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Payment.Date.2", "Payment Date.1"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Days", each [Payment Date]-[Payment Date.1]),
    #"Extracted Days" = Table.TransformColumns(#"Added Custom6",{{"Days", Duration.Days, Int64.Type}}),
    #"Added Custom8" = Table.AddColumn(#"Extracted Days", "No Of Days in Month", each Date.EndOfMonth([Payment Date.1])-Date.StartOfMonth([Payment Date.1])+#duration(1,0,0,0)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom8",{"Index",  "Payment Date.1"}),
    #"Extracted Days1" = Table.TransformColumns(#"Removed Columns1",{{"No Of Days in Month", Duration.Days, Int64.Type}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Extracted Days1",{"Payment Date", "Days", "EMI", "Loan Rate"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns1",{{"Index.1", "Index"}}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns3",{"Index", "Payment Date", "EMI", "No Of Days in Month", "Days", "Loan Rate"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns2",null,0,Replacer.ReplaceValue,{"EMI"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Replaced Value", "Interest", each Get_InterestValue([Index], Table.Buffer(#"Replaced Value"))),
    #"Added Custom9" = Table.AddColumn(#"Invoked Custom Function", "Principal", each [EMI]-[Interest]),
    #"Added Custom11" = Table.AddColumn(#"Added Custom9", "Balance", each fParameter("ParameterTable","Initial_Loan_Amount")-List.Sum(List.FirstN(#"Added Custom9"[Principal],[Index]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom11", each [Interest] > 0)
    
in
    #"Filtered Rows"

Loan_Rates table Query == This reads change of loan rates over a period:

let
    Source = Excel.CurrentWorkbook(){[Name="Loan_Rates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Loan Rate", Percentage.Type}})
in
    #"Changed Type"

Get_InterestValue Function

(indexValue,table)=>
let
    prev_idx=indexValue-1,
    rate=Table.SelectRows(table, each ([Index] = indexValue)){0}[Loan Rate],
    no_of_days_in_Month=Table.SelectRows(table, each ([Index] = indexValue)){0}[No Of Days in Month],
    no_of_days=Table.SelectRows(table, each ([Index] = indexValue)){0}[Days],
    previousbal = get_previous_balance(indexValue,table),
    interest=previousbal*rate/12/no_of_days_in_Month*no_of_days
in
    interest

GetPrincipalPaid Function:

(indexValue,table)=>
let
    EMI_Amount=Table.SelectRows(table, each ([Index] = indexValue)){0}[EMI],
    principal_amount= EMI_Amount-Get_InterestValue(indexValue,table)
in
    principal_amount

GetBalance Function:

(indexValue,table)=>
let
    bal = get_previous_balance(indexValue,table)-GetPrincipalPaid(indexValue,table) 
in
    bal

get_previous_balance function:

(indexValue,table)=>
let
prev_idx=indexValue-1,
prev_bal=if prev_idx=0 then fParameter("ParameterTable","Initial_Loan_Amount") else  GetBalance(prev_idx,table)
in 
prev_bal

EMIValue Function: let

    Source = PMT(fParameter("ParameterTable","Initial_Loan_Amount"), fParameter("ParameterTable","Rate_int_PA"), fParameter("ParameterTable","Tenure_in_Months")),
    #"Converted to Table" = #table(1, {{Source}}),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "EMI"}}){0}[EMI]
in
    #"Renamed Columns"