Question

An analyst is assigned the task of evaluating a real estate investment project. Purchase price is $700,000. Using the information in the table below, calculate the after-tax cash flow for the sale of the property.

After-tax cash flow without property sale	$33 546
Straight-line depreciation	$18 700
Mortgage Payment	$59 404
Cumulative Mortgage principle repayments until year 5	$20 783
80% financing at 10% interest rate
Marginal income tax rate	31%
Capital gains tax rate	20%
Forecasted sales price	$927 000
Sales expense as a % of price	6%

Answer 1

			Price of property	$700,000
			Amount of Down payment(20%)	$140,000
		Pv	Amount of Financing (80%)	$560,000
		Rate	interest rate	10%
		Pmt	Mortgage Payment	$59,404
		Nper	Number of Years of mortgage	30	(Using Nper function of excelwith Rate=10%, Pmt=59404, Pv=-560000)
			Excel Command:NPER(10%,59404,-560000)
			INTEREST TAX SHIELD
			Mortgage Schedule:	A	B	C=A*10%	D=B-A	E=A-D	F=C*31%
			Year	Beginning Loan Balance	MortgagePayment	Interest	Principal	Ending Loan Balance	Interest Tax Shield
			1	$560,000	$59,404	$56,000	$3,404	$556,596	$17,360
			2	$556,596	$59,404	$55,660	$3,744	$552,852	$17,254
			3	$552,852	$59,404	$55,285	$4,119	$548,733	$17,138
			4	$548,733	$59,404	$54,873	$4,531	$544,202	$17,011
			5	$544,202	$59,404	$54,420	$4,984	$539,218	$16,870
			ANNUAL DEPRECIATION TAX SHIELD
			Annual Depreciation	$18,700
			Depreciation tax shield	$5,797	(18700*31%)
			ANNUAL CASH FLOWS
			Year	After tax cash flow	Interest tax shield	Depreciation tax shield	Mortgage payment	Total AnnualCash Flow
			1	$33,546	$17,360	$5,797	($59,404)	($2,701)
			2	$33,546	$17,254	$5,797	($59,404)	($2,807)
			3	$33,546	$17,138	$5,797	($59,404)	($2,923)
			4	$33,546	$17,011	$5,797	($59,404)	($3,050)
			5	$33,546	$16,870	$5,797	($59,404)	($3,191)
			TERMINALCASH FLOW
			Forecasted Sales Price	$927,000
			Sales expense(6%)	($55,620)
			Net Sales proceeds	$871,380
			Amount of Capital gain	$171,380	(871380-700000)
			CapitalGain Tax(20%)	($34,276)
			Depreciation Recapture	$93,500	(18700*5)
			Income tax(31%)	($28,985)
			After tax cash flow fromSales	$808,119	(871380-34276-28985)
			Balance Loan Payment	-$539,217	(560000-20783)
			Net Terminalcash Flow	$268,902	(808119-539217)
			YEARWISE NET CASH FLOW
			N	CF	CF/(1.1^N)
			YEAR	CASH FLOW	PV of CASH FLOW
			0	($140,000)	($140,000)
			1	($2,701)	-$2,455
			2	($2,807)	-$2,319
			3	($2,923)	-$2,196
			4	($3,050)	-$2,083
		(-3191+268902)	5	$265,711	$164,986
				SUM	$15,932
			Net Present value	$15,932