Using appraisal for real estate, could you help me answer NPV and IRR and also c
ID: 2742547 • Letter: U
Question
Using appraisal for real estate, could you help me answer NPV and IRR and also could you show your calculations for equity investment, after tax cash flows, after tax equity reversion, interest, depreciation, and tax calculations pls?
You are considering the purchase of a Class A office building. You plan to purchase the office building on January 1st, 2017 and sell the building 5 years later on December 31st, 2021. After careful research you have determined the following:
Total acquisition price: $1,100,000.
Property consists of 10 office suites, 5 on the first floor and 5 on the second.
Contract rents: 5 suites at $2,000 per month and 5 at $1,600 per month.
Annual market rent increases: 3.0 % per year (first increase on 01/01/2018)
Vacancy and collection losses: 6.0% per year.
Operating expenses: 40% of effective gross income each year.
Capital expenditures: 5.0% of expected gross income each year.
Expected holding period: 5 years.
Property value is expected to increase 3.25% per year.
Selling expenses are expected to be 8.0% of selling price.
Loan information: 75% LTV, 9.0%, 30 years
Up-front financing costs: 3.5% of loan amount.
Depreciation: 90% of the acquisition price
Ordinary income tax rate: 30%
Capital gain tax rate: 15%
Depreciation recapture rate: 25%
Required return: 12%
Would you invest in the project based on your calculated NPV and IRR? Refer to your supporting documents.
Supporting documentation: show your calculations for: Equity Investment, after tax cash flows, after tax equity reversion, interest, depreciation, and tax calculations.
Explanation / Answer
Cost of acquisition:
Acquisition price =$1,100,000.
Upfront financing cost=3.5% of loan amount =75% of $1,100,000 *3.5%
=$28,875.
Total cash outflow at the time 0 is 25% of total value +upfront financing cost
=25%*$1,100,000+$28,875.
=$275,000+28,875
=$303,875
Annual cash flows:
Depreciation rate=90% of acquisition price =90%*$1,100,000
=$990,000.
This amount is depreciated over 30 years equally.
=$990,000/30
=$33,000.
Remark: For principal outstanding refer to the loan schedule below:
NPV=Present value of cash inflows - Present value of cash outflows.
=$739,818.90 -$752,715.81
=-$12,896.91.
As the NPV is negative, the project should not be taken up.
Uisng IRR method:
Using IRR function of excel i.e,IRR(range of cash flows)
IRR( range of cells) = -0.31%
As the IRR is also negative the project should not be taken up.
Loan instalment is calculated using PMT function =PMT(rate,nper,pv)Loan schedule is as below:
rate=.09/12
nper =30*12
PV=75% of cost of acquisition =75%*1,100,000.
=$6,638.14
Loan schedule as below:
Particulars 2017 2018 2019 2020 2021 Rents $216,000.00 $222,480.00 $229,154.40 $236,029.03 $243,109.90 Vacancy and collections issues $12,960.00 $13,348.80 $13,749.26 $14,161.74 $14,586.59 Operating expenses $86,400.00 $88,992.00 $91,661.76 $94,411.61 $97,243.96 Loan interest $74,021.26 $73,492.53 $72,914.19 $72,281.61 $71,589.68 Depreciation $33,000.00 $33,000.00 $33,000.00 $33,000.00 $33,000.00 Total costs $206,381.26 $208,833.33 $211,325.22 $213,854.96 $216,420.24 Revenue before tax $9,618.74 $13,646.67 $17,829.18 $22,174.07 $26,689.67 Tax $2,885.62 $4,094.00 $5,348.76 $6,652.22 $8,006.90 Revenue after tax $6,733.12 $9,552.67 $12,480.43 $15,521.85 $18,682.77 Add: Depreciation $33,000.00 $33,000.00 $33,000.00 $33,000.00 $33,000.00 Minus:Prinicipal payment $5,636.42 $6,165.15 $6,743.49 $7,376.07 $8,068.00 Minus:Capital expenditure $10,800.00 $11,124.00 $11,457.72 $11,801.45 $12,155.50 Cash inflows $23,296.70 $25,263.52 $27,279.22 $29,344.32 $31,459.27Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.