Let's break down the **questions** and **answers** at the bottom of your screenshot, and then discuss how to adjust your Excel model to match the correct answers. --- ## **Questions and Given Answers** These are the questions and their corresponding answers from your screenshot: | Question | Answer | |----------|--------| | 1. What is the unlevered IRR for a 5 year holding period? | 7.79% | | 2. What is the unlevered IRR for a 7 year holding period? | 7.62% | | 3. What is the levered IRR for 5 years? | 11.21% | | 4. What is the levered IRR for 7 years? | 10.79% | | 5. What is the levered cash on cash return in year 1? | 1.26% | | 6. What is the levered cash on cash return in year 3? | 2.19% | | 7. What is the levered cash on cash return in year 5? | 2.95% | | 8. What is the loan constant? | 6.48% | | 9. What is the loan amount? | 1,387,000 | --- ## **How to Approach the Fix** ### **1. Identify the Required Inputs** - **Purchase Price**: $1,800,000 - **Loan Amount**: Should be $1,387,000 according to the answer key. - **Loan Constant**: Should be 6.48% according to the answer key. - **Unlevered/Levered IRR calculations**: These use cash flows with/without debt. - **Levered Cash on Cash Return**: (Levered Cash Flow / Equity Invested) for each year. ### **2. Inputs to Check/Adjust in the Model** #### **Loan Amount** - **Check**: Cell for "Loan Amount" (Row 26, Column C in your screenshot). - **Correct**: Should be $1,387,000. #### **Loan Constant** - **Check**: Formula for Loan Constant = Annual Debt Service / Loan Amount - **Correct**: Should be 6.48%. #### **IRRs (Unlevered & Levered)** - **Check**: IRR formulas should use the correct cash flow series. - **Unlevered IRR**: Use all the property cash flows before debt. - **Levered IRR**: Use cash flows after debt service. - Make sure your cash flows (rows like "Unlevered cash flow", "Levered cash flow") are correct for each period and reflect the correct initial investment and terminal value (sale proceeds). #### **Cash-on-Cash Return** - **Formula**: (Levered Cash Flow / Equity Invested) per year. - Make sure the denominator is the equity invested (Purchase Price - Loan Amount). --- ## **Step-by-Step Fixes** ### **A. Fix Loan Amount** 1. **Input $1,387,000** in the cell for "Loan Amount" (likely C26). ### **B. Fix Loan Constant** 1. **Formula**: Loan Constant = (Annual Debt Service / Loan Amount) 2. **Check Annual Debt Service** (Sum of all annual loan payments). 3. **Ensure formula** in the "Loan Constant" cell matches the answer (should result in 6.48%). ### **C. Check/Correct IRR Calculations** - For **IRR**, use Excel's `=IRR(range)` formula. - **Unlevered IRR**: Use the row with unlevered cash flows (should be before debt service). - **Levered IRR**: Use the row with levered cash flows (after debt service). - **Initial Outflow**: Make sure the first cash flow is negative (the investment). - **Terminal Value**: Include sale proceeds in the last year's cash flow. ### **D. Fix Cash-on-Cash Returns** 1. For each year, calculate: - **Numerator**: Levered cash flow for the year. - **Denominator**: Equity Invested = Purchase Price - Loan Amount. 2. Formula: `= (Levered Cash Flow Year X) / (Equity Invested)` --- ## **Example of Inputs to Use/Check** - **Loan Amount**: $1,387,000 - **Purchase Price**: $1,800,000 - **Equity Invested**: $1,800,000 - $1,387,000 = $413,000 - **Loan Constant**: 6.48% - **Cash Flows**: Check rows for unlevered and levered cash flows are correct. - **Use Excel IRR functions** on the appropriate rows. --- ## **Summary Table for Inputs/Checks** | Cell/Row | What To Check/Input | |--------------------------|-------------------------------------------| | Loan Amount | $1,387,000 | | Loan Constant | Formula gives 6.48% | | Equity Invested | $1,800,000 - $1,387,000 = $413,000 | | Unlevered Cash Flows | Correct numbers before debt service | | Levered Cash Flows | Correct numbers after debt service | | IRR Functions | Use correct cash flow ranges | | Cash-on-Cash Returns | Formula per above for years 1, 3, 5 | --- ## **Conclusion** 1. **Update Loan Amount** to $1,387,000. 2. **Check/Update Loan Constant** formula to get 6.48%. 3. **Check all Cash Flows** (before and after debt). 4. **Recalculate IRRs** using correct cash flows. 5. **Calculate Cash-on-Cash** returns using levered cash flow and equity invested. **If you need the exact Excel formulas or help pinpointing the exact cells, please let me know! If you share the formulas in your existing model, I can help you adjust them step-by-step.**