Question

Sally & Dave’s Condo Project: Financing with a Mortgage

Overview

This mini-case takes us back to b-school grads Sally and Dave. You’ll perhaps recall from PFE Chapter 4 that   they’re   thinking   of   buying   a   condo.   In Chapter 4, Sally and Dave were planning to finance the condo purchase without borrowing. In this case we consider the case where they take out a mortgage to finance the investment. The point of this case is to get you to think about the effect of financing on returns. It should also lead to a discussion of the relation between financing and risk.

Case facts

• Sally and Dave are planning to purchase a condo that costs $120,000.
• Sally and Dave intend to take a 12-year mortgage for $60,000. The mortgage has interest rate of 5%, compounded annually. Repayment of the mortgage is in equal annual payments of interest and principal.
• Sally and Dave can rent out the condo for $2,000 per month. They’ll have to pay property taxes of $2,000 annually and they’re figuring on additional miscellaneous expenses of $1,500 per year.
• All the income from the condo has to be reported on their annual tax return. Currently Sally & Dave have a tax rate of 20%, and they think this rate will continue for the foreseeable future.
• The full cost of the condo can be depreciated over 24 years on a straight-line basis. Straight-line Depreciation Expense = Cost of Condo/24 years (do not consider a salvage value in your deprecation calculation).
• To calculate the return (IRR) from owning the condo, Sally and Dave assume that they will sell the condo at the end of 12 years for $140,000. Any gain over book value on the sale is, of course, taxable.

Assignment

1. Use the template for this case to calculate Sally and Dave’s IRR on their investment (Terminology:   Since the cost of the condo is $120,000 and since they’re borrowing $60,000, the equity investment is $60,000.) Remember that for income tax purposes depreciation and interest on the mortgage are expenses, but that repayment of mortgage principal is not an expense. Use Excel’s IPMT and PPMT functions (see explanation below).
2. Show (in a data table) the effect on the equity IRR when the mortgage goes from $0, $10,000, $20,000, ... , $110,000. Chart the results.
3. Show (in a data table) the effect on the equity IRR when the tax rate varies from 0% to 40% (in steps of 5%).
4. Suppose that Sally and Dave take a $60,000 mortgage with a 24-year term. They still plan to sell the apartment at the end of year 12. At this date they will repay the remaining mortgage principal with a 3% penalty for early repayment. Calculate the equity IRR. Recreate the data tables and chart mentioned above, as well. Note: Create a copy of your completed sheet for this second scenario.

Excel Note

A mortgage is a loan which usually involves flat annual repayments of principal and interest. We discussed such loans in Chapter 2, where we showed how to build a loan table which describes the annual breakdown of the payment into interest and principal.

Excel has two functions, IPMT and PPMT, which do this breakdown without the necessity of a loan table. You will find these functions handy in this case. Because interest is deductible for tax purposes and repayment of loan principal is not, this case requires you to distinguish between the two. That’s where IPMT and PPMT come in.

Answer 1

		12 Year Mortgage for $60,000
		Interest Rate =5%
		MORTGAGE SCHEDULE
		*Interest Calculation	(Using IPMT function of excel withRate=5%, Per =N, Nper=12, Pv=-60000)
		** Principal Calculation	(Using PPMT function of excel withRate=5%, Per =N, Nper=12, Pv=-60000)
		N	I*	P**	A	B=I+P	C=I*0.2
		Year	Interest	Principal	Ending Balance Loan	Annual Mortgage payment	Interest Tax shield
		0			$60,000
		1	$3,000.00	$3,769.52	$56,230.48	$6,769.52	$600.00
		2	$2,811.52	$3,958.00	$52,272.47	$6,769.52	$562.30
		3	$2,613.62	$4,155.90	$48,116.57	$6,769.52	$522.72
		4	$2,405.83	$4,363.70	$43,752.88	$6,769.52	$481.17
		5	$2,187.64	$4,581.88	$39,171.00	$6,769.52	$437.53
		6	$1,958.55	$4,810.97	$34,360.02	$6,769.52	$391.71
		7	$1,718.00	$5,051.52	$29,308.50	$6,769.52	$343.60
		8	$1,465.42	$5,304.10	$24,004.40	$6,769.52	$293.08
		9	$1,200.22	$5,569.30	$18,435.09	$6,769.52	$240.04
		10	$921.75	$5,847.77	$12,587.32	$6,769.52	$184.35
		11	$629.37	$6,140.16	$6,447.17	$6,769.52	$125.87
		12	$322.36	$6,447.17	($0.00)	$6,769.52	$64.47
		Annual Depreciation=120000/24	$5,000
	D	Annual Depreciation tax shield	$1,000	(5000*0.2)
		Accumulated depreciation in 12 years	$60,000	(12*5000)
		Book Value at end of 12 years	$60,000	(120000-60000)
		Selling Price at end of 12 ears	$140,000
		Gain on sale	$80,000	(140000-60000)
		Tax on gain	$16,000	(80000*0.2)
	E	After tax terminal cash flow	$124,000	(140000-16000)
		Annual Net Income
		Annual Rental Income	$24,000	(2000*12)
		Property taxes	($2,000)
		Micelleneous expenses	($1,500)
		Before tax income	$20,500
	G	After tax annual income	$16,400	(20500*(1-0.2)
		YEAR WISE CASH FLOW:
			H	G	C	D	B	E	CF=H+G+C+D+B+E
		Year	Initial Cash flow	After Tax annual income	Interest tax shield	Depreiation tax shield	Annual Mortgage payment	TerminalCash flow on selling	Net cash Flow
		0	($60,000)						($60,000)
		1		$16,400	$600.00	$1,000	($6,769.52)		$11,230.48
		2		$16,400	$562.30	$1,000	($6,769.52)		$11,192.78
		3		$16,400	$522.72	$1,000	($6,769.52)		$11,153.20
		4		$16,400	$481.17	$1,000	($6,769.52)		$11,111.64
		5		$16,400	$437.53	$1,000	($6,769.52)		$11,068.00
		6		$16,400	$391.71	$1,000	($6,769.52)		$11,022.19
		7		$16,400	$343.60	$1,000	($6,769.52)		$10,974.08
		8		$16,400	$293.08	$1,000	($6,769.52)		$10,923.56
		9		$16,400	$240.04	$1,000	($6,769.52)		$10,870.52
		10		$16,400	$184.35	$1,000	($6,769.52)		$10,814.83
		11		$16,400	$125.87	$1,000	($6,769.52)		$10,756.35
		12		$16,400	$64.47	$1,000	($6,769.52)	$124,000	$134,694.95
		Internal Rate of Return (IRR)	21.0%	(using IRR function of excel over Net Cash Flow)