ExcelQuestion
New member
- Joined
- May 27, 2018
- Messages
- 26
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
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:
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.
Many thanks to Everyone coming to my rescue! Thanks in advance,
Ricky
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:
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