Question

In: Finance

The specifics of the opportunity are as follows: Assume 100% occupancy Property purchase price $2,250,000. Loan...

The specifics of the opportunity are as follows:

Assume 100% occupancy

Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80%

Loan Terms: fully amortized over 30 years at 4.25% APR paid monthly

The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because the tenants in that unit are responsible for year round cosmetic maintenance of the walkways and greenspaces and also minor emergency repairs. You plan to invest an additional $150,000 in paid in capital for cosmetic updates on the property and closing fees. The tax rate on the property is 1.75% of the purchase price. For the sake of simplicity will assume that taxes and rents are constant.

5.If you hold the property for exactly 10 years then sell it for $3,500,000 what rate of return did you get if you consider the cost of a loan that you took to purchase the property given these loan conditions: $3,500 origination fee and $5,875 closing costs and 2.0 points paid at closing. Assume the loan was carried from purchase to close of sale then repaid exactly ten years after purchase. For simplicity we will assume that taxes and rents are held constant.

Instructions: Set up a spreadsheet for valuing this opportunity using Excel functions to answer the questions given

Solutions

Expert Solution

Please refer below spread sheet:-

Month Capital Investment Taxes Amortized Loan Payment Rent Received Total Capital
0 -$6,03,500.00 -$6,03,500.00
1 -$8,854.92 $9,295.00 $440.08
2 -$8,854.92 $9,295.00 $440.08
3 -$8,854.92 $9,295.00 $440.08
4 -$8,854.92 $9,295.00 $440.08
5 -$8,854.92 $9,295.00 $440.08
6 -$8,854.92 $9,295.00 $440.08
7 -$8,854.92 $9,295.00 $440.08
8 -$8,854.92 $9,295.00 $440.08
9 -$8,854.92 $9,295.00 $440.08
10 -$8,854.92 $9,295.00 $440.08
11 -$8,854.92 $9,295.00 $440.08
12 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
13 -$8,854.92 $9,295.00 $440.08
14 -$8,854.92 $9,295.00 $440.08
15 -$8,854.92 $9,295.00 $440.08
16 -$8,854.92 $9,295.00 $440.08
17 -$8,854.92 $9,295.00 $440.08
18 -$8,854.92 $9,295.00 $440.08
19 -$8,854.92 $9,295.00 $440.08
20 -$8,854.92 $9,295.00 $440.08
21 -$8,854.92 $9,295.00 $440.08
22 -$8,854.92 $9,295.00 $440.08
23 -$8,854.92 $9,295.00 $440.08
24 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
25 -$8,854.92 $9,295.00 $440.08
26 -$8,854.92 $9,295.00 $440.08
27 -$8,854.92 $9,295.00 $440.08
28 -$8,854.92 $9,295.00 $440.08
29 -$8,854.92 $9,295.00 $440.08
30 -$8,854.92 $9,295.00 $440.08
31 -$8,854.92 $9,295.00 $440.08
32 -$8,854.92 $9,295.00 $440.08
33 -$8,854.92 $9,295.00 $440.08
34 -$8,854.92 $9,295.00 $440.08
35 -$8,854.92 $9,295.00 $440.08
36 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
37 -$8,854.92 $9,295.00 $440.08
38 -$8,854.92 $9,295.00 $440.08
39 -$8,854.92 $9,295.00 $440.08
40 -$8,854.92 $9,295.00 $440.08
41 -$8,854.92 $9,295.00 $440.08
42 -$8,854.92 $9,295.00 $440.08
43 -$8,854.92 $9,295.00 $440.08
44 -$8,854.92 $9,295.00 $440.08
45 -$8,854.92 $9,295.00 $440.08
46 -$8,854.92 $9,295.00 $440.08
47 -$8,854.92 $9,295.00 $440.08
48 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
49 -$8,854.92 $9,295.00 $440.08
50 -$8,854.92 $9,295.00 $440.08
51 -$8,854.92 $9,295.00 $440.08
52 -$8,854.92 $9,295.00 $440.08
53 -$8,854.92 $9,295.00 $440.08
54 -$8,854.92 $9,295.00 $440.08
55 -$8,854.92 $9,295.00 $440.08
56 -$8,854.92 $9,295.00 $440.08
57 -$8,854.92 $9,295.00 $440.08
58 -$8,854.92 $9,295.00 $440.08
59 -$8,854.92 $9,295.00 $440.08
60 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
61 -$8,854.92 $9,295.00 $440.08
62 -$8,854.92 $9,295.00 $440.08
63 -$8,854.92 $9,295.00 $440.08
64 -$8,854.92 $9,295.00 $440.08
65 -$8,854.92 $9,295.00 $440.08
66 -$8,854.92 $9,295.00 $440.08
67 -$8,854.92 $9,295.00 $440.08
68 -$8,854.92 $9,295.00 $440.08
69 -$8,854.92 $9,295.00 $440.08
70 -$8,854.92 $9,295.00 $440.08
71 -$8,854.92 $9,295.00 $440.08
72 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
73 -$8,854.92 $9,295.00 $440.08
74 -$8,854.92 $9,295.00 $440.08
75 -$8,854.92 $9,295.00 $440.08
76 -$8,854.92 $9,295.00 $440.08
77 -$8,854.92 $9,295.00 $440.08
78 -$8,854.92 $9,295.00 $440.08
79 -$8,854.92 $9,295.00 $440.08
80 -$8,854.92 $9,295.00 $440.08
81 -$8,854.92 $9,295.00 $440.08
82 -$8,854.92 $9,295.00 $440.08
83 -$8,854.92 $9,295.00 $440.08
84 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
85 -$8,854.92 $9,295.00 $440.08
86 -$8,854.92 $9,295.00 $440.08
87 -$8,854.92 $9,295.00 $440.08
88 -$8,854.92 $9,295.00 $440.08
89 -$8,854.92 $9,295.00 $440.08
90 -$8,854.92 $9,295.00 $440.08
91 -$8,854.92 $9,295.00 $440.08
92 -$8,854.92 $9,295.00 $440.08
93 -$8,854.92 $9,295.00 $440.08
94 -$8,854.92 $9,295.00 $440.08
95 -$8,854.92 $9,295.00 $440.08
96 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
97 -$8,854.92 $9,295.00 $440.08
98 -$8,854.92 $9,295.00 $440.08
99 -$8,854.92 $9,295.00 $440.08
100 -$8,854.92 $9,295.00 $440.08
101 -$8,854.92 $9,295.00 $440.08
102 -$8,854.92 $9,295.00 $440.08
103 -$8,854.92 $9,295.00 $440.08
104 -$8,854.92 $9,295.00 $440.08
105 -$8,854.92 $9,295.00 $440.08
106 -$8,854.92 $9,295.00 $440.08
107 -$8,854.92 $9,295.00 $440.08
108 -$28,125.00 -$8,854.92 $9,295.00 -$27,684.92
109 -$8,854.92 $9,295.00 $440.08
110 -$8,854.92 $9,295.00 $440.08
111 -$8,854.92 $9,295.00 $440.08
112 -$8,854.92 $9,295.00 $440.08
113 -$8,854.92 $9,295.00 $440.08
114 -$8,854.92 $9,295.00 $440.08
115 -$8,854.92 $9,295.00 $440.08
116 -$8,854.92 $9,295.00 $440.08
117 -$8,854.92 $9,295.00 $440.08
118 -$8,854.92 $9,295.00 $440.08
119 -$8,854.92 $9,295.00 $440.08
120 $20,35,547.22 -$28,125.00 -$8,854.92 $9,295.00 $20,07,862.30

In above spread sheet we can see that in second column of capital investment we have initial outflow of $603500 as below =

= 20% of Purchase Price + $150000 + $3500 (Origination Fees)

and in the same column at last we have selling price - Remaining loan price - $5875 (closing fees) - 2% of remaining loaned price

= $3500000 - FV formula in excel explained below - $5875 - 0.02 * FV formula

FV (0.0425/12,120,1800000, PMT, End of period i.e.0) = $1429978.22

PMT (0.0425 / 12 , 12*30 , 1800000, 0 , 0) = $8854.92

In third column we have taxes paid

As per question taxes are 1.75 % of Purchase price so 0.0175 * 2250000 = $28125 paid at end of each year.

In fourth column we have amortized loan payment calculated with PMT formula in excel

PMT (0.0425 / 12 , 12*30 , 1800000, 0 , 0) = $8854.92

Rent received = 5.5 * 1690 = $9295

Total capital columnn is sum of all other columns.

Now for calcuating return we can use Internal Rate of Return formula in excel as below:-

IRR (Select all 120 cells of Total value complete column)

= 0.85% monthly

so yearly it will be = 0.85*12 = 10.1835%

Please let me know if you need any further help.

Thank You!!

jeff jeffy answered 2 years ago
Next > < Previous

Related Solutions

The specifics of the opportunity are as follows: Assume 100% occupancy Property purchase price $2,250,000. Loan...
The specifics of the opportunity are as follows: Assume 100% occupancy Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80% Loan Terms: fully amortized over 30 years at 4.25% APR (nominal rate) paid monthly The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because...
The specifics of the opportunity are as follows: Assume 100% occupancy Property purchase price $2,250,000. Loan...
The specifics of the opportunity are as follows: Assume 100% occupancy Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80% Loan Terms: fully amortized over 30 years at 4.25% APR paid monthly The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because the tenants...
Assume 100% occupancy The specifics of the opportunity are as follows: Property purchase price $2,250,000. Loan...
Assume 100% occupancy The specifics of the opportunity are as follows: Property purchase price $2,250,000. Loan to Value Ratio (LTV) 80% Loan Terms: fully amortized over 30 years at 4.25% APR paid monthly The property offers six recently updated “luxury” apartments. Each apartment has 3 bedrooms and 2.5 baths in 1,800 square feet of living space. Rents are $1,690 per month per apartment. All six units are leased but one of the units only receives half rent because the tenants...
The purchase price of Property and Building was of 50 million (100 single rooms and restaurants...
The purchase price of Property and Building was of 50 million (100 single rooms and restaurants with a total of 200 seats) purchased 10 years ago. Each year, 250,000 have been recorded as depreciation cost. • The present value of Capital Stock is 14 millions, Retained Earnings 3.5 millions, Mortgage 30 millions, Other long term loan 3 millions, Working Capital 3 millions. • We plan a refurbishment plan over the next 3 years of 2 million a year; the planned...
Property Assumptions: Purchase Price:                                    &n
Property Assumptions: Purchase Price:                                                  $4,000000 Year 1 PGI:                                                      $600,000 PGI Growth Rate (Annual):                                3% Annual Vacancy and Collection Loss (VCL):      5% Operating Expenses (OER):                               35% Terminal Capitalization Rate for Sales Price        .09                        Capitalize NOI (Year 4) Anticipated Holding Period:                               3 Years Maximum LTV:                                                70% Interest Rate:                                                     5% Amortization Period:                                           30 Years Payments Per Year:                                                  12 Discount (Hurdle) Rate (Unleveraged & levered):       15% What is the Unleveraged IRR and NPV? What is the leveraged IRR and NPV? What is...
Property Assumptions Purchase Price:                                    &nb
Property Assumptions Purchase Price:                                                                        $12,500,000 Year 1 Potential Rental Income (PRI):                                    $1,650,000 PGI annual growth rate:                                                          3% Annual Vacancy and Credit Loss (VCL):                               5%             Over next 6yrs. Year 1 operating expenses (OER): (Oper. Expense Ratio)     35% OPEX annual growth rate (after year 1):                                 2% Sales Price :     Terminal Cap Rate                                           .09 Capitalize 6th yr. NOI Sales Costs: Commissions                                                      3% of Sales Price Anticipated holding period                                                      5 years Maximum loan-to-value (LTV) ratio:                                      75% Interest Rate:                                                                           5.25% Amortization Period:                                                               20 years...
A property is sold for $150,000 with the buyer agreeing to assume an existing loan of...
A property is sold for $150,000 with the buyer agreeing to assume an existing loan of $98,000 and executing a second note and deed of trust to the seller for $30,000. The purchase contract states that the unpaid balance of any existing loan is approximate and at closing any differences shall be reflected in cash. If the buyers closing costs are $2,500 and the remaining balance of the first loan is $89,800, what is the total cash due from the...
A long position in a call option with an exercise price of $100. Assume that the call costs $10 when you purchase it.
A long position in a call option with an exercise price of $100. Assume that the call costs $10 when you purchase it.1B) If the stock price at expiration is $150, what is the likely value of the call option?1C) What is the maximum loss an investor would face on the call?
Straddle Example• Current stock price = $100•Purchase at-the-money call (strike = 100) for $2 •Purchase at-the-money...
Straddle Example• Current stock price = $100•Purchase at-the-money call (strike = 100) for $2 •Purchase at-the-money put (strike = 100) for $3 •What is the total value of your option portfolio for different stock prices(e.g. $120 or $85?)
You are considering the purchase of a distressed property. The price is $115,000, and you believe...
You are considering the purchase of a distressed property. The price is $115,000, and you believe that you can complete the required repairs and renovation and re-sell the property in twelve months. You can finance the purchase with a $97,750, 6.5% interest-only loan. The costs associated with acquiring the property include legal fees of $950, an inspection fee of $600, $5,500 to pay off an existing lien, and loan closing costs of $3,500. A thorough and careful assessment of the...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT