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

ExcelQuestion

New member
Joined
May 27, 2018
Messages
26
Reaction score
0
Points
0
Excel Version(s)
Office 365
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.

1) Could you please help me revise that 3rd each? Starts with "each Principal * (Number.Power..."
The desired new [Opening Balance] formula would be: show the [Balance Remaining] value from the previous [Payment] line.

Here is the desired output:
LoanSchedule JPEG.JPG

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.
LoanTableJPEG.JPG


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
 

Attachments

  • Working - 2019-06-04 Loan Schedule.xlsx
    23.6 KB · Views: 28
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
 
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

Please help...

Thank-you!

Best regards,
Ricky
 
Last edited:
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
 
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

Last 5 Rows.JPG

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
 
Last edited:
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
 
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)
It Worked - Thank-you.JPG

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

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
LoanTable.PNG
| 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.
Expanded.JPG

Many thanks. You've helped me tremendously!

Best regards,
Ricky
 
Last edited:
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
 
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
 
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! :first:
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...ffer-your-tables-in-power-bi-and-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
 
Last edited:
Hi Ricky,

Imke's post looks to Buffer when she is referencing other tables (or presumably lists). The amortization algorithm is merely referencing the prior record. I don't see a place to put in a List.Buffer or Table.Buffer, so it may not apply. I will be trying out her suggestion in List.Generate or List.Accumulate routines that i have.

I did put in a List.Buffer into the List.Accumulate version of the algorithm, but I suspect that it did not do anything as you have told me that the List.Generate and the List.Accumulate is about the same speed. In my experience, if the Buffer helps then I use it. Otherwise I take it out. So I would probably take it out if I were to use that version.

I agree with you on using List.Generate. I think it is the better version.

Thank you for the compliments. I hope you post some other challenges. They are quite fun.

I work in accounting and am using this code to support the 842 accounting standard. In a prior version of the code, I did not adjust the final payment because it was immaterial to the journal entry being booked. And I was going to argue that with the auditors. But putting in that last step avoids the argument. So thank you too.

Best Regards,
Mike
 
Hi Mike,
It's good to hear your thoughts about List.Buffer / Table.Buffer. Yes, in this scenario, the code references within the current list so probably that's why it didn't make any difference.

I just ran another of round tests using the previously posted codes:
100+ new Tranche ID's at varying Period lengths, Principal in many $ millions, output of approx 30,000 rows...
1) List.Accumulate (with List.Buffer) vs
2) List.Accumulate (removed List.Buffer) vs
3) List.Generate
Results were the same in all 3 cases. Same lighting quick refresh.

This challenge is fun, indeed, especially as your ideas came back so quickly also. I do have other challenges and will be in upcoming new posts. Perhaps, using Buffer will find it's way into the upcoming code(s).

Ending at $0 for the final payment is the icing on the cake. Shows true professionalism and credibility were hard at work. You did it!

The output leads to the full set of journal entries by period for the entire loan. Accounting could post them all in advance and integrate this data into their cash flow forecast.
1) Dr. Cash | Cr. Long-Term Debt | (Period 0)
2) Dr. Interest Expense | Dr. Long-Term Debt | Cr. Long-Term Debt | Cr. Cash | (Period 1 - 119, same $ values)
3) Dr. Interest Expense | Dr. Long-Term Debt | Cr. Long-Term Debt | Cr. Cash | (Period 120, final, rounding adjusted $ values )

That's why your code rocks!

Many thanks again, Mike

Best regards,
Ricky
 
Last edited:
Hi Mike,
Two and a half years later, the M codes that you have kindly provided are still the top functioning workhorse for me.

Thank-you so much! All the best in 2022.

Ricky
 
Back
Top