Shelly Cashman Excel 2019 | Module 4: SAM Project 1b #shellycashmanexcel #module4 #samproject1b

Shelly Cashman Excel 2019 | Module 4: SAM Project 1b #shellycashmanexcel #module4 #samproject1b If you directly want to get the project from us then contact us on our Whatsapp. Link is given here, Whatsapp Contact Link: https://api.whatsapp.com/message/4B6NMKKBKUFYN1?autoload=1&app_absent=0 All Projects Link: https://whatsapp.com/channel/0029VaBCB3P1t90m1bdQfM18 Whatsapp Number: +919116641093 +918005564456 Gmail Id: singhal.agrawal.bharati@gmail.com 1) Ricardo and Miranda Ramos are considering whether to buy their first home, and have spoken to three lenders about taking out a mortgage for the house purchase. They now pay $1,500 per month in rent and can pay up to $1,600 per month for a mortgage. Miranda has created an Excel spreadsheet to compare the terms of the mortgage. She asks you to help her complete the analysis of their loan options. Go to the Mortgage Calculator worksheet. The cells in the range B5:B7 have defined names, but one is incomplete and could be confusing. Cell A2 also has a defined name, which is unnecessary for a cell that will not be used in a formula. Update the defined names in the worksheet as follows: a. Delete the Loan_Payment_Calculator defined name. b. For cell B7, edit the defined name to use Loan_Amt as the name. [Mac Hint: Delete the existing defined name "Loan_Am" and add the new defined name.] 2) In cell B7, calculate the loan amount by entering a formula without using a function that subtracts the Down_Payment from the Price. 3) Miranda also wants to use defined names in other calculations to help her interpret the formulas. In the range D3:D7, create defined names based on the values in the range C3:C7. 4) 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. In cell D5, start to enter a formula using the PMT function. b. For the rate argument, divide the Rate (cell D3) by 12 to use the monthly interest rate. c. For the nper argument, use the Term_in_Months (cell D4) to specify the number of periods. d. For the pv argument, use the Loan_Amt (cell B7) to include the present value. e. Insert a negative sign (-) after the equal sign in the formula to display the result as a pos 5) 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. 6) 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. 7) 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. Create a two-variable data table as follows to provide the comparison that Miranda requests: a. In cell A11, enter a formula without using a function that references the Monthly_Payment amount (cell D5) because Miranda wants to compare the monthly payments. b. 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. 8) In the list of interest rates (range A12:A24), create a Conditional Formatting Highlight Cells Rule to highlight the listed rate that matches the rate for the Beecher Street house (cell D3) in Light Red Fill with Dark Red Text. 9) Change the color of the left, right, and bottom borders of the range A9:D24 to Tan, Accent 4, to match the other outside borders in the worksheet. 10) Miranda and Ricardo talked to three local banks about securing a mortgage for the Beecher Street house. With the first bank, they could borrow $315,000 at 4.40 percent annual interest and pay back the loan in 30 years. She wants to determine the monthly payment with the first bank. In cell G9, enter a formula using the PMT function and the monthly interest rate (cell G5), the loan period in months (cell G7), and the loan amount (cell G3) to calculate the monthly payment with Bank 1. 11) With the second bank, Miranda and Ricardo could reduce their monthly payments to $1,500 but pay a higher annual interest rate (4.56 percent) to pay back the loan in 30 years. Miranda wants to know how much she can borrow with those conditions. In cell H3, insert a formula using the PV function and the monthly interest rate (cell H5), the loan period in months (cell H7), and the monthly payment (cell H9) to calculate the loan amount for Bank 2. 12) The third bank will allow Miranda and Ricardo to borrow $337,500 at an annual interest rate of 4.56 percent and a monthly payment of $1,500. Miranda wants to know the amount remaining on the loan after 20 years, or the future value of the loan, so that she and Ricardo can start to pay it off more quickly at that point.

Видео Shelly Cashman Excel 2019 | Module 4: SAM Project 1b #shellycashmanexcel #module4 #samproject1b автора Учимся создавать шаблоны в Excel