Question

You bought a piece of land at $1,500,000. You plan to build a 45,000 square foot building at cost of $150 per square foot, all inclusive, in addition to land cost. The completed building is expected to generate a monthly net of $2.00 per square foot per month for year 1 and the rent will increase at 4% per year. The operating expenses will be 0.40 per square foot per month for year 1 and increase at 3% per year. At end of year 10, you plan to sell the property for $10,500,000. Assuming you will have an income tax rate of 35% every year and you will take depreciation charge every year based on the IRS allowed schedule of 1/39th of the building cost only per year. Your capital gain tax rate is 20%. Part 1 Assuming all cash investment and using a MARR of 8%, what is the Net Present Value of this 10-year investment and what is the IRR? Show the results as formatted below: 2-9 Revenue Operating Expense EBITDA Depreciation Taxable income Tax 30% tax rate Net Income CFAT 10 Also clearly show the calculations of each year's Depreciation Charges, Cash Flow After Tax at Year 10 for selling of the property and calculations of the present value and IRR, separately.

Answer 1

Details of the Investment based on given details is as follows:

Investment details	Amount in $
Cost of Land	1,500,000.00
Cost of Building- 45000 sqft @ 150 per sqft.	6,750,000.00
Total Cost of Building (a)	8,250,000.00
Dep @ 1/39 of the total cost	211,538.46
Depreciation for 10 years (b)	2,115,384.62
Balance at the end of 10th year c=(a-b)	6,134,615.38
Selling price (d)	10,500,000.00
Capital Gain e =(d-c)	4,365,384.62
Tax @ 20% (f)	873,076.92
Cashflow from sale of Investment g = (d-f)	9,626,923.08

Yearwise Cashflow computation is as follows:

Cashflow Computation in $	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Rent @ $2 for 45000 sqft p.m (b)	1,080,000.00	1,123,200.00	1,168,128.00	1,214,853.12	1,263,447.24	1,313,985.13	1,366,544.54	1,421,206.32	1,478,054.57	1,537,176.76
Operating Expense @ 0.4 for 45000 sqft p.m (b)	216,000.00	222,480.00	229,154.40	236,029.03	243,109.90	250,403.20	257,915.30	265,652.75	273,622.34	281,831.01
EBITDA c = (a-b)	864,000.00	900,720.00	938,973.60	978,824.09	1,020,337.34	1,063,581.93	1,108,629.24	1,155,553.57	1,204,432.24	1,255,345.75
Depreciation (d)	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46	211,538.46
Taxable income e=(c-d)	652,461.54	689,181.54	727,435.14	767,285.63	808,798.88	852,043.47	897,090.78	944,015.11	992,893.78	1,043,807.29
Tax @ 35% (f)	228,361.54	241,213.54	254,602.30	268,549.97	283,079.61	298,215.22	313,981.77	330,405.29	347,512.82	365,332.55
Net income g=(e-f)	424,100.00	447,968.00	472,832.84	498,735.66	525,719.27	553,828.26	583,109.01	613,609.82	645,380.95	678,474.74
CFAT h = (e+d)	635,638.46	659,506.46	684,371.30	710,274.12	737,257.73	765,366.72	794,647.47	825,148.28	856,919.42	890,013.20

NPV Calculation and IRR is derived as below:

NPV Calculation	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Initial Investment	8,250,000
CFAT till 10 years		635,638	659,506	684,371	710,274	737,258	765,367	794,647	825,148	856,919	890,013
Cashflow from Sale of Investment											9,626,923
Total Cashflow (a)	(8,250,000)	635,638	659,506	684,371	710,274	737,258	765,367	794,647	825,148	856,919	10,516,936
PV factor @8%= (1/(1+r)^n) where n is the year (b)	1	0.93	0.86	0.79	0.74	0.68	0.63	0.58	0.54	0.50	0.46
NPV of CF year wise c=(a x b)	(8,250,000)	588,554	565,420	543,276	522,073	501,765	482,311	463,669	445,802	428,673	4,871,376
NPV of Project	1,162,920
IRR of the project is the rate at which NPV of project is equal to Initial Investment
Using Trial and error method, the IRR would be	9.950%