I need help to do question 4 to 7 and show how to find those steps in Excel. # New House Mortgage Calculator## Property Details- **Date:** 10/1/2021- **Property:** Beecher Street- **Price:** $350,000- **Down Payment:** $35,000- **Loan Amount:** $315,000## Loan Terms- **Rate:** 4.32%- **Term in Months:** 360- **Monthly Payment:** $1,562.55- **Total Interest:** $247,516.76- **Total Cost:** $597,517## Varying Interest Rates and Terms| Rate | P*(i/N)/(1-(1+i/N)^-T) | Number of Months ||--------|-----------------------|------------------|| | 180 | 240 | 360 || 4.00% | $2,330.02 | $1,908.84 | $1,503.86 || 4.08% | $2,342.67 | $1,922.14 | $1,518.42 || 4.16% | $2,355.35 | $1,935.50 | $1,533.06 || 4.24% | $2,368.08 | $1,948.91 | $1,547.77 || 4.32% | $2,380.85 | $1,962.37 | $1,562.55 || 4.40% | $2,393.65 | $1,975.88 | $1,577.40 || 4.48% | $2,406.51 | $1,989.45 | $1,592.32 || 4.56% | $2,419.39 | $2,003.06 | $1,607.30 || 4.64% | $2,432.32 | $2,016.73 | $1,622.36 || 4.72% | $2,445.29 | $2,030.45 | $1,637.48 || 4.80% **Instruction for Calculating Monthly Loan Payments Using Excel**4. **Objective: Calculate Monthly Payment for a House Loan** Miranda aims to determine the monthly payment needed to purchase a Beecher Street house using the most favorable interest rate found online. The calculation follows these steps: a. In cell D5, begin by entering a formula that utilizes the PMT function. b. For the rate argument within the formula, divide the Rate (cell D3) by 12 to convert it to a monthly interest rate. c. Use the Term_in_Months (cell D4) for the nper argument, specifying the total number of periods. d. For the pv (present value) argument, employ the Loan_Amt (cell B7). e. Ensure a negative sign (-) follows the equal sign in the formula to display the result positively.5. **Total Interest Calculation** In cell D6, input a formula that doesn't use a function to multiply the Monthly_Payment (cell D5) by the Term_in_Months (cell D4), then subtract the Loan_Amt (cell B7) to find the total mortgage interest.6. **Total Cost Calculation** Enter a formula in cell D7, without using a function, to add the Price (cell B5) with the Total_Interest (cell D6), determining the house's total cost.7. **Comparing Payment Plans** Miranda needs to compare monthly payments for interest rates ranging from 4.00 to 4.96 percent over terms of 180, 240, and 360 months. The base structure for a data table is prepared in the range A11:D24. To generate a two-variable data table for comparative purposes, follow these steps: a. In cell A11, input a formula that references the Monthly_Payment amount (cell D5) to allow comparison of these payments. b. From the range A11:D24, develop a two-variable data table, using the term in months (cell D4) as the row input and the rate (cell D3) as the column input.

To figure out how much a loan will cost, use the loan payment formula. The formula for calculating…

5. 6. 7. Miranda wants to calculate the monthly payment for a loan to purchase the Beecher Street house at the most favorable interest rate she found online. Calculate the payment as follows: a. b. C. d. e. In cell D5, start to enter a formula using the PMT function. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest rate. In cell D6, enter a formula without using a function that multiples the Monthly Payment (cell D5) by the Term_in_Months (cell D4), and then subtracts the Loan_Amt (cell B7) from the result to determine the total interest on the mortgage. For the nper argument, use the Term_in_Months (cell D4) to specify the number of periods. For the pv argument, use the Loan_Amt (cell B7) to include the present value. Insert a negative sign (-) after the equal sign in the formula to display the result as a positive amount. In cell D7, enter a formula without using a function that adds the Price (cell B5) to the Total Interest (cell D6) to determine the total cost of the house. Miranda wants to compare monthly payments for interest rates that vary from 4.00 to 4.96 percent and for terms of 180, 240, and 360 months. She has already set up the structure for a data table in the range A11: D24. a. Create a two-variable data table as follows to provide the comparison that Miranda requests: b. In A11, enter a formula without using a function that references the Monthly Payment amount (cell D5) because Miranda wants to compare the monthly payments. Based on the range A11:D24, create a two-variable data table that uses the term in months (cell D4) as the row input cell and the rate (cell D3) as the column input cell.

11th Edition

ISBN:9781260013924

Author:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Publisher:Bodie, Zvi, Kane, Alex, MARCUS, Alan J.

Chapter1: Investments: Background And Issues

Section: Chapter Questions

Problem 1PS