We’ve spent a lot of time here discussing and sharing different real estate models, and we’ve received a ton of feedback from our readers as a result. In some of that feedback we’ve received, several people have asked what period and date rows I prefer to use. In response to that question, I thought I’d share with you the period and date rows I like to use at the top of my monthly cash flow reports, and give you the formulas I use to build those rows. As a companion to the post, I’ve also recorded a quick video that shows you how to build them yourself and I’ve uploaded a quick Excel file for you to use in your own projects.
Quick note before we begin: I’m not sure if “Period and Date Rows” is the proper term for this, so let me know if you refer to the area above your cash flows by another name. I don’t recall actually ever discussing this component of a real estate model with anyone in a regular conversation – who would actually talk about this stuff in a normal conversation anyway! – so there may be an official name for this and I’ve just never used it.
I like to use four rows/components to identify the period or date of a cash flow:
- The ‘Year’, which is essentially the year period of any given monthly period (e.g. Year 1, Year 2, Year 3, etc.)
- The ‘Month’, which is the monthly period a cash flow is in (e.g. Month 1, Month 2, Month 3, etc.)
- The ‘Year Ending’, which is the actually year that a given year period ends (e.g. 2016, 2017, 2018, etc)
- The ‘Month Ending’, which is the actual date that a given month period ends (e.g. 7/31/2015, 8/31/2015, 9/30/2015, etc.)
Depending on the formula I am writing, I’ll use different rows/components. So for example, if I’m using a SUMIF statement to roll-up monthly cash flows into an annual cash flow report, I’ll call out the ‘Year’ periods. If I have cash flows that should only be included within a certain date range (e.g. three months of rent abatement at the start of each new MLA lease), I’ll use the ‘Month Ending’ dates. Likewise, I’ll use the ‘Month’ period to output a residual value in a specific month (e.g. sale in month 101), pay a leasing commission at the start of a new lease, or report a specific capital expense in a specific month. In terms of how I use the ‘Year Ending’ value, the truth is that I rarely use it in my formulas, but I like the optics of showing the actual year for each monthly period. In other words, I like to be able to quickly see that, for example, year 9 ends in 2024 or year 12 ends in 2027. It is also a handy value to have, because a lot of analysts prefer to use the actual year (e.g. 2015, 2016, 2017) in their annual cash flow statements than a period (e.g. Year 1, Year 2, Year 3).
As for the formulas I use for each value, here they are (you’ll find them detailed in the embedded video):
The formulas in the first period (Column C), are specific to the first period. The formulas in the second period (Column D), can be copied to the right to complete the header.
If you have any questions or comments, please don’t hesitate to reach out. You can find my contact information at: http://www.spencerburton.org/contact/
- How to build your period/date rows
- Can easily be copied and incorporated into your next real estate model