I need help with a pro forma statement attached are the directions for it and the outline of the pro forma. I would like to know if you can structure the proforma like the outline I have provided.

Operating Cash Flows PGI V&C EGI OE NOI DS BTCF Tax ATCF Tax liability NOI Depreciation Interest Taxable income Tax Depreciation is based on the building value as a percentage of purchase price. Mid Use either payment schedule or =CUMIPMT function in Excel to determine interest Equity reversion Sales price Selling costs NSP Mortgage balance BTER CGT ATER Discounted Cash Flow Analysis (DCF) ATCF ATER Initial equity investment Total CF Capital Gains Tax NSP Adjusted basis Adjusted basis is the difference Taxable income CGT age of purchase price. Mid-year convention in year of purchase and sale (half depreciation amount). xcel to determine interest payments in a year. ted basis is the difference between purchase price and accumulated depreciation. Real Estate Finance Carry out all your calculations in Excel and show your work! Do not cooperate with other students! An investor is interested in purchasing a multitenant office building in Miami, which has an asking price of $135 per SF. Assume that, apart from 2% purchasing cost, there are no additional acquisitionrelated costs. The building size is 60,390 SF. It is currently leased to three tenants: The first tenant is currently renting 24,156 SF for $25/SF/year. The lease will expire in 2 years. The second tenant is currently renting 10,266 SF for $23.5/SF/year. The lease will expire in 4 years and has an annual rent increase of 2.5%. The third tena!nt is currently occupying the remaining space for $22/SF/year and the lease will expire in 6 years. Rental increases of $2 per SF will occur at the beginning of the 2nd and 4th 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.75/SF/year and is expected to decrease by 3.5% each year for the next 3 years and then increase again at 2.5% each year for the next 4 years. Operating expenses for all leases are currently $8.5/SF/year and will increase annually by 2.5% (i.e. with inflation). The landlord covers the remaining 25% of operating expenses. 1 Real Estate Finance In year 4, on elevator will have to be replaced, which will cost $450,000. As a result, a capital expense reserve of $150,000 has to be created for year 1 to 3. The building is depreciated over 39 years (midyear convention for first and last year) and the value of improvements (building) is considered to be 80% of the purchasing price. The going out cap rate is 8.5% 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 17%. Makes sure you tax the depreciation recapture and the pure capital gain with the appropriate tax rates. Part 1: Assuming that the investor wants to hold the property for 5 years, conduct a discounted cash flow analysis (DCF) to calculate the aftertax IRR and NPV for this investment. The investor received a lender's offer for a 30year mortgage at 7.5% (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 are the aftertax 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. Initial interest ARM I 4% ARM II ARM III 3/1 mortgage; initial interest 5.5% is fixed for 3 years and only interest is paid. Amortization 2 Real Estate Finance Margin Caps 2% None Prepayment penalty Discount Points None 2% begins in year 4. 6.5% 2.5% Periodic interest cap: 2% (no negative amortization) Periodic interest floor: 1.5% 3% None 2% Periodic payment cap of 5% (no negative amortization) 3% 2% Interest rates are expected to change as follows over the next 5 years: Year Index 2 3% 3 2.5% 4 1.8% 5 2.3% 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. Calculate the interest rate you are effectively paying (IRR) for each of the three mortgages. Which mortgage has the lowest effective rate? Redo your investment analysis with the ARM you chose as having the lowest effect rate. What are the aftertax IRR and NPV? 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% interest rate (monthly compounding) and LTV of 85%. However, the lender requires an equity participation of 35% in each year's before tax cash flow (BTCF) and 40% in the before tax equity reversion (BTER). What are the aftertax IRR and NPV? Is this investment still worth undertaking? Overall and considering the FRM, ARM and participating mortgage, which option would you recommend the investor and why? 3 Real Estate Finance 4