Encerrado

Finance Problems using Excel

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).

Habilidades: Contabilidade, Análise de Negócios, Processamento de Dados, Excel, Finanças

Ver mais: finance problems excel, excel finance problems, noi excel, mortgage constant problems, finance problem excel, cap rate problem excel, finance problems answer excel, dscr noi excel, ending principal balance excel, using excel finance problems, partition irr, dscr mortgage constant, work seek, will holding, will be holding, what would my monthly payment be, what is your goal, what is seek, what does data entry do, were can i find accounting, value options, value city, vacancy in accounting, total finance, service seek

Acerca do Empregador:
( 0 comentários ) NYC, United States

ID do Projeto: #567177

13 freelancers estão ofertando em média $120 para este trabalho

srinichal

I can do the project provided all the formulas provided

$180 USD in 2 dias
(10 Comentários)
4.7
mantripti

Hi I am Mechanical Engineer + ME (Indust Engg & Management Sc) + MBA (Mkt) + Cost Management Accountant + Certified Energy Auditor All questions are normal for me. May be you may feel my bid little high Regar Mais

$200 USD em 1 dia
(21 Comentários)
4.1
SoftSandila

I can deliver within 10 hours. Regards, SS

$150 USD in 0 dias
(9 Comentários)
4.0
oFAZo

Exceptional quality & Quick work. Please refer to Profile & PMB for details. Thanks FAZ

$55 USD em 1 dia
(9 Comentários)
3.1
katudeni

Hi, your questions will be answered by a 15 year experianced CIMA qualified analyst. READY TO START. Pls check PMB for more. Thanks.

$145 USD in 0 dias
(3 Comentários)
2.7
Khurramz

Hi! Please see my PMB for details

$150 USD in 2 dias
(1 Comentário)
1.5
andreasgoldstep

Sir, your question will be answer perfectly by excel and financial expert. THANKS

$30 USD em 1 dia
(1 Comentário)
1.0
ifte5161

Ok its possible

$100 USD in 5 dias
(0 Comentários)
0.0
Shikha80in

Hi, I am an mba finance with corporate experience in loan origination and amort analysis. I also do online tutoring for finance related subjects. Pm me to discuss details and I will start on this right away. Regards, S Mais

$175 USD in 2 dias
(0 Comentários)
0.0
sumeetbehal81

Kindly contact. Solutions are already done

$100 USD em 1 dia
(0 Comentários)
0.0
sumansaha39

hi sir allow me to work with u .

$31 USD em 1 dia
(0 Comentários)
0.0
ankit31

to be done by student of one of the top mba institute.

$100 USD in 0 dias
(0 Comentários)
0.0
dren

I can help you solve these problems using Excel financial functions

$150 USD in 2 dias
(0 Comentários)
0.0