Using an Excel spreadsheet, determine the expected levered-before-tax-annual rate of return on your capital investment of $159,000. Also, determine the portion of the return that is expected from the annual levered cash flows (CF).

$ Inputs: Acquisition Price Expected 1st Year NOI "Going in" CAP CAP over (below) market Required Rate of Return Expected NOI Growth Terminal NOI Growth $795,000 Down Payment $41,910 Mortgage Rate 5.27% Maturity 1.00% Levered Required Return 6.00% Minimum Required DCR 3.00% Expected Treasury Yield 2.50% Expected LT Inflation Expected Risk Premium (bp) Outputs: 159,000 Terminal CAP 7.00% 2.50% Loan Amount $636,000 20 Mortgage Payment (Annual) $40,798 9.00% DCR 1.03 1.25 Mimimum DCR Satisfied? No 4.50% Mortgage Balance at Sale $418,492) 2.50% Monthy -$3,3701 400 NOI DS End of year 0 Acquisition/Disposition ($795,000) 1 2 3 $41,910 $43,167 $44,462 $45,796 $47,170 $48,585 $50,043 $51,544 4 5 6 CF ($795,000) $41,910 $43,167 $44,462 $45,796 $47,170 $48,585 $50,043 $806,296 $760,223 -$34,777 5.29% PV of CF ($795,000) $39,538 $38,419 $37,331 $36,275 $35,248 $34,251 $33,281 $505,880 ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($459,289) Total PV: NPV: IRR: Levered CFPV of Levered CF $ (159,000) ($159,000) $1,112 $1,021 $2,370 $1,995 $3,665 $2,830 $4,999 $3,541 $6,373 $4,142 $7,788 $4,643 $9,245 $5,057 $347,006 $174,151 $197,379 $38,379 12.14% 7 8 $754,752 Total PV: NPV: IRR: Amortization schedule: I P 0 1 2 3 4 5 6 7 00 9 Ending balance $636,000 $24,898 $611,102 $25,520 $585,582 $26,158 $559,424 $26,812 $532,612 $27,482 $505,130 $28,169 $476,961 $28,874 $448,087 $29,595 $418,492 $30,335 $388,157 $31,094 $357,063 $31,871 $325,192 $32,668 $292,524 $33,484 $259,040 $34,322 $224,718 $35,180 $189,539 $36,059 $153,479 $36,961 $116,519 $37,885 $78,634 $38,832 $39,803 $39,803 $0 $15,900 $15,278 $14,640 $13,986 $13,315 $12,628 $11,924 $11,202 $10,462 $9,704 $8,927 $8,130 $7,313 $6,476 $5,618 $4,738 $3,837 $2,913 $1,966 $995 10 11 12 13 14 15 16 17 18 19 20 $ Inputs: Acquisition Price Expected 1st Year NOI "Going in" CAP CAP over (below) market Required Rate of Return Expected NOI Growth Terminal NOI Growth $795,000 Down Payment $41,910 Mortgage Rate 5.27% Maturity 1.00% Levered Required Return 6.00% Minimum Required DCR 3.00% Expected Treasury Yield 2.50% Expected LT Inflation Expected Risk Premium (bp) Outputs: 159,000 Terminal CAP 7.00% 2.50% Loan Amount $636,000 20 Mortgage Payment (Annual) $40,798 9.00% DCR 1.03 1.25 Mimimum DCR Satisfied? No 4.50% Mortgage Balance at Sale $418,492) 2.50% Monthy -$3,3701 400 NOI DS End of year 0 Acquisition/Disposition ($795,000) 1 2 3 $41,910 $43,167 $44,462 $45,796 $47,170 $48,585 $50,043 $51,544 4 5 6 CF ($795,000) $41,910 $43,167 $44,462 $45,796 $47,170 $48,585 $50,043 $806,296 $760,223 -$34,777 5.29% PV of CF ($795,000) $39,538 $38,419 $37,331 $36,275 $35,248 $34,251 $33,281 $505,880 ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($40,798) ($459,289) Total PV: NPV: IRR: Levered CFPV of Levered CF $ (159,000) ($159,000) $1,112 $1,021 $2,370 $1,995 $3,665 $2,830 $4,999 $3,541 $6,373 $4,142 $7,788 $4,643 $9,245 $5,057 $347,006 $174,151 $197,379 $38,379 12.14% 7 8 $754,752 Total PV: NPV: IRR: Amortization schedule: I P 0 1 2 3 4 5 6 7 00 9 Ending balance $636,000 $24,898 $611,102 $25,520 $585,582 $26,158 $559,424 $26,812 $532,612 $27,482 $505,130 $28,169 $476,961 $28,874 $448,087 $29,595 $418,492 $30,335 $388,157 $31,094 $357,063 $31,871 $325,192 $32,668 $292,524 $33,484 $259,040 $34,322 $224,718 $35,180 $189,539 $36,059 $153,479 $36,961 $116,519 $37,885 $78,634 $38,832 $39,803 $39,803 $0 $15,900 $15,278 $14,640 $13,986 $13,315 $12,628 $11,924 $11,202 $10,462 $9,704 $8,927 $8,130 $7,313 $6,476 $5,618 $4,738 $3,837 $2,913 $1,966 $995 10 11 12 13 14 15 16 17 18 19 20