Alteryx how to calculate the next 2000 values based on Row 1 and a Fixed Rate decrease

[wwc008love]

Hi Alteryx Community.  I am looking for some help to generate next 2000ish values based on the first row and a Fixed Rate decrease.  I was thinking about using Multi-row formula to implement the logic but it didn't work out as I expected. 

I have an Excel spreadsheet, the tricky part is the next value is calculated based on the previous row's value.  The goal is that I want to see all the values until the balance goes down to zero. In Type field, there are two categories, Retail and Wholesale.  Each category has a starting value and its fixed rate change, i.e. -2.0% and -1.75%.  (The Green row is the starting value, the Yellow rows need to be populated by Alteryx)

I really appreciate anyone who can help me to figure out how this could work.  (See attached file) Thank you. 

[apathetichell]

can you confirm that your principal payment is fixed at that percentage of the beginning balance?

[wwc008love]

Thank you. The period-over-period principal change is not a fixed amount because the starting balance running off based on a fixed rate of -2.00%.  Look at the Prin Delta column, 50 to 51 the prin. changed $60M,  51 to 52, the prin. changed $58.8M. However, it's a fixed Rate change period over period.  

Let me know if this makes sense to you. Many thanks. 

[apathetichell]

Hi - No. This doesn't make sense to me - sorry. 2% subtracted from the previous period will go on infinitely - this is a mathematical impossibility.

You either need to have a set payment (ie - fixed/static payment) or a fixed period (360 months for example) - which you use to calculate payment.

[wwc008love]

Agreed. To be more specific, Essentially, this formula takes previous period balance and time 98% until the balance is fairly close to zero. 

The goal is not to get to zero because it's infinite but to simulate a runoff schedule that the balance gets really close to zero. 

[apathetichell]

I've never seen a run off schedule like that. Usually you start with term and all-in-interest rate. You then calculate payment. Then you build a runoff schedule.

[danilang · Accepted solution]

Hi @wwc008love 

While I don't have as much Accounting experience as @apathetichell, I do know a future value problem when I see it.  The generic solution is

FV=PV(1+i)^n

You looking for the period where FV <=.98*PV.  Substituting this in we get 

.98*PV<=PV(1+i)^n

You can code this in a Generate Rows tool to create rows until the final condition is met

Where limit is how close to zero you want to get, in your case 98%.  Note that the conditional expression needs to be >= since the generate tool continues until the expression is false.

The results look like this 

Technically, you don't need the balance in the equation at all.  You can just use 

.98<=(1+i)^n

The result is only dependent on the interest rate and period

Dan

[wwc008love]

Thank you for the elegant solution.  I was able to figure out using the Python tool as well. Mathematically, this is a infinite calculation but I am simply looking for the first period the balance goes down to zero. (if we rounded to nearest dollar)

Attached my final workflow and compare to the solution.  Thank you both.