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

2. 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. 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```

Thank-you!

Best regards,
Ricky

4. 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. 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

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

6. 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