Waterfall Calculation Using XIRR

Question

How do I calculate the payment required to achieve the specified return.

Answer 1

I wrote:

It might help if you provided a numerical example [....] It might also help if you define your terms. [.... do] you mean "waterfall payment" as defined by this example (click here) [....] Bottom line:  the better and more detailed your question, the more likely it is that you will get a quick and useful response.

Two other people seem interested in this question.  Is someone going to provide some detail?

FYI, a google search found a number of similar questions in other forums in the past.  Most have no answers.  But as you will see, the details can vary greatly.  Basically, each is a different question with a different solution.

This one (click here) demonstrates the level of detail I would look for in a question.  And it claims to provide a solution.  (I have not vetted it myself.)  No idea if the details are similar to what Robert is asking.

Answer 2

It might help if you provided a numerical example, ideally in the form of a worksheet so that we have a common understanding of cell references.

It might also help if you define your terms.  Your subject line refers to a "waterfall calculation", but you ask about "the" payment.  If you mean "waterfall payment" as defined by this example (click here), it is unclear which required IRR(s) you are referring to:  for the total payment, or for the individual allocations of the principal and interest.  That is, each recipient might have his own IRR requirement.

Also, the use of Excel XIRR per se might be unnecessary.  If the payments are at regular intervals, it would be simpler and perhaps more correct to use Excel IRR -- or Excel RATE, if the payments are also constant.

Bottom line:  the better and more detailed your question, the more likely it is that you will get a quick and useful response.