Template: Excel Construction Cash Flow

Previously I discussed my sample of a construction cash flow and have received requests for an excel file to use as a template. Attached below is the workbook that I used to create the construction cash flow, and includes a permanent cash flow with sensitivity analysis.

As always, if you have any questions, please feel free to contact me.

Links:
Construction Cash Flow Template

Tags: Real Estate

How to Buy Real Estate

For those that read my twitter messages, you know that I had tremendous difficulty getting to Washington, DC a few weeks ago. Unfortunately I discovered sub-par customer service, by no fault of the two employees I was dealing with. It is frustrating that a service I need is unavailable to me because I do not have a credit card. Like many people, I inappropriately used my credit cards in college so I have been responsible for two years.

On this sunny Saturday morning, when I needed a credit card swiftly, I went to the only place that I thought could help. The customer service representative I spoke to was unable to assist me with securing a credit card; she allowed me to vent my frustrations for a few minutes, and made me feel better.

Reminded of the ideas I talked about in my introduction, I listen to feedback and discuss what I learn. This website has allowed me to freely express my ideas, and this community has helped me every step of the way. Now, I need some help of my own.

In my post, Real Estate Cash Flow and Sensitivity Analysis, I discussed my process for assessing real estate value. The data reflects this post at PhillyBlog.com. However, there are people who want to help including Kelliann’s, The Black Sheep, The Bishop’s Color, and Monks. My neighbors and I are doing everything we can.

Technorati Tags: Banking, community, entrepreneur, Real Estate

Real Estate Cash Flow and Sensitivity Analysis

In March, I posted about five common real estate expenses and how they affect your rental property’s net income. Recently, I have started to research purchasing multi-family real estate in Philadelphia, and needed a tool where I could easily shift data around. I came up with a worksheet that allows me to input income and expenses, calculate a capitalization (cap) rate and required equity, and calculate a sensitivity analysis for permanent financing. The proceeding are screen shots of my spreadsheet and an explanation of the sections. As usual, all data is for information purposes only.

Income & Expenses (P&L):

This section is basic and includes all income streams and all expense streams for the property. Many of the properties that I have been researching contain four-units, but lines for rental income can be added or subtracted as desired. Operating expenses are from the aforementioned blog post and can also be added or subtracted as desired. There are a few formulas which calculate the vacancy factor, maintenance, and management fees; which are noted in the description.

Capitalization Rate & Equity Injection:

With all of my workbooks, I use a separate worksheet to keep all of my repetitive data concentrated. This takes slightly longer to setup, but makes changing the data much easier. In the “Cap Rate” line, the percentage pulls from a range of capitalization rates selectable by a drop down list defined in the other worksheet. For this type of real estate, I estimate a range of 8.00% to 10.00%, increasing at increments of 0.50%. The valuation; in this case $557,830, calculates by dividing net income with the capitalization rate. Equity injection is entered directly to this worksheet, takes the defined percentage of the valuation, and is used to calculate the required loan amount.

Sensitivity Analysis:

This section has four slots for a permanent financing sensitivity analysis, and allows you to test changes in interest rate, amortization length, or both. Similar to the capitalization rate above, the interest rate and amortization length on the right-hand side pull from another worksheet via a drop-down box. Interest rates range from 6.75% to 7.75% in 0.25% intervals, and amortization lengths range from 10 years to 30 years in 5 year increments. The text box on the left-hand side updates automatically from this data so they don’t have to be changed manually. The middle columns calculate the monthly and annual debt service from the loan amount above, and the interest rate and amortization length from this section. The DSCR section at the bottom calculates by dividing net income with the annual debt service for each scenario.

Putting it Together:

This is how the cash flow looks with all three sections together. As always, feel free to contact me with any questions you may have.

Technorati Tags: Amortization, Cap Rate, Cash Flow, Debt Service, DSCR, Financing, Real Estate, Sensitivity Analysis

Sample of a Construction Cash Flow

Last week I discussed my Four Tips to Calculate an Interest Reserve. Realizing the importance of providing a visual example, I have provided a sample construction cash flow that I created in Microsoft Excel. The example is for a six-building construction project with staggered stages of development, and is populated with simulated data. If required, a larger version of the image is also available.

• The Monthly Draw section shows how much money is required for the project on a monthly basis. In January and February 2007, the customer is developing the work site, and the cost has been spread over all six buildings. In the proceeding four months, the construction will begin, and those costs are assigned to each individual building. The total of monthly draws sums up the total cost on a monthly basis.
• The Total Loan Outstanding section demonstrates how much of the construction loan has been borrowed on an accrual basis. The accrued construction line keeps a running total of construction costs after every month. This is calculated as the sum of all previous months, plus any new costs in the current month. The accrued construction costs are added to any non-construction costs (soft costs, acquisition costs, etc) to create the total loan outstanding. Interest is calculated on a monthly basis by multiplying the total loan outstanding and the projected interest rate; which in this case is 9.0%. It is important to note two things about the interest rate; it should be expressed as a decimal, and must be divided by 12 because it is being calculated on a monthly basis. In this example, an annual interest rate of 9.0% would be expressed as (0.09/12).
• The Rental Income section is used for any income generated by the property on a monthly basis while it is under construction. The section allows for gross rental income, any expenses that may be expected, and net rental income. Net income would be applied towards the monthly interest expense of the property, which reduces the required interest reserve. In this example, the property won’t be generating any income so it has been left blank.
• The Interest section further breaks out interest by expense category. In this example it is the acquisition, soft, and closing costs; which would be paid out immediately, and construction. Total interest should equal the earlier interest calculation.
• The Cash Flow Cushion / (Shortfall) is the difference between monthly interest and any income generated by the property.
• The Interest Reserve section keeps a running total of how much has been used, based on all of the aforementioned sections.

If interested further, please download the full construction cash flow.

Technorati Tags: Cash Flow, Construction, Interest Reserve

Traditional Cash Flow versus UCA Cash Flow

One of the benefits of completing a formal credit training through a third party, is the exposure to different analysis procedures. My formal credit training was provided by the Risk Management Association (RMA) Commercial Lending School; and although it was short in duration compared to traditional institutional programs,
made an excellent accentuation to my real-life banking experience. I consider the course on the Uniform Credit Analysis (UCA) Cash Flow method invaluable because it is a debt service analysis tool created by bankers for bankers.

Traditional Cash Flow:

The traditional cash flow method relies solely on the income statement for calculating a company’s ability to repay its debts. The cash flow is created using a “bottom-up” approach beginning with net income and adding certain non-cash and cash expenses back to the cash flow. Expenses which are traditionally credited in the cash flow are amortization and depreciation
(non-cash), and interest and taxes (cash) creating what is known as earnings before Interest, Taxes, Depreciation, and Amortization (EBITDA). Debt service through the traditional method is calculated by summarizing the company’s prior-year current portion of long term debt (CPLTD), current year interest expense, and the projected annual debt service from new debt facilities. EBITDA is divided by total debt service to create a debt
service coverage ratio (DSCR) which demonstrates the company’s ability to repay it’s debt. For example, a DSCR of 1.25x states based on the preceding, the company has enough cash flow to cover it’s debt payments by 125%.

UCA Cash Flow:

The UCA cash flow includes a mixture of balance sheet and income statement data which portrays actual changes to cash on hand from a year-to-year basis; which provides a multi-dimensional view into ability to service debt. Balance sheet items included which are not covered by the traditional cash flow include; but are not limited to, actual cash collected from sales, fluctuations in inventory levels, and changes to accounts payable. The cash flow gives an understanding of why cash increases
or decreases between any two periods. The UCA cash flow is split into two main categories; cash flow generated from operations after all core operating expenses have been removed, and the ensuing increases or decreases to cash. While the former is intricate to the UCA cash flow, for analysis purposes is highlighted as net cash after operations (NCAO). This is the equivalent starting point of net income when compared to the traditional cash flow.

The UCA cash flow utilizes a “top-down” approach beginning at NCAO and continuously reconciling how much cash a company has on-hand after a number of activities.  The cash flow can be broken into three sections; debt amortization, capital expenditures, and financing activities.

• Cash after debt amortization (CADA) highlights how much cash a company has on hand after paying its interest payments, shareholder dividends, and current year CPLTD. If CADA results in a negative figure, it demonstrates that a compay cannot pay it’s debt through it’s core operations.
• Capital expenditures, or fixed assets, is removed from the cash flow and demonstrates how much cash remains or financing required. A financing requirement does not automatically equate to a problem because many companies utilize debt to finance equipment upgrades, expansions, and etcetera. However, it is important to understand the difference between extraordinary expenditures and those required from a year to year basis.
• Financing activities demonstrate cash generated or reduced when borrowing or repaying loans. Traditional methods of borrowing funds include short term borrowings through a line of credit, long term borrowings through term facilities, and borrowing money from shareholders; and support working capital and capital expenditures. It is important to note that a -0- in this section doesn’t mean that no funds are outstanding, but that the loan amounts have not changed in a given period.

Ending cash is calculated by reconciling the cash surplus or deficit to beginning cash, and demonstrates funds available to service any new debt. The DSCR is calculated by dividing ending cash by the projected annual debt service from new debt facilities, providing an outlook into the company’s ability to repay. Similar to the traditional cash flow, a DSCR of 1.25x states based on the preceding, the company has enough cash flow to cover it’s debt payments by 125%.

The charts provided are from my final Commercial Lending School case study where  the company is purchasing its office and manufacturing facility from a third party, The charts highlight how the two methods can lead to conflicting results based on the exact same data.If you would like more details about this example, please view my writing sample.

Technorati Tags: Commercial Lending, RMA, Financial Statements, Traditional Cash Flow, UCA Cash Flow

Four Tips To Calculate an Interest Reserve

In integral part of any construction financing package is to create a reserve of funds to pay the monthly interest. This is possible because construction financing traditionally requires only interest payments to allow you to focus on completing your project. Interest reserves are common in construction lending but calculating the amount can be complex; especially with larger projects. If the reserve is too small it will
run out before the project is complete, and if it is too large you will over pay on loan fees.

• Budget your project: There are a number of expenses that you will face including your acquisition cost (if you don’t own the property), construction costs, and soft or other costs. Construction costs primarily consist of your materials and labor, while soft costs include insurance, taxes, legal and professional fees, and marketing. Remember that setbacks with any project can occur so adding a small contingency to you budget to account for these issues is a good idea.
• Plan your timetable: Once you have budgeted, plan out the construction time frame in bi-weekly or monthly increments. This will give you an understanding of how much of your construction loan will be outstanding at every period, which is important for calculating your reserve. Also, it will most likely be required by the financial institution you are doing business with. It is important to keep in mind that delays may occur in construction so make sure to plan for them.
• Know your interest rate: Construction loans are based off of either the Wall Street Journal Prime Rate (Prime) or the London Interbank Offered Rate (LIBOR) rate index. The former is tied to the federal funds rate, which may change at irregular intervals while the latter is quoted at intervals of either three,
  six, or twelve months. Talk with your financial institution about which rate index they will use, and how many basis points they are charging.
• Putting it all together: Your monthly interest expense is calculated when you multiply the outstanding loan balance at month’s end by your quoted interest rate, and your estimated interest reserve is the sum of those monthly interest payments.. It is important to note that you must divide the interest rate by 12, to accurately reflect monthly payments.

Technorati Tags: Interest Reserve, LIBOR, Prime, Construction Loan

Five Common Real Estate Expenses

When owning real estate you will find that there are a number of expenses taking away from your bottom line. A number of these costs are unavoidable and will vary depending on the location of your property or number of tenants, while others can be reduced or even eliminated. I have listed five of my most common expenses below, excluding mortgage payments.

• Real estate taxes; also known as property taxes, are assessed on the land and building value for your property. These expenses cannot be reduced or mitigated because they are set by the local government.
• Property insurance is integral for any owner because it protects you against damage to your real estate and any personal injuries that occur on the property. The cost to insure your property depends on a number of variables including but not limited to property value, building value, and number of families living on the premises. Try negotiating with a few insurance agents to get the best deal on property insurance.
• Repairs and maintenance  will vary from year to year ranging from minor expenses to major repairs. Annual costs will include cleaning of common areas, touch ups to the property at the end of lease terms, and general maintenance. Uncommon or extraordinary expenses include the replacement of major appliances, repairs to the building structure (for example the roof), or major renovations. It is a good tip to set a reserve of annual income to cover major repairs when they arise.
• Utilities are traditionally split between tenant and landlord with the former paying utilities related to personal living expenses. The landlord typically pays common utilities including water, sewer maintenance, and a portion of electricity. In certain situations; such as properties located in extremely hot or cold climates, landlords may opt to pay for a larger portion of the utilities.
• Administration fees occur when you hire a third party to handle the management of your property. The property manager collects rent, handles maintenance requests, leases the property, and handles other property management. Your costs will vary depending on how much of the management is handled by a third party. To save money, some or all of the management can be handled by the landlord.

Remember, this is just a guideline and your mileage may vary.

Technorati Tags: Banking, Commercial Lending, Real Estate Taxes, Property Insurance

The Costs: A Basic Rundown

So you have found your perfect property but outside of the purchase price, what are the other costs? Costs can be broken down in two subcategories; acquisition and financing. The former are incurred when completing a deal between you (buyer) and the seller, while the latter are incurred between you and your finance institution; if you require one.

Acquisition Costs

• If you are using a real estate broker, a fee ranging from 5.0% to 7.0% is usually charged and split between the buyer and seller.
• A tax known as a transfer tax occurs when title changes on real property. This tax ranges from 2.0% to 4.0% and varies by municipality. It is split between both the buyer and the seller.

Financing Costs

• The origination fee averages 1.0% but could be higher depending on the lender. It is also known for lenders to forgo an origination fee in favor of a higher interest rate.
• Interest rates vary depending on the length of your loan and the corresponding rate index. Indexes include but are not limited to the Wall Street Journal Prime Rate (Prime), the Five Year U.S. Treasury Rate (5-year UST), and the London Interbank Offered Rate (LIBOR). On top of the index, the Bank charges a spread in basis points; which are equal to 1/100th of a percent.
• Title insurance ensures that the property is owned by the seller, and that it is clear of liens for unpaid taxes, claims of ownership by third parties, and other issues which my damage your ownership in the property. Title insurance is purchased up-front at a rate determined by the loan amount, and is a requirement with a lender.
• Other small fees which may or may not be charged by your financial institution include an appraisal fee, analysis fee, and search fees.

For more information, the Zillow Real Estate Wiki offers great information, including how to save on closing costs.

Technorati Tags: Banking, Commercial Lending, Zillow