Multi Family IRR Calculator

[Trent Werner]

Does anyone have an IRR calculator for Multi Family syndications?

I am looking for recommendations for an IRR calculator that can be used to calculate asset performance over the course of us owning the property, and calculate investor returns.

[Zachary Ware]

Hey Trent, 

I would recommend looking into learning how to model out a deal. IRR is one of many return metrics that investors look at but all of these metrics rely on assumptions. Picking the right assumptions can be a skill in itself but can also make a deal look better than it really is. Specifically with IRR, the earlier the sell of a property the better the IRR looks. I would recommend looking at Breakintocre by Justin Kivel. If you have your cashflows, you do the IRR in excel with an IRR or XIRR formula.

[Taylor L.]

Michael Blank's Syndicated Deal Analyzer has been popular in recent years.

You're not looking for an IRR calculator, rather you're looking for an underwriting tool. IRR is one of the outputs of an underwriting tool, but there are several other important metrics to take into account as well. Cash on cash return, DSCR, and Equity Multiple are a few to consider as well.

[Chris Seveney]

@Trent Werner

Check out getrefm.com I am not affiliated with them but they have great courses on building models

The biggest part of any model is the data - garbage in = garbage out so whichever model you use the key is the accuracy of data that goes into it

[Cory Carlson]

Check out A.CRE; Adventures in CRE is a site with financial analysis/modeling, education opportunities and is a great resource for larger more astute property types and developments. *I have no affiliation.

[Evan Polaski]

@Trent Werner, if you need an IRR calculator, use excel: =IRR(inputs) or =XIRR(inputs, dates)

Many investor portals will also do this for you/your investors directly, as you are processing distributions to your investors.  

Are you really looking for a way to calculate the ongoing IRR of an active deal, as your post suggests? If so, to look at this on an ongoing basis, with any sort of realistic output, you need to know the total capital invested, all distributions paid and date they were paid, AND run a mark-to-market valuation to ultimately guesstimate your sale proceeds that would go to your LPs, if you were to sell the asset when you are running the calculation.

[Chris Shepard]

Hey guys, I work with Trent and we are trying to model what the returns would look like if we sold at a certain price.

The waterfall from our PPM is:

Cash Flow

Actions

Tier	Template	Description
1	Preferred Return	7% Annual Preferred Return to contributing members
2	Split	80% Upside to Class A and 20% Upside to Class B thereafter

Capital Event

Actions

Tier	Template	Description
1	Return of Capital	Return of Capital to contributing members
2	Catch Up	7% Catch Up to Class B
3	Split	80% Upside to Class B and 20% Upside to Class A up to 20% IRR for the Investment overall
4	Split	50% Upside to Class A and 50% Upside to Class B thereafter

Attached are all the cashflows for the deal.  We are working on creating a sheet for one of our employees to fill out so that we can look at different scenarios... the last cashflow is based on a projected sale with 6% of closing costs taken out.

What we need is help to identify the waterfall split amounts and what each investor would be returned and what their individual IRR is going to be.

https://docs.google.com/spreadsheets/d/1SE0s0FecxlGMKcLgKc6dNRJF8nwaCU_8iMrCbTD7vV4/edit?usp=sharing


Thanks for your responses!

[Ryan Peters]

Trent and Chris - as others have said, this wheel has been invented many times already and there are some great templates available online.  Someone mentioned Michael Blank's Syndicated Deal Analyzer and that would certainly be able to do what you're trying to do.  Another one I like is available for free from Lone Star Capital at lscre.com/resource/underwriting-model.  You could also build your own but you would need some familiarity with Excel for that.  I build financial models in Excel for a living and would be happy to jump on a quick call to point you in the right direction if you'd like.

[Chris Shepard]

We use Michael Blanks Syndicated Deal Analyzer.  I understand the underwriting model has been invented.

We have a property that is listed for sale and all the cashflows that have been paid to investors and the waterfalls that are from our PPM.

How do you figure out the exact amount that goes to an investor and the exact amount that get's paid to a sponsor based on the waterfall levels based on the cashflows that have already occurred?

If it is so simple, would you mind walking me through it... I have all the data in the spreadsheet above and the waterfall structure in the post.

Thanks!

[Chris Shepard]

I found the answer... here is the model for everyone who would like it:

https://www.asimplemodel.com/insights/determine-the-proceeds...