Please help with Excel as well as visible formulas.

CASE EXERCISE - Rental Property Suppose you have saved $25,000 for investment for your retirement. You have an opportunity to buy a rental property in good condition for $100,000. An experienced contractor estimates that the property will need $5,000 in paint and repairs to make it "move-in ready". Your bank has given you a pre-approval to finance the property at 4.25 percent annually on a 30-year mortgage with $2,000 in closing costs. Alternatively, you could pay 1 point (1 percent of the loan amount) and finance the property on a 15-year mortgage at 3.5 percent annually with $2,000 in closing costs. Both loans require a 20 percent down payment of the purchase price and have monthly payments. The closing costs and point can be rolled into the loan (added to the loan amount). A property management company and other research indicates that the occupancy rate for rental homes in this market is 95% and rents for similar homes range from $850 to $950 monthly. Residential rental properties have a 27.5 year depreciation schedule. Use straight line depreciation (It is actually a more complicated schedule, but straight line depreciation is a sufficient approximation for this exercise.). You can only depreciate the value of the building, not the land. The value of the land is estimated to be 20% of the total value. Even though you can depreciate your property for tax purposes, market value for well-maintained rental properties in this area have historically increased at a real rate of about 4 percent annually. Your research indicates that each year you should set aside about 2 percent of the total property value (including land) for maintenance costs. Property taxes cost about 1.5 percent of total value per year and property insurance is $850 per year. A property management firm charges one-month's rent annually to manage the property. Your marginal tax rate is 22%. Assume that maintenance costs, interest expense, property taxes, property management fees, and insurance can be deducted from your taxable income. Assume insurance and property management fees are paid initially and then at the end of each year with all of the other expenses listed. Further, assume rent can be increased annually by the real market value growth rate and that expenses increase annually by the real market value growth rate. If you do not buy this rental property, you would invest in a broad stock market index fund, which has had a real rate of historic yield of about 10 percent. Use a 30-year time frame for your investment (Assume you would sell the property at market value after 30 years.). Questions: 1. Should you buy this investment property? (Post an Excel file supporting your analysis) 2. Which financing option is most attractive? (include amortization tables for each option) 3. What are the key risks associated with this investment? Are there any other concerns you might have with this investment? 4. List any key assumptions used in your analysis (that are not listed in the instructions) Please post only one Excel file. Use your first tab to answer the four questions above and other tabs as needed to show your analysis. Hint: If you a going for an "A". your analysis should include the effect of taxes (Look at the discussion on operating cash flows). A minimal analysis ("C") would look at just the discounted ner cash flows ignoring depreciation and other tax-related issues CASE EXERCISE - Rental Property Suppose you have saved $25,000 for investment for your retirement. You have an opportunity to buy a rental property in good condition for $100,000. An experienced contractor estimates that the property will need $5,000 in paint and repairs to make it "move-in ready". Your bank has given you a pre-approval to finance the property at 4.25 percent annually on a 30-year mortgage with $2,000 in closing costs. Alternatively, you could pay 1 point (1 percent of the loan amount) and finance the property on a 15-year mortgage at 3.5 percent annually with $2,000 in closing costs. Both loans require a 20 percent down payment of the purchase price and have monthly payments. The closing costs and point can be rolled into the loan (added to the loan amount). A property management company and other research indicates that the occupancy rate for rental homes in this market is 95% and rents for similar homes range from $850 to $950 monthly. Residential rental properties have a 27.5 year depreciation schedule. Use straight line depreciation (It is actually a more complicated schedule, but straight line depreciation is a sufficient approximation for this exercise.). You can only depreciate the value of the building, not the land. The value of the land is estimated to be 20% of the total value. Even though you can depreciate your property for tax purposes, market value for well-maintained rental properties in this area have historically increased at a real rate of about 4 percent annually. Your research indicates that each year you should set aside about 2 percent of the total property value (including land) for maintenance costs. Property taxes cost about 1.5 percent of total value per year and property insurance is $850 per year. A property management firm charges one-month's rent annually to manage the property. Your marginal tax rate is 22%. Assume that maintenance costs, interest expense, property taxes, property management fees, and insurance can be deducted from your taxable income. Assume insurance and property management fees are paid initially and then at the end of each year with all of the other expenses listed. Further, assume rent can be increased annually by the real market value growth rate and that expenses increase annually by the real market value growth rate. If you do not buy this rental property, you would invest in a broad stock market index fund, which has had a real rate of historic yield of about 10 percent. Use a 30-year time frame for your investment (Assume you would sell the property at market value after 30 years.). Questions: 1. Should you buy this investment property? (Post an Excel file supporting your analysis) 2. Which financing option is most attractive? (include amortization tables for each option) 3. What are the key risks associated with this investment? Are there any other concerns you might have with this investment? 4. List any key assumptions used in your analysis (that are not listed in the instructions) Please post only one Excel file. Use your first tab to answer the four questions above and other tabs as needed to show your analysis. Hint: If you a going for an "A". your analysis should include the effect of taxes (Look at the discussion on operating cash flows). A minimal analysis ("C") would look at just the discounted ner cash flows ignoring depreciation and other tax-related issues