3.C2. Syndicated Deal Analyzer Spreadsheet Walk Through

Understanding the Modified Spreadsheet

Overview of the Spreadsheet

• The speaker introduces a modified spreadsheet, emphasizing that it is not necessary to purchase the original version as they have provided access to their own.

• The speaker compares this spreadsheet with a simpler one used in previous meetings, noting that this version is more complex and suited for detailed project analysis.

Pros and Cons of Using the Spreadsheet

• A key advantage of this spreadsheet is its widespread use; thousands of copies sold ensure formulas are regularly updated based on user feedback.

• Users can modify any aspect of the spreadsheet, allowing for tailored development scenarios but also increasing the risk of errors.

Understanding Returns and Scenarios

• The spreadsheet allows users to analyze different financial scenarios, such as cash-out refinancing or property sales, optimizing returns for investors.

• Unlike simpler spreadsheets, this tool accounts for various stages in a project's lifecycle, including pre-stabilization periods and potential liquidity events.

Challenges with Syndication Calculators

Limitations in Financial Projections

• A significant drawback noted by the speaker is that syndication calculators require liquidity events to provide meaningful projections; long-term holds complicate calculations.

• The speaker suggests disclosing plans for refinancing or selling within specific timeframes while maintaining flexibility regarding long-term holding strategies.

Learning Curve and User Experience

• While videos accompanying the spreadsheet offer valuable guidance, there exists a learning curve due to its complexity—especially for those less comfortable with numbers.

• Users are encouraged not to be overwhelmed by numerous tabs and macros; focusing on essential elements will simplify usage.

Navigating Through Spreadsheet Features

Key Functionalities

• The welcome tab allows users to set minimum criteria for projects, providing immediate feedback on whether targets are met through visual indicators like red and green boxes.

Summary Page Insights

• The main summary page consolidates year-over-year return data into an accessible format suitable for investor presentations without delving into excessive detail.

Understanding Real Estate Financial Projections

Importance of Accurate Financial Projections

• Many investors project rent increases and expense growth to show better margins, but it's crucial to recognize the unpredictability of these figures.

• Be cautious when creating financial projections; small changes can significantly impact returns. Avoid self-deception in deal-making.

Analyzing a Sample Deal

• The speaker uses a spreadsheet for a boutique apartment currently under construction, emphasizing ease by pulling numbers from previous calculations.

• The marketing package section is left blank for development projects since it typically applies to stable properties rather than land purchases.

Spreadsheet Mechanics

• The "My Version" column indicates interest-only periods that automatically amortize, with color coding: blue for user input, black for automatic calculations, and red for modifications made by the speaker.

• The all-in cost is calculated at $2,264,526. This figure represents the total investment needed and serves as both asking price and purchase price.

Financing Structure

• Historically, a 25% down payment has been standard; however, this may increase to 30%, impacting investor returns while improving cash flow.

• Interest-only loan periods are typically set at two years before amortization begins.

Operating Reserves and Closing Costs

• Operating reserves are calculated based on total project costs minus loans plus desired working capital; $25,000 is suggested for projects with fewer than 20 units.

• Closing costs are automatically calculated but can be adjusted if necessary.

Clarifying Cost Inclusions

• All-in costs should include due diligence expenses like appraisals and surveys; thus closing costs appear lower in other calculators used previously.

• By consolidating all costs into the asking price rather than itemizing them separately (like land vs. improvements), financing becomes more manageable.

Understanding Gross Potential Rent and Expenses

Breakdown of Rental Income

• The discussion begins with the concept of gross potential rent, emphasizing the importance of breaking down total income into specific unit types for clarity.

• A table in the spreadsheet categorizes different types of units: six small studios, six larger studios, and six one-bedroom studios to provide a clearer view of rental sources.

• Expected rents are set at $1,150 for small studios, $1,300 for larger ones, and $1,500 for one-bedroom units; this helps calculate total expected rental income.

Vacancy Rates and Expense Management

• A vacancy rate of 5% is applied consistently across calculations; however, a higher rate (7%) is suggested for mixed-use properties based on appraisal averages.

• The speaker discusses how expenses can be entered individually or as a percentage (28.1%), highlighting their preference for detailed entry to match expense line items accurately.

Detailed Expense Categories

• Various expenses are outlined: advertising ($500), contract services (e.g., lawn care), utilities ($1,800), general administration ($5,000), insurance ($4,500), legal fees ($1,300).

• Legal costs are discussed in relation to syndications; setting up an LLC is preferred over complex syndication structures due to lower costs.

Property Taxes and Additional Costs

• Property taxes are noted as significant expenses at $34,410; trash removal is combined with water/sewer costs at $1,080.

• Maintenance and repairs budgeted at $4,230 include turnover costs estimated at $3,600.

Financial Projections and Loan Terms

• Management fees are calculated as a percentage (5% if outsourced vs. 6% if managed in-house); this prepares stakeholders for future financial expectations.

• Loan terms include an interest rate of 5.5%, with projections extending over 25 years; the spreadsheet automates interest-only periods followed by amortization schedules.

Cap Rate and Debt Service Coverage Insights

• The cap rate calculation involves dividing net operating income (NOI) by total all-in cost; current market cap rates range from 6.5% to 7%.

• Debt service coverage ratios reflect only interest-only payments initially but will adjust once full loan payments commence.

Understanding Acquisition Fees in Real Estate Development

Overview of Scenario Selection and Financial Inputs

• The discussion begins with selecting a scenario from a dropdown menu, specifically scenario two, which auto-populates relevant fields.

• Earnest money is noted as zero since the project has progressed beyond the purchasing stage; other financial inputs can be adjusted accordingly.

• The speaker mentions the ability to add second mortgages and their varying terms, highlighting flexibility in managing different financing structures.

Acquisition Fee Insights

• An acquisition fee of 2% is mentioned but set to zero on the spreadsheet to avoid inflating total cash needed for financing; this reflects strategic financial management.

• The importance of not disclosing acquisition fees incorrectly to banks is emphasized, as it could misrepresent financial needs during loan applications.

Importance of Disclosure and Investor Communication

• When presenting to investors, it's crucial to disclose any acquisition fees transparently; failure to do so can lead to significant legal issues.

• A question is posed about experiences with acquisition fees among participants, leading into a discussion about their necessity in development deals.

Experiences with Acquisition Fees

• One participant shares that they haven't charged an acquisition fee due to being the sole party involved in their deals.

• Another participant discusses charging an acquisition fee while developing a boutique hotel, indicating variability based on project type.

Developer Compensation Philosophy

• Various fees are discussed (developer fee, refinance fee), typically not exceeding certain limits; understanding these helps structure compensation effectively.

• The speaker reflects on past ignorance regarding taking a piece of the deal without investing cash upfront, emphasizing learning through experience.

Balancing Job Responsibilities and Financial Freedom

• The need for developers to pay themselves adequately is highlighted as essential for sustaining long-term involvement in real estate projects.

• Developers often seek financial freedom but must balance job responsibilities with income needs; acquiring fees help cover operational costs associated with finding and managing deals.

Debt Service Considerations

• Discussion shifts towards debt service calculations; ensuring accurate representation of debt service year is critical for realistic project assessments.

Understanding Cash Flow and Reserves in Real Estate Investments

Importance of Year Two Cash Flow

• In year two, if tenants are secured and leasing is successful, debt service becomes strong as the loan isn't being advertised. The focus shifts to year three when occupancy stabilizes, revealing free cash flow after all debts are settled.

Necessity of Reserves

• It's crucial to maintain reserves for unexpected expenses (e.g., insurance deductibles from damage). New buildings can still face issues that require financial backing for repairs.

• During year two, while on interest-only payments with residents, it's advisable to retain all project cash for reserves instead of distributing it to investors. This builds a safety net against unforeseen costs.

Managing Investor Expectations

• Retaining cash flow in year two may mean investors miss out on distributions but prevents future capital calls due to insufficient reserves. Starting from zero necessitates building up funds gradually.

• Raising enough initial cash for reserves can be financially detrimental since those funds yield no return until utilized years later, potentially lowering overall investor returns.

Distribution Strategies and Preferred Returns

• The decision on how much profit to distribute versus retain is critical; a 70/30 split was used without preferred returns. Investors receive 100% until their preferred return (e.g., 8%) is met before any splits occur.

• Preferred returns imply that investors must receive their specified percentage before the operator receives any profits. This structure complicates distribution but ensures investor priority.

Simplifying Financial Structures

• The speaker prefers avoiding complex structures like waterfalls or preferred returns due to personal simplicity and clarity in communication with investors.

• Asset management fees are typically charged based on collected rents; however, they may not apply if self-managing properties.

Benefits of Preferred Returns

• While preferred returns primarily benefit investors by ensuring they receive their expected yields first, they can also be advantageous for operators under certain conditions where significant jumps in returns are anticipated.

Asset Management Fees and Financial Projections

Introduction to Asset Management Fees

• The speaker discusses charging a 2% asset management fee for managing the property management company, emphasizing that this fee is essential for optimizing total asset value.

• Investors were notified about the introduction of an asset management fee on previous deals, which had not included this charge initially. There was no pushback from investors regarding this change.

Cash Flow and Returns

• Year three is highlighted as the stabilization year in projections, indicating expected cash-on-cash returns for members. The speaker notes discrepancies between their spreadsheet and others' calculations.

• The internal rate of return (IRR) is discussed, with a caution that refinancing or selling can inflate these numbers significantly.

Spreadsheet Adjustments

• Changes made in the spreadsheet are indicated by color coding; red signifies losses while purple indicates formula adjustments. The speaker explains how they manipulate data to pull information correctly from year three instead of year one.

• Occupancy rates are outlined: starting at 100% vacancy in year one, moving to 60-70% by year two, and stabilizing thereafter based on projected rates.

Expense Management Strategies

• Expenses are adjusted based on occupancy rates and growth expectations. Initial years may show zero revenue due to leasing efforts but project increases thereafter.

• Replacement reserves are typically not held during the first year when focusing on breaking even quickly due to limited working capital allocated to projects.

Financing Costs and Interest Expenses

• Interest expenses during construction are accounted for within overall building costs; thus, they appear as zero in initial years of financial projections.

• A conservative approach is taken regarding interest expense estimates, ensuring that projections remain realistic despite potential overestimations.

Understanding Investor Returns

• The calculation of asset management fees is automated within the spreadsheet, providing clarity on distributions among members versus owner returns.

• It's crucial for investors to understand whether returns discussed pertain to project performance or actual payouts received by them (70% vs. 100%).

Debt Service Coverage Insights

Understanding Debt Service and Closing Costs

Overview of Debt Service and Amortization

• The debt service for the project is projected to be strong once it begins amortizing, indicating a solid financial foundation.

• Most closing costs are minimal or zero due to the appraisal being included in the all-in cost, with only origination fees, title policy fees, and potential legal fees remaining.

Breakdown of Closing Costs

• The speaker emphasizes that many costs are financed through the bank loan, contributing to lower apparent closing costs.

• A hypothetical cash-out refinance scenario is introduced for year three after stabilization, which could impact debt service ratios negatively if rents do not keep pace with growing debt.

Financial Projections and Returns

• After refinancing, net proceeds from the sale amount to $187,000; however, a $40,000 cash-out from refinancing may not justify the process.

• The spreadsheet provides detailed calculations on returns based on specific investments by members and general partner returns.

Syndication Structure Insights

• Discussion includes how syndications often split profits among various partners (operating GP vs. money raising GP), although this example simplifies it by showing 100% returns.

Market Conditions and Cap Rates

• Current market conditions suggest cap rates may remain stable or increase due to high interest rates; thus affecting property valuations.

• The importance of understanding formulas used in spreadsheets for calculating values is highlighted; users can manipulate inputs based on their financial strategies.

Refinancing Considerations