Automating a water fall XIRR return in excel

  • Sorry for the cryptic question I am not sure how to word this...


    I have attached an example of a typical real estate deal. In the example scenario I show an investment and a stream of cash flows. The investor covers the equity and receives 100% of the cash flow until he reaches an 18% XIRR, after that point he only receives 80% of the cash flow until he reaches a 20% XIRR. Once both preferences are met the investor receives just 50% of the cash flow.


    The real question I have is....how do you automate target XIRR returns with a formula?
    The excel question i have is....Is there a way to use goal seek within a formula?


    Thanks,


    -Dude

  • Re: Automating a water fall XIRR return in excel


    Solved! with this spreadsheet!


    Special thanks to Globaliconnect


    This uses the "FV" formula combined with the "NPV" or "XNPV" formula to automate a cash flow plug for a targeted IRR or XIRR return. You also need to convert your hurdle rate into monthly or quarterly increments (depending upon your model) to use as the "rate" in both the FV and NPV formula and then create an if statement to apply the solution.



    Thanks!
    -Dude

  • 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

  • Re: Automating a water fall XIRR return in excel


    This reminds me of conversations I have with myself daily... Grats on getting everything straightened out.

  • Re: Automating a water fall XIRR return in excel


    Great spreadsheet answers; however, it doesn't solve the XIRR question. The formulas use IRR but not XIRR to plug in the number needed in order to achieve a desired XIRR. Using XIRR vs IRR is important because not all distribution dates are done at the end of the month, some are in the middle, the fifth day, the third day, etc; hence the need for XIRR.

  • Re: Automating a water fall XIRR return in excel


    TheDude,


    your spreadsheets were impressive. I am trying to do something very similar with the xirr function. I am trying to automate the xirr function to report on the performance of my portfolio. however, it is manual in that I have relink and update the xirr function each quarter. I basically pivot and recalculate every time. any help would be much appreciated.


    thanks,
    DALPHA

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!