Question

(25 pts) Your corporation is considering the purchase of land for the cost of $60,000 and you estimate that 6 years from now there is a 100% probability to sell the land for $165,000. Holding costs consist of property taxes of $2000 each year. Assume that your corporation is in the 21% effective ordinary tax bracket and that profit from the sale of the land in 6 years will be taxed as ordinary income (ignore capital gain taxes, depreciation, deduction for interest expense), and your minimum rate of return is 14%. Determine:

a) What is the return on investment and NPV if paying cash for the land?

b) What is the return on investment and NPV if you borrowed $48,000 of the $60,000 at 8% interest per year with a mortgage agreement that provides monthly payments over 10 years. Assume the loan is paid off at the end of year 6 when you sell the property.

c) Is this an acceptable investment project and why?

d) Suppose that upon further analysis, there is an 85% probability that the land will sell for $200,000 at the end of year 6. What is the resulting cash and leverage NPV & ROR?

e) How does the analysis change; and is it still an acceptable project and why?

All answers should be calculated in excel

-I thought I had it figured out but my answers look ridiculous! Any help would be deeply appreciated!

Answer 1

a) If paying cash for land upfront the :

1--> ROI = (Current Value of Investment - Cost of Investment) / Cost of Investment

Years	Cash flow	Disc factor @ 14%	Net present value
0	(60,000.00)	1.00	(60,000.00)
1	(2,000.00)	0.88	(1,754.39)
2	(2,000.00)	0.77	(1,538.94)
3	(2,000.00)	0.67	(1,349.94)
4	(2,000.00)	0.59	(1,184.16)
5	(2,000.00)	0.52	(1,038.74)
6	140,950.00	0.46	64,214.92	165000 - (165000-60000)*0.21 - 2000
	70,950.00		(2,651.24)	<---- Cell G11
	ROI =	118.25	E11/-E4*100
	NPV =	(2,651.24)	Linked to cell G11

b) Monthly paid loan on 10 years basis paid off in 6th year with selling off land.

Years	Cash flow	Disc factor @ 14%	Net present value
0	(12,000.00)	1.00	(12,000.00)
1	(9,050.00)	0.88	(7,938.60)
2	(10,344.00)	0.77	(7,959.37)
3	(10,050.00)	0.67	(6,783.46)
4	(9,755.00)	0.59	(5,775.74)
5	(9,450.00)	0.52	(4,908.03)
6	114,700.00	0.46	52,255.78
	54,051.00		6,890.57

In the cash flow column property tax monthly payment @400 and interest charges from EMI chart has been included.

c) This is an acceptable project as NPV is positive.

d) 85% probability of $200,000 sales proceeds would mean $170,000 average proceeds.

Years	Cash flow	Disc factor @ 16%	Net present value
0	(60,000.00)	1.00	(60,000.00)
1	(2,000.00)	0.88	(1,754.39)
2	(2,000.00)	0.77	(1,538.94)
3	(2,000.00)	0.67	(1,349.94)
4	(2,000.00)	0.59	(1,184.16)
5	(2,000.00)	0.52	(1,038.74)
6	144,900.00	0.46	66,014.49	170000 - (170000-60000)*0.21 - 2000
	74,900.00		(851.67)	<--- G11
	ROI =	124.83	E11/-E4*100
	NPV =	(851.67)	Linked to cell G11

e) Still an unattractive project as NPV is in negative terms.