Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

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

  1. #1
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    Click image for larger version. 

Name:	LoanSchedule JPEG.JPG 
Views:	21 
Size:	35.1 KB 
ID:	9139

    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.
    Click image for larger version. 

Name:	LoanTableJPEG.JPG 
Views:	16 
Size:	45.7 KB 
ID:	9140


    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
    Attached Files Attached Files

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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. #3
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus
    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 by ExcelQuestion; 2019-06-06 at 02:26 AM.

  4. #4
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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. #5
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus
    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

    Click image for larger version. 

Name:	Last 5 Rows.JPG 
Views:	9 
Size:	39.2 KB 
ID:	9146

    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 by ExcelQuestion; 2019-06-06 at 09:05 PM. Reason: Added M code

  6. #6
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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. #7
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus
    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)
    Click image for larger version. 

Name:	It Worked - Thank-you.JPG 
Views:	5 
Size:	39.8 KB 
ID:	9147

    Perfect! Tested final Monthly Payment needed to be increased (6 periods)
    Click image for larger version. 

Name:	ExpandedTestMonthlyPayment.JPG 
Views:	5 
Size:	45.1 KB 
ID:	9148

    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
    Click image for larger version. 

Name:	LoanTable.PNG 
Views:	6 
Size:	12.0 KB 
ID:	9149
    | 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.
    Click image for larger version. 

Name:	Expanded.JPG 
Views:	6 
Size:	39.9 KB 
ID:	9150

    Many thanks. You've helped me tremendously!

    Best regards,
    Ricky
    Last edited by ExcelQuestion; 2019-06-08 at 06:08 AM.

  8. #8
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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. #9
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    26
    Articles
    0
    Excel Version
    Office 365
    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. #10
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    22
    Articles
    0
    Excel Version
    Excel 2013 Professional Plus
    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
    Last edited by ExcelQuestion; 2019-06-08 at 08:41 PM.

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •