50.1K

Verified Solution

Link Copied!

On Excel, please model Fund cash flow with the following parameters:

1) Starting Cash Balance on January 1st, 2024: $10M in Money Market Account

a. Each month the fund earns 4.00% annualized interest paid monthly on cash held in the Money Market Account.

2) At the end of 2023, the Fund contains 3 properties with varying states of stabilization:

a. Property 1: Stabilized asset that produces $50k/month in free cash flow. Cash is swept to the fund each month.

b. Property 2: De-stabilized property that is losing $100k/month at the start of 2024. Property is expected to reach breakeven by YE 2024. All operational deficits are funded from Fund reserves.

c. Property 3: Reached stabilization at the end of 2023. In January 2024, the property will refinance its existing bridge debt to permanent agency debt. i. Existing Debt: $5M interest only loan with 3% prepayment penalty and 1% closing costs on remaining principal balance.

ii. New Agency Debt: $7M interest only loan with 2% in anticipated closing costs on initial principal balance.

iii. The anticipated refinance delta will be swept to the fund at refinance closing.

iv. After the refinance, the property is expected to produce $15k/month in free cash flow (this includes the new debt costs). The anticipated cash flow surplus will be swept to the fund each month.

3) On February 29th, 2024, the fund acquires a fourth property for a purchase price of $5M.

a. The property has a $2,000,000 renovation budget. The renovation will take place over 4 months and be paid in 4 equal installments by the fund.

b. The acquisition will incur approx. $250k in closing costs.

c. The property is financed at 65% LTC.

d. The lender requires renovation reimbursement draws. After renovation payment is made, the lender will fund its pro-rata share of the renovation costs. The lender reimbursements will be swept back to the fund upon receipt.

e. The property is projected to lose $500k in cash flow over the first 5 months of ownership. After such time it is assumed the property produces enough cash flow to be breakeven.

f. The lender does not require interest or operating reserves. As such, all operational deficits are funded from Fund cash reserves.

4) On November 30th, 2024, the Fund will make a $2M distribution to its investors. What is the remaining cash balance on December 31st, 2024?

Answer & Explanation Solved by verified expert

See Answer