Looking for someone to assist in developing some advanced financial real estate cash flow models. Must understand basic financial concepts dealing with present value, IRRs, loan amortizations or refinancing, JV waterfalls, etc. This could incorporate VBA, but I'm trying not to use VBA if necessary. I'd imagine there will be about 20 excel tabs to serve different functions of the model.
Here is an example of a real estate development model I'm trying to build. There's a development budget with about 20-30 line items that gets funded by equity and loan draws. There's a flexible cost projection schedule that determines the amount of the loan draws. There's a budgeted interest reserve for the time of the construction loan which will capitalize interest and add to the loan balance, after the construction loan expires, there's a permanent loan that might be interest only or amortizing and would need to be paid from operating cash flow. There's a rent roll input that determines the tenants square footage and rents, allowance payouts, lease commissions, etc.
The complexity of the development project revolves around how much land is acquired and sold and how many buildings are getting developed and when they will be sold. There could be 5 acres of land acquired, I sell of 2 acres of land over the next few months that pays down the loan balance, then I develop 3-4 buildings, they are completed and then operating cash flow is coming in, then I sell them off at different times, which again pays the loan down until. Maybe the loan could be refinanced in 3 years. There's several equity investors that have different payouts in a waterfall. The waterfall needs to be flexible enough to have a few levels of IRR or equity multiple hurdles, and show returns for a hold period of years 1-10. Also there's a nuance of when there's construction cost overruns, it is either paid for by me the developer or split with the investor.
I could model most of framework and could do it all if it was static, but I need the flexibility of saying, sell this parcel at this time, then sell this building at this time, and my excel skills are not that advanced. So this needs to be very flexible for some inputs to drive the timing of sales for some assets and some options to say what happens with the sale proceeds, for example use 50% to pay down the loan, then distribute 30% to investors, keep 20% in the cash operating account to pay for future capital expenses.
I'm planning to have another model used for acquiring operating assets or managing operations when the properties are operating with cash flow. When I acquire properties I use Argus software to get the rent roll setup for the revenue, then use excel to model the capital costs and all the cash flows after that. The loan might have some sub-accounts that are prefunded and can be drawn down to pay for specific capital expenses, or not be pre-funded, but can increase one of a few loan sub-accounts balance up to a specific dollar amount (depending on use: is it building capital expense or tenant leasing expense for example), then when those loan funds are maxed out, use the an operating capital reserve to pay for capital expenses or require external funding (a capital contribution) that affects capital accounts in the JV waterfall and investor IRRs.
I have a bunch of example models that could be used for reference, but none really solves everything I'm trying to do.
Please let me know what your experience is with financial modeling and if any real estate modeling. The math is not difficult, just making the model flexible enough to handle several scenarios is. Let me know what compensation you require for an ongoing hourly project. I'm not willing to do a one-time project payment unless we work together for awhile.