Equity Waterfall Calculation using XNPV

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.

 
Most Helpful

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)

 

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 CF0),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.

 

Sint tempora nam ea a. Ipsum maxime nemo et dolorum. Dignissimos quia quia unde quia ex possimus. Assumenda doloribus similique enim sequi voluptas consequatur.

Sit qui nesciunt nostrum asperiores nemo. Sunt omnis cumque qui. Reprehenderit rerum alias aliquid blanditiis.

Doloribus ad facere aut rerum sapiente in. Placeat nemo et at. Iste quibusdam qui veniam deserunt quo ea cum.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”