As a financial consultant, you work with people who are planning to buy a new house....
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
As a financial consultant, you work with people who are planning to buy a new house. You want to create a worksheet containing variable data (the price of the house, down payment, date of the first payment, and borrower's credit rating) and constants (property tax rate, years, and number of pay- ments in one year). Borrowers pay 0.5% private mortgage insurance (PMI) on the loan amount if they do not make at least a 20% down payment. A borrower's credit rating determines the required down payment percentage and APR. For example, a person with an excellent credit rating may make only a 5% down payment with a 3.25% APR loan. A person with a fair credit rating will make a 15% down payment and have a higher APR at 5.25%. Your worksheet needs to perform various calculations. The filled cells in column F indicate cells containing formulas, not values. Refer to Figure 2.43 as you complete this exercise. 1 2 3 Inputs 4 Negotiated Cost of House 5 Additional Down Payment 6 Date of First Payment 7 Credit Rating 8 9 Constants 10 Property Tax Rate 11 Down Payment to Avoid PMI A 12 PMI Rate 13 Term of Loan in Years. 14 # of Payments Per Year B 15 16 Credit Down Payment 17 Excellent 5% 18 Good 10% 19 Fair 15% 20 Poor 20% 21 . . $375,000.00 5,000.00 $ . Mortgage Calculator . 5/1/2016 Excellent 0.75% 20.00% 0.50% 30 12 D APR 3.25% 3.50% 4.25% 5.25% E Intermediate Calculations APR Based on Credit Rating Min Down Payment Required Annual Property Tax Annual PMI Outputs Total Down Payment Amount of the Loan Monthly Payment (P&I) Monthly Property Tax Monthly PMI Total Monthly Payment Date of Last Payment a. Start a new Excel workbook, save it as e02m2Loan_LastFirst, rename Sheet1 Payment, add a new sheet, and then rename it Range Names. b. Select the Payment sheet, type Mortgage Calculator in cell A1, and then merge and center the title on the first row in the range A1:F1. Apply bold, 18 pt size, and Gold, Accent 4, Darker 25% font color. 3.25% $ 18,750.00 $ 2,812.50 $ 1,756.25 c. Create and format the Inputs and Constants areas by doing the following: Type the labels in the range A3:A20. For each label, such as Negotiated Cost of House, merge the cells, such as the range A4:B4, and apply Align Text Left. You will have to merge cells for nine $ 23,750.00 $351,250.00 $1,528.66 234.38 146.35 $ 1,909.39 4/1/2046 labels. Enter and format the Inputs and Constants values in column C. Create the lookup table in the range A16:C20 to use the credit ratings to identify the appropriate required percentage down payment and the respective APR by doing the following: . Type Credit, Down Payment, and APR in the range A16:C16. Type the four credit ratings in the first column, the required down payment percentages in the second column, and the respective APRS in the third column. Format the percentages, apply Align Text Right, and then indent the percentages in the cells as needed. e. Assign range names to cells containing individual values in the Inputs and Constants sections. Do not use the Create from Selection feature because the labels are stored in merged cells. Assign a range name to the lookup table. f. Type labels in the Intermediate Calculations and Outputs sections in column E and assign a range name to each cell in the ranges F4:F7 and F10:F12. Widen column E as needed. g. Enter formulas in the Intermediate Calculations and Outputs sections using range names to calcu- late the following: . . Annual property tax based on the negotiated cost of the house and the annual property tax rate. Annual PMI. If the borrower's total down payment (required and additional) is 20% or higher of the negotiated purchase price (multiply the cost by the PMI avoidance percentage), PMI is zero. If the total down payment is less than 20%, the borrower has to pay PMI based on multi- plying the amount of the loan by the PMI rate. Total down payment, which is sum of the required minimum down payment (calculated previ- ously) and any additional down payment entered in the Inputs section. . Amount of the loan, which is the difference between the negotiated cost of the house and the total down payment. Monthly payment of principal and interest using the PMT function. Monthly property tax, the monthly PMI, and the total monthly payment. • APR based on the borrower's credit rating by using a lookup function. Include the range_lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating gets a 3.25% APR. . Minimum down payment required amount by using a lookup function and calculation. Include the range_lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating is required to pay a minimum of 5% down payment of the negotiated purchase price. Multiply the function results by the negotiated cost of the house. Hint: The cal- culation comes after the closing parenthesis. . Last payment date using the EDATE function. The function's second argument must calculate the correct number of months based on the total length of the loan. For example, if the first pay- ment date is 5/1/2016, the final payment date is 4/1/2046 for a 30-year loan. The last argument of the function must subtract 1 to ensure the last payment date is correct. If the last payment date calculated to 5/1/2046, you would be making an extra payment. h. Format each section with fill color, bold, underline, number formats, borders, and column widths as shown in the figure. i. Paste a list of range names in the Range Names worksheet. Insert a row above the list and type and format column labels above the two columns in the list of range names. . • j. Center the worksheet data horizontally between the left and right margins. k. Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of both sheets. 1. Save and close the workbook, and submit based on your instructor's directions. As a financial consultant, you work with people who are planning to buy a new house. You want to create a worksheet containing variable data (the price of the house, down payment, date of the first payment, and borrower's credit rating) and constants (property tax rate, years, and number of pay- ments in one year). Borrowers pay 0.5% private mortgage insurance (PMI) on the loan amount if they do not make at least a 20% down payment. A borrower's credit rating determines the required down payment percentage and APR. For example, a person with an excellent credit rating may make only a 5% down payment with a 3.25% APR loan. A person with a fair credit rating will make a 15% down payment and have a higher APR at 5.25%. Your worksheet needs to perform various calculations. The filled cells in column F indicate cells containing formulas, not values. Refer to Figure 2.43 as you complete this exercise. 1 2 3 Inputs 4 Negotiated Cost of House 5 Additional Down Payment 6 Date of First Payment 7 Credit Rating 8 9 Constants 10 Property Tax Rate 11 Down Payment to Avoid PMI A 12 PMI Rate 13 Term of Loan in Years. 14 # of Payments Per Year B 15 16 Credit Down Payment 17 Excellent 5% 18 Good 10% 19 Fair 15% 20 Poor 20% 21 . . $375,000.00 5,000.00 $ . Mortgage Calculator . 5/1/2016 Excellent 0.75% 20.00% 0.50% 30 12 D APR 3.25% 3.50% 4.25% 5.25% E Intermediate Calculations APR Based on Credit Rating Min Down Payment Required Annual Property Tax Annual PMI Outputs Total Down Payment Amount of the Loan Monthly Payment (P&I) Monthly Property Tax Monthly PMI Total Monthly Payment Date of Last Payment a. Start a new Excel workbook, save it as e02m2Loan_LastFirst, rename Sheet1 Payment, add a new sheet, and then rename it Range Names. b. Select the Payment sheet, type Mortgage Calculator in cell A1, and then merge and center the title on the first row in the range A1:F1. Apply bold, 18 pt size, and Gold, Accent 4, Darker 25% font color. 3.25% $ 18,750.00 $ 2,812.50 $ 1,756.25 c. Create and format the Inputs and Constants areas by doing the following: Type the labels in the range A3:A20. For each label, such as Negotiated Cost of House, merge the cells, such as the range A4:B4, and apply Align Text Left. You will have to merge cells for nine $ 23,750.00 $351,250.00 $1,528.66 234.38 146.35 $ 1,909.39 4/1/2046 labels. Enter and format the Inputs and Constants values in column C. Create the lookup table in the range A16:C20 to use the credit ratings to identify the appropriate required percentage down payment and the respective APR by doing the following: . Type Credit, Down Payment, and APR in the range A16:C16. Type the four credit ratings in the first column, the required down payment percentages in the second column, and the respective APRS in the third column. Format the percentages, apply Align Text Right, and then indent the percentages in the cells as needed. e. Assign range names to cells containing individual values in the Inputs and Constants sections. Do not use the Create from Selection feature because the labels are stored in merged cells. Assign a range name to the lookup table. f. Type labels in the Intermediate Calculations and Outputs sections in column E and assign a range name to each cell in the ranges F4:F7 and F10:F12. Widen column E as needed. g. Enter formulas in the Intermediate Calculations and Outputs sections using range names to calcu- late the following: . . Annual property tax based on the negotiated cost of the house and the annual property tax rate. Annual PMI. If the borrower's total down payment (required and additional) is 20% or higher of the negotiated purchase price (multiply the cost by the PMI avoidance percentage), PMI is zero. If the total down payment is less than 20%, the borrower has to pay PMI based on multi- plying the amount of the loan by the PMI rate. Total down payment, which is sum of the required minimum down payment (calculated previ- ously) and any additional down payment entered in the Inputs section. . Amount of the loan, which is the difference between the negotiated cost of the house and the total down payment. Monthly payment of principal and interest using the PMT function. Monthly property tax, the monthly PMI, and the total monthly payment. • APR based on the borrower's credit rating by using a lookup function. Include the range_lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating gets a 3.25% APR. . Minimum down payment required amount by using a lookup function and calculation. Include the range_lookup argument to ensure an exact match. For example, a borrower who has an Excellent rating is required to pay a minimum of 5% down payment of the negotiated purchase price. Multiply the function results by the negotiated cost of the house. Hint: The cal- culation comes after the closing parenthesis. . Last payment date using the EDATE function. The function's second argument must calculate the correct number of months based on the total length of the loan. For example, if the first pay- ment date is 5/1/2016, the final payment date is 4/1/2046 for a 30-year loan. The last argument of the function must subtract 1 to ensure the last payment date is correct. If the last payment date calculated to 5/1/2046, you would be making an extra payment. h. Format each section with fill color, bold, underline, number formats, borders, and column widths as shown in the figure. i. Paste a list of range names in the Range Names worksheet. Insert a row above the list and type and format column labels above the two columns in the list of range names. . • j. Center the worksheet data horizontally between the left and right margins. k. Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of both sheets. 1. Save and close the workbook, and submit based on your instructor's directions.
Expert Answer:
Answer rating: 100% (QA)
the following are the steps to assign range names to cells containing individual values in the Inputs and Constants sections type labels in the Intermediate Calculations and Outputs sections in column ... View the full answer
Related Book For
Financial and Managerial Accounting the basis for business decisions
ISBN: 978-1259692406
18th edition
Authors: Jan Williams, Susan Haka, Mark Bettner, Joseph Carcello
Posted Date:
Students also viewed these accounting questions
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
Managing Scope Changes Case Study Scope changes on a project can occur regardless of how well the project is planned or executed. Scope changes can be the result of something that was omitted during...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Suppose that one worker can shovel snow from a storefront sidewalk in 50 minutes and another worker can shovel it in 30 minutes. How long will it take if they work together?
-
Burglar Bob breaks into Vince Victims house. Bob steals a flat-screen TV and laptop and does a significant amount of damage to the property before he leaves. Fortunately, Vince has a state-of-the-art...
-
Distinguish between an alluvial fan and a moraine.
-
Explain the difference between Inverse ETFs and Leverage ETFs.
-
Derive cost of goods sold in data in the statement of cash flows The section showing cash flow from operations, using the indirect method, for Ann Taylor Stores reported an increase in inventories of...
-
A project is expected to generate annual revenues of $129,700, with variable costs of $79,300, and fixed costs of $19,800. The annual depreciation is $4,600 and the tax rate is 21 percent. What is...
-
Janice Morgan, age 24, is single and has no dependents. She is a freelance writer. In January 2021, Janice opened her own office located at 2751 Waldham Road, Pleasant Hill, NM 88135. She called her...
-
4. Recall the SIR infectious disease model d.S dt -aSI, dI dt dR B1 dt satisfied by a community with a fixed total population S + I + R consisting of sus- ceptibles (S), infectives (I), and...
-
What companies do Coca-Cola describe as significant equity method investments? How do these investments help Coca-Cola? What criteria does Coca-Cola use in choosing to apply the equity method for...
-
Your father loans you 12,000 and his repayment schedule requires 1,317.54 at the end of the year for the next 15 years , what interest rate is he charging?
-
what is the monthly payment for a car purchased at $24,500 when terms are 4 years loan , 3.99% interest rate? No money down payment. 2.) given the following information calculate the expected return...
-
Explain how you will incorporate into your planning process The Health Belief Model (HBM)
-
Describe how businesses can guard against becoming participants in money laundering schemes. Include a discussion of at least five red flags that might be seen in a possible money laundering scheme.
-
Dr. Jones' accountant has told him he will need to clear an additional $200,000 of cash flow (above break-even) in order to pay the debt service on the cost of the equipment and yield the required...
-
Which task is performed by a book-keeper? A. Analysing the trading results B. Entering transactions in the ledger C. Preparing year-end financial statements D. Providing information for...
-
Amigos, Inc., purchased a used piece of heavy equipment for $30,000. Delivery of the equipment to Amigos' business site cost $750. Expenditures to recondition the equipment and prepare it for use...
-
The manager of Wilsons Toy Division is evaluated on her divisions return on investment and residual income. The company requires that all divisions generate a minimum return on invested assets of 10...
-
Viking Beer is a microbrewery that produces one type of beer. The production level is 18,000 gallons per month, which is bottled in 192,000 twelve-ounce bottles. The beer is brewed in batches of...
-
Describe the five financial statements and how they are prepared.
-
Presented below in alphabetical order are statement of financial position items for Feagler Gardening at December 31, 2025. Prepare a statement of financial position following the format of...
-
Presented below is selected information related to Tsui Repairs at December 31, 2025. Tsui reports financial information monthly (amounts in thousands). a. Determine the total assets of Tsui Repairs...
Study smarter with the SolutionInn App