# Thread: Loan Schedule - List.Generate (needs Opening Balance calculation)

1. ## Loan Schedule - List.Generate (needs Opening Balance calculation)

Hello Experts,
This is a Loan Schedule. Inside of List.Generate, the entire "3rd each" line has an incorrect formula for the [Opening Balance]...wrong interest payment, not rounded, etc.

The desired new [Opening Balance] formula would be: show the [Balance Remaining] value from the previous [Payment] line.

Here is the desired output:

2) Also, how do you add List.Buffer into this function for better efficiency?

3) Please keep the LoanTable as unexpanded. I won't need to expand it yet.

Code:
```(Principal as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,
Payment = Number.Round( (Principal * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),
LoanTable = Table.FromList(

List.Generate( ()=>[Counter=0],
each [Counter] < Periods,
each [Counter = [Counter]+1],
each Principal * ( Number.Power(1+(Rate/12),Periods) - Number.Power( 1+(Rate/12),[Counter]+0) ) / ( Number.Power( 1+(Rate/12), Periods)-1 )
)
,Splitter.SplitByNothing(), {"Opening Balance"}, null, ExtraValues.Error),

Period = Table.AddIndexColumn(LoanTable, "Payment Number", 1, 1),
MonthlyPayment = Table.AddColumn(Period,"Monthly Payment", each Payment),
MonthlyInterest = Table.AddColumn(MonthlyPayment,"Monthly Interest", each Number.Round( PeriodicInterestRate * [Opening Balance], 2)),
MonthlyPrincipal = Table.AddColumn(MonthlyInterest,"Monthly Principal", each [Monthly Payment] - [Monthly Interest] ),
BalanceRemaining = Table.AddColumn(MonthlyPrincipal, "Balance Remaining", each [Opening Balance] - [Monthly Principal])

in
BalanceRemaining```

Many thanks to Everyone coming to my rescue! Thanks in advance,

Ricky

2. Is the calculated payment amount incorrect? The excel pmt function calculates 478.91. If I override Payment = Number.Round( (Principal * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2), with 478.91 then your table has an ending balance of -0.01 (when expanded).
If performance is a problem, you could try List.Accumulate. The code below has been pretty fast for me.

List.Accumulate(List.Buffer({2..[Periods]}),
{[Period = 1,
BegBal= [Amount],
Interest = BegBal*[InterestRate],
Principal = [Payment] - Interest,
EndBal = BegBal - Principal]},
(s,c) =>
{[Period = c,
BegBal= List.First(s)[EndBal],
Interest = BegBal*[InterestRate],
Principal = [Payment] - Interest,
EndBal = BegBal - Principal
]}
& s)

Hope this helps,
Mike

3. Thanks Mike,
The table should have 6 columns:

[Payment Number] {1..Periods}
| Would be better to start at {0..Periods} and show the \$100,000 in the [Balance Remaining] column

[Opening Balance] need help here please
| replace the original formula altogether (it was based on nominal rate, even conversion to effective rate, isn't precise; thus Excel PMT function won't match to the Canadian banks)
| replace the original formula with the value of the previous [Payment Number]'s [Balance Remaining]

[Monthly Payment] formula is perfect (to the penny, and matches to the Canadian banks)

[Monthly Interest] formula is perfect (to the penny, and matches to the Canadian banks)

[Principal] formula is perfect (to the penny, and matches to the Canadian banks)

[Balance Remaining] once the [Opening Balance] is fixed, this column will be to the penny also.

I've tried your List.Accumulate but failed. I have done something wrong...is it the "s" and "c" part?
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Code:
```(Principal as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,

Payment = Number.Round( (Principal * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),

LoanTable = List.Accumulate(List.Buffer(
{2..[Periods]}),
{[Period = 1,
BegBal= [Amount],
Interest = BegBal*[InterestRate],
Principal = [Payment] - Interest,
EndBal = BegBal - Principal]},
(s,c) =>
{[Period = c,
BegBal= List.First(s)[EndBal],
Interest = BegBal*[InterestRate],
Principal = [Payment] - Interest,
EndBal = BegBal - Principal
]}
& s)

in
LoanTable```

Thank-you!

Best regards,
Ricky

4. Hi Ricky,
My version of List.Accumulate was part of a calculated field. You are using a function. They are different approaches that should lead to the same result. To convert to the function approach, I had to change my field names to your function arguments. You will see that [Amount] now read Principal, [InterestRate] now reads PeriodicInterestRate and [Payment] now reads Payment. Notice that brackets are stripped out because the calculate field approach references other columns in a table while the function approach references the function arguments.
The List.Accumulate produces most of the schedule that you need. I opted to handle the rest of the changes through additional steps in the function.
Finally, the monthly payment seems to work fine with this schedule so I left it. Below is the modified function. Let me know if it works.
Best Regards,
Mike

(LoanAmount as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,

Payment = Number.Round( (LoanAmount * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),

LoanList = List.Accumulate(List.Buffer(
{2..Periods}),
{[Period = 1,
BegBal= LoanAmount ,
MonthlyPayment = Payment,
Interest = BegBal*PeriodicInterestRate ,
Principal = Payment - Interest,
EndBal = BegBal - Principal]},
(s,c) =>
{[Period = c,
BegBal= List.First(s)[EndBal],
MonthlyPayment = Payment,
Interest = BegBal*PeriodicInterestRate ,
Principal = Payment - Interest,
EndBal = BegBal - Principal
]}
& s),

LoanTable = Table.FromRecords(LoanList),

LoanTableRename = Table.RenameColumns(LoanTable ,{{"Period", "Payment Number"}, {"BegBal", "Opening Balance"}, {"Interest", "Monthly Interest"}, {"MonthlyPayment", "Monthly Payment"}, {"Principal", "Monthly Principal"}, {"EndBal", "Balance Remaining"}}),

LoanTableSort = Table.Sort(LoanTableRename ,{{"Payment Number", Order.Ascending}}),

LoanTableFinal = #table( {"Payment Number", "Balance Remaining"}, { {0,LoanAmount} }) & LoanTableSort

in
LoanTableFinal

5. Hi Mike,
Thank-you so much for coming my rescue. I appreciate it very much.

1) To make it 100% perfect, how do you add a logic handler just for the last [Payment Number], 300 in this example? Balance Remaining should be \$0, currently shows (\$2.05)

[Monthly Payment] column
| if List.Last or max value of [Periods] which is 300 and ( [Opening Balance] \$474.62 + [Monthly Interest] \$1.21 ) < [Monthly Payment] \$477.88
| then sum [Opening Balance] \$474.62 + [Monthly Interest] \$1.21 = \$475.83
| else [Monthly Payment]

[Monthly Interest] formula remains unchanged \$1.21
[Monthly Principal] formula remains unchanged, \$474.62 = [Monthly Payment] uses the lesser value \$474.62 + \$1.21 - [Monthly Interest] \$1.21
[Balance Remaining] formula remains unchanged, \$0 = [Opening Balance] \$474.62 - [Monthly Principal] \$474.62

2) Genuis! This is the first time I've seen a second function inside the first! I think I follow your logic. The first {2..Periods} handles Payment Periods from #2 to #300. The second function (s, c) handles Payment Periods #0 and #1 for the initial loan amount, the 1st [Balance Remaining], and the 1st & 2nd [Opening Balance].

3) Curious...could the [Balance Remaining] column be shown at the End within the Table record (before Expanding)? I'm not sure how I could re-order it through Advanced Editor. Didn't want to expand then re-order.

Many thanks Mike! You've been super helpful.

Here's the latest code with Interest rounded to 2 decimals.

Code:
```(LoanAmount as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,
Payment = Number.Round( (LoanAmount * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),

LoanList = List.Accumulate(List.Buffer(
{2..Periods}),
{[Period = 1,
BegBal= LoanAmount ,
MonthlyPayment = Payment,
Interest = Number.Round(BegBal*PeriodicInterestRate, 2),
Principal = Payment - Interest,
EndBal = BegBal - Principal]},

(s,c) =>
{[Period = c,
BegBal= List.First(s)[EndBal],
MonthlyPayment = Payment,
Interest = Number.Round(BegBal*PeriodicInterestRate ,2),
Principal = Payment - Interest,
EndBal = BegBal - Principal
]}
& s),

LoanTable = Table.FromRecords(LoanList),
LoanTableRename = Table.RenameColumns(LoanTable ,{{"Period", "Payment Number"}, {"BegBal", "Opening Balance"}, {"Interest", "Monthly Interest"}, {"MonthlyPayment", "Monthly Payment"}, {"Principal", "Monthly Principal"}, {"EndBal", "Balance Remaining"}}),
LoanTableSort = Table.Sort(LoanTableRename ,{{"Payment Number", Order.Ascending}}),
LoanTableFinal = #table( {"Payment Number", "Balance Remaining"}, { {0,LoanAmount} }) & LoanTableSort

in
LoanTableFinal```
Best regards,
Ricky

6. Hi Ricky,

I decided to cut the List.Accumulate off one period early. I can then handle the last record with much simpler logic. I just set the MonthlyPayment = BegBal + Interest, the Principal = BegBal and the EndBal = 0. I think this works, but please verify.

Its not easily seen here, but the List.Accumulate has three arguments. The first argument is simply a list of numbers from 2 to Periods-1. The second argument is an initialization record and handles Payment record 1. The third argument generates the additional records by taking the numbers from the first argument and then the subsequent EndBalances (originally from the second argument and then each iteration of the third argument thereafter).
The first remaining balance (aka the loan amount) is added in the LoanTableFinal line.

I am not sure what you are asking in question 3.

Below is the modified code.

Best Regards,
Mike

(LoanAmount as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,
Payment = Number.Round( (LoanAmount * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),

LoanList = List.Accumulate(

List.Buffer({2..Periods-1}), // Changed to Periods-1

{[Period = 1,
BegBal= LoanAmount ,
MonthlyPayment = Payment,
Interest = Number.Round(BegBal*PeriodicInterestRate, 2),
Principal = Payment - Interest,
EndBal = BegBal - Principal]},

(s,c) =>
{[Period = c,
BegBal= List.First(s)[EndBal],
MonthlyPayment = Payment,
Interest = Number.Round(BegBal*PeriodicInterestRate ,2),
Principal = Payment - Interest,
EndBal = BegBal - Principal
]}
& s),

// We are now one list element short

LastPayment = [Period = Periods,
BegBal= List.First(LoanList)[EndBal],
MonthlyPayment = BegBal + Interest,
Interest = Number.Round(BegBal*PeriodicInterestRate ,2),
Principal = BegBal,
EndBal = 0
],

LoanTable = Table.FromRecords({LastPayment} & LoanList),
LoanTableRename = Table.RenameColumns(LoanTable ,{{"Period", "Payment Number"}, {"BegBal", "Opening Balance"}, {"Interest", "Monthly Interest"}, {"MonthlyPayment", "Monthly Payment"}, {"Principal", "Monthly Principal"}, {"EndBal", "Balance Remaining"}}),
LoanTableReverse = Table.ReverseRows(LoanTableRename ),
LoanTableFinal = #table( {"Payment Number", "Balance Remaining"}, { {0,LoanAmount} }) & LoanTableReverse

in
LoanTableFinal

7. Thank-you so much Mike!
It looks so good.

I decided to cut the List.Accumulate off one period early. I can then handle the last record with much simpler logic. I just set the MonthlyPayment = BegBal + Interest, the Principal = BegBal and the EndBal = 0. I think this works, but please verify.
You're a genius! The final Monthly Payment now adjusts +/- to pay for interest and balance remaining. Here's how it looks:

Perfect! Tested final Monthly Payment needed to be decreased (120 periods)

Perfect! Tested final Monthly Payment needed to be increased (6 periods)

Your three arguments approach makes sense. I wouldn't have been able to conceptualize nor write this code without your expertise. Thank-you! I've learned a lot.

My earlier question #3 was about showing the [Balance Remaining] column at the end.
| Currently: Payment #, Remaining, Opening, Payment, Interest, Principal

| How would you order the record to be: Payment #, Opening, Payment, Interest, Principal, Remaining without expanding this list?
| Here, I had to expand then re-order the Remaining column.

Many thanks. You've helped me tremendously!

Best regards,
Ricky

8. You are welcome Ricky.

I did not realize that I reordered the columns when I appended the first record to the schedule. A couple of options come to mind. The first is to type in all of the fields of the #table and fill in the values on the LoanFinalTable line. It is probably easier and requires one less function call.

LoanTableFinal = #table( {"Payment Number","Opening Balance", "Monthly Payment", "Monthly Interest", "Monthly Principal", "Balance Remaining"}, { {0,null,null,null,null,LoanAmount} }) & LoanTableReverse

The second is more for learning purposes. You can add a step LoanFinalTableReorder and return that from the In statement
LoanFinalTableReorder = Table.ReorderColumns(LoanTableFinal, {"Payment Number","Opening Balance", "Monthly Payment", "Monthly Interest", "Monthly Principal", "Balance Remaining"})
in
LoanFinalTableReorder

9. Hey Ricky,

Working through the amortization with List.Accumulate gave me additional insight on List.Generate. I decided to create a List.Generate version. I think it is more straight forward and expect it would be just as fast. Let me know which is faster if you decide to try it.

(LoanAmount as number, Rate as number, Periods as number)=>

let

PeriodicInterestRate = Number.Power( Number.Power( (1+(Rate/2)), 2), (1/12)) - 1,
Payment = Number.Round( (LoanAmount * PeriodicInterestRate) / ( (1 - Number.Power( (1 + PeriodicInterestRate), -Periods) )), 2),

FirstRow = [#"Payment Number" = 0, #"Opening Balance" = null, #"Monthly Payment"=null,
#"Monthly Interest" = null, #"Monthly Principal" = null, #"Balance Remaining"=LoanAmount],

LoanList = List.Generate(

()=>
[Payment Number= 1,
#"Opening Balance" = LoanAmount ,
#"Monthly Payment" = Payment,
#"Monthly Interest" = Number.Round(#"Opening Balance" *PeriodicInterestRate, 2),
#"Monthly Principal" = Payment - #"Monthly Interest",
#"Balance Remaining" = #"Opening Balance"- #"Monthly Principal"],

each [Payment Number] < Periods,

each [Payment Number= [#"Payment Number"] +1,
#"Opening Balance" = [#"Balance Remaining"],
#"Monthly Payment" = Payment,
#"Monthly Interest" = Number.Round(#"Opening Balance" *PeriodicInterestRate ,2),
#"Monthly Principal"= Payment - #"Monthly Interest",
#"Balance Remaining"= #"Opening Balance" - #"Monthly Principal"
]
),

// We are now one list element short

LastPayment = [Payment Number= Periods,
#"Opening Balance" = List.Last(LoanList)[#"Balance Remaining"],
#"Monthly Payment" = #"Opening Balance" + #"Monthly Interest",
#"Monthly Interest" = Number.Round(#"Opening Balance" *PeriodicInterestRate ,2),
#"Monthly Principal"= #"Opening Balance",
#"Balance Remaining"= 0
],

LoanTableFinal = Table.FromRecords( {FirstRow} & LoanList & {LastPayment} )

in
LoanTableFinal

10. Hi Mike,
This thread has a wealth of knowledge. Thank-you for sharing them.

Option #1 of adding an extra ReorderColumns step is definitely easier to follow
LoanFinalTableReorder = Table.ReorderColumns(LoanTableFinal, {"Payment Number","Opening Balance", "Monthly Payment", "Monthly Interest", "Monthly Principal", "Balance Remaining"})in
LoanFinalTableReorder
Option #2 is good to know how to update it within advanced editor
LoanTableFinal = #table( {"Payment Number","Opening Balance", "Monthly Payment", "Monthly Interest", "Monthly Principal", "Balance Remaining"}, { {0,null,null,null,null,LoanAmount} }) & LoanTableReverse
List.Generate is the winner!
The syntax are similar to codes generated out of the Power Query user interface. Very easy to follow. I appreciate this very much! I will choose this method.

Added 100+ new Tranche ID's, output of approx 30,000 rows...
The refresh speed tests of List.Accumulate & List.Buffer vs List.Generate seemed to be a tie. Both methods flashed "Connecting to DataSource" and "Refreshing" only for a split second, blink of an eye.

Curious...how do you add List.Buffer/Table.Buffer into our List.Generate method?

I'm referencing Imke Feldmann's blog to add Table.Buffer or List.Buffer, where appropriate.
https://www.thebiccountant.com/2018/...d-power-query/

Mike, you've fully contributed to this excellent reference thread. I hope many users will find this knowledge useful within their professional careers as I have.

Many thanks!

Best regards,
Ricky

Page 1 of 2 1 2 Last