Real Estate Investment Analysis
Excel Spreadsheet Assignment
More to read: PESTL Analysis Assessment Task
Develop an Excel spreadsheet to conduct a before-tax cash flow analysis of a real estate investment. Your spreadsheet should allow you to alter the following inputs:
- Purchase price;
- Total rentable square feet and initial base rent per square foot;
- Market vacancy rate and operating expense ratio;
- Rent growth rate;
- Financing assumptions (maximum LTV ratio, minimum DCR, interest rate and amortization length in years);
- Expected holding period in years;
- Terminal cap rate at sale; and
- Your assumed equity discount rates.Build your spreadsheet to calculate the following:
- The pro forma operating statement for each year of the holding period, beginning with potential gross income and ending with before-tax cash flow;
- The going-in capitalization rate, cash-on-cash return, gross income multiplier, breakeven ratio, loan-to-value ratio and debt-coverage ratio;
- The up-front cash flows required to acquire the property;
- The cash flows from the sale of the property;
- The total levered cash flows in each year of the holding period; and
- The NPV and IRR of the levered cash flows.
Note that because the user can changed the assumed holding period, your spreadsheet will need to adjust properly to take this into account.
Read factors to consider in a real estate investment analysis
Your spreadsheet should be professional in appearance. This means it should display in a manner that you would turn over to a client or your boss. To this end, you should keep the following in mind:
- The spreadsheet should work correctly if I change any of the assumptions listed above. It should also be clear to the user where to input these values.
- The spreadsheet should be easy to read and attractive. Make sure your number formats are consistent. Choose professional fonts and colors.
- You must use absolute and relative cell referencing properly. The true power of spreadsheets lies in writing your formula correctly so that you can just copy them down rather than rewriting them for each cell. In addition to looking at the formulas themselves, I will adjust the inputs to your spreadsheet and see if it calculates everything properly.
- MAKE SURE YOUR NAME APPEARS ON YOUR SPREADSHEET.
There are a handful of built-in formulas you may want to use in your spreadsheet. I have listed a few of them here. Note that you can get help for these functions in Microsoft Excel.
- PV − Calculates the present value of an ordinary annuity (mortgage). Note that you must enter the periodic rate in this formula, which is the annual interest rate divided by 12 in our case.
- PMT – Calculates the payment on an ordinary annuity. Once again, enter the periodic interest rate.
- FV − Future value after a series of periodic payments. This function also uses the periodic interest rate.
- NPV – Calculates the present value of a set of future cash flows; to get the NET present value you must subtract off any date zero cash flows.
- IRR – Calculates the internal rate of return on a set of cash flows. Unlike the NPV function, you include the “date zero” cash flows in the range of values in this function.
- VLOOKUP and HLOOKUP – These functions allow you to “look up” and pull specific data from a table based on a given input.
- IF – The IF function allows you to change what goes into a cell based on conditions.