Harrietta Hind February 2, 2021 Spreadsheet
The second component of a good real estate investment spreadsheet is a long cash flow projection period. Property investments are typically very long term ‐‐ from 10 to 20 years on average. In particular, if you are buying real property for long term portfolio holdings, it makes sense to have at least 10 years of monthly or quarterly data, and even up to 30 years. This ensures you capture the entire future cash flows, and enables a much more accurate determination of net present value, internal rate of return, and capitalization factors.
Next, how much detail do you need in your cash flow template Excel spreadsheet at the individual line item level? Is cash from financing sufficient or do you need equity financing, debt financing, interest earned, etc. Also how do you intend to handle depreciation and amortization, since these are non‐cash items that are typically added back to the income statement entries when determining the cash effect.
Thanks to budgeting tools such as a monthly budget spreadsheet, planning and monitoring your income and expenses can be done in a breeze. A budget spreadsheet allows you to update your entries easily without affecting the accuracy of figures in your budget.
The small business has different accounting needs which are better described as bookkeeping than accounting. For non limited companies that do not need to produce a balance sheet then a simple income and expenditure account can be produced much simpler using single entry bookkeeping principles. Less financial control is often required from small business accounting software as the bookkeeper is often the owner manager who already has an intimate knowledge of each transaction. Books are still required for tax purposes and a solid requirement of preparing a set of financial books for tax purposes is that each entry is supported by third party evidence.
One of the first things to consider is what kind of data you have to work with in your cash flow template Excel spreadsheet. Ideally, you‘re looking for accurate monthly data including income statement items like revenue and operating expenses and balance sheet items like equipment purchases and cash from financing activities. If you have a longer time frame you can go with quarterly periods, but annual tends to be too long. After all, how can you predict what is going to happen beyond 5 years with any accuracy unless you‘re valuing an annuity?
Designing a strong real estate spreadsheet requires some forethought about the uses, calculations, and net results you‘re looking for. This should be done before you ever get started. Here we demonstrate some key considerations by means of a case study. To demonstrate the proper approach to designing and building a real estate spreadsheet in Excel, let‘s use a residential multi‐unit rehabilitation project example. To keep it simple, let‘s assume it has 4 apartment units, was built 60 years ago, has 3 existing tenants, and requires new interior and exterior paint, some plumbing and electrical work to update the property to modern safety standards, and a partial re‐roofing to fix some water damage.