Results 1 to 10 of 12

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Acolyte ExcelQuestion's Avatar
    Join Date
    May 2018
    Posts
    24
    Articles
    0
    Excel Version
    Office 365

    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:	26 
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:	17 
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

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
  •