Help with PART 2 Please

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

ANSWER TO PART 1 BELOW

Also clearly show the calculations of each years Depreciation Charges, Cash Flow After Tax at Year 10 for selling of the property and calculations of the present value and IRR, separately.

Part 2:

Assuming you will obtain a bank loan of 65% of the initial cost at an interest rate of 7.00% per year. The loan requires interest payment only at end of each year and the loan principle is due at end of the 10th year (like a bond arrangement). Everything else stays the same as Part 1 except interest expenses are tax deductible. Re-calculate everything as you did in Part 1. Based on the result of Part 2 and an Incremental IRR analysis, make your recommendation as which way, either Part 1 or Part 2, to do the project and explain how does financial leverage affect your decision in relation to the selection of this project? (Please note without an Incremental IRR analysis, 10 points will be deducted)

2.9 10 Revenue Operating Expense EBITDA Depreciation Taxable income Tax @30% tax rate Net Income CFAT Building Area (SF) $ 150 Rent/SF/MO $ 2.00 Exp/SF/MO $ 0.40 Base at end of each year Sales Price BVO Capital Gain Tax @ 20% 10,500,000 8,250,000 2,250,000 450,000 ONMON 45,000 Land cost LTV 2,56% 6,750,000 6,576,923 2.56% 6,403,846 2.56% 6,230,769 2.56% 6,057,692 2.56% 5,884,615 2.56% 5,711,538 2.56% 5,538,462 2.56% 5,365,385 2.56% 5,192,308 2.56% 5,019,231 2.56% 1,500,000 Bldg Cost $/SF 65% 7.00% Dpreciation BV 8,250,000 173,077 8,076,923 173,077 7,903,846 173,077 7,730,769 173,077 7,557,692 173,077 7,384,615 173,077 7,211,538 173,077 7,038,462 173,077 6,865,385 173,077 6,692,308 173,077 6,519,231 BV10 recapture Tax @ 35% CFAT 6,519,231 1,730,769 605,769 9,444,231 $ 10 $ 4% 3% Revenue Operating Expense EBITDA Depreciation Charge Taxable income Tax @35% tax rate Net Income CFAT 5 1,080,000 $ 1,123,200 $ 1,168,128 $ 1,214,853 $ 1,263,447 $ 1,313,985 $ 216,000 $ 222,480 $ 229,154 $ 236,029 $ 243,110 $ 250,403 $ 864,000 900,720 938,974 978,824 1,020,337 1,063,582 173,077 173,077 173,077 173,077 173,077 173,077 690,923 727,643 765,897 805,747 8 47,260 890,505 241,823 254,675 268,064 282,012 296,541 311,677 449,100 472,968 497,833 523,736 550,719 578,828 622,177 $ 646,045 $ 670,910 $ 696,813 $ 723,796 $ 751,905 $ 1,366,545 257,915 1,108,629 173,077 935,552 327,443 608,109 781,186 $ 1,421,206 $ 1,478,055 $ $ 265,653 $ 273,622 $ 1,155,554 1,204,432 1 73,077 173,077 982,477 1,031,355 343,867 360,974 638,610 670,381 $ 811,687 $ 843,458 $ 1,537,177 281,831 1,255,346 173,077 1,082,269 378,794 703,475 876,552 S 10 0 (8,250,000) $ 8% 9.7% 1 622,177 $ 987,970 2 646,045 34 $ 670,910 $ 696,813 $ 723,796 $ 751,905 $ 781,186 $ 811,687 $ 843,458 CF NPV @ MARR of IRR = $ 10,320,782