Calculating installment payments for home purchase

Hi,

I have been struggling with different payment types for a project I have that I hope someone could help to solve.

Scenario -

- I am selling 12,000m2 of floor area.
- Assume this 12,000m2 is completely sold out over 24 months
- Assume only 2 payment types: a. pay by cash upon purchase b. pay by installment over 6 months.
- Assume that 30% of buyers chose to pay by cash (a) and 70% chose to pay by installment (b)
- Assume that discount rate for Future Value or Present Value is 5%
- Assume that cash price escalates by 0.2% per month (meaning month 1 sell for $500 psm and month 2 sell for $501 psm (500*1.002)

Problem -
1) Since cash price is set at $500 psm at month 1 and escalates 0.2% each month, the installment price has to be higher to compensate for the waiting time of 6 months to receive same cash. How do you calculate the price to sell if buyer chooses to pay by installment?
2) How do you calculate the cash collection for the buyers who chose to pay by installment method? Column G12 onwards (highlighted in yellow). The problem is that the guy who buys in month 1 pays from month 1-6 and so forth.

I have attached a spreadsheet with assumptions filled in.

Appreciate any assistance at all.

Hi,

Thank you for the suggestion but I'm not sure if it's right.

I assume your formula suggestion is to solve 1) in OP. Then that is for column E and not G. But for a 5% annual discount rate on a 6month payment term, the expected premium of 6x installment price should be 2.5-5% (since I have to wait half a year to collect my money). Based on your formula it is 8+%.

For column G, that is to solve 2) where 1st month buyer pays from month 1 - 6 and the 24th month buyer pays from month 24 - 30.

Thanks,