IRR calculation

[cakivibes]

I have dates in one column and amounts in one column, and I want to calculate the last cash flow needed to achieve the IRR of 12% in Alteryx. Does anyone help with the workflow?

[DanielG]

@cakivibes  -- looks this this previous post might be of assistance to you.  Solved: Calculate IRR in Alteryx - Alteryx Community

[cakivibes]

It's simply IRR calculation; I need to use goal-seeking with IRR to find the last cash flow to meet certain IRR

[DanielG]

@cakivibes 

Sorry.  I have no idea what any of that means...  :)

But it sounds like you should be using multi-row as a running IRR calc to figure out when you hit your threshold

[apathetichell]

Are you looking at IRR or XIRR? XIRR - timing matters. IRR - timing does not matter.

[Bren_Spill]

@cakivibes - attached is an iterative macro that will handle increases in XIRR. You can set your desired XIRR in the macro interface:

You'll need to test this thoroughly but seems to be working.

Thanks!

[apathetichell]

If you want to use a non-iterative macro approach - you can use MATH. To figure out your daily period interest rate you'd use LOG(1+[required_return])  - this is key because the required return is what you want the compounded rate to be - not the APR/quoted rate. The actual APR equivalent will be lower because of MATH.

Next you can isolate your date of issuance and use a multi-row formula to calculate the number of days since issuance. You can throw on the date of your baloon payment via a union tool. 

Now you are aready to start discounting - I used pow((1+([required_return]/365)),[DateDif]) - which looks at 1/365th of your required annual rate (which is basically what your compounding to).

Now we can take the discounted values via the discounting terms and sum them. We have the discounting rate for our last value and we have the total gap which that last value has to be discounted to. Working backwards we can find the remaining value by multiplying the discount rate * the negative gap amount. I got 

7809437.524

vs

7813653

and an XIRR of 

0.119980298

[apathetichell]

@cakivibes - can you mark @Bren_Spill 's solution correct ( and test mine)?