Help With Cap Rate Spreadsheet

The Cap Rate Spreadsheet is designed to allow entry of rental income and expense data, compute totals, arrive at net income and the market capitalization rate.  I also recommend a mortgage calculator program.  Here is a link to my favorite.

When you download the spreadsheet, be sure to keep an original copy to use as your entries may override existing formulas.

Columns exist for actual, pro-forma, and bank.

Actual is for the numbers reported by the seller

Pro-forma is a what if scenario.  Typically this is for factoring in changes in rent and expenses.

Bank is for you to estimate how the lender will view the property. Commercial lenders often will arrive at their own set of numbers when evaluating a property and determining their loan limits. Their numbers may differ significantly from the reported or the pro-forma numbers.

If you need help on specific terminology that you see on the spreadsheet as well as other important aspects of buying and selling multi-family properties,  Click here.

Back to the Spreadsheet..  Getting Started

1. Always keep a back-up copy of the original spreadsheet.
2. Fields that you can enter are highlighted in yellow.
3. Pro-forma and Bank columns will default to the values entered in the Actual column, so always enter the actual column first.  When you adjust values in the proforma, or bank columns, the equations in those columns will be wiped out.. (see above, always keep a backup copy)

You can enter a vacancy factor as a percentage and the adjusted income will be computed.

Enter the actual expense data.  These numbers are annual expenses. The other 2 columns will populate with those values.  You may need to combine several expense categories into one. For example, some properties itemize utilities as separate for water, garbage and electricity.  If this is the case for your property, then combine the number into the utilities entry.

Totals columns will change as your numbers change.

When you begin changing income and expense data in the Pro-Forma or Bank columns, those numbers will override the defaults and the cells will no longer self populate from the Actual column.

You can enter a selling price and see the cap rate.
You can see the cap rate change as you change selling price, income, and expense data

At the bottom of the page are 3 rows with monthly payment, monthly income and monthly net income.
Income and Net income are computed.  Based on your anticipated loan amount, interest rate etc, you can enter your debt service figure on a monthly basis to see what kind of cash flow to expect.

If you are looking for a multi-family property to purchase or are interesting in Selling your multi-family property,  the market area that I cover includes King, Snohomish, and Pierce Counties.  I provide expert service, a no-nonsense approach, effective pricing and most importantly RESULTS!