Re: Automating a water fall XIRR return in excel

I have attached a revised model that shows quarterly timing. I am also revising the explanation.

To automate a waterfall return you simply use the *NPV* of the current period and the *NPV* of the previous period to find out when the current value is positive and the preceding value is negative. Since the discount rate is essentially the cost of capital you will not have a positive Net Present Value until you reach the targeted *IRR*.

Using the *IF(and())* formula you will plug in the *And()* formula for both the *NPV *of the current period total cash flow as well as the *NPV *of the previous periods *NPV* using the ">=" and "<=" arguments. This results in finding the correct period to plug the correct value to hit the targeted return.

Once you have found that period the *FV *or Future Value formula plugs the right amount to hit the targeted *IRR *or Internal Rate of Return. Should you use the *NPV *formula with the correct plug for the targeted return from the *FV *formula then you would have a zero *NPV*.

Thanks,

-Dude