Double Nested Loop

[PowerMark123]

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

[wdx223_Daniel]

it is hard to get your point just depending on the code.

could you provide some original data and the desired output?

[PowerMark123]

Hi, Thanks for your response, much appreciated! Here is the Sample Data that's the Source in the Query.

AmortisingRate	ApplicationID	APR	Division.1	FinalFee	ContractualInstalment	OpeningBalance	RemainingTerm	Term
0.178024	1937	0.18812	Division 1	160	196.66	8000	1	61
0.134145	9380	0.160837	Division 2	160	288.22	8596.5	3	37
0.150768	25282	0.159187	Division 3	160	338.1	14490	1	61

This is the Output from running the inner loop on just one account (AccountID 9380 in the Sampe Data).

Payment Number	Opening Balance	Monthly Payment	Monthly Principal	Monthly Interest	Balance Remaining	Funder
1	8596.5	288.22	197.57	90.65	8398.93	Division 2
2	8398.93	288.22	199.65	88.57	8199.28	Division 2
3	8199.28	288.22	201.76	86.46	7997.52	Division 2
4	7997.52	288.22	203.89	84.33	7793.63	Division 2
5	7793.63	288.22	206.04	82.18	7587.59	Division 2
6	7587.59	288.22	208.21	80.01	7379.38	Division 2
7	7379.38	288.22	210.4	77.82	7168.98	Division 2
8	7168.98	288.22	212.62	75.6	6956.36	Division 2
9	6956.36	288.22	214.86	73.36	6741.5	Division 2
10	6741.5	288.22	217.13	71.09	6524.37	Division 2
11	6524.37	288.22	219.42	68.8	6304.95	Division 2
12	6304.95	288.22	221.73	66.49	6083.22	Division 2
13	6083.22	288.22	224.07	64.15	5859.15	Division 2
14	5859.15	288.22	226.43	61.79	5632.72	Division 2
15	5632.72	288.22	228.82	59.4	5403.9	Division 2
16	5403.9	288.22	231.24	56.98	5172.66	Division 2
17	5172.66	288.22	233.67	54.55	4938.99	Division 2
18	4938.99	288.22	236.14	52.08	4702.85	Division 2
19	4702.85	288.22	238.63	49.59	4464.22	Division 2
20	4464.22	288.22	241.14	47.08	4223.08	Division 2
21	4223.08	288.22	243.69	44.53	3979.39	Division 2
22	3979.39	288.22	246.26	41.96	3733.13	Division 2
23	3733.13	288.22	248.85	39.37	3484.28	Division 2
24	3484.28	288.22	251.48	36.74	3232.8	Division 2
25	3232.8	288.22	254.13	34.09	2978.67	Division 2
26	2978.67	288.22	256.81	31.41	2721.86	Division 2
27	2721.86	288.22	259.52	28.7	2462.34	Division 2
28	2462.34	288.22	262.25	25.97	2200.09	Division 2
29	2200.09	288.22	265.02	23.2	1935.07	Division 2
30	1935.07	288.22	267.81	20.41	1667.26	Division 2
31	1667.26	288.22	270.64	17.58	1396.62	Division 2
32	1396.62	288.22	273.49	14.73	1123.13	Division 2
33	1123.13	288.22	276.38	11.84	846.75	Division 2
34	846.75	288.22	279.29	8.93	567.46	Division 2
35	567.46	288.22	282.24	5.98	285.22	Division 2
36	285.22	288.22	285.21	3.01	0.01	Division 2

I basically want this output for each of the accounts in the sample data. The query I posted performs a few further operations to remove payments from the schedule that are before the RemainingTerm and also adds an index number for the payments that are still valid.

Hope that helps but please do let me know if you have any other questions!!!

Cheers.