Loan Question

Ok ill bite - but I do admit a little googling and rolling up the sleeves might do some good here. This is a relatively basic question that has insane amount of info available...

There are usually 1 of 4 ways debt gets sized, of which the minimum is almost always the utilized loan amount... 1) based on a % of the value (LTV)...used most for stabilized assets that have a forecast NOI/sales comp 2) a % of the cost (LTC %)...used most for construction projects...where the cost is the budget 3) sized based on a debt service coverage ratio (DSCR)...can be for anything...but more so on stabilized assets / refi's where there is CF 4) a set dollar amount quoted in the term sheet ($Xmm)...almost always quoted in a term sheet - and dictates the maximum $ they are ok with. However, almost always the corresponding sizing metrics equal out to this input anyway...

REGARDING YOUR PROBLEM: since you dont have any LTC/LTV inputs, and only have a DCR with an income statement, you must size the loan using the coverage ratio...see below:

CALCULATE YOUR NOI Revenue = GRI * (1-vacancy rate) = $95,000*(1-5%) = $90,250 Expenses = 25% * GRI = 25% * $95,000 = $23,750 NOI = Rev minus expenses = $90,250 - $23,750 = $66,500

CALCULATE YOUR DEBT AMOUNT Use the PV excel formula to back into a loan value using the DCR rate...this looks like: =-PV(loan rate / 12, amortization yrs * 12, NOI/DCR) / 12 ==-PV(11% / 12, 25 * 12, $66,500 / 1.2) / 12 ===$471,176 is your CALCULATED LOAN AMOUNT based off the provided data

CALCULATE ALL IN COSTS FOR DEBT - they threw in "2 points". I'm not sure what they mean as it's way too shorthand for an exercise like this. If I had to guess, I'd say they meant the closing/commitment fee for the loan is 2pts or 2.0%.
- simply, using the above, your loan closing costs would be $471,176 * 2.0% = $9,424

OTHER now, they gave you some other info too...which is either trash, or you can use to quote would be metrics. for example: - a 471K loan over a 600K purchase price is 78.5%...a reasonable but high leverage for say residential - lets say you cap your NOI by an exit cap of 10% off F12 NOI...your forward 12mo NOI would be $69,160: (($95,000 * 1.04) * (1-5%)) - (($95,000 * 1.04) * 25%) = $69,160. at a 10% exit cap, your value is $691,000. $471,176 / $691,000 = 68.1% LTV, Yes a 10% exit cap seems high...but keep in mind your given a property with a 4% growth rate, a 26% expense ratio (much more normal to be around 40%-60%), and a note with an 11% rate.... Normal yield cap spreads historically are anywhere from 2%-4%...which would conservatively place your exit cap of 13%...bumping your LTV up to 88.6% (471,176 / (69,160/13%))...I'll let you decide which one you think is better to quote/use.

I dont think this is a job interview question...and I do hope you're just starting out and trying to learn the metrics and how they work. I would urge you to take the above, and really pull it together. understand the why behind the formulas...i.e. why do you PV to calculate the loan value? and not some other excel formula? Well, think it through...the PV formula is =PV(rate,nper,pmt,fv)...your rate is obviously the 11%...but as quoted in the example it's monthly - besides you would always assume this - so divide that by 12. the nper or number of periods is how long the loan is amortizing for, in months, i.e. 25 * 12 (usually see either 25 or 30 for this part). Your payment is the would be debt payment on this present valued loan amount...herein is NOI/DCR. Why this? What is DCR? your NOI/total debt payments. Apply simple algebra and saying NOI/DCR is the same thing as saying total annual debt service...meaning if you have those 2 metrics then you know what your debt service is...lastly your FV you leave blank...as we don't know this and don't need this for the formula. I understand that I asked for you to dig in to the meaning behind the formulas...and then did it for you....but if I were you I'd take that same logic and apply it to your next questions.

Hope this helped - good luck.