Part 2: Loan Schedule

Fill in the loan schedules and property data. Make sure you reference cells and simply type in values. For example, the monthly payment in part 2 should be referenced from part 1. Interest is calculated as loan balance * periodic rate. Amortization can be calculated as monthly payment interest. Ending loan balance is calculated as beginning loan balance amortization. (25 points)

Part 3: Cash Flow Analysis

Fill in the cash flow tables. Property taxes in year 1 are calculated as purchase price * property tax rate. Property taxes in future years grow at 2 percent per year. Note that tax deductions include property taxes and interest. Tax savings are calculated as total tax deductions * income tax rate. After-tax cash flows are calculated as tax savings cash outflows before taxes. After tax cash flows from owning are calculated as after-tax cash flows + rent saved. Before-tax cash flow from sale can be calculated property value selling costs mortgage balance. The after-tax cash flow from sales are identical to the before-tax cash flow from sales due to exclusions. (25 points)

Part 1) Propery and Loan Information
Property Information			Loan Information
Purchase price			Loan amount
Yearly Rent			Loan-to-value ratio
Rental growth rate			Interest rate
Property growth rate			Loan term (years)
Insurance			Payments (per year)
Maintenance			Equity investment (down payment)
Expense growth rate			Periodic (monthly) rate
Income tax rate			Number of periods
Property tax %			Monthly loan Payment		Hint: Use the Excel PMT function
Selling expenses %

Part 2) Loan Schedule and Property Data
Loan Schedule (5 years)
Month	Beginning Loan Balance	Monthly Payment	Interest	Amortization	Ending Loan Balance
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

Yearly Summary of Loan Schedule
End of year	1	2	3	4	5
Payment						Hint: Monthly loan payment * 12
Balance						Hint: Ending loan balance at year end
Interest						Hint: Use sum Excel function to find sum of interest over each year
Principal						Hint: Use sum Excel function to find sum of amortization over each year

Yearly Property Data
Year	0	1	2	3	4	5
Property value
Rents