In today’s fast-paced real estate world, there’s a common misconception that cutting-edge software or artificial intelligence will soon replace Excel. However, as I discovered during a recent live session with a colleague, Excel remains indispensable—not because it’s the flashiest tool on the market, but because it forces you to articulate and reveal how you think about each deal.
In our session, we dove into the nuts and bolts of building a cash flow model for a commercial investment property from scratch. Here’s a recap of our discussion, along with some practical tips and formulas to help you create a robust, transparent model.
Why Excel Still Reigns Supreme in Real Estate
Many professionals today face a choice: stick with traditional Excel models or jump to more automated platforms like Argus. Yet in our transactions—where each deal is unique—Excel’s flexibility is invaluable. It acts as a sophisticated calculator, capturing every nuance of your financial engineering. As one of my peers put it, Excel isn’t about automating everything; it’s about clearly showing how you think.
For instance, even in a world where some transactions now incorporate AI, I’ve yet to see a deal where specialized software completely replaces Excel. Whether you’re dealing with residential, retail, or mixed-use properties, Excel forces you to build a model that fits each project’s particular characteristics.
Setting Up the Model: Dates, Periods, and Cash Flows
Before crunching any numbers, the first step in any cash flow model is to define your timeline. In commercial real estate, cash flows typically extend over many years—so I prefer to use quarterly periods. Here’s how we began:
- Define the Periods and Dates:
- Establish a timeline (e.g., 10 years) divided into quarters.
- Use Excel’s date functions to create a series of period-end dates. A helpful tip: press Ctrl + ; (semicolon) to quickly insert today’s date as your starting point.
Rounding Dates to the End of the Quarter:
To standardize your cash flow dates, use a formula that rounds up your acquisition date to the next quarter-end. For example, a formula might look like:
excel
Copy
=DATE(YEAR(AcquisitionDate), ROUNDUP(MONTH(AcquisitionDate)/3, 0)*3 + 1, 0)
- This ensures your timeline aligns with your deal’s milestones—critical when calculating partial period rents.
Calculating Rent: Passing Rent, ERV, and Rent Reviews
Once your timeline is set, the next step is to model the income. In our live session, we focused on calculating the passing rent (the rent you’re actually receiving) versus the ERV (Estimated Rental Value), particularly around the time of rent reviews.
Pro-Rating Rent for Partial Periods
Real-life leases rarely start or end exactly on a quarter boundary. To handle these “partial period” scenarios, you need to calculate the fraction of the period that is active. Two Excel functions come in handy here:
- YEARFRAC:
This function calculates the fraction of a year between two dates. However, because lease periods are often not a full year, the result may not always align with standard monthly or quarterly assumptions.
DAYS360:
Because many commercial leases assume a 360-day year (i.e., 30 days per month), the DAYS360 function can offer a more consistent basis for calculating prorated rent. For example:
excel
Copy
= (DAYS360(StartDate, EndDate, TRUE) / 360) * Rent
- This formula computes the number of days in the partial period (using a 360-day year convention) and scales the annual rent accordingly.
Tackling Rent Reviews
Rent reviews add another layer of complexity. In our session, we needed to account for:
- The period before the review date, where rent is fixed.
- The period after the review date, where the rent adjusts to the new ERV.
To ensure your model doesn’t accidentally produce negative cash flows (for example, if the review date falls between two cash flow periods), it’s useful to incorporate MAX and MIN functions:
excel
Copy
= MAX(0, (DAYS360(MAX(RentReviewStart, PeriodStart), MIN(LeaseEnd, PeriodEnd), TRUE) / 360) * (NewRent – OldRent))
This formula calculates the additional rent earned after a review—only if the dates are valid—and ensures that negative values are replaced by zero.
Overcoming Excel Challenges
During our live build, we encountered some common pitfalls:
- Array Formula Issues:
Copying data from PDFs sometimes results in array formulas that can be tricky to adjust. One workaround is to convert the array into single cell values by dragging the formula across, then editing as needed. - Date Overlaps:
When modeling, be aware that your start and end dates might overlap. While this might not affect the total rent received, it can lead to complications if you’re trying to match lease-specific timelines.
Despite the challenges, every struggle is a learning opportunity. The speed at which you can troubleshoot a formula—and ultimately master it—is a testament to your understanding of the underlying deal mechanics.
Final Thoughts
Excel may not be the newest tool on the block, but its transparency and flexibility make it a trusted ally in real estate financial modeling. By building models from scratch, you not only ensure that every nuance of the deal is captured, but you also demonstrate your ability to think critically about complex financial transactions.
If you’re looking to dive deeper, I invite you to explore additional models and examples available in my book on real estate financial modeling. You’ll find step-by-step walkthroughs and downloadable templates designed to help you master the art of financial analysis in real estate.
Happy modeling—and remember, every great deal starts with a well-thought-out Excel sheet!