Financial_leverage

.XLS

School

University of Wisconsin, Madison *

*We aren’t endorsed by this school

Course

410

Subject

Finance

Date

Jan 9, 2024

Type

XLS

Pages

1

Uploaded by EarlCrab248

Report
Page 1 of 1 Data Input Box: Asking Price $2,000,000 Tax Considerations: NOI year 1 $190,000 Building Value $1,600,000 Growth-NOI 3.00% Depreciation 27.5 years Loan-to-Value 70.00% Tax rate 28.00% Loan Interest 7.00% CGs Tax Rate 15% Loan Term 25 years Depreciation Recapture Tax 25% Payments per Year 12 Appreciation Rate 3.00% Holding Period 5 years Selling Costs 6.00% of sale price Equity 600,000 Loan 1,400,000 Annual Loan Payment 118,739 Mortgage Balance EOY 5 1,276,270 year 5 SUMMARY LOAN INFORMATION: End of Year 1 2 3 4 5 Payment 118,739 118,739 118,739 118,739 118,739 Mortgage Balance 1,378,583 1,355,617 1,330,991 1,304,585 1,276,270 Interest 97,322 95,773 94,113 92,333 90,424 Principal 21,417 22,966 24,626 26,406 28,315 Year 1 2 3 4 5 NOI 190,000 195,700 201,571 207,618 213,847 Debt Service 118,739 118,739 118,739 118,739 118,739 Before-tax Cash Flow 71,261 76,961 82,832 88,879 95,108 NOI 190,000 195,700 201,571 207,618 213,847 Less: Interest 97,322 95,773 94,113 92,333 90,424 Depreciation 58,182 58,182 58,182 58,182 58,182 Taxable Income 34,497 41,745 49,276 57,103 65,241 Tax (Savings) 9,659 11,689 13,797 15,989 18,267 After-tax Cash Flow 61,602 65,273 69,035 72,890 76,840 Cash flow from sale in year 5 Sales Price 2,318,548 Sales costs 139,113 Mortgage Balance 1,276,270 Before-tax cash flow 903,165 Original Cost Basis 2,000,000 Capital Exp 0 Capital Gain 179,435 CG Tax 26,915 Accumulated Depreciation 290,909 Dep Recapture Tax 72,727 After-tax cash flow from sale 803,523 IRR with Financing Year 0 1 2 3 4 5 BTCF (600,000) 71,261 76,961 82,832 88,879 998,273 BTIRR on Equity 20.23% Year 0 1 2 3 4 5 ATCF (600,000) 61,602 65,273 69,035 72,890 880,363 ATIRR on Equity 16.24% IRR without Financing Year 0 1 2 3 4 5 BTCF-no loan (=NOI) (2,000,000) 190,000 195,700 201,571 207,618 2,393,282 BTIRR - no loan 11.45% Year 0 1 2 3 4 5 ATCF-no loan (2,000,000) 153,091 157,195 161,422 165,776 2,250,053 ATIRR - no loan 8.71%
Discover more documents: Sign up today!
Unlock a world of knowledge! Explore tailored content for a richer learning experience. Here's what you'll get:
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help