This is an Economic Analysis Excel Project. Please answer the questions with the given info and add a written explanation of what you did on excel with each problem. So do this in excel and answer here with what you did for each problem. Will Give Thumbs Up!
Info: House Price = $634,900
Housing Interest Rate = 3.19% 30 Year
2. For this project you will have to complete an economic analysis of mortgage loan of your dream home. Assume that a local bank has agreed to finance the purchase of your house. The bank offers lour financing options: Option 2: Financing for 25 years at a fixed-rate mortgage with a 20% down payment. Option 3: Financing for 20 years at a fixed-rate mortgage with a 30% down payment. Note: The interest rates you get will depend on your credit score but, for simplicity, use the average housing interest rate of the state your house is located. 4. For each of the selected options: (e) After the first 3 years of payments, what is the outstanding balance? (1) How long does it take to payoff the mortgage if you double the amount of the 12th payment at the end of each year? (g) How long does it take to payoff the mortgage if your monthly payments are 20% more than the base monthly payments determined in part a? (h) How long does it take to payoff the mortgage if starting from the second month you add an extra $20 to the amount paid in the previous month (i.e., in the first month you pay the base amount determined in part a, in the second month you pay the base amount plus $20, in the third month you pay the base amount plus $40, and so forth)? 1 Hint: For this you need to calculate the outstanding balance of each period (i.e., each month) and try to figure out how long it takes for the outstanding balance value to become negative (i.e., until the total amount including interest is paid). (i) Continuing to assume monthly compounding, what is the payment amount if you pay in 6 payment periods per year (i.e., every two months)? Hint: Check lecture 6 where we discussed that the period of compounding does not align with the frequency of the CFs. In effect, you have to calculate the effective interest rate per CF period and use it in your Excel calculations. (j) Assuming an adjustable payment method, what is the total amount paid over the life of the loan if the interest rate for the first five years is 4%, the second five years is 3.75% and for the remaining loan period (if exists) is 3.5%? Hint: When you are initially assigned an interest rate, the basic assumption is that the interest rate will be applicable for the entire loan period. After some time, if the interest rate changes, it will result in a modified monthly payment based on your outstanding balance at that specific time period. Hence, you should recalculate the monthly payment each time the interest rate changes. 5. Use a separate tab in excel for each of the financing options. Include two separate tabs in your excel file (one for each option), rename the sheet names according to the problem you are working on. For example: if you are assigned option 1, instead of "Sheetl" rename it to "Optionl. 6. In addition to including the listing details, the project report should mention which of your assigned loan options you would select and include a brief explanation. 2. For this project you will have to complete an economic analysis of mortgage loan of your dream home. Assume that a local bank has agreed to finance the purchase of your house. The bank offers lour financing options: Option 2: Financing for 25 years at a fixed-rate mortgage with a 20% down payment. Option 3: Financing for 20 years at a fixed-rate mortgage with a 30% down payment. Note: The interest rates you get will depend on your credit score but, for simplicity, use the average housing interest rate of the state your house is located. 4. For each of the selected options: (e) After the first 3 years of payments, what is the outstanding balance? (1) How long does it take to payoff the mortgage if you double the amount of the 12th payment at the end of each year? (g) How long does it take to payoff the mortgage if your monthly payments are 20% more than the base monthly payments determined in part a? (h) How long does it take to payoff the mortgage if starting from the second month you add an extra $20 to the amount paid in the previous month (i.e., in the first month you pay the base amount determined in part a, in the second month you pay the base amount plus $20, in the third month you pay the base amount plus $40, and so forth)? 1 Hint: For this you need to calculate the outstanding balance of each period (i.e., each month) and try to figure out how long it takes for the outstanding balance value to become negative (i.e., until the total amount including interest is paid). (i) Continuing to assume monthly compounding, what is the payment amount if you pay in 6 payment periods per year (i.e., every two months)? Hint: Check lecture 6 where we discussed that the period of compounding does not align with the frequency of the CFs. In effect, you have to calculate the effective interest rate per CF period and use it in your Excel calculations. (j) Assuming an adjustable payment method, what is the total amount paid over the life of the loan if the interest rate for the first five years is 4%, the second five years is 3.75% and for the remaining loan period (if exists) is 3.5%? Hint: When you are initially assigned an interest rate, the basic assumption is that the interest rate will be applicable for the entire loan period. After some time, if the interest rate changes, it will result in a modified monthly payment based on your outstanding balance at that specific time period. Hence, you should recalculate the monthly payment each time the interest rate changes. 5. Use a separate tab in excel for each of the financing options. Include two separate tabs in your excel file (one for each option), rename the sheet names according to the problem you are working on. For example: if you are assigned option 1, instead of "Sheetl" rename it to "Optionl. 6. In addition to including the listing details, the project report should mention which of your assigned loan options you would select and include a brief explanation
