Results 1 to 10 of 12

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

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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)

    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
    39
    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
    39
    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
    39
    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

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
  •