Trouble with Multi Formula Tool and Formula

[wonka1234]

Hi,

I am creating an amortization schedule.

So when I use this formula, 

PAYMENT = FinancePMT([Semi annual], [REM BAL AMORT],[OPEN BAL],0 , 0)

but then I update the OPEN BAL column

So then I need to recalculate payment as it uses OPEN BAL and do the formula again. And this process essentially has to be repeated for each row..

Is there any workaround?

It is almost like they need each other. I need OPEN BAL to be accurate to calc the PAYMENT, but then Ineed the latest open BAL for the next row.

[Qiu]

@wonka1234 
I am having difficulty to understand the logic here. sorry. 😁
Maybe you can provide a sample set of data as input and output?

[wonka1234]

@Qiu 

Hi. 

formulas:

OPEN BAL:

Not sure if this helps. It is hard for me to explain. But when I update Open bal I need to re update the other formulas. But it then needs then Open bal again and then the formulas again. and so on. As its always being updated.

[binuacs]

@wonka1234 Can you provide some sample data and expected output result, that would be more easy to understand your use case.

[wonka1234]

@binuacs 

What im getting:

Expected:

REM BAL AMORT	OPEN BAL	PAYMENT	# of INT Days	INTEREST	PRINCIPAL	PRE-PAY	LIQUIDATION	TOTAL CF	CLOSE BAL
78	49,226,066	(663,932)	31	(64,989)	(598,942)	(68,196)	(353,309)	(1,085,437)	48,205,619
77	48,205,619	(658,193)	30	(61,589)	(596,604)	(66,768)	(345,912)	(1,070,874)	47,196,334
76	47,196,334	(652,476)	31	(62,310)	(590,167)	(65,362)	(338,625)	(1,056,464)	46,202,180

See I calculate the PAYMENT first.. and thent he OPEN BALANCE is recalcd. But then the Payment isnt correct as its not based on the NEW OPEN BAL.