Real Estate Financial Model and Report
Budget: ₹1,500 – ₹12,500 INR
I’m in need of a professional who can develop a detailed financial model and generate a summary report for a sample real estate deal. The report must be power-point based while the model should be in excel format.
Key requirements for the model are as follows:
- Calculation of Project Internal Rate of Return (IRR)
- Calculation of Equity IRR
- Computation of Free Cash Flow to the Firm (FCFF)
- Determining Free Cash Flow to Equity (FCFE)
- Compilation of Profit & Loss (P&L)
- Formulation of Balance Sheet
- Analysis of IRR sensitivity to key assumptions
Specific assumptions to incorporate into the financial model include: purchase price, annual rental income, operating expenses, financing costs, and tax rates.
The requested Key Performance Indicators (KPIs) to track are: Net Present Value (NPV), Internal Rate of Return (IRR), Return on Investment (ROI), Payback period and Cashflow metrics.
Ideal freelancers for the job should possess strong analytical skills for financial modeling, familiarity with real estate financial considerations, and proficiency in Excel and PowerPoint. Additionally, a background in financial analysis, real estate, or a related field is desirable.
Below is the project
Case – Financial Model for a sample real estate Deal
Task – prepare summary report in power point & detailed financial model in excel for Project IRR, Equity
IRR, FCFF, FCFE, P&L, Balance sheet, Sensitivity of IRR to key assumptions.
The model should be an intelligent one so that it is comprehensive enough to capture all the key
dynamics of the deal & show the assumptions clearly, flexible so that the key assumptions can be easily
changed but the model should be simple i.e. not be unwieldy / cumbersome to review. Preferably make
annual projections rather than quarterly/monthly.
Project details
Location – Western Express Highway, Goregaon, Mumbai
Size – 10 acres, FSI 4
Mixed use development – 20% retail, 20% office space, balance is residential apartments
Stake of PE fund in SPV = 50%; Developer = 50%
Pvt. Equity Funding JV with developer in an SPV. Key terms of JV/SPV - equity infusion in shareholding
ratio; preferred return of 8% to PE fund, catch-up to Developer till 8%, sharing cash flows in
shareholding ratio till 20%; thereafter 60% to developer & 40% to PE Fund
Land deal – INR 60 crs per acre for 5 acres outright purchase; Joint Development on bal 5 acres with 45%
revenue share to third party Land Lord i.e. 55% of revenues to SPV and 100% of construction cost to
SPV.
Retail & office space areas → build, lease up, then sell at a cap rate on stabilization;
Apartments → pre-sell & build.
Costs, sales & construction timelines, escalations in sale prices & costs, collections, phasing, interest
cost, Exit multiples etc – make reasonable assumptions
Factor in income tax.
Land + Initial working capital are funded by shareholders in a tax efficient structure of 50% Equity & 50%
NCDs. Rest is funded by construction finance / LRD / customer advances.
Make a reasonable assumption on any factor not listed above.
Output:
(1) Excel Financial Model and (2) Summary report in Powerpoint
Key requirements for the model are as follows:
- Calculation of Project Internal Rate of Return (IRR)
- Calculation of Equity IRR
- Computation of Free Cash Flow to the Firm (FCFF)
- Determining Free Cash Flow to Equity (FCFE)
- Compilation of Profit & Loss (P&L)
- Formulation of Balance Sheet
- Analysis of IRR sensitivity to key assumptions
Specific assumptions to incorporate into the financial model include: purchase price, annual rental income, operating expenses, financing costs, and tax rates.
The requested Key Performance Indicators (KPIs) to track are: Net Present Value (NPV), Internal Rate of Return (IRR), Return on Investment (ROI), Payback period and Cashflow metrics.
Ideal freelancers for the job should possess strong analytical skills for financial modeling, familiarity with real estate financial considerations, and proficiency in Excel and PowerPoint. Additionally, a background in financial analysis, real estate, or a related field is desirable.
Below is the project
Case – Financial Model for a sample real estate Deal
Task – prepare summary report in power point & detailed financial model in excel for Project IRR, Equity
IRR, FCFF, FCFE, P&L, Balance sheet, Sensitivity of IRR to key assumptions.
The model should be an intelligent one so that it is comprehensive enough to capture all the key
dynamics of the deal & show the assumptions clearly, flexible so that the key assumptions can be easily
changed but the model should be simple i.e. not be unwieldy / cumbersome to review. Preferably make
annual projections rather than quarterly/monthly.
Project details
Location – Western Express Highway, Goregaon, Mumbai
Size – 10 acres, FSI 4
Mixed use development – 20% retail, 20% office space, balance is residential apartments
Stake of PE fund in SPV = 50%; Developer = 50%
Pvt. Equity Funding JV with developer in an SPV. Key terms of JV/SPV - equity infusion in shareholding
ratio; preferred return of 8% to PE fund, catch-up to Developer till 8%, sharing cash flows in
shareholding ratio till 20%; thereafter 60% to developer & 40% to PE Fund
Land deal – INR 60 crs per acre for 5 acres outright purchase; Joint Development on bal 5 acres with 45%
revenue share to third party Land Lord i.e. 55% of revenues to SPV and 100% of construction cost to
SPV.
Retail & office space areas → build, lease up, then sell at a cap rate on stabilization;
Apartments → pre-sell & build.
Costs, sales & construction timelines, escalations in sale prices & costs, collections, phasing, interest
cost, Exit multiples etc – make reasonable assumptions
Factor in income tax.
Land + Initial working capital are funded by shareholders in a tax efficient structure of 50% Equity & 50%
NCDs. Rest is funded by construction finance / LRD / customer advances.
Make a reasonable assumption on any factor not listed above.
Output:
(1) Excel Financial Model and (2) Summary report in Powerpoint