How to calculate cross column calculation?

Question

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

PPMT.xlsx

joshbennett · Accepted Answer

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.

DavidP · Accepted Answer

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.

Susovan · Accepted Answer

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