calculate running total and Cross column calculation

[Susovan]

Revised Gross Book Balance = When we are calculating Revised Gross Book Balance for the period 1 we are taking Gross Book Balance But When we are calculating Periods 2 onwards we are taking Revised Gross Book Balance + Principal Payment (initial)+PSA$

 Formula for : Principal Payment (initial)= PPMT - rev GBB * Effective Interest Rate

 Formula for (PPMT) used in Alteryx : PPMT = (([Revised Gross Book Balance]*[Effective Interest Rate])/(1-(POW(1+[Effective Interest Rate],(-1*[Remaining Periods])))) is depending upon Effective Interest Rate, Revised Gross Book Balance and Remaining Periods

Formula for : rev GBB * Effective Interest Rate =  Revised Gross Book Balance * Effective Interest Rate+PSA$

Formula for: PSA$= if (Revised Gross Book Balance= Principal Payment (initial),0, Revised Gross Book Balance*PSA%)

Things need to calculate in Alteryx 1. Revised Gross Book Balance 2. Principal Payment (initial) 3. PPMT 4. rev GBB * Effective Interest Rate. 5.PSA$ These five items are interdependent on each other.

Effective Interest Rate	Gross Book Balance	Revised Gross Book Balance	Period	rev GBB * Effective Interest Rate	Remaining Periods	PPMT	Principal Payment (initial)	PSA%	PSA ($)
7.763260%	1,458,452	1458452	1	113223.3927	13	182130.32	(68907)	0	0
7.763260%	1,458,452	1389545	2	107873.9688	12	182130.32	(74256)	0	0
7.763260%	1,458,452	1315288	3	102109.2553	11	182130.32	(80021)	0	0
7.763260%	1,458,452	1235267	4	95897.01211	10	182130.32	(86233)	0	0
7.763260%	1,458,452	1149034	5	89202.49632	9	182130.32	(92928)	0	0
7.763260%	1,458,452	1056106	6	81988.26787	8	182130.32	(100142)	0	0
7.763260%	1,458,452	955964	7	74213.98012	7	182130.32	(107916)	0	0
7.763260%	1,458,452	848048	8	65836.15421	6	182130.32	(116294)	0	0
7.763260%	1,458,452	731754	9	56807.93589	5	182130.32	(125322)	0	0
7.763260%	1,458,452	606431	10	47078.83353	4	182130.32	(135051)	0	0
7.763260%	1,458,452	471380	11	36594.43567	3	182130.32	(145536)	0	0
7.763260%	1,458,452	325844	12	25296.10675	2	182130.32	(156834)	0	0
7.763260%	1,458,452	169010	13	13120.65919	1	182130.32	(169010)	0	0

Please refer to the attached excel sheet that will help for better understanding.

Any help would be highly appreciated.

[ChrisTX]

It would be helpful if you provide a starting workflow, and identify any challenges you have,

With all of the details you provided, it seemed like there may be a circular reference in one or two formulas.  Do any of the formulas, besides one that you did mention, use a value from a previous row?

Suggest you start out with a simple Formula tool, if all of the values you need are on the same row.

For formulas that depend on values from other rows, try the Multi Row Formula tool.

Write out a step-by-step plan you would follow if you had to make the calculations manually.  Then use the right tools to replicate the manual process.

Chris

[bmcclelland46]

I think for this dataset, it would be better to avoid the multi-row formulas. This is a loan schedule and at any point in time, you have all the information you need in each row to solve the problem without any need to reference another row (an amortization schedule is not the only way to figure this out). I would only introduce multi-row formulas if you foresee your payments being inconsistent across the loan period.

[DavidP · Accepted solution]

Hi @Susovan 

I've amended the solution I built for your earlier post to include PSA%. I've added documentation to describe my process.

It replicates the logic in the Excel example you provided, where the Row-1 values are used for Principal Payment Initial and PSA$ for rows other than the 1st row.

I don't know how to do this without using multi-row logic as @bmcclelland46 suggested, because everything then becomes circular. I'd love to understand how to do this but I guess that would need completely different logic for the loan schedule that I'm not clever enough to figure out.

Updated workflow attached.

[bmcclelland46]

@DavidP  I'll admit it is still a little hazy for me at the moment - I have screenshot of common formulas for the financial mathematics Actuary exam that shows how to calculate the principal, interest, etc. balances in a given period for an annuity. Been maybe 7 years since I took this exam haha.

Taken from site: https://faculty.atu.edu/mfinan/actuarieshall/Formulas.pdf