Please show all Excel formulas.
2. Apache's real estate department is considering buying a hangar and leasing it out to private jet operators. They ask you to calculate the NPV and IRR of the investment and have given you the data below. Assume that the hangar is sold in year 25 and that the mortgage runs 25 years. Inflator Item Square Footage Property Price ($) Down Payment Interest Rate Closing Costs at Start Broker Fee in Year 25 Yearly Property Appreciation Rent/ sq. ft/Inflator Op. Costs/year ($) Inflator Tax Rate Depreciation/ year ($) Value 1,910 1,015,000 10% 3.9% 8,000 5.0% 1.5% 3.00 11,640 21.0% 4,524 1.0% 1.0% Prob. 1 Prob. 2 Mort. Amort. 2 2 4 3 el 7 9 10 - Key Assumptions Square footage Property price (8) Down payment Interest rate Closing costs at start Broker fee in year 25 Property Value Yearly appreciation Mortgage Balance Net Property Value Operating Assumptions Rents../Inflator Op. costsyr (5)/Inflator Taste Depreciation year (3) Cash Flows Rent Income minus: Operating Costs minus: Debt Amortization plus interest tax shield plus: Depreciation tax shield minus: Initial Expenses plus: Sale Property in year 25 Total Cash Flows IRR NPV 5% Note: Tax shields are the tax pains from expensing interest or depreciation. The general formula is interest expense x tax rate for interest tax shield Prob. 1 Prob. 2 Mort. Amort. 11 12 13 14 15 16 12 18 19 20 21 22 23 24 25 2. Apache's real estate department is considering buying a hangar and leasing it out to private jet operators. They ask you to calculate the NPV and IRR of the investment and have given you the data below. Assume that the hangar is sold in year 25 and that the mortgage runs 25 years. Inflator Item Square Footage Property Price ($) Down Payment Interest Rate Closing Costs at Start Broker Fee in Year 25 Yearly Property Appreciation Rent/ sq. ft/Inflator Op. Costs/year ($) Inflator Tax Rate Depreciation/ year ($) Value 1,910 1,015,000 10% 3.9% 8,000 5.0% 1.5% 3.00 11,640 21.0% 4,524 1.0% 1.0% Prob. 1 Prob. 2 Mort. Amort. 2 2 4 3 el 7 9 10 - Key Assumptions Square footage Property price (8) Down payment Interest rate Closing costs at start Broker fee in year 25 Property Value Yearly appreciation Mortgage Balance Net Property Value Operating Assumptions Rents../Inflator Op. costsyr (5)/Inflator Taste Depreciation year (3) Cash Flows Rent Income minus: Operating Costs minus: Debt Amortization plus interest tax shield plus: Depreciation tax shield minus: Initial Expenses plus: Sale Property in year 25 Total Cash Flows IRR NPV 5% Note: Tax shields are the tax pains from expensing interest or depreciation. The general formula is interest expense x tax rate for interest tax shield Prob. 1 Prob. 2 Mort. Amort. 11 12 13 14 15 16 12 18 19 20 21 22 23 24 25
