I have to build a waterfall with monthly periods the goes Pref->ROC->Split->Split. Normally this would be pretty straight forward but the splits have "hurdles" that are "x percentage of total distributions" to GP type of things. Anyways my question is about the Pref accrual calculation.
The LP agreement says 15% per annum compounded quarterly, computed for the actual number of days for which the preferred return is being determined.
I used -(FV((1+EFFECT(15%,4)^(1/365)-1),Current Period-Past Period,0,Beginning Balance)+Beginning Balance
My CFO seems to think this results in compounding monthly and quarterly. Basically doubling up. because the beginning balance is the previous months ending balance which is principal + accrued pref.
Am I doing this correctly? Thanks.
Hey ChangedMyName, I'm the WSO Monkey Bot...do any of these help:
Hope that helps.
no, i don't think you're doubling up.
a quick check i always run, assuming you return the equity at some point, is to run a total line item that shows your equity contr plus all your pref months and then finally your ROE...mainly just to run an IRR check to make sure it's flowing correctly.
if your XIRR check = the EFFECT(15%,4) value, then you know your doing it correctly.
Could you please share the entire waterfall structure? Id like to practice some different scenarios and this one sounds interesting.
dont have it on this desktop...but i posted it to the forum a while back. There were some minor bugs in it as it was posted very late on a weekday unfortunately. However, I think it will more than answer your question posted here.
The one I just finished/am referring to is as follows
There are a couple other little funky things in there to account for land contributions and such but thats the high level.
Would love to see your final model excel if you don't mind PMing or posting. I didn't see it on another post.
I do something like this pref structure frequently, but 360 day year quarterly compounding. My LPA isn't clear about actual number of days pref is computed though.
I use the MOD formula to count number of days to say every 90 days, add the quarter of accrued interest to the capital account balance (for calculation, not the equity balance), if not just do simple daily interest method if last period is less than 90 days. Pretty sure I use the DAYS360 formula set to the European method in there somewhere too.
I don't think you're getting the right interest rate in this function. To get the interest rate, I think you'd just calculate the 15% effective rate at a quarterly compound and divide by 365.
(((1+15%/4)^4)-1)/365
When you raise to a fraction, it means you're discounting, if you go back to actuary formulas.
Suscipit optio voluptatibus molestias neque odit magni quia. Nam ut tenetur impedit aut. Sequi ut dolorem quod debitis molestiae iure itaque occaecati. At nobis corrupti eos quo nostrum aut.
Aspernatur molestias est deleniti vitae. Nihil minima voluptatem id error consequuntur sit ratione. Qui dolor laudantium et et repudiandae possimus sed. Quia et voluptas veniam qui.
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...