Please help me to solve this in Excel.

THE BUY VERSUS RENT DECISION In Jan 2020, Miss Anna moved to Dallas for a new job in an investment firm. There, she rented a spacious, two-bedroom house for $2,000 per month, which included parking but not utilities or cable television. In July 2020, the virtually identical unit next door became available for sale with an asking price of $620,000, and Anna believed she could purchase it for $600,000. She realized she was facing the classic buy-versus-rent decision. It was time for her to apply some of the analytical tools like "time value of money concepts she had acquired in business school to her personal life. While she really liked the house unit she was renting, as well as the house building itself, she felt that it would be inadequate for her long-term needs, as she planned to move to a house within 6 to 12 years - even sooner if her job continued to work out well. She consulted her friends and family and a variety of mixed opinions concerning the best move (buy-versus-rent), ranging from "your money on rent will go waste" to "renting is the best option until you are ready to settle in for good." She realized that both sides presented good arguments, but she wanted to analyze the buy-versus-rent decision from a quantitative point of view. FINANCIAL DETAILS If Anna purchased the new house, she would pay monthly HOA fees of S1,055 per month, plus property taxes of $300 per month on the unit. Unlike when renting, she would also be responsible for repairs and general maintenance, which she estimated would average $600 per year. If she decided to purchase the new unit, Anna intended to provide a cash down payment of 20 per cent of the purchase price. There was also a local deed-transfer tax of approximately 1.5 per cent of the purchase price, and a provincial deed-transfer tax of 1.5 per cent, both due on the purchase date. (For simplicity, ignore any other tax considerations throughout her analysis.). Other closing fees were estimated to be around $2,000. In order to finance the remaining 80 per cent of the purchase price, Anna contacted several lenders and found that she would be able to obtain a mortgage at a 3 per cent "quoted" annual rate that would be locked in for the initial 1-year term and would then be pegged to the LIBOR at LIBOR +1.5 per cent. Also she would amortize the mortgage over 25 years, with monthly payments. The money that Anna was planning to use for her down payment and closing costs was presently invested and was earning the same rate of return as she would be paying on her mortgage. Anna assumed that if she were to sell the house say, in the next two to three years - she would pay 5 per cent of the selling price to realtor foes plus $2,000 in other closing fees. SCENARIO ANALYSIS In order to complete a financial analysis of the buy-versus-rent decision, Anna realized that her first task would be to determine the required monthly mortgage payments. Next, she wanted to determine the opportunity cost (on a monthly basis) of using the lump-sum required funds for the house purchase rather than leaving those funds invested and earning the rate of return, assumed to be equivalent to the mortgage rate. She would then be able to determine additional monthly payments required to buy the house compared to renting, including the opportunity cost. Anna wanted to consider what might happen if she chose to sell the house at a future date. She was confident that any re-sell would not happen for at least a year, but it could certainly happen in 2 or 3-years" time. She needed to model the amount of the outstanding principal at various points in the future two or three years from now. She then wanted to determine the net future gain or loss after two, and three years under the following scenarios, which she had determined were possible after some due diligence regarding future real-estate prices in the Dallas housing market: (a) The house price remains unchanged; (b) The house price drops 10 per cent over the next 2 years, then increases back to its purchase price by the end of three years (c) The house price increases annually by the annual rate of inflation of 2 per cent per year over the next 10 years, FINAL CONSIDERATIONS Anna realized she had a tough decision ahead of her, but she was well trained to make these types of decisions. She also recognized that her decision would not be based on quantitative factors alone, it would need to be based on any qualitative considerations as well. She knew she needed to act soon because houses were selling fairly quickly, and she would need to arrange financing and contact a lawyer to assist in any paperwork if she decided to buy. Case Analysis - Questions 1. (2 points) Determine the required monthly payments for the mortgage 2. (2 points) Determine the principal outstanding on the mortgage after: a. 2 years b. 3 years 3. (2 points) Determine the opportunity costs of using the funds for closing (i.e. down payment plus all closing costs) rather than leaving those funds invested and earning the rate of return discussed in the case. 4. (2 points) Determine the monthly additional payments required to buy versus rent (include the monthly opportunity costs) 5.(2 points) What qualitative factors should be considered? 6. Determine the "net" future gain or loss assuming a three year horizon and three scenarios (a,b,c) provided in the case study? - Bonus 5 points THE BUY VERSUS RENT DECISION In Jan 2020, Miss Anna moved to Dallas for a new job in an investment firm. There, she rented a spacious, two-bedroom house for $2,000 per month, which included parking but not utilities or cable television. In July 2020, the virtually identical unit next door became available for sale with an asking price of $620,000, and Anna believed she could purchase it for $600,000. She realized she was facing the classic buy-versus-rent decision. It was time for her to apply some of the analytical tools like "time value of money concepts she had acquired in business school to her personal life. While she really liked the house unit she was renting, as well as the house building itself, she felt that it would be inadequate for her long-term needs, as she planned to move to a house within 6 to 12 years - even sooner if her job continued to work out well. She consulted her friends and family and a variety of mixed opinions concerning the best move (buy-versus-rent), ranging from "your money on rent will go waste" to "renting is the best option until you are ready to settle in for good." She realized that both sides presented good arguments, but she wanted to analyze the buy-versus-rent decision from a quantitative point of view. FINANCIAL DETAILS If Anna purchased the new house, she would pay monthly HOA fees of S1,055 per month, plus property taxes of $300 per month on the unit. Unlike when renting, she would also be responsible for repairs and general maintenance, which she estimated would average $600 per year. If she decided to purchase the new unit, Anna intended to provide a cash down payment of 20 per cent of the purchase price. There was also a local deed-transfer tax of approximately 1.5 per cent of the purchase price, and a provincial deed-transfer tax of 1.5 per cent, both due on the purchase date. (For simplicity, ignore any other tax considerations throughout her analysis.). Other closing fees were estimated to be around $2,000. In order to finance the remaining 80 per cent of the purchase price, Anna contacted several lenders and found that she would be able to obtain a mortgage at a 3 per cent "quoted" annual rate that would be locked in for the initial 1-year term and would then be pegged to the LIBOR at LIBOR +1.5 per cent. Also she would amortize the mortgage over 25 years, with monthly payments. The money that Anna was planning to use for her down payment and closing costs was presently invested and was earning the same rate of return as she would be paying on her mortgage. Anna assumed that if she were to sell the house say, in the next two to three years - she would pay 5 per cent of the selling price to realtor foes plus $2,000 in other closing fees. SCENARIO ANALYSIS In order to complete a financial analysis of the buy-versus-rent decision, Anna realized that her first task would be to determine the required monthly mortgage payments. Next, she wanted to determine the opportunity cost (on a monthly basis) of using the lump-sum required funds for the house purchase rather than leaving those funds invested and earning the rate of return, assumed to be equivalent to the mortgage rate. She would then be able to determine additional monthly payments required to buy the house compared to renting, including the opportunity cost. Anna wanted to consider what might happen if she chose to sell the house at a future date. She was confident that any re-sell would not happen for at least a year, but it could certainly happen in 2 or 3-years" time. She needed to model the amount of the outstanding principal at various points in the future two or three years from now. She then wanted to determine the net future gain or loss after two, and three years under the following scenarios, which she had determined were possible after some due diligence regarding future real-estate prices in the Dallas housing market: (a) The house price remains unchanged; (b) The house price drops 10 per cent over the next 2 years, then increases back to its purchase price by the end of three years (c) The house price increases annually by the annual rate of inflation of 2 per cent per year over the next 10 years, FINAL CONSIDERATIONS Anna realized she had a tough decision ahead of her, but she was well trained to make these types of decisions. She also recognized that her decision would not be based on quantitative factors alone, it would need to be based on any qualitative considerations as well. She knew she needed to act soon because houses were selling fairly quickly, and she would need to arrange financing and contact a lawyer to assist in any paperwork if she decided to buy. Case Analysis - Questions 1. (2 points) Determine the required monthly payments for the mortgage 2. (2 points) Determine the principal outstanding on the mortgage after: a. 2 years b. 3 years 3. (2 points) Determine the opportunity costs of using the funds for closing (i.e. down payment plus all closing costs) rather than leaving those funds invested and earning the rate of return discussed in the case. 4. (2 points) Determine the monthly additional payments required to buy versus rent (include the monthly opportunity costs) 5.(2 points) What qualitative factors should be considered? 6. Determine the "net" future gain or loss assuming a three year horizon and three scenarios (a,b,c) provided in the case study? - Bonus 5 points