Accruing Pref to Match XIRR

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.   

Real Estate Modeling Course

  • Real-life RE Modeling Tests from actual Interviews
  • Various asset classes including multi-family, commercial and more
  • Huge discount - until more tests and cases added

Comments (8)

Funniest
Nov 18, 2021 - 4:56pm

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.  

I come from down in the valley, where mister when you're young, they bring you up to do like your daddy done

  • 4
Nov 18, 2021 - 8:40pm

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"

I come from down in the valley, where mister when you're young, they bring you up to do like your daddy done

  • 1
Nov 18, 2021 - 9:43pm

Yeah - I got touchy when someone implied that I suck at my one marketable skill

I come from down in the valley, where mister when you're young, they bring you up to do like your daddy done

Learn More

300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.

Learn more
Most Helpful
Nov 19, 2021 - 9:38am

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.

Nov 19, 2021 - 11:16am

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. 

I come from down in the valley, where mister when you're young, they bring you up to do like your daddy done

Nov 19, 2021 - 4:09am

Impedit magnam error deserunt dolor dignissimos. Quia asperiores nam dolore ut voluptatibus nihil. Nemo nulla assumenda incidunt et id. Nemo ex necessitatibus odio ut dignissimos qui provident velit.

Start Discussion

Total Avg Compensation

December 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (40) $360
  • Associates (237) $235
  • 2nd Year Analyst (146) $155
  • 3rd+ Year Analyst (34) $154
  • Intern/Summer Associate (107) $146
  • 1st Year Analyst (515) $136
  • Intern/Summer Analyst (397) $84