course description
in this course, excel expert david h. ringstrom, cpa, brings his prior commercial real estate experience to the foreground again. david will walk you through building a simplified real estate pro forma. the course 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. 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.
david demonstrates every technique at least twice: first, on a powerpoint slide with numbered steps, and second, in the subscription-based microsoft 365 (formerly office 365) version of excel. david draws your attention to any differences in the older versions of excel (2021, 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 course.
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.
who should attend:
practitioners seeking to build more effective real estate models.
level: advanced
topics typically covered:
- calculating the principal portion of a loan paid during a specific time period by way of the cumprinc function.
- computing the internal rate of return for a series of cash flows with the irr function.
- contrasting sending reports to excel versus exporting to comma-separated value (csv) files.
- illustrating financial statements with the waterfall chart in excel 2016 and later.
- importing cash flow reports into excel with power query to create self-updating connections to the data.
- jump-starting spreadsheet projects using free, prebuilt templates in excel.
- understanding how xirr provides more accurate return calculations than irr, as well as computing returns on irregular cash flow distributions when needed.
- using a keyboard shortcut so you’ll never have to manually type $ signs in a formula again.
- using the sumifs function to sum values based on multiple criteria.
- utilizing the cumipmt worksheet function to calculate interest amounts for any period of a loan without utilizing an amortization table.
learning objectives:
- 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
review our course policies and procedures page for further information