Trouble with Multi Formula Tool and Formula

Question

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.

wonka1234 · Answer

@binu_acs

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,61977               48,205,619                 (658,193)                   30                 (61,589)                 (596,604)               (66,768)               (345,912)              (1,070,874)               47,196,33476               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.

binu_acs · Answer

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

wonka1234 · Answer

@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.