Calculating since inception returns
If you had 20 Multifamily properties ranging from 3 to 40 units all through out New York, and by if you have I mean the owner gave you the data for these properties, and he said give me since inception returns son - how would you calculate that in excel? What would you specifically do?
(I’m not his actual son he is my daddy in the other way)
Depends, what data do you have? If you have the monthly/yearly cash flows (including capital costs/debt/waterfall if applicable) of each property then you can aggregate that data to one whole portfolio. Then, you could easily do an =IRR calculation on the entire portfolio's cash flows and also something like a multiple on peak equity.
Hey I need serious help on this.
Below is just about verbatim what I was being told - some is off cause I can’t type that fast:
“So you need to get the personal IRR for Bill.
And his Unrealized IRRS.
You need to do since inception IRR but if it doesn't have full return of capital you will get an error.
Then go one step deeper and find out what would have been his IRR from a property level perspective, pre and post fees.
So, since Inception IRR.
What is HIS personal IRR in each of the deals.
You have to understand 2025 NOI and assume the sale of the property on december 31 2024
and his implied since inception IRR is going to be his intial capital contribution, and then all initial since inception, plus the assumed sale of the property, minus the current debt, multiplied by his equity percentage, which is why we need to go through the operating agreements and get out his promote.
And maybe a waterfall.”
Someone please explain this shyt to me. What does he want me to make?
I have made 23 forecast sheets, 23 property overview sheets, a property data sheet where by every other sheet in the workbook pulls from, and a portfolio overview sheet.
He gave me props on every single one aside from the data sheet which he said looked absolutely terrible because there is way too much going on it’s too complicated it’s blasphemous how could a client ever want to see all this.
I’m confused because without the data sheet the rest of the book can’t exist. All the other stuff he liked.
And I said this and asked can we just hide it whatever etc and he said no and really wants me to change the data sheet. He has ten times the skill and experience I do in excel so I’m truly confused on this. If it was that he just doesn’t like me that would be awesome but I don’t think that’s it so what could it be? Like, why would you ever care at all if you liked an entire workbook aside from the one sheet that made it all work why would you fudge it up and not just deal with that one sheet being hidden.
But guys that may as well be irrelevant. My Real concern is how to move forward.
My real concern is WHAT DO I DO?
For
SINCE INCEPTION RETURNS
But guys it’s nothing compared to my confusion on the since inception returns. Like I don’t know what he wants to see what does he even want to see ? Why does he say the word waterfall? Why imply we will do an equity waterfall. I know I know he was saying well read THEIR waterfalls right? Of course? But maybe just maybe this sadist means to have a waterfall done somewhere in here. If you think so and understand that somehow tell me.
But above and beyond all go rookie with me
Tell me literally what the headings should be in the godamnit spreadsheet start in whatever cell you want.
Like for the since inception returns I don’t know what to do SOS xoxoz
Sold properties: Calculate IRR and equity multiple based on equity in at acquisition and equity back at sale.
Current properties: Figure out an in-place NOI for each property, apply a market cap rate to each of them and add a 25-50 bps buffer, then reduce that value by 2% to account for sales fees. Then take that number and plug it in as a hypothetical sale in a certain year, and use that to calculate your IRR and MOIC.
Then you'll have IRRs on all properties. Aggregate those net cash flows to calculate an overall IRR and MOIC.
This is an odd request and I can't tell if it's a s**tpost or not.
It’s not a shit post if that means trolling nope I am asking a question about my job in the right board right place everything all checks out regardless of what even god if he exists would say
so thanks for answering I appreciate it truly
Here is what I was told verbatim
"So you need to get the personal IRR for William.
And his Unrealized IRRS.
You need to do since inception IRR but if it doesn't have full return of capital you will get an error.
Then go one step deeper and find out what would have been his IRR from a property level perspective, pre and post fees.
Since Inception IRR.
What is HIS personal IRR in each of the deals.
You have to understand 2025 NOI and assume the sale of the property on december 31 2024 and his implied since inception IRR is going tobe his intial capital contribution and then all initial since inception plus the assumed sale of the property minus the current debt multiplied by his equity percentage, which is why we need to go through the operating agreements."
If you and anyone else would chime in exactly what you would do in my shoes I would be grateful.
Comment has been deleted
This helps a lot I don’t understand how to factor in debt and the future possibility of the returns it makes my head blister.
Right now I have over 70 tabs, a set for forecasts a set for since inception returns a set for just a break down of each property
It’s a nightmare
There is so much data and it’s messy because he has 5 or 6 partners but Small very small compared to his equity but they all have different loans and some he swapped partners and others management companies so it’s little slices of info here and there
Father let me focus and stop whining and get out a clear question
What would you suggest I make since inception returns look like? I have had it suggested I can do a cash flow statement monthly for all years since ownership, but the advice stopped there and it wasn’t complete
So I will do all that
Which for the first time I encounter something in excel to my disbelief I think has to be done by hand
Like no formulas can help me escape it when the line items vary so much from year to year
It will be 55 to 80 line items a year for a few years then 45 for two years then 10 then 3 (YES 3) then back to 50
It just has to be done by hand then right ? All line items accounted for and put to the left then go out all the years and months and enter it by hand
So after all that though investor distributions I have separate attachments for those what do I do where do those play into the work I just described which has owner distributions and equity line items in itself.
I’m looking at the investor distributions attachments and then the investor distributions line items in the cash flow statement I’m making and I’m looking at the multiple partners and I’m wondering how one sorts this out behind asking over and over and over for all the details
Lastly
Say piece this shit together and get that all correct
So now it’s all correct data going out years and years and years and I got every line item right and amount right
And distributions separated correctly
Okay so where’s the since inception returns? What do I IRR and equity multiple?
Yeah it's not great when there are too many tab to keep track of because of the guy wanting to know so much detail and yet doesn't know how to model it himself..
1. Regarding the debt, all you have to do is get the debt balance of all the properties you currently have in the portfolio (these should have come down steadily unless were either interest-only loans or rolled-up interest facilities), and then you can simply copy and paste the last monthly interest debt to forecast the next 3 months, and then put a repayment of all debts in Dec-24. This should happen in conjunction with the sale of the portfolio.
2. Regarding contributions, you can assume 100% in the above lines, before starting to focus on his personal IRR. This means, all those negative cash flows that are paid with equity on a portfolio-level CF, and then to calculate his personal IRR, I hope it should be as easy as looking at his company's bank statement, and extract the relevant contributions and putting them down as inputs, since these are theoretically actuals (can't use formulas for these obvs).
3. On the distributions point, these are most probably dividends being paid out to the owner himself, so it should still be accounted for within the personal IRR calculation (not property/portfolio-level CF).
So in summary:
The partnership model should indeed look like a monthly CF, and should look like:
- Total Levered Free Cash Flow (this comes from the total FCF of all properties combined, which should include the debt draw amount, the deduction of interest and repayment of the outstanding debt, otherwise the contributions would show all partners paying 100% of the property investment with equity, as well as the exit price in Dec-24)
- Contributions from all partners (all negative numbers that appear in the equity CF line in your portfolio model, which is essentially everything that is not covered by the debt draw)
- Distributions (in the form of dividends, not preferred return as this comes in the next line)
- Any preferred return distributions/promotes which should be paid out post dividend income (because these should already be incorporated in the distributions handed out to everyone)
Once this is done, you can focus on his personal IRR, which is essentially the returns as a proportion of his contributions (say 10%), plus any promotes and dividends handed out to him. The promotes should include the profit he makes on sale of the portfolio (this should be obvious right, otherwise you don't have promotes, since these are driven by the IRR a portfolio has achieved once it has been sold).
This is what it should theoretically show in the model you are trying to create.
It's also important to not forget any cash reserves in the SPVs, which are essentially dividends that are still left to be handed out post sale.
Hope it clears up some of your questions.
Aut inventore dolor amet sint reprehenderit ratione. Nemo placeat ut non repudiandae cum ut.
Nesciunt perferendis voluptas dolorem corporis. Totam qui dolore velit voluptate. Quo eligendi id animi saepe modi. Nihil et aut aliquid animi sunt. Unde dolores vitae mollitia aperiam iusto quo autem.
Natus veniam nam sed voluptatem autem et. Aperiam aut doloremque blanditiis aliquam sapiente tenetur. Modi sed et et dolore recusandae. Alias eum incidunt voluptas exercitationem consectetur dignissimos. Dignissimos ipsa soluta eveniet reprehenderit quo recusandae.
Ex molestiae placeat rerum quo voluptatem. Repellendus cum consequuntur sunt. Delectus non vero fugit aliquid veniam animi. Quos illo sit dolores aut corporis quo provident. Laudantium consequatur sint facilis dolores consectetur. Sit doloremque non debitis.
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...