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 agoRelated 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
ADVERTISEMENT
Latest Questions
- Financing Deficit Garlington Technologies Inc.'s 2018 financial statements are shown below: Balance Sheet as of December...
- Tesco is a customer-orientated business. It aims to offer products that provide value for money for...
- Discuss a movie that focuses on individuals mental illness. 1. What movie did you watch and...
- So previously in my class I had to write a python program where the user would...
- You are considered to be an expert in false memories, and a local district attorney has...
- Financing Deficit Stevens Textile Corporation's 2018 financial statements are shown below: Balance Sheet as of December...
- Maggie's Muffins Bakery generated $4 million in sales during 2018, and its year-end total assets were...
ADVERTISEMENT