From the descriptions above, you can immediately detect one big issue with these capital costs: they’re often lumpy. Assisted Living Development Pro Forma Model in Microsoft Excel. It’s also simpler because Working Capital tends to be less important for properties, and you effectively use Cash Accounting rather than Accrual Accounting in the analysis. Also, you usually ignore income taxes because properties tend to be owned by pass-through entities such as Partnerships, S Corporations, and REITs that do not pay corporate income taxes. If you form a partnership to acquire apartment buildings, each partner will pay taxes individually, but the partnership as a whole will not pay corporate-level taxes. The following components are generally found in all real estate proformas. This line item represents the capital costs that are covered by the Reserves in a given year. For example, if you deduct the Reserve Allocations, as we do, then NOI partially reflects capital costs. Instead, they hire 3rd party management companies to deal with tenants, collect rent, resolve problems, and set up repairs and maintenance. It is a fairly basic worksheet for doing a rental property valuation, including calculation of net operating income, capitalization rate, cash flow, and cash on cash return. Adrienne Schmitz Freelance Writer Follow 57. “Capital costs” refer to spending on items that will provide a benefit for many years to come. Take it to the bank! Even if you disagree with our treatment of the Reserves in NOI, you’d still have to deduct the Reserve Allocations in this section – so Adjusted NOI ends up being the same. Calculating Projected Returns Using a Pro Forma. And since individuals rather than companies are the tenants, there is a substantial risk of non-payment. But it cannot tell you whether or not you’ll earn an out-sized return on a property acquisition or development. Direct Excel download from Resheets – Use this template to figure out your debt service coverage ratio, which is your ability to pay your real estate debt given your income from your properties. Simple Calculation: If the property has 10,000 rentable square feet and the market rate is $50 per square foot per year, the Base Rental Income is $500,000. If you're new here, please click here to get my FREE 57-page investment banking recruiting guide - plus, get weekly updates so that you can break into investment banking. Tenants are often responsible for Utilities, but not much else, so Expense Reimbursements might be labeled just “Utility Reimbursements.”. You can determine what the proper rates might be by conducting comparative market analysis. We’ve used Argus Developer and your spreadsheets allow us to be considerably more flexible in our assumptions, give us peace of mind with fully transparent formulas and assumptions, save huge amounts of time, and make us look really on point with investors. The #1 mistake made by real estate investors is that they use emotion to make purchase decisions on rental properties. Go ahead! Professional Real Estate Development: Developer's Tool KitThe financial analysis spreadsheets presented here are intended to be used together with the third edition of Professional Real Estate Development: The ULI Guide to the Business. You can also see Excel Matrix Templates. Here is a list of items which should be included: Operating Expenses This number represents the income from all space in the building, ignoring permanently vacant spaces and non-rent sources. “Permanently vacant” means there is no tenant in the space currently, and the owner has no near-term plans to find a tenant for that space. Simple Calculation: A property’s most recent assessed value was $20 million. That creates differences in the Interest and Principal Repayment calculations and the returns for lenders. Examples might include additional walls and doors in an office suite. For example, if a property’s projected NOI is $5 million and Cap Rates for similar properties in the area are 5%, this property might be worth $5 million / 5% = $100 million. The main categories are Capital Expenditures, Tenant Improvements, and Leasing Commissions. Those TIs will not recur each year – only when a tenant renews a lease, or a new lease begins. Hotels require far more staff to operate since they’re more like “real companies,” so expense categories like Sales & Marketing and General & Administrative are much bigger. The first thing you have to do when it comes to a real estate investment decision is to build a real estate pro forma, which really just means a cash flow projection. It’s easiest to answer this question by pointing out that it’s possible to do the same thing for companies. We cover more complex examples and cases in our full Real Estate Financial Modeling course. Here’s the high-level view (click the image to see a larger version): You always start the pro-forma by showing the “potential” revenue of a property if it were 100% occupied and all tenants paid market rates. 2) And what about Depreciation? This item found in the top line makes up the cash that could potentially be generated if the property was 100% leased. In this article, we’ve focused on a real estate pro-forma for a mixed office/retail property. But the problem is that all these solutions are less ubiquitous than Excel; everyone knows and uses spreadsheets, but only small percentages of people use one specific software product. We respect your privacy. Reversion Cash Flows Enter the variables in the cells with blue titles on the right hand side. This is the final line on the typical real estate pro-forma, and it represents the amount available for distribution to the equity investors (the “owners”) in the property. This represents the foregone rental income when a tenant leaves and it takes several months to find a new tenant. To be clear: properties still have full financial statements. In places like Australia and the U.K., there’s also a “Stamp Duty” on property sales, which may exist along with or in place of annual property taxes. You can use this proforma for an investment property or for your own home. And in some cases, the full loan proceeds will not be distributed upfront, but rather “held back” and distributed when certain milestones are met. In the simple real estate pro-forma above, we group these items into a single line (“CapEx, TIs, and LCs”), but we calculate them separately and show them on separate lines in more complex models. This pattern makes the property’s cash flows fluctuate significantly, and it makes it difficult for owners to plan their long-term strategy. If you’re looking for a solid, free real estate proforma template, then look no further. Here’s how it works over the first 3 years in the example above: There is significant disagreement over where the Reserve Allocations should be shown. Retailers might agree to this term in exchange for lower base rent. This is a value that is estimated in various ways with the aim of arriving at a net sales proceeds figure. Please refer to our full privacy policy. Simple Calculation: If a new tenant moves into a 2,000-square-foot space with rent of $50 per square foot per year and receives 3 rent-free months, the “Concessions & Free Rent” line item will be 2,000 * $50 * (3 / 12) = $25,000. Do I just delete Tenant 1 and 2 and then copy and paste Tenant 3 a few more times into the spreadsheet? When capital costs are owed, the owners draw on the Reserves to pay for them. This one is the other component of Debt Service: repaying the principal over time. Then, you make deductions and adjustments because: Each line item in the Revenue section corresponds to one or more of these points: This one represents the “Potential” Rental Income of the property if it were 100% occupied and all tenants paid market rent. A few other, more advanced items on the real estate pro-forma include Loss to Lease and Percentage Rent. Variable Expenses. For instance, let’s say you paid $77,000 cash for the property, including closing costs. There are various software solutions for real estate, the most famous one being ARGUS. The Leasing Commissions will be 5% of the total lease value over these 5 years. This is a simple template model to create financial projections for hemp business for cultivating activity in the US. Examples of Pro Forma Statements. And you don’t need a full Cash Flow Statement because many of the items on it are non-recurring and, therefore, do not factor into the projections. The first step in building a real estate development model is to fill in the assumptions for schedule and property stats. If the property is big enough, this category might also include staff payroll, sales & marketing, janitorial, landscaping, and security services. Commercial Real Estate Valuation Spreadsheet : real estate development pro forma excel download. Hotels are so different that we’d probably need a separate article to explain the hotel pro-forma fully. A detailed real estate model designed to be used to estimate future cash flows to the sponsor and investor(s). Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. Very general question, Which are some of the reasons or parameters property developers compare among available plots/options and decide on one specific plot or property to go ahead and invest in it. Effective Gross Income (EGI) You typically exclude income taxes because, as mentioned above, properties are often owned by pass-through entities. The PropertyMetrics team has created a free and helpful package for commercial real estate professionals who need a real estate development budget: Download Real Estate Development Budget Template Fill out the quick form below and we'll email you a free real estate development budget template. If you’re looking for solid, free residential or commercial real estate pro forma, then look no further. This allows a clear picture of the free cash flow that is available before taxes to the owners of a property. Discover How To Break Into Investment Banking, Hedge Funds or Private Equity. The model includes a renovation/real estate development pro forma model, where you can easily phase in a renovation over up to a 5-year renovation timeline. Then, create a “mini-Cash Flow Statement” and include only the key, recurring line items, such as Depreciation, the Change in Working Capital, and CapEx. Before wrapping up, I want to address two topics that generate a lot of questions: “1) Why does your real estate pro-forma not include income taxes? A property might have $200K in capital costs one year, then $0 the next year, then $800K, then $300K, and then $200K. A complete & thorough Commercial Real Estate Model for you to adapt and use for your own usage. Real Estate Financial Excel Analysis Tools Kit and Templates for educational purposes ... Real Estate Professional Developer's Excel Tool Kit Template. Not all tenants pay market rates; some might be paying lower rates, and some might be paying higher rates.  by Robert Schmidt. And if you want more before then, there’s the full Real Estate Financial Modeling course. cost gap cash flow pymts.