Please include an excel spreadsheet with all formulas. Thank you!

Question 1. We will begin by considering a fairly straightforward situation. Suppose you are are purchasing a new home and need to borrow $450,000 from a mortgage lender. The mortgage lender quotes you a rate of 3.80% APR for a 30-year fixed rate mortgage. (a.) Assuming that your payment will be due at the beginning of each month, calculate what your mortgage payments will be. (b.) Produce an amortization table which shows how much of your mortgage payment is allocated to interest service and how much is dedicated to paying down the principal of the loan each month. - Hint: We did this in class, but assumed payments happened at the end of the month. So you will have to modify what we did slighly. Just think carefully about how the timing of the cash flows changes. . (c.) Calculate the mortgage payment amount if the mortgage had its payments due at the end of the month. (d.) Compare the amounts you found in parts (a.) and (c.). Does it makes sense which one is higher? Briefly explain the underlying logic. Question 2. Suppose the mortgage lender from Question 1 also tells you that if you are willing to pay two points, they can offer you a lower rate of 3.65% APR for a 30-year fixed rate mortgage. One point is equal to 1.00% of the loan value. . (a.) What is the total amount you would have to pay (i.e. the two points) to get the lower interest rate? . (b.) Suppose you borrowed the amount necessary to get the points from the bank. Calculate what your new monthly payment would be. - Hint: Understand what is happening here...you are borrowing more money from the bank, but you are paying a lower interest rate on that money. (c.) Compare your answer to part (b.) to they payment you found in Question 1, part (a.). Is it worth it to take the points? Briefly explain why or why not. Question 3. Assume you go with the loan offered in Question 1. But, being the dilligent type, you decide you will over pay on the mortgage on each month. . (a.) Assuming that you decide to over pay on the mortgage by $200.00 a month, how many months will it take you to pay off the mortgage? . (b.) Using the =cumipmt() function in Excel, calculate the total amount of interest you would pay on the mortgage offered in Question 1. - Hint: See this link (here) for an example of how to use the =cumipmt() function * Annoyingly, pv must be entered as a positive number in this function, unlike how we typically do it...not great consistancy on Microsoft's part * Also you might find it helpful to report your answer as =-cumipmt() You must use the rcumipmt() function to get full credit, but you can use the amortization table in Question 1, part (b.) to check your answer. = (c.) Using the =cumipmt() function in Excel, calculate the total amount of interest you would pay if you over pay as you planned in part (a.). How much money do you save by overpaying? Question 1. We will begin by considering a fairly straightforward situation. Suppose you are are purchasing a new home and need to borrow $450,000 from a mortgage lender. The mortgage lender quotes you a rate of 3.80% APR for a 30-year fixed rate mortgage. (a.) Assuming that your payment will be due at the beginning of each month, calculate what your mortgage payments will be. (b.) Produce an amortization table which shows how much of your mortgage payment is allocated to interest service and how much is dedicated to paying down the principal of the loan each month. - Hint: We did this in class, but assumed payments happened at the end of the month. So you will have to modify what we did slighly. Just think carefully about how the timing of the cash flows changes. . (c.) Calculate the mortgage payment amount if the mortgage had its payments due at the end of the month. (d.) Compare the amounts you found in parts (a.) and (c.). Does it makes sense which one is higher? Briefly explain the underlying logic. Question 2. Suppose the mortgage lender from Question 1 also tells you that if you are willing to pay two points, they can offer you a lower rate of 3.65% APR for a 30-year fixed rate mortgage. One point is equal to 1.00% of the loan value. . (a.) What is the total amount you would have to pay (i.e. the two points) to get the lower interest rate? . (b.) Suppose you borrowed the amount necessary to get the points from the bank. Calculate what your new monthly payment would be. - Hint: Understand what is happening here...you are borrowing more money from the bank, but you are paying a lower interest rate on that money. (c.) Compare your answer to part (b.) to they payment you found in Question 1, part (a.). Is it worth it to take the points? Briefly explain why or why not. Question 3. Assume you go with the loan offered in Question 1. But, being the dilligent type, you decide you will over pay on the mortgage on each month. . (a.) Assuming that you decide to over pay on the mortgage by $200.00 a month, how many months will it take you to pay off the mortgage? . (b.) Using the =cumipmt() function in Excel, calculate the total amount of interest you would pay on the mortgage offered in Question 1. - Hint: See this link (here) for an example of how to use the =cumipmt() function * Annoyingly, pv must be entered as a positive number in this function, unlike how we typically do it...not great consistancy on Microsoft's part * Also you might find it helpful to report your answer as =-cumipmt() You must use the rcumipmt() function to get full credit, but you can use the amortization table in Question 1, part (b.) to check your answer. = (c.) Using the =cumipmt() function in Excel, calculate the total amount of interest you would pay if you over pay as you planned in part (a.). How much money do you save by overpaying