Question

Using the Ellwood formula, estimate the required overall rate for an investor who is expected to put down 25%, desires a 14% equity yeild, expects the property to decrease 11% during his/her 9 year holding period, and expects to finance the balance of the purchase with a 30 year, monthly payment loan at 10.25% interest. (Check the correct answer.)

Question 5 options:

	.1190
	.1054
	.1259
	.1153
	.1300

Answer 1

First option showing 0.1190 is the correct answer.

Ellwood Formula:

Overall capitalization rate = R_E - LTV x [R_E + P x SFF_RE - M_C] - .SFF_RE

Where R_E = desired equity yield = 14%

LTV = Loan to Value ratio = 1 - 25% = 75% = 0.75

P = %age of mortgage paid off during the holding period

SFF_RE = Sinking fund factor at desired equity yield

M_C = Mortgage constant

= change in property value over holding period = - 11% = - 0.11

Loan terms & conditions:

Period = no. of months in 30 years,= 12 x 30 = 360

Frequency = monthly payment loan

Interest rate per annum = 10.25%

Interest rate per period = interest rate / month = 10.25% / 12 = 0.85417%

Hence monthly payment for a debt of $ 1 = PMT(Rate, Period, PV) = PV(0.85417%, 360, -1) = $ 0.00896

Annual amount serviced = 12 x PMT = $ 0.1075322

Mortgage constant, M_C = Annual amount serviced per $ of loan = 0.1075322

Holding period = 9 years = 12 x 9 = 108 months

P = %age of loan paid off during the holding period = CUMPRINC(Rate, Period, PV, Start period, End period, Type)

We want to cumulate the total principal paid in the loan starting from t = 1 to end of t = 108 periods where payment is made at the end of every period. Since we are interested in %age loan paid down, we can take PV as 1

Hence, P = CUMPRINC(0.85417%, 360, 1, 108, 0) = 0.073922898

SFF_RE = R_E / [(1 + R_E)^N - 1} where N = holding period = 9 years

Hence, SFF_RE = 14% / [(1+14%)⁹ - 1] = 0.062168384

We have calculated all the inputs. We can get the desired output as:

Overall capitalization rate = R_E - LTV x [R_E + P x SFF_RE - M_C] - .SFF_RE = 14% - 0.75 x [14% + 0.073922898 x 0.062168384 - 0.1075322] - (-0.11) x 0.062168384 = 11.9041% =  0.11904

Hence, the first option is correct.