This real estate project has me stumped

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.

1. A) Absorption (velocity) is 25 per month, but the selling price is increased to $100,000.
2. B) Absorption (velocity) increases to 50 per month at the proposed $90,000 selling price.
3. C) Absorption (velocity) increases to 50 per month, at a $100,000 selling price.
4. D) Absorption (velocity) decreases to 20 per month, at the $90,000 selling price.
5. E) Absorption (velocity) decreases to 20 per month, at the $100,000 selling price.

I got the excel sheet already filled out but I don't know what to do about absorption.

My interest is wrong, and everything else below interest is iffy, just wondering how I should go about solding the questions.

N 6 7 25 25 25 150 25 125 repeat until there 75 are no lots left 100 7,000,000 42,000 7,000,000 42,000 7,000,000 42,000 600,000 7,000,000 18,000 600,000 N13 - X fi 600000 F | G | H | I | J | 1 This is a shortened version of your homework, designed to allow you to reverse engineer formulas when necessary. 2 It is also last year's version and does not reflect enhancements and improvements 3 Inputs Total Investment (Price) 10,000,000 Amt Borrowed 7,000,000 Month 0 1 2 Development Costs 5,500,000 Inventory and Absorption Fixed Operating Costs 25,000 Lots sold 25 Lots left 175 Sales Velocity 25 per month Commissions 10% Loan Information Total Lots 200 Loan Balance 7,000,000 7,000,000 7,000,000 7,000,000 7,000,000 Selling Price per lot 90,000 Interest 42,000 42,000 42,000 42,000 Principal Interest Rate 6% LTV Ratio 50% Monthly P and L (Cash Flow) Reduction Pct 10.00% Revenue Amortization 300,000 Development Costs 916,667 916,667 916,667 916,667 Operating Expenses 25,000 25,000 25,000 25,000 Commissions 20 Note: Sales Velocity / Total Lots can have no remainder! Net Revenue (261,667) (261,667) (261,667) (261,667) 21 Note: Development Costs are equally spread over 6 months 22 Note: To repay principal, lender receives the original loan balance Equity Invested 7,000,000 23 multiplied by the proportion of lots sold (sales rate). 24 Note: Lots start selling in month 4 Cumulative Accounts Cumulative Revenue Cumulative Expenses 241,667 483,333 725,000 966,667 Cum Prin Repay Cumulative Interest 20,000 40,000 60,000 80,000 Cumulative Net Revenue (261,667) (523,333) (785,000) (1,046,667) Cumulative Net Investment (3,261,667) (3,523,333) (3,785,000) (4,046,667) 2,250,000 2,250,000 916,667 25,000 916,667 25,000 25,000 225,000 555,000 25,000 225,000 557,000 (261,667) (261,667) 1,208,333 1,450,000 100,000 (1,308,333) (4,308,333) 120,000 (1,570,000) (4,570,000) 1,000,000 1,575,000 300,000 140,000 (1,015,000) (4,015,000) 2,000,000 1,700,000 600,000 158,000 (458,000) (3,458,000) N 6 7 25 25 25 150 25 125 repeat until there 75 are no lots left 100 7,000,000 42,000 7,000,000 42,000 7,000,000 42,000 600,000 7,000,000 18,000 600,000 N13 - X fi 600000 F | G | H | I | J | 1 This is a shortened version of your homework, designed to allow you to reverse engineer formulas when necessary. 2 It is also last year's version and does not reflect enhancements and improvements 3 Inputs Total Investment (Price) 10,000,000 Amt Borrowed 7,000,000 Month 0 1 2 Development Costs 5,500,000 Inventory and Absorption Fixed Operating Costs 25,000 Lots sold 25 Lots left 175 Sales Velocity 25 per month Commissions 10% Loan Information Total Lots 200 Loan Balance 7,000,000 7,000,000 7,000,000 7,000,000 7,000,000 Selling Price per lot 90,000 Interest 42,000 42,000 42,000 42,000 Principal Interest Rate 6% LTV Ratio 50% Monthly P and L (Cash Flow) Reduction Pct 10.00% Revenue Amortization 300,000 Development Costs 916,667 916,667 916,667 916,667 Operating Expenses 25,000 25,000 25,000 25,000 Commissions 20 Note: Sales Velocity / Total Lots can have no remainder! Net Revenue (261,667) (261,667) (261,667) (261,667) 21 Note: Development Costs are equally spread over 6 months 22 Note: To repay principal, lender receives the original loan balance Equity Invested 7,000,000 23 multiplied by the proportion of lots sold (sales rate). 24 Note: Lots start selling in month 4 Cumulative Accounts Cumulative Revenue Cumulative Expenses 241,667 483,333 725,000 966,667 Cum Prin Repay Cumulative Interest 20,000 40,000 60,000 80,000 Cumulative Net Revenue (261,667) (523,333) (785,000) (1,046,667) Cumulative Net Investment (3,261,667) (3,523,333) (3,785,000) (4,046,667) 2,250,000 2,250,000 916,667 25,000 916,667 25,000 25,000 225,000 555,000 25,000 225,000 557,000 (261,667) (261,667) 1,208,333 1,450,000 100,000 (1,308,333) (4,308,333) 120,000 (1,570,000) (4,570,000) 1,000,000 1,575,000 300,000 140,000 (1,015,000) (4,015,000) 2,000,000 1,700,000 600,000 158,000 (458,000) (3,458,000)