Equity Waterfall Calculation using XNPV

JL1900's picture
Rank: Chimp | 8

I have been working through a waterfall distribution and for the hurdle calcs I came across the following formula:

ROUND(-XNPV(X,B2:M2,B1:M1)*POWER(1+X,(DATEDIF(B1,N1,"d")/365)),2)

Whereby:

X = The Target IRR
B2:M2 = range of string of cashflows
B1:M1 = range of dates
B1 = investment (start) date
N1 = final payment (end) date

I am however a little stumped as to why XNPV is used in this instance? Why are the cash flows discounted before they are compounded?

If someone could explain to me it's role in the calculation it would be a great help, I'm fairly new to all of this & (very) slowly getting there. Thanks for taking the time.

Comments (7)

Jun 28, 2019

What line of the model is this for? Meaning, what is the heading, what is the value it is meant to calculate? That said, there should't be any NPV calculations in a waterfall anyway....

Jun 28, 2019

I've never seen a single waterfall incorporating NPV... This would only be used to compare project profitability.

Jun 29, 2019

I dont think I can tell why without looking at it. That being said in response to the two other comments, we always use NPV in our waterfalls. Just how we structure our OA, for our IRR driver I calculate the cash flow from the NPV of the cash flow.

Jul 5, 2019

its just another way to skin a cat, so to speak. you can take the previous accrued value, and add to it a period worth of compounding interest..which typically looks something like previous balance * Effect(%,n)/365*(end date - beg date). You can also run it using a FV formula. which will start with your prior balance, number of months in the period you are calculating, and the effective interest rate...i.e. to return the future/ending interest that is compounded. The XNPV approach is simply the reverse version of this FV approach. This concept is just taking a period of future cash flows, discounting them back to the current period based on the compounded interest rate that was used, then layers on the effective rate, to arrive at an ending balance that you must achieve in THAT period to move on to the next step.

again, many many ways to get to the same place in waterfalls. me personally, I prefer the above version...and to set my line items for pref up like this:
1)beginning balance (if first period, 0...if not then prior period's #4 aka ending balance)
2)preferred return due in current period (use the above formula mentioned here)
3)distributions (pay, with whatever available, up to and including the prior two bullets)
4)ending balance (=1+2-3)

    • 1
Jun 28, 2019

Do you have an example of this you can share?

Learn More

Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.

Jul 5, 2019

without creating a new excel sheet, below are the formulas and ideas that all lead to the same result.

lets say you want to run a monthly compounding pref rate...use effect(pref rate, 12)...for a quarterly use effect(pref rate, 4)...annual=1 semi=2, etc etc etc.

FV FORMULA: = -(FV((1+EFFECT(pref rate, # compounding periods aka 12))^(1/365)-1,(current period - last period),0,(beg equity bal + accrued and unpaid pref bal))+(beg equity bal + accrued and unpaid pref bal))

% GROWTH RATE FORMULA: =equity beg bal * ((1+EFFECT(pref rate,12))^(1/12)-1 OR beg equity bal * effect(pref rate, 12) / 365 * (current date - starting date)

FV USING NPV FORMULA:
=IF(NPV(EFFECT(pref,12)/12,2nd period:current period CF)+1st period CF<0,current period CF,0)+IF(AND(NPV(effect(pref rate,12)/12,2nd period CF:immediate prior period CF)+1st period CF<0,NPV(effect(pref rate,12)/12,2nd per CF : curr per CF)+1st per CF>0),FV(effect(pref rate,12)/12,prior period month #,,NPV(effect(pref rate,12)/12,2nd per CF : current per CF)+1st per CF),0))

in my opinion using the first FV formula is best. if it's non-compounding then you just need to do rate/12*beg balance NOT beg balance + accrued pref. I build my waterfalls to have a dropdown box that says compounding or simple interest...then in the pref due line item i run an IF statement with formulas for each scenario. ACRE has some good options, but if you look at their check %...it's a good bit off...for example when I looked at it, the rate was 10% and they had 10.05%...but still a good place to look at a strong layout template.

Jul 11, 2019
Comment