Question

Create an NPV model in excel for a land subdivision with the following assumptions:

- 30 lots at $50,000 per lot, increasing at 3% per year

- Absorption rate of 10 lots per year

- Costs of marketing – 5% of gross revenues

- IRR of 15%

1. What is the justified acquisition cost of the land?

2. What would be the justified acquisition cost of the land if the absorption changed from 3 to 5

years?

Answer 1

1) What is the justified acquistion cost of the land.

The justified acquistion for the cost of the land, should be Net Present Value (NPV) of the cash inflows. To find out the NPV, the estimated annual cash inflow (CF) needs to be found out. The below table shows the annual cash flow on sale of the land in lots.

Based on the above cash flows, NPV to be found based on the discounting factor of 15% (which is the IRR for this project). Below table shows the NPV calculation.

Particulars	Value
Total lots	30
Absorption lot per year	10
Number of years	3
Sale price per lot	$        50,000
Increase in sale price every year	3%
Particulars	Year 1	Year 2	Year 3
Lots sold	10	10	10
Sale price per lot	$        50,000	$        51,500	$        53,045
Less: Marketing costs @ 5% of sales	$          2,500	$           2,575	$          2,652
Net cash from sales per Lot	$        47,500	$        48,925	$        50,393
Net cash from sales (Cash inflow)	$     475,000	$      489,250	$      503,928

Year	Cash Flow - A	DF @15% - B	NPV - AxB
1	475,000	0.870	413,043
2	489,250	0.756	369,943
3	503,928	0.658	331,341
		NPV (Sum)	1,114,327

Discounting factor = 1/(1+i)^n
i = Discounting rate (in this case 15%)
n = Period (in thi case 1 to 3).

Conclusion - The land to be acquired at $ 1,114,327 as at this price, the NPV is neither positive nor negative.

2) What would be the justified acquisition cost of the land if the absorption changed from 3 to 5 years?

The justified acquistion for the cost of the land, should be Net Present Value (NPV) of the cash inflows. To find out the NPV, the estimated annual cash inflow (CF) needs to be found out. The below table shows the annual cash flow on sale of the land in lots.

Based on the above cash flows, NPV to be found based on the discounting factor of 15% (which is the IRR for this project). Below table shows the NPV calculation.

Particulars	Value
Total lots	30
Number of years	5
Absorption lot per year	6
Sale price per lot	$    50,000
Increase in sale price every year	3%
Particulars	Year 1	Year 2	Year 3	Year 4	Year 5
Lots sold	6	6	6	6	6
Sale price per lot	$    50,000	$    51,500	$    53,045	$    54,636	$    56,275
Less: Marketing costs @ 5% of sales	$      2,500	$      2,575	$      2,652	$      2,732	$      2,814
Net cash from sales per Lot	$    47,500	$    48,925	$    50,393	$    51,905	$    53,462
Net cash from sales (Cash inflow)	$ 285,000	$ 293,550	$ 302,357	$ 311,427	$ 320,770

Year	Cash Flow - A	DF @15% - B	NPV - AxB
1	285,000	0.870	247,826
2	293,550	0.756	221,966
3	302,357	0.658	198,804
4	311,427	0.572	178,060
5	320,770	0.497	159,479
		NPV (Sum)	668,596

Discounting factor = 1/(1+i)^n
i = Discounting rate (in this case 15%)
n = Period (in thi case 1 to 3).

Conclusion - The land to be acquired at $ 668,596 as at this price, the NPV is neither positive nor negative.