How can I get the first part of this done ? I am not sure how to put it in excel.
REE4204 Real Estate Finance CARRY OUT ALL YO UR CAL C ULA TlONS IN EXCEL ! You will be penalized ifm)! all calculations are done with Excel form ulas. DO NOT COOPERA TE WITH OTHER STUDENTS! This is an individual assignment. You are advising an investor, who is interested in purchasing a multi-tenant office building in Fort Lauderdale, which has an asking price of $120 per SF. Assume that, apart from 2% purchasing costs, there are no additional acquisition-related costs. Purchasing costs cannot be rolled into a mortgage and have to be paid out of pocket. The building size is 60,390 SF. It is currently leased to three tenants: 0 The first tenant is currently renting 24,156 SF for $24.5/SF/year. The lease will expire in 2 years (end of year). c The second tenant is currently renting 10,266 SF for $23lSnyear. The lease will expire in 4 years (end of year) and has an annual rent increase of 2.5%. o The third tenant is currently occupying the remaining space for $22lSFlyear and the lease will expire in 6 years (end of year). Rental increases of $2 per SF will occur at the beginning of the 2" and 4 year. After the first lease expires, assume a V&C of 5% of the PGI each year, which will increase to 10% once the second lease expires. The market rent is currently $19.25/SF/year and is expected to decrease by 3% each year for the next 3 years and then increase again by 2.5% each year for the next 4 years. Operating expenses for all leases are currently $9/SF/year and will increase annually by 2.5% (i.e. with ination). The landlord covers 30% of operating expenses. No non- REE4204 Real Estate Finance operating expenses occur. A capital expense reserve of $2 per SF is created for each year. At the point of sale, this reserve is eliminated and represents a cash inow. The building is depreciated over 39 years (mid-year convention for rst and last year apply) and the value of improvements (building) is considered to be 80% of the purchasing price. The going out cap rate is 9% and the investor requires a return of 10%. Selling costs are 2% of the sales price. The investor expects to hold the building for 5 years. Assume an income tax of 35% and a capital gains tax of 15%. For simpiicity, you can use the capital gains tax rate for depreciation recapture and pure capitai gain at the time of sate. Part 1: Assuming that the investor wants to hold the property for 5 years, conduct a discounted cash flow analysis (DCF) to calculate the after-tax [RR and NPV for this investment. The investor received a lender's offer for a 30year mortgage at 8% (compounded monthly) with a loan to value ratio (LTV) of 75%. No financing costs (e.g. origination fees) or discount points occur. Considering this FRM, what is the after-tax NPV and IRR? Is this investment worth undertaking? Part 2: The investor also has received offers for adjustable rate mortgages by three lenders. All have annual interest rate adjustments. All mortgages assume a LTV of 70% and 30 years maturity. For simplicity, use annual compounding. ARM I ARM [1 ARM III REE4204 Real Estate Finance Initial interest 5% 3\" mortgage; initial interest is fixed for 3 years and only interest is paid. Amortization begins in year 4. 6.5% Caps None Periodic interest cap: 1.5% Periodic payment cap (no negative amortization) of 4.5% (no negative Periodic interest floor: 2% amortization Interest rates are expected to change as follows over the next 5 years: -_E-E-_ 2.25% 1.75% 0 Calculate the payments for all three mortgages, i.e. calculate the initial cash inflow from the mortgage, the annual payments and ending balance after 5 years. 0 Calculate the interest rate you are effectively paying for each of the three mortgages. Which mortgage has the lowest effective rate? 0 Redo your investment analysis with the ARM you chose as having the lowest effect rate. What is the after-tax [RR and NPV for this mortgage option? Is this investment still worth undertaking? Should the investor choose the FRM or ARM? Part 3: The investor is also approached by a lender, who is interested in providing a 30yr FRM with a 4.5% interest rate (monthly compounding) and LTV of 85%. However, the lender requires a share of 60 % in the before tax equity reversion (BTER). What is the after-tax [RR and NPV? Is this investment still worth undertaking
