Bit of an in the weeds question, but has anyone successfully built a waterfall where they were able to accrue pref at a given tier to exactly match what XIRR spits out?
I'm currently running a waterfall with an 8.25% hurdle where pref is accrued on a daily basis and hits the capital account on the first day of the month, so using a January 1 accrual date as an example, the logic for the accrual schedule is (Beg. Bal * ((1.0825)^(31/365)-1)). The issue is that after accruing that way, once everything in the capital account is distributed at sale, XIRR is giving me an 8.244% IRR. This results in distribution splits getting fucked since those percentages are being looked up based on what the IRR is...so 1/10 of a bp has wrecked my functionality.
Has anyone had success getting your accrual schedule to marry up w/ XIRR or vice-versa? I'm guessing that the issue is related to accrual days used in each month (something like 360 days vs 365 or including an extra day in each month to account for day-of accrual), but haven't been able to make any changes that worked.
Found the issue and in case someone runs into the same issue in the future, the non-attention to detail having motherfucker who designed this formula is unfamiliar w/ leap years.
As you discovered, need to account for leap years. Also make your number of days dynamic by changing 31 to (Current Month Date - Prior Month Date).
It's the opposite - XIRR doesn't account for leap years...also, not hardcoding anything, dawg.
Know you're actually trying to be helpful, but spent ~3 years in an RX group so have used Excel a few times and if I was reading someone else's post where they spelled out date formulas, I'd immediately stop reading. For example, I assume/hope anyone would've stopped reading if wrote "={(eomonth(max(if(year range=current year,year range)),0)-min(if(year range=current year,year range))+1}" insted of "365"
Damn bro, three years in RX? That’s sick 😎
Rx is fuckin hard core bro, what was that poser thinking when he questioned you and your excel skills?
Yeah - I got touchy when someone implied that I suck at my one marketable skill
Lol whoa there my dude, defensive much? We agree with each other, you completely misinterpreted my post.
1. That was my point, XIRR doesn't account for leap years. Your pref accrual formula has to. Either you use 365.25 days in the formula or you have to write something that picks up whether the current year of your cash flow is a leap year (this is excessive, but I've seen teams do it).
2. Nobody said write a date formula... Do you not have a row at the top of your cash flow that has the date of each month/quarter/year? Pro tip my excel wizard friend, if you link to the cell that says 12/31/2021 and deduct the cell that says 11/30/2021, you will get the number of days between them. Alternatively DATEDIF.
The issue was that my pref accrual calc was picking up whether a year was a leap year, so had to make each year 365 days for pref accrual calcs for XIRR to agree w/ accrual schedule.
Quia reiciendis distinctio qui aspernatur aut et dolores. Dolorem blanditiis voluptate saepe hic illo quia.
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...