Let's break down your request into three deliverables: 1. **Table with Formulas** for the key questions. 2. **Answer Table** with provided correct answers. 3. **Excel Inputs to Fix the Model** so your model matches the answers. --- ### 1. Table with Formulas | Question # | Description | Excel Formula (example) | |------------|--------------------------------------------------|------------------------------------------------------------| | 1 | Unlevered IRR for 5-year holding period | `=IRR(range_of_unlevered_cash_flows)` | | 2 | Levered IRR for 5-year holding period | `=IRR(range_of_levered_cash_flows)` | | 3 | Unlevered IRR for 3-year holding period | `=IRR(range_of_unlevered_CF_first_3_years)` | | 4 | Levered IRR for 3-year holding period | `=IRR(range_of_levered_CF_first_3_years)` | | 5 | Unlevered equity multiple (5 years) | `=SUM(range_of_unlevered_CF)/Initial_Equity_Investment` | | 6 | Levered equity multiple (5 years) | `=SUM(range_of_levered_CF)/Initial_Equity_Investment` | | 7 | Loan constant | `=PMT(loan_interest_rate/periods_per_year, loan_term*periods_per_year, -1)*periods_per_year` | | 8 | Debt yield (Year 1 NOI / loan amount) | `=NOI_Year1/Loan_Amount` | | 9 | Year 1 debt service coverage ratio (DSCR) | `=NOI_Year1/Debt_Service_Year1` | | 10 | Year 1 loan-to-value (LTV) | `=Loan_Amount/Property_Value_Year1` | | 11 | Year 1 cash-on-cash return | `=Levered_Cash_Flow_Year1/Equity_Invested` | | 12 | Sale price at end of Year 5 | `=NOI_Year6/Exit_Cap_Rate` | | 13 | Proceeds after paying off loan at Yr 5 sale | `=Sale_Price - Loan_Balance_End_Year5 - Selling_Costs` | | 14 | New IRR if sale occurs at end of year 3 | `=IRR(leverage_CF_with_3_year_sale)` | | 15 | New IRR if sale occurs at end of year 1 | `=IRR(leverage_CF_with_1_year_sale)` | *Replace range_of_unlevered_cash_flows, etc. with specific cell ranges from your sheet.* --- ### 2. Table with Answers | Question # | Correct Answer | |------------|---------------| | 1 | 7.79% | | 2 | 11.16% | | 3 | 6.24% | | 4 | 12.23% | | 5 | 1.42x | | 6 | 1.59x | | 7 | 6.85% | | 8 | 8.48% | | 9 | 1.29 | | 10 | 0.75 | | 11 | 6.85% | | 12 | 1,378,000 | --- ### 3. Model Review & Excel Inputs to Fix Model #### **Steps to Fix the Model:** - **Ensure correct cash flows:** Double-check your unlevered and levered cash flow rows. Ensure all acquisition, operation, debt service, and sale proceeds are flowing into the correct cash flow lines. - **Correct IRR Ranges:** Make sure the IRR formula references the right periods: - For 5-year IRR: Include all periods from acquisition to year 5 sale proceeds. - For 3-year IRR: Include only up to year 3 and the sale proceeds if applicable. - **Check Loan Inputs:** - Loan constant: Make sure you are using the correct rate and amortization period. - DSCR, LTV, Debt Yield: Confirm the formulas use Year 1 figures. - **Sale Price and Loan Payoff:** - Sale price should use forward NOI and correct exit cap rate. - Loan payoff should include principal remaining after scheduled amortization. - **Cash-on-cash:** Use Year 1 levered cash flow divided by equity invested. #### **Typical Excel Inputs to Check:** - Purchase price, loan amount, interest rate, amortization, cap rate, expense growth, rent growth, selling costs. - Double-check your date ranges and period calculations for IRR. - Make sure your cash flow signs are correct (outflows are negative, inflows positive). --- ### **Example Excel IRR Formula:** If your levered cash flows are in E22:I22 (years 0-5): ```excel =IRR(E22:I22) ``` For sale at year 3, use: ```excel =IRR(E22:G22) ``` (Assuming sale proceeds are added at the end of year 3 in G22.) --- **If you provide your Excel cash flow rows (cell references), I can give you exact formulas with your ranges.** Let me know if you want specific formulas with your sheet’s cell references!

Certainly! Let's walk through **how** each of the answers is obtained step-by-step, focusing on the **logic and formulas** behind each calculation. This way, you'll understand **how the numbers are derived** from the model inputs. --- ### 1. **Unlevered IRR for 5-year holding period (Answer: 7.79%)** **How it's calculated:** - **Unlevered cash flows** represent the property's operating cash flows **without debt**. - These are typically: - Year 0: Purchase price (negative cash flow, outflow) - Years 1-5: Operating NOI minus capital expenditures, taxes, etc. (positive inflows/outflows) - Year 5: Sale proceeds (sale price minus selling costs and remaining mortgage, if any). **Excel formula:** ```excel =IRR(range_of_unlevered_cash_flows) ``` **Example:** | Year | Cash Flow | |--------|-------------------| | 0 | -$X (purchase) | | 1-5 | NOI (operating cash flows) | | 5 | Sale price + Year 5 cash flow | By applying the IRR function over these cash flows, you get approximately **7.79%**. --- ### 2. **Levered IRR for 5-year holding period (Answer: 11.16%)** **How it's calculated:** - **Levered cash flows** account for debt service, interest, principal payments. - These are: - Year 0: Initial equity investment (negative) - Years 1-5: Net cash flows after debt service (positive or negative depending on operations) - Year 5: Sale proceeds minus debt payoff. **Excel formula:** ```excel =IRR(range_of_levered_cash_flows) ``` This IRR reflects the investor's actual return, which is higher than unlevered IRR due to leverage. --- ### 3. **Unlevered IRR for 3-year holding period (Answer: 6.24%)** **How it's calculated:** - Similar to the 5-year unlevered IRR, but only considering cash flows **up to year 3**. - The sale occurs at Year 3 with corresponding sale price. **Excel formula:** ```excel =IRR(range_of_unlevered_cash_flows_up_to_year_3) ``` --- ### 4. **Levered IRR for 3-year holding period (Answer: 12.23%)** Same as above, but factoring in debt and debt repayment at sale. The IRR is typically higher due to leverage effects over a shorter holding period. --- ### 5. **Equity Multiple (Unlevered: 1.42x, Levered: 1.59x)** **How it's calculated:** - **Equity Multiple** = Total cash inflows / Initial equity invested **In Excel:** ```excel =SUM(range_of_unlevered_cash_flows) / Initial_Equity ``` or ```excel =SUM(range_of_levered_cash_flows) / Initial_Equity ``` **Example:** - Sum all cash inflows (from Year 1 to Year 5, including sale proceeds). - Divide by the initial equity to get the multiple. --- ### 6. **Loan Constant (Answer: 6.85%)** **How it's calculated:** - Loan constant = Annual debt service / Original loan amount - Compute annual debt service using the PMT function: ```excel =PMT(interest_rate / periods_per_year, total_periods, -loan_amount) ``` - Then, divide by loan amount to get the rate: ```excel =Annual_Debt_Service / Loan_Amount ``` which yields approximately **6.85%**. --- ### 7. **Year 1 Debt Yield (Answer: 8.48%)** **How it's calculated:** - Debt Yield = Year 1 NOI / Loan Amount **Excel formula:** ```excel =NOI_Year1 / Loan_Amount ``` --- ### 8. **Year 1 DSCR (Answer: 1.29)** **How it's calculated:** - DSCR = NOI Year 1 / Debt Service Year 1 **Excel:** ```excel =NOI_Year1 / Debt_Service_Year1 ``` --- ### 9. **LTV at Year 1 (Answer: 0.75 or 75%)** **How it's calculated:** - LTV = Loan Amount / Property Value at Year 1 **Excel:** ```excel =Loan_Amount / Property_Value_Year1 ``` --- ### 10. **Cash-on-Cash Return Year 1 (Answer: 6.85%)** **How it's calculated:** - Cash-on-cash = Year 1 levered cash flow / Equity invested **Excel:** ```excel =Levered_Cash_Flow_Year1 / Equity_Invested ``` --- ### 11. **Sale Price at end of Year 5 (Answer: 1,378,000)** **How it's calculated:** - Sale Price = NOI in Year 6 / Exit Cap Rate **Excel:** ```excel =NOI_Year6 / Exit_Cap_Rate ``` --- ### 12. **Proceeds after paying off loan at Year 5** **How it's calculated:** - Proceeds = Sale Price - Remaining Loan Balance - Selling Costs **Excel:** ```excel =Sale_Price - Loan_Balance_End_Year5 - Selling_Costs ``` --- ### 13. **IRR if sale occurs at Year 3 or Year 1** **How it's calculated:** - Adjust cash flows to include sale proceeds at the respective year. - Use `IRR()` over these modified cash flows. --- ### **Summary of the Approach:** - **Identify the key cash flows:** purchase, operations, debt service, sale. - **Use IRR() function** on the relevant cash flow series to get IRRs. - **Calculate multiples** by dividing total cash inflows by initial equity. - **Compute ratios** like DSCR, LTV, Debt Yield with straightforward formulas. - **Use the appropriate formulas** for sale price, loan payoff, and exit proceeds. --- ### **In short:** - The *answers* are derived from the **cash flow model** you build in Excel. - The **formulas** are standard financial functions (`IRR()`, `PMT()`, simple ratios). - To **match the answers**, ensure your **cash flows are correct** and **formulas reference the right ranges**. --- If you share your **specific cell ranges** or **cash flow layout**, I can help you craft **precise formulas** to replicate these results exactly.