Please see the attached pdf files and answer the question according to the each pdf file in their own project file attached

FINC 4355 - Real Estate Finance Project 2 Rent versus Own Analysis The goal of this project is to explore and analyze the difference between renting and owning a personal residence. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word (one to two pages) with a separate introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (conclusions, summary) section. Your grade will depend on both quantity and quality. Make sure to follow all directions or points will be deducted. Upon completion, please submit both your Word report and Excel file to blackboard. Each of the three parts is worth 25 point each. The report is worth 15 points and the Excel file is worth 10 points. Rent versus Own Analysis This project requires you to compare renting versus owning a home. Assume a property can be rented for $12,000 per year ($1,000 per month) or purchased for $150,000 with $30,000 down and financed with a fully amortizing mortgage loan of $120,000 at 7 percent interest for 30 years. Other costs associated with owning include maintenance costs of $500, insurance costs of $500, and property taxes of 2% of the purchase price. Assume the federal income tax rate is 28 percent. Growth rates for expenses (insurance, maintenance, property taxes), rents, and property value are a constant 2 percent per year. After five years, the property will be sold. Selling expenses of 7 percent would have to be paid at that time. Be sure to show your work in Excel. In other words, do not simply type values into the boxes, but reference prior cells when calculating results. In your report, identify how much money is saved from owning relative to renting after selling the house in year 5. If an annual after-tax return of 15% is available on an investment of comparable risk, which is the better option, owning or renting? Part 1: Monthly Payment Fill in the two tables, for property information and loan information, respectively. (25 points) Part 2: CPM Loan Fill in the loan schedules and property data. (25 points) Part 3: CAM Loan Fill in the cash flow tables. (25 points) Excel Note: If you want to lock in a cell reference, use the $ symbol. For example, if you would like to keep the value of cell A5 constant for use in a formula, reference it as $A$5. See https://support.office.com/enus/article/Switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e5f0d8d0baca9 FINC 4355 - Real Estate Finance Project 3 Effective Rent The goal of this project is to analyze and calculate effective rent. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word (one to two pages) with a separate introduction (description of the project), findings (answer assignment questions, plots, etc.), and conclusion (conclusions, summary) section. Your grade will depend on both quantity and quality. Make sure to follow all directions or points will be deducted. Upon completion, please submit both your Word report and Excel file to blackboard. Each of the five parts is worth 15 point each. The report is worth 15 points and the Excel file is worth 10 points. Effective Rent This project requires you to compute the effective rent for five alternatives for a five-year lease on 15,000 square feet of rentable space. Assume a discount rate of 8 percent. In your report, mention which rent type should be chosen to maximize the price per square foot. Part 1: Gross lease Rent will be $30.00 per rentable square foot each year. The lessor (owner) will be responsible for the payment of all operating expenses. Expenses are estimated to be $10.00 during the first year and will increase by $2.00 per year thereafter. Part 2: Gross lease with expense stop Rent will be $28.00 per rentable square foot per year with the lessor responsible for payment of recoverable operating expenses as identified in the lease, up to an expense stop of $10.00 per square foot. Lessee pays all expenses in excess of $10.00 per square foot. Expenses are estimated to be $10.00 during the first year and will increase by $2.00 per year thereafter. Part 3: Gross lease with step up rents Rent will be $15.00 per rentable square foot and will increase by $7.00 per square foot each year thereafter. Operating expense will be $10.00 per square foot and will increase by $0.50 per year. Part 4: Gross lease with step up rents and expense stop Rents will be $22.00 per square foot and will increase by $2 per year. Operating expenses will be $10.00, and will increase by $2.00 per year. The tenant will pay (the owner will recover) all increases in operating expense above a $10.00 expense stop. Part 5: Gross lease with expense stop and CPI adjustment Rent will be $20.00 per rentable square foot the first year and will increase by the full amount of any change in the CPI after the first year with an expense stop at $10.00 per square foot. The CPI is estimated to be 5% in year 2 and is estimated to increase at a rate of 25% per year (e.g. it will be 6.25% in year 3). Operating expenses are assumed to be $3 per square foot and will increase by $3 per year thereafter. FINC 4355 - Project 2 - Rent versus Own Analysis Last Name First Name Part 1) Propery and Loan Information Property Information Purchase price Initial Rent Rental growth rate Property growth rate Insurance Maintenance Expense growth rate Marginal tax rate Property tax % Selling expenses % Part 2) Loan Schedule and Property Data Loan Schedule (5 years) Month Beginning Loan Balance Monthly Payment 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 Summary Loan Schedule End of year Payment Balance Interest Principal 1 2 0 1 1 2 1 2 1 2 1 2 Property Data Year Property value Rents Part 3) Cash Flow Analysis Before-Tax Cash Flows-Owner Year Property taxes Insurance Maintenance Principal and Interest Cash Outflows before taxes Before-Tax Cash Flows-Owner Year Property taxes Interest Total tax deductions Tax savings Rents Year Rents Net Cash Flows-Owner Year Cash outflows before taxes Tax savings After tax cost Cost of renting After tax cash flow own vs. rent Before-Tax Cash Flows-Sales Year Sales price Selling Costs Mortgage balance Benefit from sale After-tax cash flow-sales Cash Savings and IRR Years Cash flow 1 2 0 1 Loan Information Loan amount Loan-to-value ratio Interest rate Loan term (years) Payments (per year) Equity investment (down payment) Periodic (monthly) rate Number of periods Monthly loan Payment Interest Hint: Use the Excel PMT function Amortization Ending Loan Balance 3 4 5 Hint: Monthly loan payment * 12 Hint: Ending loan balance at year end Hint: Use sum Excel function to find sum of Hint: Use sum Excel function to find sum of 2 3 4 3 4 5 5 Hint: Use the FV formula for one period Hint: Use the FV formula for one period Hint: Use the FV formula for one period Hint: The annual payment on the loan Hint: Sum the values above 3 4 5 Hint: Same values as above Hint: Values from the summary loan schedu Hint: Sum of property taces and interest Hint: Total tax * income tax rate 3 4 5 Hint: Same values from property data 3 4 5 Hint: Tax savings - cash outflows vefore taxe Hint: After tax cost + cost of renting 3 4 5 Hint: Same as benefit from sale above, due 2 3 4 5 hly loan payment * 12 ng loan balance at year end um Excel function to find sum of amortization over each year um Excel function to find sum of interest over each year Hint: Use the FV formula for one period Hint: Use the FV formula for one period he FV formula for one period he FV formula for one period he FV formula for one period nnual payment on the loan the values above e values as above s from the summary loan schedule of property taces and interest tax * income tax rate e values from property data avings - cash outflows vefore taxes tax cost + cost of renting e as benefit from sale above, due to tax exclusions IRR Hint: The down payment at time zero is a cash outflow (negative) Hint: The cash flows in years 1 to 4 are the after tax cash flows of owning compared to renting Hint: The cash flow includes the sum of the after tax cash flow of owning relative to renting and after tax cash flow f nting and after tax cash flow from the sale FINC 4355 - Project 3 - Effective Rent Last Name First Name Part 1) Gross lease Year Gross Rent Operating Expenses Recoveries Net Rent 1 Present Value Effective Net Rent 2 Hint: use the Excel NPV function Hint: use the Excel PMT function Part 2: Gross lease with expense stop Year Gross Rent Operating Expenses Recoveries Net Rent 1 2 1 2 Present Value Effective Net Rent Part 3: Gross lease with step up rents Year Gross Rent Operating Expenses Recoveries Net Rent Present Value Effective Net Rent Part 4: Gross lease with step up rents and expense stop Year Gross Rent Operating Expenses Recoveries Net Rent 1 2 1 2 Present Value Effective Net Rent Part 5: Gross lease with expense stop and CPI adjustment Year CPI Gross Rent Operating Expenses Recoveries Net Rent Present Value Effective Net Rent 3 4 5 3 4 5 3 4 5 el NPV function el PMT function 3 4 5 3 4 5 Hint: use the Excel MAX function he Excel MAX function