Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to calculate cross column calculation?

Susovan
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 RateGross Book BalanceRevised Gross Book BalancePeriodsrev GBB * Effective Interest RateRemaining PeriodsPPMTPrincipal Payment (initial)
7.763260%1458451.6614584521113223.392713182130.32(68907)
7.763260%1458451.6613895452107873.968812182130.32(74256)
7.763260%1458451.6613841953107458.679211191671.98(84213)
7.763260%1458451.6613742384106685.694910202620.49(95935)
7.763260%1458451.6613625175105775.72519215968.92(110193)
7.763260%1458451.6613482586104668.80878232513.31(127845)
7.763260%1458451.6613306077103298.49177253507.32(150209)
7.763260%1458451.6613082438101562.29086280963.75(179401)
7.763260%1458451.661279050999295.991465318350.08(219054)
7.763260%1458451.6612393981096217.654834372229.96(276012)
7.763260%1458451.6611824391191795.840223456867.43(365072)
7.763260%1458451.6610933801284881.936462611144.41(526262)
7.763260%1458451.669321891372368.2691511004557.46(932189)

 

Any help would be highly appreciated

 

3 REPLIES 3
joshbennett
11 - Bolide
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):

 

DextersLaboratory_0-1596705682119.png

 

Workflow attached for reference. 

 

DavidP
17 - Castor
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.

 

DavidP_0-1596709830230.png

 

Susovan
5 - Atom

Thank you for your try.

 

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.

 

Please find the attached excel sheet that will help you to understand my query.

 

Thanks a lot

Labels