Supercharge Excel with the 'Excel 4 CRE' Add-In

Today, we’re thrilled to share with the A.CRE community a new timesaving (and free) Excel tool: the ‘Excel 4 CRE’ Add-in! Over the past several months, we at A.CRE (together with our new favorite companion, ChatGPT!) have been working to develop this Excel add-in. And today, we’re excited to release the publicly available version.

This add-in seeks to save each one of us time by helping speed along modeling tasks common to real estate. The add-in is very much in its infancy, and so we look forward to hearing your thoughts for how we can improve it. As we receive that feedback from you and use the add-in ourselves, we’ll look to improve and expand the add-in’s capability in the coming months and years.

In this post, you’ll find a download link to get the free add-in as well as written and video instructions for how to install and use the ‘Excel 4 CRE’ Add-in.

Note: we’re regularly improving this tool and it is already on beta version 0.8.x (11 releases as of May 2023). And while the A.CRE team runs a version or two ahead of the public releases, the ‘Excel 4 CRE’ add-in undoubtedly contains errors. Use the tool at your own risk.

Screenshot of the ‘Excel 4 CRE’ add-in, beta v0.7.x

What is an Excel Add-in?

In case you’re unfamiliar with an Excel add-in. An add-in is a software program designed to extend the functionality of Microsoft Excel. Packaged as an .xlam file type, it is essentially a separate program that integrates with Excel and provides additional features and capabilities beyond what is built into the standard Excel software.

Add-ins can be created by Microsoft, third-party developers, or even users themselves using programming languages like VBA (Visual Basic for Applications). They can range from simple utilities that automate repetitive tasks to complex tools that perform advanced data analysis or integrate with external data sources.

Examples of popular Excel add-ins you may be familiar with include Solver (which helps find optimal solutions to mathematical problems), Analysis ToolPak (which provides statistical analysis functions), and Power Query (which allows users to connect and transform data from various sources).

Download the ‘Excel 4 CRE’ Add-in

This Excel add-in is provided free of charge as a service to the CRE industry, with all code openly viewable/editable via the VBA Project section of Excel. By downloading this add-in, you:

agree to the A.CRE Terms and Condition of Use,
acknowledge that this Excel add-in likely contains errors,
acknowledge that A.CRE is unable to provide you with support for this free Excel add-in and that you will install the add-in on your own and at your own risk, and
acknowledge that this Excel add-in is ONLY compatible with Excel for PC; it is NOT compatible with Excel for Mac or web versions of Excel. Additionally, the add-in is NOT compatible with custom Quick Access Toolbar settings, reverting any custom changes to the Quick Access Toolbar to default.

Download the Excel 4 CRE Add-in

What’s Included in the ‘Excel 4 CRE’ Add-in and What’s Coming

As of the current release, the ‘Excel 4 CRE’ Add-in includes:

Custom ribbon item called 4-CRE that appears upon installation.
Tools to connect with Google’s Geocoding API and auto-populate latitude/longitude/address via several custom functions.
Various custom Excel shortcuts widely used by commercial real estate finance professionals.
Custom Excel functions specific to CRE professionals (note that the functions do NOT work when used in Excel without this add-in; only use these functions in workbooks you don’t intend to share)
A feature to automatically generate a two or three row date header.
Auto-populate forward yield curve.
Quick links to A.CRE resources.
A convenient uninstall feature to easily remove the add-in.
A changelog and documentation.

In our development roadmap, we have the following upcoming features:

Auto populate other benchmark rates (e.g. 10-Yr UST).
Input custom modules specific to CRE models.
Button on 4-CRE ribbon that pulls open browser and links to key CRE resources on the web (e.g. CoStar, REIS, Crexi, Reonomy, Loopnet, Compstak, CBRE Econometrics, A.CRE, etc.).
Ability to create custom keyboard shortcuts and/or change existing custom shortcuts.

How to Install the ‘Excel 4 CRE’ Add-in (PC Only)

To install the add-in in Excel for PC, simply follow these steps:

Download the .ZIP file that contains the Excel-4-CRE.xlam add-in file and save it to your computer.
Unzip the .ZIP file and save the contents of the file (.xlam + .txt files) to a permanent location on your computer. It’s important that the Excel-4-CRE.xlam file is saved to a permanent location on your computer.
Open Microsoft Excel.
Click on the “File” menu at the top left corner of the Excel window.
Select “Options” from the left-hand side menu.
In the Excel Options dialog box, select “Add-Ins” from the left-hand side menu.
At the bottom of the screen, next to the “Manage” dropdown menu, select “Excel Add-ins” and click “Go”.
In the Add-Ins dialog box, click “Browse”.
Navigate to the location where you saved the add-in file.
Before selecting the file, right-click on the file and click ‘Properties’.
In the Properties dialog box, click the “Unblock” button next to the “Security” message in the lower-right hand corner of the Properties dialog box. Note that in some cases the ‘Unblock’ option does not appear. In that case, simply close the Properties dialog box and continue.
Now select the file and click “OK”.
The add-in should now be listed in the Add-Ins dialog box. Make sure the checkbox next to the add-in name is selected and click “OK”.
If the add-in contains macros, Excel may display a warning message stating that the file contains macros and asking whether to enable them. Click “Enable Macros” to allow the add-in to function properly.
A custom ribbon item entitled 4-CRE will appear along the top of the Excel window. If that custom ribbon item does not appear, the add-in has not been installed properly.

Assuming the custom ribbon (4-CRE) appears, the add-in is now installed and functioning properly in Excel. If you have trouble installing the add-in, refer to Microsoft Excel’s documentation for adding and removing add-ins.

How to Upgrade the ‘Excel 4 CRE’ Add-in (PC Only)

In beta v0.6, we made it easier to upgrade to the latest version of the add-in. To upgrade the add-in in Excel for PC, simply follow these steps:

Ensure that Excel is closed.
Download the latest version of the Add-in. The download is a .ZIP file that contains the Excel-4-CRE.xlam add-in file as well as an Instructions.txt file.
Save the .ZIP file to a temporary location on your computer.
Unzip the .ZIP file and copy the newly unzipped Excel-4-CRE.xlam file.
Locate the permanent location of your existing Exce-4-CRE.xlam file (i.e. the old version).
Paste the newly unzipped ‘Excel-4-CRE.xlam’ to the permanent location containing your existing file.
Windows will ask whether to ‘Replace or Skip Files’; choose ‘Replace the file in the destination’.
You have now overwritten the old version of the add-in for the new version.
Right-click on the newly overwritten file and click ‘Properties’.
In the Properties dialog box, click the “Unblock” button next to the “Security” message in the lower-right hand corner of the Properties dialog box. Note that in some cases the ‘Unblock’ option does not appear. In that case, simply close the Properties dialog box and continue.
Open Excel and navigate to the 4-CRE ribbon (note that if the 4-CRE ribbon does NOT appear, the add-in a) may not be activated – follow the installation instructions above for how to activate the add-in, b) the Unblock in the Properties dialog box wasn’t completed correctly, or c) the add-in was not installed correctly).
Click the ‘Version Notes’ button under Settings of the 4-CRE ribbon to ensure the add-in has been updated successfully.

The add-in should now be upgraded to the latest version and functioning properly in Excel. If you have trouble installing the add-in, refer to Microsoft Excel’s documentation for adding and removing add-ins.

How to Use the ‘Excel 4 CRE’ Add-in

The ‘Excel 4 CRE’ Add-in expands the functionality of Excel in a variety of ways. Below find instructions for using the features currently available in the add-in. As time permits, and based on user feedback, we will continue to add new features to the add-in.

Section: ‘Geocoding Lat/Long’

Using the Auto Latitude and Longitude (Google Geocoding) Feature

This add-in includes the features in our standard alone A.CRE Geocoding Excel Add-in – an Add-in used by real estate and non-real estate professionals like. If you have that add-in installed, installing this new add-in should automatically uninstall the Geocoding Add-in such that they don’t conflict.

To learn how to use this add-in for auto-populating latitude and longitude coordinates (or getting an address from a latitude/longitude coordinate), follow the instructions here:

https://www.adventuresincre.com/geocoding-excel-add-in/

Note that while the instructions above may refer to the A.CRE Geocoding Excel Add-in, the process to activate and use the Geocoding features in the ‘Excel 4 CRE’ add-in are identical.

Section: ‘Custom Shortcuts/Functions’

Using the CRE Shortcuts

The add-in expands the keyboard shortcuts available in Excel to include various shortcuts common in financial modeling. You can find the entire list of shortcuts by clicking the ‘Shortcuts’ button in the 4-CRE ribbon in Excel or by referring to the list below:

KEYSTROKE	ACTION	TYPE
Ctrl+Shift+D	Change formatting to Dollar formatting with no decimals	Formatting
Ctrl+Shift+M	Change formatting to Number formatting with no decimals	Formatting
Ctrl+Shift+C	Change formatting to Percentage formatting with two decimals	Formatting
Ctrl+Shift+A	Change formatting to Accounting formatting with no decimals	Formatting
Ctrl+Alt+>	Increase by one decimal	Formatting
Ctrl+Alt+<	Decrease by one decimal	Formatting
Ctrl+Shift+Y	Change cell background to Yellow (255,255,0)	Formatting
Ctrl+Alt+X	Change cell background to Red (255,0,0)	Formatting
Ctrl+Alt+G	Change cell background to Green (0,255,0)	Formatting
Ctrl+Shift+N	Change cell background to None	Formatting
Ctrl+Shift+B	Change font color to Blue (0,0,255)	Formatting
Ctrl+Shift+K	Change font color to Black (0,0,0)	Formatting
Ctrl+Shift+R	Change font color to Red (255,0,0)	Formatting
Ctrl+Shift+G	Change font color to Green (0,255,0)	Formatting
Ctrl+Shift+W	Change font color to White (255,255,255), cell background None	Formatting
Ctrl+Shift+Alt+M/Q/Y	Change formatting to “Month” 0 / “Quarter” 0 / “Year” 0 for period header	Formatting
Ctrl+Shift+Alt+X	Change formatting to 0.00”x” for DSCR and Equity Multipler	Formatting
Ctrl+Shift+<	Go to the first visible worksheet	Navigation
Ctrl+Shift+>	Go to the last visible worksheet	Navigation
Ctrl+Shift+?	Unhide all worksheets	Navigation
Ctrl+Alt+A	Select all worksheets	Navigation
Ctrl+Shift+Alt+D	Autofill formula down (assumes column to the left is not empty)	Functionality
Ctrl+Shift+Alt+R	Autofill formula right (assumes row above is not empty)	Functionality

Using the CRE Functions (List of Functions)

This add-in includes various custom functions specific to both the add-in (i.e. the Geocoding specific functions) and helpful for real estate financial modeling.

It’s important to note that these functions ONLY work when the ‘Excel 4 CRE’ add-in is installed. If you share a workbook with a user who doesn’t have the Excel 4 CRE add-in, the cells containing these functions will show an error. Endures that when using these functions, you don’t intend to share the workbook with other users who don’t have the custom add-in installed.

FUNCTION	ACTION	LOGIC
=FINDCOORDINATES()	Finds latitude and longitude coordinates from an address via Google Geocoding API	=FINDCOORDINATES([address city state])
=FINDLATITUDE()	Finds latitude from an address via Google Geocoding API	=FINDLATITUDE([address city state])
=FINDLONGITUDE()	Finds longitude from an address via Google Geocoding API	=FINDLONGITUDE([address city state])
=FINDADDRESS()	Finds address from a coordinates via Google Geocoding API	=FINDADDRESS([latitude],[longitude])
=EMX()	Calculates the Equity Multiple of a Net CF line; assumes no mid-hold capital event	=EMX([net CF line])
=ERRCHECK()	Checks if two ranges are equal to one another, if so it outputs OK, otherwise Err	=ERRCHECK([range1],[range2])
=ISNOTBLANK()	Checks if a cell is blank, and if so returns a blank cell, otherwise executes some code	=ISNOTBLANK([cell ref],[some code])
=AVERAGE_WEIGHTED()	Averages a given range, using a seperate range as the weight (i.e SUMPRODUCT/SUM)	=AVERAGE_WEIGHTED([average range], [weight range])

Section: ‘CRE Content’

Using the ‘Create Date Header’ Feature

Nearly every real estate financial model includes a date header. This header includes the ending date of each period, the period type (i.e. month, quarter, year), the number of each period, and the year of each period. To speed the creation and duplication of date headers in CRE models, the add-in includes a feature that quickly creates a date header for you.

To use this feature:

First, make sure you have an input for ‘Analysis Start Date’ and that a date is entered into that cell.

Then, with beta version 0.5 or later installed, go to the 4-CRE ribbon item. Under the ‘CRE Contents’ section, click the ‘Create Date Header’ button. A series of boxes will appear that will ask for information specific to the model you’re building. Upon answering those questions a two or three row date header will be created.

Options include:

Select an Analysis Start Date on which the date will be based
Choose whether periods are monthly, quarterly, or annual
Choose the analysis length
Choose where to add the header

If you choose monthly or quarterly periods, three rows will be added: i) a Period Ending row with a series of ending date for each period; ii) an ‘Analysis Month/Quarter/Year’ row, and iii) an ‘Analysis Year’ row where periods are months or quarters.

The module adds dynamic formulas, so you can copy and paste the header in other parts of your model. Additionally, the header automatically updates based on the date entered in the ‘Analysis Start Date’ input cell.

Using the ‘Forward Rate Curve’ feature

This feature helps the user to quickly download from the Term SOFR, USD LIBOR, and UST forward yield curves from Chatham Financial (Thomson Reuters). The forward yield curve is often used as inputs in floating/variable rate debt underwriting.

To use the tool:

Click the ‘Forward Rate Curve’ button
A dialog box appears with instructions and two buttons
The first button takes the user to Chatham Financial’s website to manually download an Excel file with the forward curves
The second button quickly takes the file the user downloads and imports the first worksheet from Chatham’s forward yield curve Excel file

‘Excel 4 CRE’ and A.CRE have no affiliation with Chatham Financial nor do we guarantee the accuracy nor reliability of the forward yield curve data. Use at your own risk.

A.CRE Quick Links

To help Excel 4 CRE users access A.CRE resources quickly, we’ve added an ‘A.CRE Quick Links’ menu in the CRE Content section. The dropdown menu provides links to the Excel Models Library, career and educational resources, the event calendar, job board, A.CRE Accelerator, and A.CRE Audio Series.

Section: ‘Add-in Settings’

Version Notes

This feature was added in beta v0.6. This add-in is regularly updated. As of beta v0.6, the file name does not change from one version to the next. Therefore, to allow the user to quickly see which version they’re using we’ve added a ‘Versions Notes’ button to the Settings section.

Click the ‘Version Notes’ button and a dialog box appears that provides information on which version is currently running.

View Documentation, Instructions, or Add-in Code

To quickly view this documentation, written/video instructions for using the add-in, or details for accessing the add-in code, we’ve added quick links within the 4-CRE ribbon ‘Settings’ section. Simply click the ‘Excel 4 CRE Settings’ button, select any of the items in the dropdown menu, and either the webpage or a dialog box with instructions will appear.

Uninstalling the Add-in

To make uninstalling the add-in simple, we added an ‘Uninstall’ feature within the 4-CRE ribbon ‘Settings’ section. Simply click the ‘Excel 4 CRE Settings’ button, select ‘‘Uninstall Add-in’, answer ‘Yes’ to the question whether to uninstall the add-in, and the add-in will be deactivated.

Note that the 4-CRE Ribbon item will not disappear until you close Excel and reopen it.

We Hope You Find the ‘Excel 4 CRE’ Add-in Useful

There you have it! We hope this new Excel add-in helps make your real estate financial modeling tasks more efficient and enjoyable.

As always, we welcome your feedback and suggestions to make this add-in even better. Happy modeling!

A screenshot of the backend code for the add-in. All code is publicly visible and editable.