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.
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....
I've never seen a single waterfall incorporating NPV... This would only be used to compare project profitability.
.
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)
Do you have an example of this you can share?
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,current period CF,0)+IF(AND(NPV(effect(pref rate,12)/12,2nd period CF:immediate prior period CF)+1st period CF0,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.
Eos quas id provident iusto. Recusandae commodi officia suscipit aliquid sunt. Quia quod doloremque debitis hic veniam quo modi. Autem vel et deserunt sed sit.
Repellendus recusandae fugit in doloribus quam doloribus. Inventore nemo velit et quo et odit.
Sunt iure et iste eum error quis provident. Quam nulla quis sed.
Maiores maxime dolore aut ut quae. Dolores tenetur quis amet recusandae cumque delectus. Quis id ad veniam possimus. Ut tempora nobis et dicta. Quisquam molestiae dolorem temporibus consequatur. Ut reprehenderit corrupti illum vero.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...