Please give me answer to 16 questions about NOI, PMT, LTV, and NOV-IRR, in question and answer format.
QUESTION 1
- Use the first sheet of the spreadsheet (titled “NOI”) to compute the Net Operating Income (NOI) for this property for Year 1.
Using the Income and Expense information as provided in the Setup on Page 2, Fill in the missing information in the spreadsheet.
Include the handwritten amounts. Do not assume the broker totals are correct.
Do
not use the management fee amount given, you will calculate the Fee based on 3% of EGI.
Vacancy and Collection loss are estimated to be 4% combined.
What is the NOI for Year 1?
QUESTION 2
- At the asking price of $5,400,000, what is the asking cap rate?
QUESTION 3
- What is the purchase price per Gross SF of existing building area (assuming 4,500 sf as given)?
QUESTION 4
Fill in the second sheet (titled “underwriting”).
Where it says “Income Test”, compute the biggest mortgage payment You can make given the NOI in Year 1 computed above.
Recall from slides 12, the NOI divided by the PMT must be equal to or greater than 1.20. This implies the maximum PMT is equal to NOI divided by 1.20.
What is the biggest mortgage payment the bank will allow You to make given the expected NOI in Year 1?
QUESTION 5
- Assume you can obtain a 30-year, fully amortizing loan, with monthly compounding at an interest rate of 4.50%.
What is the annual loan constant corresponding to these loan terms? Remember, the loan constant is equal to the annual payment divided by the loan amount.
Recall for an amortizing loan you must calculate the loan amount based on the annual loan constant which is the payment corresponding to $1 of principal (N=total # payments, I/Y= Int rate/# payments per year, PV=-1, FV=0, CPT PMT) then annualize.
QUESTION 6
- For the Income Test, what is the maximum loan amount corresponding to the loan terms and a DSCR of 1.2?
QUESTION 7
- For the Collateral Test, what is the biggest loan You can get assuming 75% maximum LTV?
QUESTION 8
- Assume for the rest of the question that You will Borrow the maximum available loan amount (that you qualify for). What is the loan that You borrow to buy the property?
QUESTION 9
What will your debt service payment be?
QUESTION 10
- Fill in the sheet titled “NPV-IRR”.
You will buy the property now (Year 0), collect NOI for 5 years, Year 1-Year 5, and sell it at the end of Year 5.
Your loan has a 5/4/3/2/1 prepayment penalty structure, so if you prepay in the first year, you will pay a penalty equal to 5% of the balance, in the second year you will pay a penalty equal to 4% of the balance etc.
Payoff the loan balance, the prepayment penalty and any transaction costs.
You forecast NOI will grow at 3% per year, compounded annually.
You forecast you can sell the property at the end of year 5 at a 5.25% cap rate.
Recall: Sale price in Year 5 equals the NOI in Year 6 divided by the cap rate.
How much will you sell the property for in Year 5?
QUESTION 11
- What is Your IRR for this investment?
QUESTION 12
- What is your “cash on cash” return for Year 1? Recall that the cash on cash return is equal to your annual Net Cash Flow divided by your total cash investment at closing. The formula = NCF Year 1 divided by NCF Year 0.
QUESTION 13
- For the following questions, change only the variable indicated. Keep the others as dictated above.
What is your IRR if NOI growth is 5% compounded annually (all other variables held constant)?
QUESTION 14
- Change only the variable indicated below. Keep the others as originally indicated.
What is your IRR if your exit cap rate is 4.75%?
Complete the data table using the What-if Analysis in Excel. Submit spreadsheet as directed below.
QUESTION 15
- Fill in the sheet titled “NPV-IRR Unlevered”
Assume you purchase this property without any financing.
What is your unleveraged IRR (using the base case of 3% NOI growth and 5.25% Exit Cap Rate)?
QUESTION 16
- Fill in the chart “Breaking down the contribution of NOI and Terminal Value to Investment Return.” Use the numbers from the Net Cash Flow line.
Using the NCF from years 1 through 5 as presented, how much would you be willing to invest at Year 0 if you wanted to earn a 10% IRR on this investment?
Place your order
