Creating Real Estate Finance Models

Level: Intermediate

Available Durations:

• 50 minutes
• 60 minutes
• 75 minutes
• 90 minutes
• 100 minutes
• 120 minutes

Description:

In this presentation Excel expert David H. Ringstrom, CPA brings his prior commercial real estate experience to the foreground again. In this presentation David will walk you through building a simplified real estate proforma. The discussion will include worksheet functions such as SUMIF for looking up numbers from profit & loss reports and other sources, contrasting IRR and XIRR, as well as using CUMIPMT and CUMPRINC to eliminate building and referencing amortization tables when calculating debt service. Other techniques include using conditional formatting to color code lease expirations and heat map rental rates. You’ll see how to build a waterfall chart to illustrate NOI (net operating income) for a given year, along with keyboard shortcuts such as the F4 key for toggling absolute references and Ctrl-R for copying formulas to the right. Along the way David will cover nuances such as opening CSV files in Excel and managing the Protected View prompt that can slow you down when you open reports exported from cloud-based software.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the Microsoft 365 (formerly known as Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.

Microsoft 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.

Topics/Areas Typically Covered:

• Building a simplified real estate proforma model for determining the internal rate of return (IRR) on a given investment.
• Contrasting sending reports to Excel versus exporting to comma-separated value (CSV) files.
• Opening CSV files versus opening Excel workbooks.
• Understanding how to manage the Protected View prompt in Excel 2010 and later.
• Overcoming VLOOKUP’s quirks by using the SUMIF function to look up numeric values.
• Calculating the principal portion of a loan paid during a specific time period by way of the CUMPRINC function.
• Maximizing space in spreadsheets by using custom number formats to incorporate words into cells that contain numeric values.
• Jump-starting spreadsheet projects using free, prebuilt templates in Excel.
• Computing the internal rate of return for a series of cash flows with the IRR function.
• Understanding how XIRR provides more accurate return calculations than IRR, as well as computing returns on irregular cash flow distributions when needed.
• Heat-mapping rental rates with conditional formatting.
• Color-coding cells between a range of amounts.
• Building an instant vacancy schedule by year via Excel’s pivot table feature.
• Illustrating financial statements with the Waterfall chart in Excel 2016 and later.
• Using a keyboard shortcut so you’ll never have to manually type $ signs in a formula again.

Learning Objectives/Why You Should Attend:

• Identify the worksheet function that enables you to calculate a return for a series of cash flows distributed at irregular dates.
• Recall the keyboard shortcut that enables you to transfer the contents of a cell across a row of adjacent cells that you’ve selected.
• State which character signifies an absolute reference within a formula.

About the Instructor:

David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.

Pricing and Format Options:

Click here to learn about the presentation format and view pricing information.

Click here to view other topics.