Hi - Im new to the forum. I working on a pretty basic cash flow model that calculates investment metrics on a developing and selling residential land lots.
The basic scenario is that the investor buys a piece of land, spends development dollars and sells the land as individual lots over time. The model includes receiving debt funding (leverage) from a bank as well as future funding based on future development expenses at a set loan to value (LTV). For example, if there's a million dollars in future development expenses, and the set LTV is 65%, then at the time the $1MM in development expenses hits the cash flow, 35% is funding by the investor as "equity" and 65% is funding by the lender as a draw against the loan.
The curve ball is that now the structure will be all the required equity is funded day 1 (if needed) and the loan will then fund up to the maximum LTV.
For example, if the purchase price of the land is $2MM and there's an additional $1MM in development expenses, the total cost of the investment would then be $3MM. If the bank's LTV is 50%, then the required equity on the total investment would equal 50% of the $3MM or $1.5MM. In this scenario, the initial equity would be $1.5MM and the bank would fund the remaining $500k to purchase the land as well as 100% of the future $1MM in development expenses.
Now lets say say the land price is $1MM and there's an additional $2MM in development expenses and the LTV is 50%. In this scenario, the total equity of $1.5MM exceeds the initial $1MM purchase price of the land. So the equity would fund 100% of the purchase price and then 50% (LTV) of the remaining development expenses until the equity reaches the $1.5MM total. Once the equity reaches the $1.5MM total, the bank would then fund 100% of the development expenses.
I'm having trouble figuring a clean way to automate this within the excel model so that you can quickly adjust certain assumptions and everything flows through.
If possible I can attach a worksheet with an attempt to accomplish what I've described.
Not sure if anyone would be able to help me pull this together, but I thought I'd at lease ask!
Thanks,
Kris
The basic scenario is that the investor buys a piece of land, spends development dollars and sells the land as individual lots over time. The model includes receiving debt funding (leverage) from a bank as well as future funding based on future development expenses at a set loan to value (LTV). For example, if there's a million dollars in future development expenses, and the set LTV is 65%, then at the time the $1MM in development expenses hits the cash flow, 35% is funding by the investor as "equity" and 65% is funding by the lender as a draw against the loan.
The curve ball is that now the structure will be all the required equity is funded day 1 (if needed) and the loan will then fund up to the maximum LTV.
For example, if the purchase price of the land is $2MM and there's an additional $1MM in development expenses, the total cost of the investment would then be $3MM. If the bank's LTV is 50%, then the required equity on the total investment would equal 50% of the $3MM or $1.5MM. In this scenario, the initial equity would be $1.5MM and the bank would fund the remaining $500k to purchase the land as well as 100% of the future $1MM in development expenses.
Now lets say say the land price is $1MM and there's an additional $2MM in development expenses and the LTV is 50%. In this scenario, the total equity of $1.5MM exceeds the initial $1MM purchase price of the land. So the equity would fund 100% of the purchase price and then 50% (LTV) of the remaining development expenses until the equity reaches the $1.5MM total. Once the equity reaches the $1.5MM total, the bank would then fund 100% of the development expenses.
I'm having trouble figuring a clean way to automate this within the excel model so that you can quickly adjust certain assumptions and everything flows through.
If possible I can attach a worksheet with an attempt to accomplish what I've described.
Not sure if anyone would be able to help me pull this together, but I thought I'd at lease ask!
Thanks,
Kris
