Analyzing multifamily real estate investments is challenging because such analysis requires both complicated tax and financial calculations.
To show clients and others how this analysis is supposed to work, our firm created a simple starter Excel workbook. You can download the multifamily real estate investment analysis template by clicking its link here:
Based on your inputs, this multifamily real estate investment analysis template creates a cash flow forecast. Then, using this cash flow forecast, the template calculates two common measures of investment profitability, an internal rate of return and a net present value.
Some Tips Before You Start
As you begin working with the multifamily real estate investment analysis template, consider employing the following tips:
Tactic #1: If you’re new to financial analysis, focus on the schedules that show operating cash flows and taxable income. Furthermore, to assess the overall goodness of a prospective investment, use the internal rate of return measure, or IRR. An IRR calculates the return or yield you earn on a particular real estate investment considering all the cash flows, including income, expenses, taxes, and appreciation that’s realized upon sale.
Note: Whether you realize it or not, you’ve probably already used IRR measures in your investment decision making. When you choose a bank certificate of deposit based on the annual percentage yield, for example, you’re using the IRR measure (because the annual percentage yield is essentially the IRR).
Tactic #2: Don’t worry about the net present value profitability, or NPV, measure. That financial tool is a superior measure of investment goodness, but it’s also something you probably need a college degree in finance to use appropriately. (If you ask a CPA to help you with your real estate investment analysis, however, do have him or her explain how the NPV measure works and why it’s a better way to think about investments.)
Tactic #3: Be sure to experiment with different inputs. Real estate investment returns are often very sensitive to small changes in the inputs. Experimenting with different inputs will let you see how attractive (or unattractive) a prospective real estate investment becomes if, for example, the vacancy rate is higher or the management fee is lower.
Instructions for Using the Multifamily Real Estate Investment Analysis Template
To use the multifamily real estate investment analysis template, you describe a prospective real estate investment by providing about forty inputs.
The general rule is that you need to replace any value shown in a cell that’s green. You may replace (carefully) the formula shown in a cell that’s yellow with either another formula or a value.
You should not replace or edit the formula shown in a cell that’s red. Like a colors on a traffic signal light, then, green signals “go ahead,” yellow signals “caution,” and red signals “stop—danger!”.
Here, by the way, are the specific “green cell” inputs you should enter:
1. Enter the inflation rate you expect in cell B2. This value inflates the rental and other income amounts and the operating expense amounts. (The template initially shows the inflation rate as .03, or 3%, which is close to the long-run inflation rate and the long-run appreciation rate of real estate.)
2. Enter the vacancy rate you expect in cell B3. (The template initially shows this value as .05, or 5%. You’ll probably want to use the local vacancy rate as your starting point, however. Note that the vacancy rate is used to “adjust” the potential income from rents and other sources like laundry, late fees, and parking.)
3. Enter the purchase price in cell B6, the down payment amount in cell B7, the depreciable basis in cell B8, and the closing costs paid by you as the purchaser in cell B9.
4. Describe the loan you’ll use to finance the property by entering the loan fee you’ll pay into cell B12, the loan amount into cell B13, the loan’s annual interest rate in cell B14, and the loan term (stated as a number of monthly payments) into cell B15. The template calculates the monthly loan payment based on the loan inputs you enter and shows this value in cell B17. The workbook’s calculated value should match closely what the lender calculates.
5. Describe how your real estate investment losses or profits will be taxed by entering your marginal income tax rate into cell F2 and your capital gains tax rate into cell F3. Note that the template implicitly assumes any real estate losses are not limited by passive loss limitation rules. (You might want to confer with your CPA if you have questions about how these loss limitation rules work.)
6. Describe your forecast of the property’s value at the end of the ten-year forecasting horizon by entering an income capitalization rate into cell F6 and an estimate of the selling costs (as a percentage) into cell F7. Note that the property value at the end of the tenth year (the last year of the forecast) is calculated by capitalizing the expected income in the eleventh year.
7. If you understand how to work with and use net present value profitability measures, enter a pretax discount rate into cell F10 and an aftertax discount rate into cell F11.
8. Describe the rental income by specifying the monthly rental amounts into the worksheet rate B23:B25 and the numbers of each type of rental unit into the worksheet range C23:C25. In the example set of numbers, the workbook shows that studio apartments rent (initially) for $625 a month and that there are two studio apartments in the property.
9. Describe the other income by specifying the price per laundry use in cell B28 and the number of laundry uses in cell C28, the late fee amount in cell B29 and the number of late fee charges in cell C29, and the monthly parking fee amount in cell B30 and the number of monthly parking fees in cell C30. In the example set of numbers, the template shows a laundry fee of $0.75 and 500 uses during the year.
10. Use the worksheet range C39:C50 to record your starting year expense estimates. These expense categories match those used on the U.S. Federal Income Tax Schedule E, so you may be able to get good estimates of the numbers from the owner (because he or she can get them from his or her recent tax return). Note that the expenses for future years are adjusted by annually inflating your starting values by the inflation rate.
Good luck in your real estate investment analysis! If you have questions about how real estate accounting or taxation works, confer with your CPA. For existing clients, our firm is also happy to review and discuss the results of your real estate investment analysis.
One other, tangential tip: The Real Estate Loopholes ebook is a wonderful resource for both new and experienced real estate investors. Note that we offer a money-back guarantee for this book, which is available for instant download. So you should try it and then ask for a refund if the book isn’t (in your asessment) well worth the modest price.