alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## How to calculate cross column calculation?

5 - Atom

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 Gross Book Balance + Principal Payment (initial)...

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

Formula for (PPMT) used in Alteryx : PPMT(([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

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

 Effective Interest Rate Gross Book Balance Revised Gross Book Balance Periods rev GBB * Effective Interest Rate Remaining Periods PPMT Principal Payment (initial) 7.763260% 1458451.66 1458452 1 113223.3927 13 182130.32 (68907) 7.763260% 1458451.66 1389545 2 107873.9688 12 182130.32 (74256) 7.763260% 1458451.66 1384195 3 107458.6792 11 191671.98 (84213) 7.763260% 1458451.66 1374238 4 106685.6949 10 202620.49 (95935) 7.763260% 1458451.66 1362517 5 105775.7251 9 215968.92 (110193) 7.763260% 1458451.66 1348258 6 104668.8087 8 232513.31 (127845) 7.763260% 1458451.66 1330607 7 103298.4917 7 253507.32 (150209) 7.763260% 1458451.66 1308243 8 101562.2908 6 280963.75 (179401) 7.763260% 1458451.66 1279050 9 99295.99146 5 318350.08 (219054) 7.763260% 1458451.66 1239398 10 96217.65483 4 372229.96 (276012) 7.763260% 1458451.66 1182439 11 91795.84022 3 456867.43 (365072) 7.763260% 1458451.66 1093380 12 84881.93646 2 611144.41 (526262) 7.763260% 1458451.66 932189 13 72368.26915 1 1004557.46 (932189)

Any help would be highly appreciated

3 REPLIES 3
11 - Bolide

Hi @Susovan

Not sure if I am 100% understanding your question, but let me know if this is in line with what you are trying to do (or if not, where I am misinterpreting you):

Workflow attached for reference.

17 - Castor

Hi @Susovan

This problem took me back a long time to my school day substitution maths to eliminate the circular reference problem - as you mentioned, the 4 things we're trying to calculate all depend on each other.

Except for Row 1 that is.

Your Alteryx formula for PPMT should be [Revised Gross Book Balance] instead of [Gross Book Balance]

To use effective interest rate I had to remove the % and then divide by 100 in all the formulas.

So If Periods = 1:

[New Revised Gross Book Balance] = [Gross Book Balance]

[New PPMT] = [New Revised Gross Book Balance]*[Effective Interest Rate]/100/(1-(POW(1+[Effective Interest Rate]/100,(-1*[Remaining Periods]))))

[New Principal Payment initial] = [Gross Book Balance]*[Effective Interest Rate]/100 - [New PPMT]
= [Gross Book Balance]*[Effective Interest Rate]/100 - [Gross Book Balance]*[Effective Interest Rate]/100/(1-(POW(1+[Effective Interest Rate]/100,(-1*[Remaining Periods]))))

For other periods:

The key to the whole thing is that we now use the Principal Payment initial and Revised Gross Book Balance from the previous row/period.

[New PPMT] = [New Revised Gross Book Balance]*[Effective Interest Rate]/(1-(POW(1+[Effective Interest Rate],(-1*[Remaining Periods]))))

[New Revised Gross Book Balance] = ([Gross Book Balance]+[Row-1:New Principal Payment initial])

[New Principal Payment initial] = [New Revised Gross Book Balance]*[Effective Interest Rate] - [New PPMT]
= ([Gross Book Balance]+[Row-1:New Principal Payment initial])*[Effective Interest Rate] - [New PPMT]
= ([Gross Book Balance]+[Row-1:New Principal Payment initial])*[Effective Interest Rate]/100 - ([Gross Book Balance]+[Row-1:New Principal Payment initial])*[Effective Interest Rate]/100/(1-(POW(1+[Effective Interest Rate]/100,(-1*[Remaining Periods]))))

I've used the prefix New in my calculated fields so that you can compare them to the original fields from your Excel file.

Workflow attached.

5 - Atom

rev GBB * Effective Interest Rate = Revised Gross Book Balance * Effective Interest Rate (rev GBB * Effective Interest Rate will not be same in all periods as it is depending on Revised Gross Book Balance which dynamic.