# 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