 |
| Discounted Cash Flow Analysis of Realty Investments |
You can calculate capitalization rates and property overall rates of return by hand, on a piece of scratch paper or with a calculator. And you can get a general idea about the effect of financial leverage using a simple rule of thumb...Positive leverage occurs when the property's overall rate of return exceeds the loan interest rate.
Unfortunately, you can't estimate precise rates of return on leveraged investments that include all of these factors except with a computer's help.
Fortunately, if you do have a computer and know a little bit about working with Microsoft Excel, you can easily and very precisely calculate what happens when, for example, you borrow money costing four percent to invest in a property that generates a five percent capitalization rate and that appreciates by three percent.
In this discussion, therefore, I'm going to describe in step-by-step fashion how to calculate precise rates of return generated by leveraged rental property investments using the Short Sale Investment Analyzer available at here at this website.
Unfortunately, you can't estimate precise rates of return on leveraged investments that include all of these factors except with a computer's help.
Fortunately, if you do have a computer and know a little bit about working with Microsoft Excel, you can easily and very precisely calculate what happens when, for example, you borrow money costing four percent to invest in a property that generates a five percent capitalization rate and that appreciates by three percent.
In this discussion, therefore, I'm going to describe in step-by-step fashion how to calculate precise rates of return generated by leveraged rental property investments using the Short Sale Investment Analyzer available at here at this website.
Entering Data into the Short Sale Investment Analyzer
To enter the data into the worksheet, start Excel, open the Short Sale Investment Analyzer workbook, and then follow these steps:
Note: Worksheet cells into which you enter values appear green.
To enter the data into the worksheet, start Excel, open the Short Sale Investment Analyzer workbook, and then follow these steps:
Note: Worksheet cells into which you enter values appear green.
1. Estimate the property appreciation.
To describe the appreciation you expect over the worksheet’s ten year forecasting horizon, enter the inflation rate into cell B2. As noted earlier, I use a 2%, or .02, inflation rate for estimating the appreciation in short sale property. In essence, I’m saying that over ten years, the short sale discount will “disappear” and mean in the end I can sell the property for roughly twenty percent more than I paid for it. You may however want to use 3%, or .03, since that’s the long-term inflation rate.
2. Estimate a vacancy rate.
To account for the average effect of vacancies, enter the area vacancy rate into cell B3. You can surely get this information by Googling the name of your town or neighborhood and the phrase “vacancy rate.” To learn the vacancy rate in Seattle, for example, one can Google on phrase “vacancy rate Seattle.”
3. Describe the purchase terms.
To describe the purchase, enter the selling price into cell B6, the down payment you’ll make into cell B7, the depreciable basis of the building (house or condo) into cell B8, and the amounts you’ll pay for closing and for pre-rental renovation. The selling price is what you think you’ll offer, of course. The down payment is the amount you’ll pay toward that price out of your funds rather than the lender’s funds. The depreciable basis is the amount of the sales price that you’re paying for the building. For example, if you pay $150,000 for a rental house but $120,000 of this amount is for the structure and $30,000 of this amount is for the land, the depreciable basis equals $120,000. The closing costs figure needs to include the escrow and closing costs and any pre-rental renovation or clean www.irs.govup costs you’ll incur (painting, appliances, reseeding lawns, repairs and so forth).
4. Describe your financing arrangements.
3. Describe the purchase terms.
To describe the purchase, enter the selling price into cell B6, the down payment you’ll make into cell B7, the depreciable basis of the building (house or condo) into cell B8, and the amounts you’ll pay for closing and for pre-rental renovation. The selling price is what you think you’ll offer, of course. The down payment is the amount you’ll pay toward that price out of your funds rather than the lender’s funds. The depreciable basis is the amount of the sales price that you’re paying for the building. For example, if you pay $150,000 for a rental house but $120,000 of this amount is for the structure and $30,000 of this amount is for the land, the depreciable basis equals $120,000. The closing costs figure needs to include the escrow and closing costs and any pre-rental renovation or clean www.irs.govup costs you’ll incur (painting, appliances, reseeding lawns, repairs and so forth).
4. Describe your financing arrangements.
To describe the loan you’ll use to finance your investment, enter the loan fee into cell B12, the loan amount into cell B13, the annual interest rate into cell B14, and the loan term (the number of months over which the loan will get paid off) into cell B15. After you enter this information, the worksheet calculates your loan payment, displaying the value in cell B17.
5.Identify your marginal tax rate and expected capital gains tax rate.
To describe the tax effects of the real estate investment, enter the marginal tax rate you pay on your last dollars of income into cell F2 and the capital gains rate you expect if you sell your property at a profit into cell F3. You can get current marginal tax rate information from the www.irs.gov website and (if you live in a state with income taxes) from the equivalent state revenue agency tax website. Note that many short sale property investors will enjoy above average family incomes, so the example set of inputs in the worksheet shows the marginal tax rate as 25%, or .25, and the capital gains tax rate as 15%, or .15. If your income is truly “average,” your marginal tax rate may be 15% and your capital gains rate may be 0%. (Note: The 15% and 0% capital gains rates expire at the end of 2012. You may therefore just want to use the same rate for both the marginal and capital gains tax rate.)
6. Provide an exit capitalization rate.
One of the conventional ways to forecast the ultimate selling price you receive when you sell an investment property is to estimate the future annual net income of the property and then “cap” this income using a future, “exit” capitalization rate. To provide this input to the Short Sale Investment Analyzer workbook, you enter an exit capitalization rate into cell F6. (Note: I think it’s probably most conservative to say that capitalization rates for small rental properties will stay constant and that, therefore, your “exit” cap rate will roughly equal your “entry” cap rate. But you might want to use a lower exit cap rate than the entry cap rate because the entry cap rate should be higher due to the short sale discount. You should also enter an estimated selling costs percentage into cell F7.
7. Provide a pretax discount rate, or “threshold” required rate of return.
Here’s the one, rather tricky conceptual part of doing discounted cash flow analysis: You need to enter the pretax discount rate you expect into cell F10 and the after-tax discount rate you expect into cell F11. If you’ve taken a finance course, you can change the values shown in cells F10 and F11. (The example set of inputs in the Short Sale Investment Analyzer worksheet show as 12% and 9%.) But if you’re not familiar with how discount rates work, don’t worry about changing the default values just yet. I’ll talk about this more in a few paragraphs, but essentially the discount rate inputs let you verify that you’re earning a specified pretax, threshold rate of return. If you enter a pretax discount rate of 12%, for example, the worksheet lets you see in dollars whether you’re achieving the 12% return—and if so, by how much. The after-tax discount rate works the same way. If you set the after-tax discount rate to 9%, or .09, the worksheet checks that you’re earning an after-tax 9% rate of return.
8. Forecast your starting monthly rent.
To allow for a forecast of the income the property will generate, enter the monthly rent into cell B23. The worksheet range B24:C26 lets you include additional income in your forecast. In the case of a single family home, you may not have any additional income. But if you have coin laundry machines, you can enter the price per wash load into cell B24 and the number of wash loads into cell C24. You can forecast late fee income by entering the late fee you’ll charge a tenant into cell B25 and the number of times you think you’ll assess a late fee each year into cell C25. If you charge for parking, you could enter the monthly parking fee into cell B26 and the number of parking spots into cell C26. You can experiment with different sets of values to see how the formulas in D23, D24, D25 and D26 work. But know that the only income amount you need to enter is the monthly rent amount shown in cell B23 and that the worksheet annualizes your monthly values to turn them into annual forecasts.
9. Forecast your starting annual operating expenses.
To describe the operating expenses of the rental property, enter the annual budgeted expense amounts into the worksheet range D35:D46. For example, if you expect to pay an advertising charge equal to $50 the first year, enter 50 into cell D35. If you expect to spend $50 on car or truck expenses (perhaps because you run by the property each month), enter 50 into cell D36. You can leave some cells empty. But know that on average operating expenses probably run about 45% if you’ll out-source the management and maintenance. If you’ll do the maintenance and management yourself, your total operating expenses may drop to 25%.
Using the Short Sale Investment Analyzer
After you enter the inputs into the Short Sale Analyzer worksheet, Excel calculates estimates of the property’s profitability, including the operating income, the pretax and after-tax cash flows, and the rates of return measures. These rate of return measures let you see what sort of return you can expect on an investment in the property.
Tip: Be sure to try different sets of inputs, experimenting with variables that may easily change such as the inflation rate, the vacancy rate and so forth. Trying different “what-if” scenarios will help you size up both the potential profitability and risks of a particular property.
Reviewing and Understanding the Forecasted Income
Part I of the Short Sale Investment Analyzer worksheet estimates the operating income, operating expenses and cash flows from the property and appears in the worksheet range A22:N49. Essentially, the worksheet takes your year 1 estimates and then inflates these values annually using the inflation rate you provided.
For example, if your year 1 estimate of the monthly rate is $1500 that means the year 1 annual rent equals $18,000, because $1500 a month times twelve months equals $18,000. If you’ve set your annual inflation or appreciation to 2%, in year 2 the worksheet inflates the $18,000 year 1 annual income by 2% to $18,360. And expenses work the same basic way with your starting (year 1) values getting annually bumped by the inflation rate you specified.
Part I of the worksheet totals forecasted income and expenses and then estimates the operating income by subtracting the expenses from the income. The operating income shows in the worksheet range D49:N49. The worksheet also shows the first year’s cap rate in D51.
Reviewing and Understanding the pretax Cash Flows and Rate of Return Measurements
Part II of the Short Sale Investment Analyzer worksheet, which appears in the worksheet range A53:M68, estimates the pretax cash flows that the property generates. To estimate these values, the worksheet range calculates the initial investment required to “get into” the investment (this includes the down payment, any closing costs, and any upfront renovation costs), the annual cash flows (which include the operating income and the loan payments), and the sales proceeds you’ll receive if you sell the property in ten years based on the forecasted year 11 income being capped using your exit cap rate from cell F6.
With the pretax cash flows estimates, the worksheet calculates two, incredibly useful rate of return measures: The pretax IRR, or internal rate of return, shown in cell C66 and the pretax NPV, or net present value, shown in cell C68.
The pretax IRR value gives you the annualized, before taxes return you would expect to earn annually given your estimates of operating income, annual inflation, and the effects of financial leverage. You can compare these values, for example, to the rates of return that mutual funds and investment managers tout. Keep in mind, however, that you cannot always calculate an IRR on a real estate investment.
Note: Just to make this important point, when you see that some investment (a mutual fund) or that some investor advisor has earned 14% in some year, that percentage is a pretax internal rate of return. You can therefore compare the estimated pretax internal rate of return from the worksheet with these sorts of actual historical pretax returns. It’s obvious, I hope, that real estate investors should do this...
Caution: Without getting into the mathematical complexities, some investments don’t have IRRs. For example, if you truly get into a property with no money down, you can’t calculate an IRR because logically there’s no “investment” to use as the denominator in the return on investment calculation. Furthermore, sometimes IRRs become sort of meaningless for properties with cash flows the fluctuate—some years showing positive cash flows and other years showing negative cash flows.
The pretax NPV value shows you whether the investment property you’ve described achieves or falls short of a specified rate of return. With the example real estate investment shown initially in the Short Sale Investment Analyzer worksheet, the pretax NPV value equals $10,504 based on a 12% pretax discount rate. What this means is that the example investment exceeds a 12% return by $10,504 in present dollars. Note that if the NPV equaled zero, that would mean the investment generates exactly a 12% return. Furthermore, if the NPV equaled a negative value, that would mean that the investment falls short of the 12% threshold implied by a 12% pretax discount rate.
Two quick points about the NPV measure: First, you should set the discount rate to the threshold investment return you want to achieve on a property given your other investment options. A 12% pretax discount rate is common for real estate and would essentially be like saying, “well if the stock market produces 10% over long periods of time, I want to be able to earn at least 12% on my real estate deals given the extra risk and time involved.”
A second quick point: You can easily compare investments using NPVs. The investment with the highest NPV is the one you should select since that’s the one that will make you the most money. Note that you can’t use the IRRs to pick confidently the best investment in many circumstances. For example, using only IRRs and being a little exaggerated, blind application of the IRR measure would mean that you could tell yourself that it’d make more sense to invest in a small $10,000 property that delivered a 20% rate of return for one year, instead of a $100,000 property that delivered 19% return for, say, fifty years. (Just think about this for a minute if you don’t get at first why the second investment is better...)
Reviewing and Understanding the After-tax Cash Flows and Rate of Return Measurements
Part III of the Short Sale Investment Analyzer worksheet, which appears in the worksheet range A70:M93, estimates the after-tax cash flows that the property generates by taking the pretax operating cash flows and then adding any tax savings and subtracting any tax expenses that the investment generates either through positive income or because you enjoy a profit when you sell.
Note: I talk more about the tax accounting issues related to real estate investment in "Appendix B - Understanding Real Estate Tax Accounting,” but the two new deductions that appear in Part III are the building depreciation deduction and the loan fee amortization. These extra expenses along with the deductible mortgage interest you pay on any financing may mean, as discussed in Appendix B, that you pay income taxes on investment profits or that you save taxes because the investment shows a loss.
With the after-tax cash flows estimates, the worksheet calculates two, after-tax rate of return measures: The after-tax IRR, or internal rate of return, shown in cell C91, and the after-tax NPV, or net present value, shown in cell C93.
Reviewing the Loan Amortization Schedule
The second sheet in the Short Sale Investment Analyzer workbook, “Loan Amortization Schedule,” shows the monthly payments for each month of the loan, the breakdown of payments into interest and principal components, and the remaining mortgage loan balance after the payment of principal.
Part II and Part III of the Short Sale Investment Analyzer workbook use data from this worksheet in their calculations. But you can also use the loan amortization schedule to see how the loan balance declines over time and to guess at the loan balance at particular points in time.
5.Identify your marginal tax rate and expected capital gains tax rate.
To describe the tax effects of the real estate investment, enter the marginal tax rate you pay on your last dollars of income into cell F2 and the capital gains rate you expect if you sell your property at a profit into cell F3. You can get current marginal tax rate information from the www.irs.gov website and (if you live in a state with income taxes) from the equivalent state revenue agency tax website. Note that many short sale property investors will enjoy above average family incomes, so the example set of inputs in the worksheet shows the marginal tax rate as 25%, or .25, and the capital gains tax rate as 15%, or .15. If your income is truly “average,” your marginal tax rate may be 15% and your capital gains rate may be 0%. (Note: The 15% and 0% capital gains rates expire at the end of 2012. You may therefore just want to use the same rate for both the marginal and capital gains tax rate.)
6. Provide an exit capitalization rate.
One of the conventional ways to forecast the ultimate selling price you receive when you sell an investment property is to estimate the future annual net income of the property and then “cap” this income using a future, “exit” capitalization rate. To provide this input to the Short Sale Investment Analyzer workbook, you enter an exit capitalization rate into cell F6. (Note: I think it’s probably most conservative to say that capitalization rates for small rental properties will stay constant and that, therefore, your “exit” cap rate will roughly equal your “entry” cap rate. But you might want to use a lower exit cap rate than the entry cap rate because the entry cap rate should be higher due to the short sale discount. You should also enter an estimated selling costs percentage into cell F7.
7. Provide a pretax discount rate, or “threshold” required rate of return.
Here’s the one, rather tricky conceptual part of doing discounted cash flow analysis: You need to enter the pretax discount rate you expect into cell F10 and the after-tax discount rate you expect into cell F11. If you’ve taken a finance course, you can change the values shown in cells F10 and F11. (The example set of inputs in the Short Sale Investment Analyzer worksheet show as 12% and 9%.) But if you’re not familiar with how discount rates work, don’t worry about changing the default values just yet. I’ll talk about this more in a few paragraphs, but essentially the discount rate inputs let you verify that you’re earning a specified pretax, threshold rate of return. If you enter a pretax discount rate of 12%, for example, the worksheet lets you see in dollars whether you’re achieving the 12% return—and if so, by how much. The after-tax discount rate works the same way. If you set the after-tax discount rate to 9%, or .09, the worksheet checks that you’re earning an after-tax 9% rate of return.
8. Forecast your starting monthly rent.
To allow for a forecast of the income the property will generate, enter the monthly rent into cell B23. The worksheet range B24:C26 lets you include additional income in your forecast. In the case of a single family home, you may not have any additional income. But if you have coin laundry machines, you can enter the price per wash load into cell B24 and the number of wash loads into cell C24. You can forecast late fee income by entering the late fee you’ll charge a tenant into cell B25 and the number of times you think you’ll assess a late fee each year into cell C25. If you charge for parking, you could enter the monthly parking fee into cell B26 and the number of parking spots into cell C26. You can experiment with different sets of values to see how the formulas in D23, D24, D25 and D26 work. But know that the only income amount you need to enter is the monthly rent amount shown in cell B23 and that the worksheet annualizes your monthly values to turn them into annual forecasts.
9. Forecast your starting annual operating expenses.
To describe the operating expenses of the rental property, enter the annual budgeted expense amounts into the worksheet range D35:D46. For example, if you expect to pay an advertising charge equal to $50 the first year, enter 50 into cell D35. If you expect to spend $50 on car or truck expenses (perhaps because you run by the property each month), enter 50 into cell D36. You can leave some cells empty. But know that on average operating expenses probably run about 45% if you’ll out-source the management and maintenance. If you’ll do the maintenance and management yourself, your total operating expenses may drop to 25%.
Using the Short Sale Investment Analyzer
After you enter the inputs into the Short Sale Analyzer worksheet, Excel calculates estimates of the property’s profitability, including the operating income, the pretax and after-tax cash flows, and the rates of return measures. These rate of return measures let you see what sort of return you can expect on an investment in the property.
Tip: Be sure to try different sets of inputs, experimenting with variables that may easily change such as the inflation rate, the vacancy rate and so forth. Trying different “what-if” scenarios will help you size up both the potential profitability and risks of a particular property.
Reviewing and Understanding the Forecasted Income
Part I of the Short Sale Investment Analyzer worksheet estimates the operating income, operating expenses and cash flows from the property and appears in the worksheet range A22:N49. Essentially, the worksheet takes your year 1 estimates and then inflates these values annually using the inflation rate you provided.
For example, if your year 1 estimate of the monthly rate is $1500 that means the year 1 annual rent equals $18,000, because $1500 a month times twelve months equals $18,000. If you’ve set your annual inflation or appreciation to 2%, in year 2 the worksheet inflates the $18,000 year 1 annual income by 2% to $18,360. And expenses work the same basic way with your starting (year 1) values getting annually bumped by the inflation rate you specified.
Part I of the worksheet totals forecasted income and expenses and then estimates the operating income by subtracting the expenses from the income. The operating income shows in the worksheet range D49:N49. The worksheet also shows the first year’s cap rate in D51.
Reviewing and Understanding the pretax Cash Flows and Rate of Return Measurements
Part II of the Short Sale Investment Analyzer worksheet, which appears in the worksheet range A53:M68, estimates the pretax cash flows that the property generates. To estimate these values, the worksheet range calculates the initial investment required to “get into” the investment (this includes the down payment, any closing costs, and any upfront renovation costs), the annual cash flows (which include the operating income and the loan payments), and the sales proceeds you’ll receive if you sell the property in ten years based on the forecasted year 11 income being capped using your exit cap rate from cell F6.
With the pretax cash flows estimates, the worksheet calculates two, incredibly useful rate of return measures: The pretax IRR, or internal rate of return, shown in cell C66 and the pretax NPV, or net present value, shown in cell C68.
The pretax IRR value gives you the annualized, before taxes return you would expect to earn annually given your estimates of operating income, annual inflation, and the effects of financial leverage. You can compare these values, for example, to the rates of return that mutual funds and investment managers tout. Keep in mind, however, that you cannot always calculate an IRR on a real estate investment.
Note: Just to make this important point, when you see that some investment (a mutual fund) or that some investor advisor has earned 14% in some year, that percentage is a pretax internal rate of return. You can therefore compare the estimated pretax internal rate of return from the worksheet with these sorts of actual historical pretax returns. It’s obvious, I hope, that real estate investors should do this...
Caution: Without getting into the mathematical complexities, some investments don’t have IRRs. For example, if you truly get into a property with no money down, you can’t calculate an IRR because logically there’s no “investment” to use as the denominator in the return on investment calculation. Furthermore, sometimes IRRs become sort of meaningless for properties with cash flows the fluctuate—some years showing positive cash flows and other years showing negative cash flows.
The pretax NPV value shows you whether the investment property you’ve described achieves or falls short of a specified rate of return. With the example real estate investment shown initially in the Short Sale Investment Analyzer worksheet, the pretax NPV value equals $10,504 based on a 12% pretax discount rate. What this means is that the example investment exceeds a 12% return by $10,504 in present dollars. Note that if the NPV equaled zero, that would mean the investment generates exactly a 12% return. Furthermore, if the NPV equaled a negative value, that would mean that the investment falls short of the 12% threshold implied by a 12% pretax discount rate.
Two quick points about the NPV measure: First, you should set the discount rate to the threshold investment return you want to achieve on a property given your other investment options. A 12% pretax discount rate is common for real estate and would essentially be like saying, “well if the stock market produces 10% over long periods of time, I want to be able to earn at least 12% on my real estate deals given the extra risk and time involved.”
A second quick point: You can easily compare investments using NPVs. The investment with the highest NPV is the one you should select since that’s the one that will make you the most money. Note that you can’t use the IRRs to pick confidently the best investment in many circumstances. For example, using only IRRs and being a little exaggerated, blind application of the IRR measure would mean that you could tell yourself that it’d make more sense to invest in a small $10,000 property that delivered a 20% rate of return for one year, instead of a $100,000 property that delivered 19% return for, say, fifty years. (Just think about this for a minute if you don’t get at first why the second investment is better...)
Reviewing and Understanding the After-tax Cash Flows and Rate of Return Measurements
Part III of the Short Sale Investment Analyzer worksheet, which appears in the worksheet range A70:M93, estimates the after-tax cash flows that the property generates by taking the pretax operating cash flows and then adding any tax savings and subtracting any tax expenses that the investment generates either through positive income or because you enjoy a profit when you sell.
Note: I talk more about the tax accounting issues related to real estate investment in "Appendix B - Understanding Real Estate Tax Accounting,” but the two new deductions that appear in Part III are the building depreciation deduction and the loan fee amortization. These extra expenses along with the deductible mortgage interest you pay on any financing may mean, as discussed in Appendix B, that you pay income taxes on investment profits or that you save taxes because the investment shows a loss.
With the after-tax cash flows estimates, the worksheet calculates two, after-tax rate of return measures: The after-tax IRR, or internal rate of return, shown in cell C91, and the after-tax NPV, or net present value, shown in cell C93.
Reviewing the Loan Amortization Schedule
The second sheet in the Short Sale Investment Analyzer workbook, “Loan Amortization Schedule,” shows the monthly payments for each month of the loan, the breakdown of payments into interest and principal components, and the remaining mortgage loan balance after the payment of principal.
Part II and Part III of the Short Sale Investment Analyzer workbook use data from this worksheet in their calculations. But you can also use the loan amortization schedule to see how the loan balance declines over time and to guess at the loan balance at particular points in time.
0 komentar: