Spreadsheets! (Pro Forma & Accounting)

[Frank Jiang]

Hi BP! I wanted to share the spreadsheets my wife and I created to do deal analysis and accounting for SFR rentals.

Proforma:

http://www.biggerpockets.com/files/user/SpiralLoxo...

- Assumption inputs can be entered as either a % of some other field or a flat number.

- Can flexibly change holding period & loan terms

- Can factor in annual increase in property value, rental income, and operational expenses

Accounting:

http://www.biggerpockets.com/files/user/SpiralLoxo...

- Because I'm too cheap to pay for accounting software

- Automatically generates Balance Sheet & Income Statement

- BS & IS can be viewed by MTH/QTR/YR and can isolate any single property to determine profitability or view the entire portfolio as a whole

Feedback highly desired!  Especially if you see mistakes!

[Frank Jiang]

Hey Jon,

For the Proforma spreadsheet:

The only tab with fields that need to be populated is the 'Assumptions' tab.  In fields under the section called 'ACQUISITION', you'll see that some fields have 2 or more inputs that allow you to input amounts as either a straight dollar amount or as a % of either rent or purchase price.  For these rows, you only populate 1 of these fields and keep all others blank.

For example, row 40 Property tax can be input as either a % of property value, $ (monthly), or $ amt (annually).  You only fill in one cell (either B40, C40 or D40) and the spreadsheet will normalize everything for you.  You can tell whether or not you did it right because there will be a green checkmark in column G.  The proforma is ready to read when every field has a green checkmark next to it.

For the Accounting spreadsheet:

On this spreadsheet, the only important tab that needs to be updated is the 'Transactions' tab Columns A-F. Columns G-Z can be copy and pasted from the row above.

Each transaction is like a single journal entry

A: What property is this transaction for

B: What is the effective date of this transaction

C: What is the Dollar amount of this transaction

D: What account is being debited

E: What account is being credited

F: Description/Notes/Misc

The other tabs all populate automatically. The balance sheet and income statement tabs have list data validation that you to see each property individually or choose a different time view.

If you would like to add your own GL Account, you give it a number in column C of the Chart of Accounts tab (only the number, dashes is just a formatting thing) and what account that account rolls into in column A.