Amortization Table with Variable Monthly Payment

Question

Hi Alteryx experts!

Attached sample input & output files. Excel formula included.

This process involves different reporting & maturity dates, but let's take this one as example.

I am trying to create an amortization table with variable monthly payment.

Here are the given or easy to transform fields :

Maturity Date               : fixed

Rate A                           : fixed

Effective Annual Rate : fixed

Reporting Date            : consecutive months until Maturity Date

Record ID                     : extend based on number of Reporting Date

Amortization Months  : deduct 1 from previous value

I was able to create these fields using some multi-rows tool.

Here are the harder ones that might need iterative macro, where number of iterations depend on how many months between first Reporting Date and Maturity Date.

Opening Balance      : Previous Ending Balance

Payment                    : not fixed across the months, depends on Opening Balance which depends on other columns.

: formula in Excel : =PMT(Effective Annual Rate, Amortization Months, -Opening Balance,0,0)

Interest Portion        : Effective Annual Rate * Opening Balance

Principal Portion      : Payment - Interest Portion

Additional Payment : not fixed, depends on Opening Balance

: formula in Excel =-( ( (1- Rate A)^(1/12))-1)*(Opening Balance - Principal Portion)

Ending Balance        : Opening Balance - Principal Portion - Additional Payment

: Feed into next Opening Balance

repeats until Reporting Date = Maturity Date

Sample of final output :

Challenge here is multiple fields are dependent on previous rows.

Without Ending Balance, I was not able to calculate the other columns, and without the other columns, it is impossible to calculate Ending Balance.

Again, this process involves different reporting & maturity dates as well.

Looking for solution & advice with or without macro!

Thanks in advanced :)

Amortization Output Example.xlsx

Amortization Input Example.xlsx

Nilsend · Answer

Hi Can I see your final product. I am working on a similar case

pchong · Answer

I spent some time trying a few methods, and was able to get my solution using both iterative & batch macro.

Unfortunately I am not allowed to delete the post.

Feel free to reach out if you need help with related cases 😁

caltang · Answer

I've done a sample here.

Basically, your road block is your Opening Balance and your Ending Balance.

You will need to find a way to get these values at first before anything else. Once you settle this part, then the rest follows.

I've done one whereby your RowCount 1's Ending Balance becomes your RowCount 2's Opening Balance. The rest of the formulas fall in place no problem.

So now, the hard part is to make an Iterative Macro that encompasses this condition, and simultaneously loops until the Max RowCount is achieved.

I'll preface by saying that I'm currently stumped at that Iterative Macro stage, but the idea is to iterate my solution... you may even need two iterative macros and a chain application to get this going methinks.

Hope this sparks some idea for the community to engage as well.

Hope this helps somewhat.

-Cal

Amortization Example.yxzp