Hi, I'm completely new to M Language so on a steep learnign curve here, I could use a helping hand!!
I'm trying to calculate the repayment schedules of a number of loans. Using List.Generate, I've got the repayment schedule working for a loan in SampleData, but I now need to move to the next loan in my SampleData table. So, I'm attempting a nested loop, the inner calculates the repayment schedule and the outer loop moves to the next loan. Here's what I have so far, anyone got any ideas to point me in teh right direction? I suspect it's my absolute lack of knowledge about what is fed into the outer loop that's holding me back!
let
Source = SampleData,
OuterLoop = (Source, RepaymentSchedule) =>
List.Generate
(
()=>[Index = 0],
each [Index] < Table.RowCount(Source),
each [Index = [Index]+1],
each RepaymentSchedule([Index])
),
ContractualInstalments = (Index) =>
List.Generate
(
()=>
[Payment Number = 1,
APR = Source[AmortisingRate]{Index},
Term = Source[Term]{Index},
RemainingTerm = Source[RemainingTerm]{Index},
LoanAmount = Source[OpeningBalance]{Index},
Division = Source[Division.1]{Index},
Rate = Number.Power( (1+APR), (1/12) ) - 1,
Payment = Number.Round( (LoanAmount * Rate) / ( (1 - Number.Power( (1 + Rate), -(Term - 1)) )), 2),
Funder = Division,
#"Opening Balance" = LoanAmount,
#"Monthly Payment" = Payment,
#"Monthly Interest" = Number.Round(#"Opening Balance" *Rate, 2),
#"Monthly Principal" = Payment - #"Monthly Interest",
#"Balance Remaining" = #"Opening Balance"- #"Monthly Principal"],
each [Payment Number] < [Term],
each [Payment Number = [#"Payment Number"] +1,
APR = Source[AmortisingRate]{Index},
Term = Source[Term]{Index},
RemainingTerm = Source[RemainingTerm]{Index},
LoanAmount = Source[OpeningBalance]{Index},
Division = Source[Division.1]{Index},
Rate = Number.Power( (1+APR), (1/12) ) - 1,
Payment = Number.Round( (LoanAmount * Rate) / ( (1 - Number.Power( (1 + Rate), -(Term - 1)) )), 2),
Funder = Division,
#"Opening Balance" = [#"Balance Remaining"],
#"Monthly Payment" = Payment,
#"Monthly Interest" = Number.Round(#"Opening Balance" *Rate ,2),
#"Monthly Principal"= Payment - #"Monthly Interest",
#"Balance Remaining"= #"Opening Balance" - #"Monthly Principal"
]
),
LastN = Table.LastN(Table.FromRecords(ContractualInstalments), RemainingTerm),
RemoveColumns = Table.RemoveColumns(LastN, {"Opening Balance","Monthly Payment","Monthly Interest","Balance Remaining"}),
AddIndex = Table.AddIndexColumn(RemoveColumns, "Index", 0, 1)
RepaymentSchedule = Table.ReorderColumns(AddIndex,{"Funder","Index","Monthly Principal","Payment Number"})
OutputSchedule = OuterLoop(Source, ContractualInstalments),
FinalSchedules = Table.FromList(OutputSchedule)
in
FinalSchedules
