Question

1.       A hotel is on the market for a price of $ 10,000,000. The NOI projection for the 1st year is $ 900,000. It is then estimated to grow at a constant rate of 1.5%. You plan on getting a loan from a bank which offers you a 20-year, quarterly amortizing loan at a nominal rate of 6%. What is the maximum loan the bank would give you with a DSCR of 1.3?

a)      $ 8,152,517.31

b)      $ 8,032,036.76

c)       $ 8,502,736.83

d)      $ 10,000,000.00

Answer 1

DSCR = NOI / total debt service

total debt service is the yearly loan payment

NOI is the projected NOI for 1st year. After the 1st year, the NOI will increase, and DSCR will increase. Hence, the 1st year's NOI should be considered, as the 1st year will have the lowest DSCR.

DSCR = NOI / total debt service

Maximum total debt service = NOI / DSCR

Maximum total debt service = $900,000 / 1.3 = $692,307.69

Maximum quarterly debt service = $692,307.69 / 4 = $173,076.92

The maximum loan the bank would give you is calculated using PV function in Excel :

rate = 6% / 4 (quarterly rate = annual rate / 4)

nper = 20 * 4 (total number of quarterly loan payments = number of years * 4 = 20 * 4)

pmt = -173076.92 (maximum quarterly debt service)

PV is calculated to be $8,032,036.76

АЗ : x ft =PV(6%/4,20*4,-A2) B C D E $692,307.69 $173,076.92 $8,032,036.76

The maximum loan the bank would give you is $8,032,036.76