# Finance Problems using Excel

Budget $30-250 USD

Need these questions to be answered in Excel using the functions (PMT,PV,FV,nPV,Rate,etc...) All work must be shown in

excel (the formulas have to be used and entered into the cells) I need it to be done 15 hours.

Please let me know.

Thanks

PART I

Question 1

Borrower is looking to obtain a loan for a property that she wants to purchase. The Banks loan officer came up with the following proposal:

Amount - $3,666,000

Term - 5 year loan

Interest rate – 5.25%

Amortization – 30 years

Origination fee – 200 bps

a. What is the monthly debt service?

b. What will the ending principal balance be at maturity (at the end of Year 5)?

c. What is the debt constant (also know as a mortgage constant or loan constant)?

d. What is the effective yield of the loan to the lender?

e. What is the total interest paid in Year 2 of the loan?

f. What is the total principal paid in year 4 of the loan?

g. What is the interest portion of the monthly payment in month 32?

h. What is the principal portion of the monthly payment in month 21?

i. If Borrower were to refinance the loan at the end of the 5th year at a 7% and 25 year amortization, what would be the new monthly payments assuming that the original loan maturity was not changed.

Question 2:

Jo is evaluating two investment opportunities.

Investment 1: An investment of $7,500,000 and annual cashflows for 3 years of $1,000,000 each year and a residual of $9,000,000

Investment 2: An investment of $5,000,000 and annual cashflows for 4 years of $100,000 each year and a residual of $10,000,000

a. Assuming a 15% quarterly compounding discount rate, what is the Net Present Value of each investment? What does this value represent?

b. What is the IRR of each investment?

c. Partition the IRR for each investment and explain which one you would recommend.

Question 3:

Cheatm & How, LLC is evaluating the purchase of a hotel for $9,100,000. It is estimated that the property will generate $934,000 of NOI in the first year. NOI is expected to increase 3% annually thereafter. At the end of a 5 year holding period the property will be sold at an 8.5% cap rate.

a. What is the going in cap rate (go out two decimal points)?

b. What is the IRR?

Question 4

Joan is looking at purchasing an office building in New York City. She believes that the building can be purchased for $125 million. NOI is expected to be $8 million in year 1 growing at 10% per year. The financing is comprised of 1st mortgage debt and mezzanine debt as illustrated below:

$ presented in millions

Option LTV Rate

First Mortgage Loan 70% 8% Fixed Rate , Interest Only

Mezzanine Loan 20% 21% Fixed Rate, Interest Only

a. What is the blended cost of debt?

b. Over a 3 year holding period, what is the DSCR in year 1, year 2 and year 3?

c. If the terms of the first mortgage loan change from no amortization to 25 year amortization period, then what is the DSCR in year 1, year 2, and year 3?

Question 5

Define the following terms,

a. First Mortgage

b. Second Mortgage

c. Note

d. Mezzanine Loan

e. Preferred Equity

f. Points

g. Holdback

h. Bondable Lease

Question 6

Assuming the following;

Property Purchase Price - $7,345,981

Closing Costs – 3.5%

NOI - $853,256

NOI Growth Rate – 2%

a. What is my Acquisition Cap Rate? (different then my purchase price cap rate)

Assuming the following for debt market requirements;

Term – 10 years

Required DSCR – [url removed, login to view]

Interest Rate – 8.2%

Amortization - 22

Points in – 1%

Points out – 3%

b. What is the maximum amount I can borrow in year 1 (assume a mortgage constant using monthly payments). (hint use the DSCR and Mortgage constant to get your answer)

c. If the loan is to become a floating rate loan at what interest rate does DSCR drop below 1x in every year. (you do not have to adjust the principal balance for this question, just base your answer of the original amount)

d. Assuming the answer to B is around $6,123,456 (its fairly close to that), What is the yield to the lender if the loan is outstanding for 2 years, 7 years.

Question 7

Find the Debt Service (mortgage) Constant for the following three scenarios.

a. Interest Rate – 5.75% annual compounding, Amortization – 25 years

b. Interest Rate – 9.25% quarterly compounding, Amortization – 40 years

c. Interest Rate – 8.00% monthly compounding, Amortization – 15 years

a. How likely is it that the IRR shown will be achieved, be very specific in your answer?

Question 9

Would you prefer making a $25k investment that will earn interest at a rate of 8.75% compounded monthly or 9% compounded quarterly.

Question 10

John makes a loan of $[url removed, login to view] on a building. The terms of the loan is 17% for 4 years. (assume 100% of the coupon is accrued)

a. What will be the outstanding balance at the end of the term assuming monthly compounding.

b. Assuming the loan earned 12% compounded quarterly, how much would the outstanding balance be at the end of the term.

c. Compare the two options together and compute the effective annual yields of the loans.

Question 11

A first mortgage loan in the amount of $[url removed, login to view] is advanced at 11% for 25 years. Payments are to be made monthly in each part of this problem.

1. What will monthly payments be if?

A. The loan is fully amortizing.

B. It is partially amortizing and a balloon payment of $[url removed, login to view] is scheduled at the end of the term.

C. If it is interest only

D. It is a negative amortization loan and the loan balance will be $[url removed, login to view] at the end of the term.

2. What will the loan balance be at the end of year 7 under scenarios 1A-D?

3. what would be the principal portion of the payment schedule for payments at the end of the 21st month under scenarios 1A-D

4. Assume the lender charged 1 points to close the loan under scenario 1A-D.

A. If the loan is prepaid at the end of year 7 what would the effective rate of interest be for each scenario

5. Assume the loan is to be fully amortizing. Except the payments will be zero (interest accrued) for the first 3 years. If the loan is to fully amortize over the remaining term, what must the monthly payments be.

Question 12

Assuming the following;

Purchase price $[url removed, login to view]

Closing Costs 1%

Goss potential revenue - $[url removed, login to view]

Expected vacancy and bad debt – 8%

Expense Ratio – 40%

Revenue inflator – 4%

Expense Inflator – 2.5%

Holding period – 6 years

Exit cap rate – 7.5% (on projected year 6 NOI)

Disposition expenses -3.5%

LIBOR – 250bps

Spread – 5%

LIBOR annual inflator - 25bps

Max LTV – 77% of purchase price

Minimum DSC [url removed, login to view]

• Assuming the lender will advance a loan the lesser of the Max LTV or the Min. DSCR required, what is the maximum I can borrow.

• What is the unleveraged IRR for this investment

• What is the leavered IRR for this investment.

• What is the DSCR for each year

• Extra Credit - Assuming I want a 25% return on my equity (a levered return), what is the most I should pay? (hint – try to figure out how to use the goal seek function).