Please show setp by step solution in excel formulae

Cirque du Soleil(CDS) is a Canadian entertainment company and the largest contemporary circus producer in the world. Located in the inner-city area of Saint-Michel, it was founded in Baie-Saint-Paul on 16 June 1984 by former street performers Guy Lalibert and Gilles Ste-Croix. In 2015 , CDS bought a practice facility for $80,000 in Chicago at a 15% interest for 30 years (monthly payment). After five years, or in 2020, interest rates fall, and a new mortgage loan is available at 14 percent for 25 years. Suppose that the prepayment penalty of 2 percent must be paid on the existing loan, and the lender who is making the new loan available also requires an origination fee of $2,500 plus $25 for incidental closing costs if the new loan is made. 2020 is a tough year for CDS. During Covid, they have had a lot of canceled shows and ended up losing a lot of money. Stephane Lefebvre, CEO of CDS, wants to cut costs and wants to know if they should refinance the mortgage for their Chicago facility and take advantage of lower interest rates. To start thinking about refinance, CDS needs to know what is the current monthly payment on the existing loan. Can you help them determine the monthly payment? Hint: $80,000,15%,30 years \begin{tabular}{l} \hline$1,011.56 \\ \hline$1,111.56 \\ \hline$1,211.56 \\ \hline$1,311.56 \\ \hline \end{tabular} Question 2 1 pts In order to get the new monthly payment, CDS needs to know how much loan balance is available through the existing loan. Use the remaining mortgage balance method discussed in class, can you calculate the loan balance in 2020 ? Hint: It is 5 years after the old loan's origination. $78,976.50 $77,976.50 $76,976.50 $75,976.50 Benefits from refinancing are obviously the interest savings that result from a lower interest rate. Hence, if refinancing occurs, the monthly mortgage payment under the new loan terms will be lower than payments under the existing mortgage. Can you help CDS figure out the new monthly payment? Hint: Use current remaining balance, 14\%, 25 years. \begin{tabular}{l} \hline$950.69 \\ \hline$940.69 \\ \hline$930.69 \\ \hline 920.69 \end{tabular} Question 4 1pts What is monthly saving due to refinancing? $60.87 Now, let's help CDS figure out the total costs associated with refinancing. Hint: prepayment penalty + origination fees and discount points + recording for incidental closing cost $4,105 $4,205 $4,305 $4,405 Question 6 1 pts Let's do a simple comparison. It is useful to know what is the total cash savings for the 25 years (300 month) period would be. What is total saving? $18,260.05 $17,260.05 $16,260.05 $15,260.05 What is the actual rate of return for refinancing that CDS can compare to other investment opportunities? Hint: use Rate (PV, nper, PMT, FV) When you have the rate, make sure to 12 to get the annual rate. \begin{tabular}{l} \hline 17.57% \\ \hline 16.57% \\ \hline 15.57% \\ \hline 14.57% \end{tabular} Now, let's discuss one important factor, (which you may already know)- holding period. If the property is not held for the full 25 years, the monthly savings do not occur for the entire 25year term, and therefore the refinancing may not be as attractive. If CDS plans to hold the property for only 10 more years after refinancing, is refinancing still worthwhile? To analyze this alternative, note that the refinancing cost will not change, should the refinancing be undertaken; however, the benefits (savings) will change. The monthly benefits will be realized for only 10 years. What else does CDS need to consider? Since CDS expects to repay the refinanced loan after 10 years, there will be a difference between loan balances on the existing loan and the new loan due to different amortization rates. We assume that there will be no prepayment penalty on either loan if they are prepaid 10 years from now. Let's first help CDS calculate the remaining balance 10 years from 2020, based on the old loan structure. $72,275 $73,275 $74,275 $75,275 What is the loan balance on the new loan after 10 years, if CDS decides to refinance? $71,386 $73,386 $72,386 $70,386 Question 10 1 pts Now, let's use this new calculation comparing loan balances under the existing loan and the new loan terms. Should the new loan be made, if refinancing occurs, then what is the amount saved with the lower loan balance, after 10 years hold period? $889 $789 $989 $689 Hence, total savings with refinancing will be (1) the monthly saving for 10 years, plus (2) the saving at the end of 10 years. Do these savings justify an outlay of about $4K in refinancing costs? To answer this question, CDS needs to calculate the rate of return. What is it? 14.21% 15.21% 16.21% 17.21%