This is probably a question that makes me sound like an idiot - Why am I getting different equity multiples with monthly and yearly cash flows? I figure this is because of the negative cash flows throughout the holding period? If so, should I use the multiple calculated with yearly cash flows / monthly?
| +30 | Uncertain of what To Do Next | 5 | 2d |
| +29 | What should we be buying? | 24 | 1h |
| +28 | Capital Restricted Developer | 2 | 2d |
| +27 | Basis play / value-add office in high-growth markets - value trap or generational buy? | 10 | 4d |
| +23 | Warning: J. Shaw Recruiters | 5 | 2d |
| +20 | Laughable Job Posting | 3 | 1d |
| +18 | Due Diligence - ALTA Survey | 3 | 2d |
| +15 | Seeking Advice: How Do You Get Value from ULI Membership? | 2 | 5d |
| +11 | Where are free agents signing? | 1 | 1h |
| +11 | PGIM Real Estate Equity vs. Private Credit | 3 | 5d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
Definitely a valid question - my team was debating the best way to calculate EM just the other day. How are you doing the calcs? And yes it's likely due to the negative CFs throughout your hold period.
For monthly cashflows: I'm summing up the positive CFs (your total inflows) and the negative CFs (your total outflows) throughout your hold period and then dividing them to arrive at an equity multiple (you'd want to take the absolute value of this result i.e. remove the negative sign)
For annual cash flows: I'm summing up all the CFs in Year 1, Year 2 etc. and dividing them with the initial outflow in Year 0 (at acquisition).
Annual method arrives at a different EM vs. the monthly method *if* you have negative CFs during your hold period (Year 1, 2 etc.). This is because you're essentially doing a "Total Profit / Initial Outflow" in the annual method vs. "Total Inflows / Total Outflows" in the monthly method (total profit = total inflows - total outflows, while total outflows = initial outflows + negative CFs)
I prefer the monthly method since you're using the totality of your equity investment (initial acquisition price + all the capital activities that you're doing i.e. leasing, capex etc.) as the denominator - works well for deals with a heavy value-add component. Hope that makes sense
Could you please explain to me why you’re using the one method for the monthly and the other for the annual CFs? Sorry if this is very obvious
Hey great Q - it’s unnecessary to use two methods, but the different results is a consequence of how the annual cash flows are derived. I’ll do my best to explain
Most models use monthly cashflows for the basis of return calculations. However some firms, including mine, “roll up” the monthly cash flows into an annual figure for summary purposes (Y1, Y2, etc.)
How this roll up works is that for each monthly CF you assign a specific year number. So for month 3 that would be year 1, month 15 would be year 2, and month 28 would be year 3 - you get the point. You can do this in excel using a quotient or roundup function (I personally take month #, divide by 12, and use the roundup function to round up to the the closest whole number, which gives me the intended result)
Then you typically use a sum-if function which says “sum all the CFs that are tagged with the year number 1, 2, etc.” - now the key reason why the annual cashflows yield a different equity multiple is because of the negative CFs that arise during your hold period.
Remember that the monthly method takes “total positive CF” and divides by “total negative CF”, while the annual cashflows take “total profit” and divide by “initial outflow” — this is different for the reasons explained above. and because you used a sum-if function, your annual cashflows (the numerator) is by definition a TOTAL PROFIT figure for that year (they net out the negative CFs since you’re summing both positive and negative CFs with the relevant year tag)
You can obviously override this by using SUMIFS instead, which allows you to add another criteria that says, in addition to summing up cash flows with the intended year tag, only sum cashflows that are-nonnegative” - but this is additional work and it can all be avoided if you just link the return results from the monthly CF tab.
I hope that makes sense! didn’t mean to go too deep. best to work through a real example and see how the #s change.
If you have negative cash flows in your monthly model that don't show up in the annual, then that's your reason. Your monthly model is implying that you're contributing more equity.
We work around this in our monthly models through having input rows for cash management (e.g. holding back cash flow for a few months to cover an upcoming TI that would be a negative monthly CF even if the annual CF covers it).
Blanditiis voluptates nulla nisi dolorum odit. Mollitia veniam amet repellendus aliquid veritatis.
Facere impedit enim reiciendis assumenda. Ipsum delectus blanditiis sapiente suscipit. Doloribus neque minima sed et. Minima aspernatur architecto cumque nobis nostrum cupiditate. Velit qui sint unde sint sint in. Amet veritatis omnis molestiae facere in.
Modi deserunt est voluptas dolor quod facilis placeat. Qui recusandae est omnis nobis quod.
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...