Pre-Sold Apartments Real Estate Model presents the case where a land plot is purchased and an apartment complex is built with the aim to sell each apartment to interested clients. The model generates the three financial statements as well as the cash flows of the project & equity investors and calculates the relevant metrics (cash on cash, IRR). The financing options for the project include a construction loan, a bridge financing as well as an overdraft facility and of course equity funding from investors.
The model structure is as follows:
Manual: main conventions used in the model.
Inputs (in yellow whatever can be amended as an assumption): detailed inputs for pre-construction, construction and post construction period, inflation, taxes, apartment and plot characteristics (# of apartments & size), floor area ratio and apartment to gross surface, plot & apartment prices per square feet as well as the installments schedule paid by the buyers / clients, timing of apartments sold, construction costs allocation, construction costs per square feet, overhead expenses, and debt financing assumptions (overdraft, bridge financing and construction loan) such as interest rates, repayments and loan to value ratio.
Calculations: this were all calculations are performed. the size of the plot of land to be bought is calculated based on the size and number of apartments that the developer wants to build. Based on the percentage of completion method (% of costs on total costs) the revenues and costs are allocated accordingly. Land costs, Hard & Soft costs are calculated as well, a detailed construction in progress schedule that is matched with a billing schedule is incorporated as well together with accounts payable and accounts receivable schedules. Finally the sources and uses of funds are presented and the relevant debt financing is calculated (construction loan, bridge financing and overdraft)
Outputs: everything is aggregated here into the relevant statements: profit and loss, balance sheet and cash flow, as well as a cash flow analysis on a project basis as well as on an equity basis. Then total return, cash on cash multiples and IRR are calculated.
Ratios & Charts: on the ratios tab you will find all the relevant KPIs summarized and on the charts tab the most important metrics are displayed through graphs.
Checks: A dedicated worksheet that makes sure that everything is working as it should!
Important Notice: Yellow indicates inputs and assumptions that the user is able to change, Blue cells are used for called up cells, and white cells with black characters indicates calculation cells. The model is using a macro therefore you need to enable macros. The macro is used to calculate the amount of the bridge financing during the construction period. There is a button in the calculations tab named “Calculate Bridge Financing” that needs to be pressed as soon as you are done with your inputs.
The model comes as MS Excel file and contains Macros.