70.2K

Verified Solution

Link Copied!

10. In the Financial Statements worksheet, complete the worksheet to project the income and expenses from the vacation home for each of the next 10 years by doing the following: a. In cell C6, enter $36,000 as the Year 1 rental income from the property. b. In range the D6:L6, extrapolate the Year 2 income through Year 10 assuming a growth rate of 4 percent per year. c. In the range C8:C10, enter 1,200 for the Year 1 property taxes, 3,200 for the Year 1 maintenance costs, and 2,500 for the Year 1 miscellaneous costs. d. In the range D8:L10, extrapolate the Year 2 property taxes, maintenance costs, and miscellaneous costs through Year 10 assuming a growth rate of 3.5 percent per year. e. In the range C12:L12, calculate the total expenses for each year by summing the property taxes, maintenance costs, miscellaneous expenses, and deprecation for Year 1 through Year 10 . f. In the range C13:L13, calculate the pretax income for each year by subtracting the total expenses from the rental income. B. In the range C15:L15, calculate the income taxes owed by the company each year by multiplying the pretax income by the rental income tax rate. Use an If function so that if the pretax income is negative, the income tax is 0 . h. In the range Cl6:16, calculate the after-tax income for each year by subtracting the taxes owed from the pretax income. 11. At the end of Year 10, the company will sell the vacation home based on its anticipated future value. Calculate the finances associated with selling the property as follows: a. In cell L18, enter the income from the home sale using the value in coll B12 of the Home Info worksheet. b. In cell L19, enter the taxes owed on the home sale using the value in cell B15 of the Home Info worksheet. c. In cell L20, subtract the taxes owed on the home sale from the home sale income to calculate the net income from the sale of the home. 12. In the C.22:122 range, determine the yearly net income by adding the after-tax income to the resale net income (note that only in Year 10 is there resale income). 13. Complete the financial statement by calculating the yearly cash flow as follows: a. In Year 0 , the company will purchase the vation home. In cell B24, enter as a negative cash flow the cost of the home using the present value from cell 84 in the Home Info worksheet. b. To calculate the cash flow for each year, add the total net income in row 22 to the depreciation values in row 11 for Year 1 through Year 10. 14. In the Home Info worksheet, do the following to analyze the profitability of this investment: a. In cell 818 , enter 10.00% as the discount rate that Katherine wants to assume. b. In cell B19, calculate the net present value of the investment, and add the initial expenditure in Year 0 from cell B24 of the Financial Statements worksheet to the net present value of the net cash flow values in the range C24:24 of the Financial Statements worksheet. Use the assumed discount rate in cell B18. c. In cell B20, calculate the internal rate of retum from the investment using the cash flow values in the range B24:t24 of the Financial Statements worksheet

Answer & Explanation Solved by verified expert

See Answer