Related data and formulas are given in the PDFs.

Along with the purchase price and estimated rental rates, please use the following assumptions for the purposes of your analysis:

Closing costs = 1% of purchase price

Interest Rate = 4%, 80% LTV, 30yr fully amortized

Home Price Appreciation = 5%

Annual Rent Increase = 4%

Homeowners Insurance = $1,000/yr (increases 4% each year)

Property Taxes = 1.2% of purchase price (increases 2% each year)

Water, Sewer, Garbage = $80/mo (increases 5% each year)

Annual Maintenance = $2,000 (increases 4% each year)

Depreciation: Tax Life = 27.5 years

Depreciation: Building = 80% of Total House Price

Marginal Tax Bracket = 40%

Capital Gains Tax Rate = 25%

A.Make sure your spreadsheet shows a First Year Operating Projection, 10 Year Operating Projection, Sales Analysis After 10 Years and Rate of Return Analysis After 10 Years

B.Once you have completed your spreadsheet and have found your IRR, play around with the variables for Appreciation, Annual Rent Increase and Interest Rate on the loan. Changing each of these variables up or down by 1%, which one makes the biggest impact on IRR?

A B C D E F G H K M N 94 Mortgage Paydown 1,981 2,062 2, 146 2,233 2,324 2,419 2,518 2,620 2, 727 2,838 95 Depreciation 4, 091) (4,091) (4, 091) (4, 091) (4, 091) (4,091) (4, 091) (4,091) 4, 091) 4,091) 96 Taxable Income 2,307 2,674 3,050 3,436 3,831 4,236 4, 651 5,077 5,512 5,959 97 |Cash Flow 4,417 4,703 4,995 5,293 5,597 5,908 6,225 6,547 6,877 7,212 99 Tax Savings @ 40% 1923) (1,070) (1,220) (1,374) 1,532 (1, 694) (1,860) (2,031) 2,205) 2,384 100 AFTER TAX CASH FLOW 3,494 3,633 3,775 3,919 4,065 4,214 4,364 4,517 4,672 4,828 101 102 SALES ANALYSIS AFTER 10 YEARS 104 Purchase Price $150,000 105 Appreciation @ 5% $94,334 106 Target Sale Price $244, 334 108 Estimated Closing Costs 1.0% $2,443 109 Real Estate Commission 5% $12,217 110 Gross Procedes of Sale $229,674 112 Accumulated Depreciation 10ys $40,909 113 Cost Basis (Purchost + Imp. - Acc. D $109,091 114 115 Loan Balance-end of 10yrs $88,632 116 Net Procedes of Sale (before taxes) $141, 042 118 Act. Cap Gain (Gross Proc. Tot. Cost Ba $120,583 119 Capital Gains Tax 25% $30, 146 120 121 RATE OF RETURN ANALYSIS AFTER 10 YEARS 122 Purchase Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 123 Purchase Price + Closing Costs ($151,800) 124 Initial Improvement Project $0 125 Traditional Mortgage $112,500 126 Cash Flow $4,417 $4,703 $4,995 $5,293 $5,597 $5,908 $6,225 $6,547 $6,877 $7,212 127 Net Proceeds of Sale $141,042 128 Cash Flow (before tax) $39,300) $4,417 $4,703 $4,995 $5,293 $5,597 $5,908 $6,225 $6,547 $6,877 $148,254 129 Internal Rate of Return (before tax) 22% 130 131 Tax Savings ($923) ($1, 070) ($1,220) ($1,374) ($1,532 ($1, 694) ($1,860) ($2,031) ($2, 205) $2,384 132 Capital Gains Tax Due ($30, 146) 133 Cash Flow (after tax) $39,300) $3,494 $3,633 $3,775 $3,919 $4,065 $4,214 $4, 364 $4,517 134 Internal Rate of Return (after tax) $4,672 $115,725 18%A B C D E F G H I J K L M N FIRST YEAR OPERATING PROJECTION (this is operating.. Doesn't take into acct. fix up) 8 Income/ Expense Baed on |Monthly Annually 49 50 Rent amount per bedroom * # of bedrooms $1,350 $16,200 51 Management Fee $0 $0 52 TOTAL RENTAL INCOME 53 $1,350 $16,200 54 Insurance $54 $646 55 Property Taxes 1.20% per year $150 $1,800 56 Property Maintenance $167 $2,000 57 Water, Sewer, Garbage $74 $892 58 TOTAL OPERATING EXPENSE ($445 59 ($5,338) 60 NET OPERATING INCOME $905.17 $10,862.04 61 62 Cap Rate (NOI / (Purchase Price + Initial Fix Up)) 7.24% 63 64 Mortgage Payment $537.09) ($6,445.11) 65 CASH FLOW (before taxes $368.08 $4,416.93 66 67 Mortgage Paydown above data from financing analysis $1,981 68 Depreciation (4, 091) 69 TAXABLE INCOME $2,307.19 70 71 Pre-Tax Cash Flow $4,416.93 72 Tax Savings @ 40% ($922.88) 73 AFTER TAX CASH FLOW $291.17 $3,494.06 74 75 76 TEN YEAR OPERATING PROJECTION 77 Income/ Expense Escalator Year Year 2 Year 3 Year 4 78 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10 79 Rent 3% 16,200 16, 686 17,187 17,702 18,233 18,780 19,344 80 Management Fee 19,924 20,522 21, 137 0 81 TOTAL RENTAL INCOME 0 16,200 16,686 17,187 17,702 82 18,233 18,780 19,344 19,924 20,522 21, 137 83 Insurance 3% 646 665 685 706 727 749 771 794 84 Property Taxes 818 843 2% 1,800 1,836 1,873 1,910 1,948 1,987 2,027 85 Property Maintenance 2, 068 5% 2, 109 $2,000 2, 151 2, 100 2,205 2,315 2,431 2,553 86 Water, Sewer, Garbage 2,680 2,814 2,955 5% 3,103 892 937 983 1,033 1,084 1 , 138 1, 195 1,255 87 TOTAL OPERATING EXPENSE 1 ,318 1,384 (5,338) 5, 538 15,746 (5,964) 6, 191) 88 (6,427) (6,674) (6,931) (7,200) (7,480) 89 NET OPERATING INCOME 10,862 11, 148 11,440 11,738 12,043 12,353 90 12,670 12,993 13,322 13,657 91 Mortgage Payment (6,445) 6,445) (6,445) (6,445 6,445 6,445 92 CASH FLOW (6,445) (6,445 6,445 (6,445) 4,417 4,703 4,995 5,293 93 5,597 5,908 6,225 6,547 6,877 7,212Amberwood Rd ASSUMPTIONS urchase Price terest Rate - 1st Mortgage Annual Rent Increase ment Fee (9% of Rents) Capital Gains Rate (Fed + CA) wners insurance (ann.) initial Improvement Project epredation : Building = of Total Value PURCHASE ANALYSIS Purchase Price Closing Costs 25 Total Cost Mortgage Equity -C4+828 -C4-C28 FINANCING ANALYSIS After Year 33 Monthly Payment -PMT(C6/12,360,C28,0,0) G$32 TOTAL PD -H32 -133+H3 EEEEEEEEEE G35-G36 =135+H 2 212 =138+H (-$C$33) .(1-(1+ WISCs6 OH+GET= -140+H41 FIRST YEAR OPERATING Income/ Expense Monthly Annually Management Fee mount per bedroom . # of bedrooms TOTAL RENTAL INCOME -SG$49*12 - G49+G50 -G50+12 -H49+H50 Property Taxes per year -$H$53/12 - SHES4/ 12 -$C$14 =H55/12 TOTAL OPERATING EXPENSE -$C$18 =$0$16 --SUM(G53:G56) NET OPERATING INCOME -G57+12 -G51+G57 Cap Rate (NOT / (Purchase -H51+H57 artgage Payment -H59/(C4+C17) CASH FLOW (before taxes) -G59+GG G63+12 Mortgage Paydown -H59+H63 bove data from financing analysis TAXABLE INCOME -$H$32 =-(1/$C$19)*($C$20*SCS Pre-Tax Cash Flow H64+ H6 6 + H67 AFTER TAX CASH FLOW Desc$11 -H64 H72/12 75 TEN YEAR OPERATING P Income/ Expense 77 Escalator Year 1 ear 2 Year 3 Year 4 Year S Rent 79 MS -$C$9 rear 6 ear 7 Year 8 Year 9 ear 10 ement Fee =$H$49 (1+5( $78) TOTA (1+$C$78) -D78+D79 -G78*(1+$C$78) -E78+E79 -H78*(1+$0478) -F78+F79 -G78+G79 =-H78*$C$10 -178+$C$10 1+5( $78) -378*(1+$C$78) L78*(1+$0$78) 82 Insurance -H78+H79 -178+179 -178+179 =-K78*$C$10 -K79*(1+$C$78) -K78+K79 -$H$53 -D82*(1+$($82) -L78+L79 -M78'SC$10 -M78+M79 Property Taxes D83*(1+$C83) -E82* (1+$0$82) -E83.(1+4083) -F82*(1+$($82) -G82*(1+$C$92) 85 Water , Sewer Garbage -SH$55 -F83*(1+$083) =H82*(1+$0482) -182*(1+$0$82) -J82*(1+$C$82) -383*(1+SC83) -K82"(1+$C$82) -F84*(1+$084) -Has ( 1+9085) LB2+(1+$C$82) SH$56 -GB4*(1+$C84) -H84*(1+$084) -185 (1+9085) -K83-(1+9083) L83*(1+$083) 96 TOTAL OPERATING EXPENSE --SUM(DB2:D85) -SUM( EB2:145) - -SUM( FB2:F85) - -SUM(GB2:G85) -H85"(1+$085) 38 NET OPERATING INCOME =-SUM(H82:H85) --SUM(182:185) -SUM(J82:185) 385"(1+$085) -$D$80+$0$96 --SUM (KB2: K85) EBD+696 -SUM(L82: L85) -SUM ( M82: MBS ) 90 Ma -F80+F86 -G80+GB6 HBO+H86 $C$33+12 180+186 -J80+386 CASH FLOW K80+K86 =LBD+L86 180+MB6 D88+D90 SC$33-12 EBB+ 290 $C$33*12 F88+F90 - $C$33.12 -GB8+G90 -HBB+H90 =$C$33+12 $C$ 33+12 189+190 -J88+190 -$C$33+12 93 ME SC$33+12 $C$33-12 Depredation -$H32 -$H33 -K98+K90 -$H34 BB+190 - MBB+ MSO 95 7 -$H35 Taxable Income -D91+D93+094 =$H37 291+293+194 =-(1/$C$19)"($C$20*$C$: =-(1/$C$19).($C$20*$C: =-(1/$C$19).($C$20*$C:=-(1/$C$19)"($C$20*$C$23) -F91+F93+F94 -G91+G93+694 =$H39 -$H41 az Cash Flow -191+193+194 -(1/$C$19)*($C$20*$CS: =-(1/$C$19).($C$20*SC=-(1/$C$19)*($C$20*SC =-(1/SC$19).($C$20*$C =-(1/$C$19)+($C$20*$C.=-(1/$C$19).($C$20*$0 -$H40 -H91+193+H94 191+193+194 -K91+K93+K94 =191+L93+194 - Me1+M93+M494 99 AFTER TAX CASH FLOW =$C$11 =-095409 -D98+097 =-F95*$09 -F98+F97 =-G95*$C9 -H91 =-H95*409 -191 -G98+G97 18+H97 =-395.SC9 198+397 -K95*$C9 101 SALES ANALYSIS AFTER =K98+K97 198+L97 =-M95+$09 MOB+M97 104 Appredation 195 Target S 09 Reading Costs -C107*D105 If Gross Procedes of Sale -C108*D105 =D105-D107-D109 111 Accumulated Depreciation 10ys =-10*$D$94