Repeating Formula + Multi Formula tool - Amortization Schedule

[wonka1234]

Hi,

Current output:

Payment is wrong after the first row.  As payment is calculated on the Open Bal before it gets Updated in the next tool.

Its almost like I need a payment formula to update at the same time the multi formula OPEN bal is updated. 

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

The issue is that Payment gets calculated before OPEN BAL.

OPEN BAL then REcalculated

Then Payment needs to be recalculated on the new OPEN BAL.

formulas:

Open Bal:

How can I loop this? I cant keep just adding these two actions and keep repeating this to get accurate results:

[apathetichell]

Hi - take a look at my solution to this thread and discussion of how to handle amortization modeling in Alteryx...

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Created-two-columns-that-use-each-othe...

[wonka1234]

@apathetichell 

Hi, is there anyway you can configure mine to yours?

Im having trouble configuring mine to your workflow.. Ive attached my workflow if you would like to take a look..

[wonka1234]

bump

[wonka1234]

bumppppp!

[wonka1234]

bump

[wonka1234]

@Qiu 

please help!

[Qiu]

@wonka1234 
Thank you for calling me.
Honestly I am not a financial guy....
@apathetichell seems giving good approach, maybe you can take a better look?

[apathetichell · Accepted solution]

@wonka1234 - sorry - Jewish holiday of Shavuot. This looks like an asset backed waterfall of some sort - can you sketch out some assumptions here:

1) Prepays are working in a linear matter with no month peaking? In mortgage pools I used to see a peak prepayment a few years into a pool - you seem to working on a straight line? There's also usually some more complicated period default modeling than straight-line.

2) Same for LIQ? Is this a liquidation style event? Is there a recovery that you look at? Does that matter?

assuming both LIQ and PPY are following a straight-line amortization you should really add them back to the monthly interest rate to get the proper monthly payment. Are you calculating days in period as 360? 365? actual? I'm confused because you have a daily rate but you also have a semi-annual rate. Which one is it? 

Basically you need to set your values at period 0. calculate the payment properly. append the payment to the principal/coupon/ppy/liq for beginning balance. Then you use a generate row to create your table (I think 79 or however many period you have.). My old workflow should work once you have the payment calculated and you have the periods properly set.

[wonka1234]

Thanks guys, I got it working with an interative macro, just got to loop through all the formulas!