Line by Line execution of interdependent columns

[Sonali_25]

Hi,

I have an excel file where I need to create two columns. Each column has formulas that are interdependent on the other column.

The excel file looks like this -

Payment No.	PIK Interest	PIK Accrual	Ending Principle
-	-	-	110000000
32	4%	391111	110391111
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%
90	4%

"PIK Accrual" Column is calculated as (110000000*0.04*(32/360)) which equals to 110000000 whereas,

"Ending Principle" Column is calculated as (391111+110000000) which equals to 110391111.

That is, to calculate "PIK Accrual", we need to have the prior row of "Ending Principle" populated and to calculate "Ending Principle", we need to have the active row of "PIK Accrual" populated.

Please Note : Only the starting value of "Ending Principle" (i.e., 110000000) is given in the data set as shown above.

I have attached the excel file with the formula for your reference.

[afv2688 · Accepted solution]

Hello @Sonali_25 ,

Hope this helps. The way I solved it was using an iterative macro.

Cheers

[JoeS · Accepted solution]

Hi @Sonali_25 

I was just beaten to it by @afv2688 however, I went the more straight forward way (not necessarily any better) of two multi-row formulas.

[Sonali_25]

Thank you so much @afv2688 and @JoeS  for the quick help. Both the concepts are working absolutely fine.

Much Thanks !!