Need help with an excel spreadsheet – Due TODAY

Please do not respond if you cannot complete today:

  

I need help creating an excel spread sheet, I am not sure where to start on this one. Thank you

PLEASE NOTE – ALL RELEVANT information is attached. I mainly need the help with the spreadsheet.

   

Using the information calculated in previous weeks’ activities (NOI, debt service, interest, depreciation, and taxes (using an assumed 28% tax bracket) and ATCF) compute the before- tax IRR and NPV as well as the after-tax IRR and NPV.  Briefly discuss the impact of the following:

1)         A lower interest rate on the loan

2)         A higher depreciation deduction

3)         A lower NOI (aka higher expenses)

4)         A lower tax rate

You  must submit an EXCEL spreadsheet to validate your analysis.  Show the different scenarios in different tabs of the spreadsheet.   Ideally you will create a “master” table with all of your variables and reference each of the variables in your formulas.

 

Property I am using:

http://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=6&ved=0CFsQFjAF&url=http%3A%2F%2Fcp.capitalpacific.com%2FProperties%2FBalentine-Plaza–NewarkCA &ei=Qwb0UabMD6vriQLz8oHoCA&usg=AFQjCNEp_-SB0ISu74lMOrJQaTEpcNuHYg&sig2=XrDBUjj7osxxn4rxqlzv4A&bvm=bv.49784469,d.cGE 

Net Operating Income

2,102,272 2,191,064 2,204,764 2,190,012 299,623

																																																Pro Forma NOI
Year 00	Year 1	Year 2	Year 3	Year 4	Year 5
Income:
Gross Potential Rent (from Rental Summary)	$ 1,606,034	$ 1,626,081	$ 1,618,283	$ 1,645,647	$ 1,687,583
Other Income (from Rental Survey)	$ 587,938	$ 612,822	$ 634,979	$ 638,176	$ 661,935
Total Poential Rent:	$ 2,193,972	$ 2,238,903	$ 2,253,262	$ 2,283,823	$ 2,349,518
Less: Vacancy	91,700	47,839	48,498	93,811	49,895
Adjusted Gross Rent		2,102,272		2,191,064		2,204,764		2,190,012	2,	299,623
Total Cash In:
Expenses
Total Turnover and Operating Expense ($400/month/occupied unit)	600,444	617,227	633,661	651,649	670,590
Expense Reimbursement from tenants ($100/month/occupied unit)	95,574	98,441	101,394	104,436	107,569
Recurring Expenses (carpet, paint, repairs, etc.)
Commissions Paid to Apartment Locator Services	3,611	5,700	64,854	9,620	9,360
Total Expenses	508,481	524,486	597,121	556,833	572,381
Net Operating Income (NOI)	1,503,028	1,583,607	1,593,500	1,566,710	1,661,334

Activity #3:

Using the property you selected in Weeks 1 and 2 determine the following:

What are the financing requirements for the purchase of the property?   I began by utilizing the website for the lender that holds the current mortgage. According to Greenwich capital there are numerous documents that are needed as well as the following information just to get started:

http://greenwich.brixtec.com/ContactInfo.php

The required down payment according to the commercial listing agent website is showing 43% at $8,530,929

Conduct a search for a commercial lender that services your property class and determine the following items:

http://www.commercialbanc.com/calculator-payment.html

· What is the amount of the down payment? (Equity) $8,530,929

· What is the interest rate on a new loan? 7.5%

· What is the amortization period? 30 years

· Calculate the debt service (amount of monthly payments to pay off the debt) $80,682 per month

(Provide a link to your lender’s information)
http://www.commercialbanc.com/calculator-payment.html

Assume that the sales price (and BTER) are 25% greater than the purchase price.

Assume the sales price is 25% greater than actual purchase price : $15,043,678.50

I then input the 5 years of cashflow calculated in week 2 into the NPV calculator with a discount rate of 7.5% , it gave me an NPV of $3,921,331.60

Using the Week 2 spreadsheet calculate the NPV of the investment (using the 5-year projection).

Calculate the IRR for this investment. IRR for this investment is 18.03%

http://tools.financial-projections.com/IRRInternalRateOfReturn.html

http://www.financeformulas.net/Net_Present_Value.html