I need help questions 4-7.
For number 4, it is telling me to use the formula containing the PMT function which I did with: =-PMT(D4/12,D5,Loan_Am) but it didn't work.
The others, 5-7, I am unsure of how to do it.
Shelly Cashman Excel 2019 | Module 4: SAM Project la 4. a. c. e. 5. Liam needs to calculate the monthly payment for a loan to purchase the Charles Street property. Calculate the payment as follows: In cell D6, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate (cell D4) by 12 to use the monthly interest rate. For the nper argument, use the Term_in_Months (cell D5) to specify the number of periods. d. For the pv argument, use the Loan_Amount (cell B8) 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 multiples the Monthly_Payment (cell D6) by the Term_in_Months (cell D5) and then subtracts the Loan_Amount (cell B8) from the result to determine the total interest. In cell D8, enter a formula without using a function that adds the Price (cell B6) to the Total_Interest (cell D7) to determine the total cost. Liam wants to compare monthly payments for interest rates that vary from 3.95% to 5.15% and for terms of 120, 180, and 240 months. He has already set up the structure for a data table in the range A12:D25. Create a two-variable data table as follows to provide the comparison that Liam requests: In cell A12, enter a formula without using a function that references the monthly payment amount (cell D6) because Liam wants to compare the monthly payments. Based on the range A12:D25, create a two-variable data table that uses the term in months (cell D5) as the row input cell and the rate (cell D4) as the column input cell. 6. 7. a. b. E10 for D E F G H 1 J 4.45% 180 Renovation 20 Years $ 1,020,000 $ 968,938 $ 4.45% 4.25% Scenarios Loan Amount Annual Interest Rate Monthly Interest Rate Loan Period in Years Loan Period in Months Start Date Monthly Payment Future Value 8 Years 920,000 4.45% 0.37% 8 0.35% 20 0.37% 15 180 1/3/2022 (7,777) $ n/a $ 240 96 1/3/2022 1/3/2022 (6,000) S (8,000) n/a ($392,082.42) B C 1 New Office Building 2 Loan Calculator 3 Loan Payment Calculator 4 Date 9/15/2021 Rate 5 Property Charles Street Term in Months 6 Price $ 1,150,000 Monthly Payment 7 Down Payment $ 230,000 Total Interest 8 Loan Amount $ 920,000 Total Cost 9 10 Varying Interest Rates and Terms 11 Rate Number of Months 12 S 7,014 120 180 13 3.95% $ 9,292.71 $ 6,782.10 $ 14 4.05% $ 9,336.43 $ 6,828.20 $ 15 4.15% $ S 9,380.28 $ 6,874.49 $ 16 4.25% $ 9,424.25 $ 6,920.96 $ 17 4.35% $ 9,468.35 $ 6,967.62 $ 18 4.45% $ 9,512.58 $ 7,014.45 $ 19 4.55% $ 9,556.92 $ 7,061.47 $ 20 4.65% $ 9,601.40 $ 7,108.67 $ 21 4.75% $ 9,645.99 $ 7,156.05 $ 22 4.85% $ 9,690.71 $ 7,203.62 $ 23 4.95% $ 9,735.56 $ 7,251.36 $ 24 5.05% $ 9,780.53 $ 7,299.29 $ 25 5.15% $ 9,825.62 $ 7,347.39 $ 26 240 5,550.81 5,599.29 5,648.00 5,696.96 5,746.15 5,795.57 5,845.23 5,895.13 5,945.26 5,995.62 6,046.21 6,097.03 6,148.09
