REE4204
Real Estate Finance
PROJECT
Please submit all parts in an Excel file (each part in a separate sheet) to
jfreybot@fiu.edu on October 16, 2018 (11.59pm).
CARRY OUT ALL YOUR CALCULATIONS IN EXCEL! You will be penalized if not
all calculations are done with Excel formulas.
DO NOT COOPERATE WITH OTHER STUDENTS! This is an individual
assignment.
You are advising an investor, who is interested in purchasing a multi-tenant office
building in Fort Lauderdale, which has an asking price of $120 per SF. Assume that,
apart from 2% purchasing costs, there are no additional acquisition-related costs.
Purchasing costs cannot be rolled into a mortgage and have to be paid out of pocket. The
building size is 60,390 SF. It is currently leased to three tenants:
•
The first tenant is currently renting 24,156 SF for $24.5/SF/year. The lease will expire
in 2 years (end of year).
•
The second tenant is currently renting 10,266 SF for $23/SF/year. The lease will
expire in 4 years (end of year) and has an annual rent increase of 2.5%.
•
The third tenant is currently occupying the remaining space for $22/SF/year and the
lease will expire in 6 years (end of year). Rental increases of $2 per SF will occur at
the beginning of the 2nd and 4th year.
After the first lease expires, assume a V&C of 5% of the PGI each year, which will
increase to 10% once the second lease expires. The market rent is currently
$19.25/SF/year and is expected to decrease by 3% each year for the next 3 years and then
increase again by 2.5% each year for the next 4 years.
1
REE4204
Real Estate Finance
Operating expenses for all leases are currently $9/SF/year and will increase annually by
2.5% (i.e. with inflation). The landlord covers 30% of operating expenses. No nonoperating expenses occur. A capital expense reserve of $2 per SF is created for each year.
At the point of sale, this reserve is eliminated and represents a cash inflow.
The building is depreciated over 39 years (mid-year convention for first and last
year apply) and the value of improvements (building) is considered to be 80% of the
purchasing price. The going out cap rate is 9% and the investor requires a return of 10%.
Selling costs are 2% of the sales price. The investor expects to hold the building for 5
years. Assume an income tax of 35% and a capital gains tax of 15%. For simplicity, you
can use the capital gains tax rate for depreciation recapture and pure capital gain at
the time of sale.
Part 1: Assuming that the investor wants to hold the property for 5 years, conduct a
discounted cash flow analysis (DCF) to calculate the after-tax IRR and NPV for this
investment. The investor received a lender’s offer for a 30year mortgage at 8%
(compounded monthly) with a loan to value ratio (LTV) of 75%. No financing costs (e.g.
origination fees) or discount points occur. Considering this FRM, what is the after-tax
NPV and IRR? Is this investment worth undertaking?
Part 2: The investor also has received offers for adjustable rate mortgages by three
lenders. All have annual interest rate adjustments. All mortgages assume a LTV of 70%
and 30 years maturity. For simplicity, use annual compounding.
2
REE4204
Real Estate Finance
Initial interest
ARM I
5%
Margin
Caps
2%
None
Prepayment penalty
Discount Points
None
2%
ARM II
3/1 mortgage; initial interest
is fixed for 3 years and only
interest is paid. Amortization
begins in year 4.
6.5%
2.5%
Periodic interest cap: 1.5%
(no negative amortization)
Periodic interest floor: 2%
3%
None
ARM III
5.5%
2%
Periodic payment cap
of 4.5% (no negative
amortization)
3%
2%
Interest rates are expected to change as follows over the next 5 years:
Year
Index
•
2
3%
3
2.25%
4
1.75%
5
2.5%
Calculate the payments for all three mortgages, i.e. calculate the initial cash
inflow from the mortgage, the annual payments and ending balance after 5 years.
•
Calculate the interest rate you are effectively paying for each of the three
mortgages. Which mortgage has the lowest effective rate?
•
Redo your investment analysis with the ARM you chose as having the lowest
effect rate. What is the after-tax IRR and NPV for this mortgage option? Is this
investment still worth undertaking? Should the investor choose the FRM or
ARM?
Part 3: The investor is also approached by a lender, who is interested in providing a 30yr
FRM with a 4.5% interest rate (monthly compounding) and LTV of 85%. However, the
lender requires a share of 60 % in the before tax equity reversion (BTER). What is the
after-tax IRR and NPV? Is this investment still worth undertaking?
3
REE4204
Real Estate Finance
Part 4: Overall and considering the FRM, ARM and SAM, which option would you
recommend the investor and why? (You can answer this in an easy-to-find space in
your Excel file.)
4
Pro-Forma Statement
Operating Cash Flows
Year
Potential Gross Income (PGI)
-Vacancy & Collection Losses (V&C)
= Effective Gross Income (EGI)
- Operating Expenses (OE)
= Net Operating Income (NOI)
- Capital Reserve
- Debt Service (Mortgage Payment)
= Before Tax Cash Flow (BTCF)
-Tax
= After Tax Cash Flow (ATCF)
1
2
Tax liability
NOI
- Depreciation
- Interest
= Taxable income
Tax
Equity reversion
Sales price
Capital Gains Tax
NSP
- Selling costs
= Net Selling Price (NSP)
- Mortgage balance
= Before Tax Equity Reversion (BTER)
- Capital Gains Tax (CGT)
= ATER
- Adjusted basis
= Taxable income
CGT
Discounted Cash Flow Analysis (DCF)
Year
ATCF
ATER
Initial equity investment
Total CF
0
1
3
4
5
6
2
3
4
5
Dear all,
Please find attached the FRM payment schedule as discussed in class today. You only have to change it from an
annual schedule to a monthly.
Please also see my comments below:
1) Tenant 2 rental rate and OE: The rates given in the assignment are for year 0, so for year 1 you have to increase
both of these in line with the information given (e.g. OE by inflation).
2) OE: The landlord only covers 30% of the OE (I spoke to one of you after class and forgot to mention this to him).
Just a reminder if any of you haven't accounted for this either.
3) Depreciation: Please remember the mid-year convention according to which you can only deduct half the annual
depreciation amount in the first and last year of ownership.
4) Adjusted basis: This is the purchase price less the accumulated (sum of) depreciation over the years of
ownership.
5) Capital Reserve: When the building is sold and the reserve liquidated, please treat it as a cash inflow below the net
selling price (NSP), i.e. it does not reduce the NSP (which is important for the capital gain calculation).
6) Accumulated interest for each year: If you want to use the =CUMIPMT formula, please remember to enter ‘0’ as
type to indicate payments at the end of a month (you’ll get an error otherwise) and change the start and end quarter
in the formula each year.
7) Negative taxable income: If you get a negative taxable income, set your tax to ‘0' for those years.
8) Negative ATCF: If you get a negative ATCF for certain years, remember that you can’t calculate the IRR. In these
cases, you can only calculate the NPV.
Please let me know if you have any questions.
Best,
JF
Mortgage
N
i
PMT
$
Year
Beginning Balance
Debt Service
Interest
Principal
Ending balance
$
3,500,000.00
$286,935.18 $ 227,500.00
$59,435.18 $ 3,440,564.82
$
3,440,564.82
$286,935.18 $ 223,636.71 $ 63,298.47 $ 3,377,266.35
$
3,377,266.35
$286,935.18 $ 219,522.31 $ 67,412.87 $ 3,309,853.47
$
3,309,853.47
$286,935.18 $ 215,140.48 $ 71,794.71 $ 3,238,058.77
$
3,238,058.77
$286,935.18 $ 210,473.82 $ 76,461.36 $ 3,161,597.40
$
3,161,597.40
$286,935.18 $ 205,503.83 $ 81,431.35 $ 3,080,166.05
$
3,080,166.05
$286,935.18 $ 200,210.79 $ 86,724.39 $ 2,993,441.66
$
2,993,441.66
$286,935.18 $ 194,573.71 $ 92,361.48 $ 2,901,080.18
$
2,901,080.18
$286,935.18 $ 188,570.21 $ 98,364.97 $ 2,802,715.21
$
2,802,715.21
$286,935.18 $ 182,176.49 $ 104,758.70 $ 2,697,956.52
$
2,697,956.52
$286,935.18 $ 175,367.17 $ 111,568.01 $ 2,586,388.51
$
2,586,388.51
$286,935.18 $ 168,115.25 $ 118,819.93 $ 2,467,568.57
$
2,467,568.57
$286,935.18 $ 160,391.96 $ 126,543.23 $ 2,341,025.35
$
2,341,025.35
$286,935.18 $ 152,166.65 $ 134,768.54 $ 2,206,256.81
$
2,206,256.81
$286,935.18 $ 143,406.69 $ 143,528.49 $ 2,062,728.32
$
2,062,728.32
$286,935.18 $ 134,077.34 $ 152,857.84 $ 1,909,870.48
$
1,909,870.48
$286,935.18 $ 124,141.58 $ 162,793.60 $ 1,747,076.88
$
1,747,076.88
$286,935.18 $ 113,560.00 $ 173,375.19 $ 1,573,701.69
$
1,573,701.69
$286,935.18 $ 102,290.61 $ 184,644.57 $ 1,389,057.11
$
1,389,057.11
$286,935.18 $ 90,288.71 $ 196,646.47 $ 1,192,410.64
$
1,192,410.64
$286,935.18 $ 77,506.69 $ 209,428.49 $
982,982.15
$
982,982.15
$286,935.18 $ 63,893.84 $ 223,041.34 $
759,940.81
$
759,940.81
$286,935.18 $ 49,396.15 $ 237,539.03 $
522,401.78
$
522,401.78
$286,935.18 $ 33,956.12 $ 252,979.07 $
269,422.71
$
269,422.71
$286,935.18 $ 17,512.48 $ 269,422.71 $
0.00
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
3,500,000.00
25 annually
6.50%
($286,935.18)
This payment schedule assumes annual payments. For your project you
Remember monthly payments require the interest rate to be divided by
yments. For your project you need to calculate monthly payments and change the payment schedule to monthly instead of annually!
nterest rate to be divided by 12 and N multiplied by 12 (in the PMT formula).
monthly instead of annually!
Mortgage $
N
i
PMT
Year
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
3,500,000.00
25 annually
6.50%
($286,935.18)
Beginning Balance
Debt Service
$
3,500,000.00
$286,935.18
$
3,440,564.82
$286,935.18
$
3,377,266.35
$286,935.18
$
3,309,853.47
$286,935.18
$
3,238,058.77
$286,935.18
$
3,161,597.40
$286,935.18
$
3,080,166.05
$286,935.18
$
2,993,441.66
$286,935.18
$
2,901,080.18
$286,935.18
$
2,802,715.21
$286,935.18
$
2,697,956.52
$286,935.18
$
2,586,388.51
$286,935.18
$
2,467,568.57
$286,935.18
$
2,341,025.35
$286,935.18
$
2,206,256.81
$286,935.18
$
2,062,728.32
$286,935.18
$
1,909,870.48
$286,935.18
$
1,747,076.88
$286,935.18
$
1,573,701.69
$286,935.18
$
1,389,057.11
$286,935.18
$
1,192,410.64
$286,935.18
$
982,982.15
$286,935.18
$
759,940.81
$286,935.18
$
522,401.78
$286,935.18
$
269,422.71
$286,935.18
This payment schedule assumes annual payments. For your project you ne
Remember monthly payments require the interest rate to be divided by 12
Interest
$ 227,500.00
$ 223,636.71
$ 219,522.31
$ 215,140.48
$ 210,473.82
$ 205,503.83
$ 200,210.79
$ 194,573.71
$ 188,570.21
$ 182,176.49
$ 175,367.17
$ 168,115.25
$ 160,391.96
$ 152,166.65
$ 143,406.69
$ 134,077.34
$ 124,141.58
$ 113,560.00
$ 102,290.61
$ 90,288.71
$ 77,506.69
$ 63,893.84
$ 49,396.15
$ 33,956.12
$ 17,512.48
Principal
$59,435.18
$ 63,298.47
$ 67,412.87
$ 71,794.71
$ 76,461.36
$ 81,431.35
$ 86,724.39
$ 92,361.48
$ 98,364.97
$ 104,758.70
$ 111,568.01
$ 118,819.93
$ 126,543.23
$ 134,768.54
$ 143,528.49
$ 152,857.84
$ 162,793.60
$ 173,375.19
$ 184,644.57
$ 196,646.47
$ 209,428.49
$ 223,041.34
$ 237,539.03
$ 252,979.07
$ 269,422.71
Ending balance
$ 3,440,564.82
$ 3,377,266.35
$ 3,309,853.47
$ 3,238,058.77
$ 3,161,597.40
$ 3,080,166.05
$ 2,993,441.66
$ 2,901,080.18
$ 2,802,715.21
$ 2,697,956.52
$ 2,586,388.51
$ 2,467,568.57
$ 2,341,025.35
$ 2,206,256.81
$ 2,062,728.32
$ 1,909,870.48
$ 1,747,076.88
$ 1,573,701.69
$ 1,389,057.11
$ 1,192,410.64
$
982,982.15
$
759,940.81
$
522,401.78
$
269,422.71
$
0.00
l payments. For your project you need to calculate monthly payments and change the payment schedule to monthly instead of annual
monthly instead of annually!
Mortgage
i
$ 3,500,000.00 N
6.25% PMT
25
($280,331.17)
With discount points
2% of mortgage$ amount
70,000.00 $ 3,430,000.00
Held until maturity (N=25)
6.47% Effective interest rate
With early repayment (year 9) and discount points
Outstanding balance
($2,784,987.74)
(after 9 years)
6.57% Effective interest rate
With early repayment, prepayment penalty and discount points
Outstanding balance
($2,868,537.37)
and penalty
6.79% Effective interest rate
Purchase answer to see full
attachment