Using excel, assess the following. For a total investment of $10 million, the proposed subdivision will have 200 buildable lots. Development costs of $5.5 million are evenly spread over the first six months. The lots will start selling in month 4 at a pace of 25 per month. The lots will sell for $90,000 per lot. $7 million of the capital investment will be financed at 6% annual interest with principal paid back in proportion to the number of lots sold. Sales expenses will run 10% of the lot price. The firm allocates fixed operating expenses of $25,000 per month to the project.

1) In the month of sellout, what is the total cumulative net investment?

2) What is the payback month on the equity invested?

Set up a simple =IF statement to indicate that the subdivision has sold out. Also, answer questions 1) and 2) above for the following scenarios, as well.

A) Absorption (velocity) is 25 per month, but the selling price is increased to $100,000.

B) Absorption (velocity) increases to 50 per month at the proposed $90,000 selling price.

C) Absorption (velocity) increases to 50 per month, at a $100,000 selling price.

D) Absorption (velocity) decreases to 20 per month, at the $90,000 selling price.

E) Absorption (velocity) decreases to 20 per month, at the $100,000 selling price.

What I have so far:

INPUTS:
Total Investment	$10,000,000			Sales Velocity	25	Per Month - starting at month 4		Interest Rate	6%
Amount Borrowed	$7,000,000			Commissions	10%			LTV Ratio
Development Costs	$5,500,000	Spread evenly over the 1st 6 months		Total Lots	200			Reduction Pct
Fixed Operating Costs	$25,000	Per Month		Selling Price/Lot	$90,000			Amortization
	Inventory and Absorption			Loan Information				Monthly Cash Flows								Cummulative Accounts
Month	Lots Sold	Lots Left		Loan Balance	Interest	Principle		Revenue	Development Costs	Operating Expenses	Comissions	Net Revenue		Equity Invested		Cummulative Revenues	Cummulative Expenses	Cummulative Principle Repayment	Cummulative Interest	Cummulative Net Revenue	Cummulative Net Investment
0	0	200							$916,666.67	$25,000	$0
1	0	200							$916,666.67	$25,000	$0
2	0	200							$916,666.67	$25,000	$0
3	0	200							$916,666.67	$25,000	$0
4	25	175							$916,666.67	$25,000	$225,000
5	25	150							$916,666.67	$25,000	$225,000
6	25	125							$916,666.67	$25,000	$225,000
7	25	100							0	$25,000	$225,000
8	25	75							0	$25,000	$225,000
9	25	50							0	$25,000	$225,000
10	25	25							0	$25,000	$225,000
11	25	0							0	$25,000	$225,000
	SOLD OUT	SOLD OUT

I need the formulas please!