rader-Instructions tep roject Description: ou have been hired as a finance manager at Granger vacation homes,...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
rader-Instructions tep roject Description: ou have been hired as a finance manager at Granger vacation homes, a regional construction company that finances nd builds vacation properties. As part of your responsibilities you manage mortgage applications. Your predecessor left partially created document you will use to calculate various details about each property being financed. You will use the LOOKUP function to help determine mortgage interest rates, IF statements to calculate down payment, various atistical calculations, and XLOOKUP to create a quick search within the document. teps to Perform: tep 2 Exp22_Excel_Ch02_ML2 - Vacation Property = Start Excel. Download and open the file named Exp22_Excel_Ch02_ML2_Vacation Property.xlsx. Grader has automatically added your last name to the beginning of the filename. Instructions Write an IF function in cell D9 that calculates the down payment for the mortgage. The down payment is 15% of the purchase price if the customer is considered high risk or 5% of the purchase price if considered low risk. The criteria to assess the risk of the loan is based on the credit score value in cell B6. Be sure to use the appropriate absolute or mixed cell references when referencing the input values in the range B4:B6. Then use the fill handle to copy the formula down, stopping in cell D28. Enter a formula to calculate the percent financed in cell E9. The percent financed is the amount financed/purchase price. Next use the fill handle to copy the formula down, stopping cell E28. Enter a formula in cell F9 to calculate the amount financed. The amount financed is the purchase price-down payment. After entering the formula, use the fill handle to copy the formula down, stopping in cell F28. Use the VLOOKUP function in cell G9 to lookup the mortgage rate based on the customer credit score and the data in the range D4:E6. Be sure to use the appropriate absolute or mixed cell references. Use the fill handle to copy the formula down, stopping in cell G28. Calculate the rate per period in cell H9. All mortgages being financed will be paid monthly. After completing the formula, use the fill handle to copy the formula down, stopping in cell H28. reated On: 11/17/2022 rader-Instructions Use the PMT function to calculate the total monthly payment in cell K10 based on the periodic interest rate, number of periods, and amount financed. Ensure the results are a positive value then use the fill handle to copy the function, down stopping in cell K28. Use Quick Analysis to calculate the total of all payments in cell K29. (Quick Analysis is not available for Mac. If using a Mac, use AutoSum to calculate the SUM of the range K9:K28). Use the appropriate statistical functions in the range B32:B36 to calculate descriptive statistics based on the row headings in the range A32:B36. In cell B32, calculate the total count of loans based on column A, lowest payment in cell B33 based on the data in column K, highest payment in B34, average payment in B35, and median payment in B36. 1 Instructions Excel 2022 Proje 10 Calculate the total number of payment periods in cell J9 based on the years financed in cell 19. After completing the formula, use the fill handle to copy the formula down, stopping in cell J28. Use the XLOOKUP function in cell E32 to lookup the employee number in cell D32 and return the corresponding down payment, % finances, and amount financed. Use the appropriate function to insert the current date and time in cell B2. Save and close the workbook. Submit the file as directed. Points Possible Total Points 0 10 10 10 10 10 10 8 Exp22_ExcelCh02_ML2-Vacation Property 1. 6 Excel 2022 Proje Points Possible 10 6 0 100 rader-Instructions tep roject Description: ou have been hired as a finance manager at Granger vacation homes, a regional construction company that finances nd builds vacation properties. As part of your responsibilities you manage mortgage applications. Your predecessor left partially created document you will use to calculate various details about each property being financed. You will use the LOOKUP function to help determine mortgage interest rates, IF statements to calculate down payment, various atistical calculations, and XLOOKUP to create a quick search within the document. teps to Perform: tep 2 Exp22_Excel_Ch02_ML2 - Vacation Property = Start Excel. Download and open the file named Exp22_Excel_Ch02_ML2_Vacation Property.xlsx. Grader has automatically added your last name to the beginning of the filename. Instructions Write an IF function in cell D9 that calculates the down payment for the mortgage. The down payment is 15% of the purchase price if the customer is considered high risk or 5% of the purchase price if considered low risk. The criteria to assess the risk of the loan is based on the credit score value in cell B6. Be sure to use the appropriate absolute or mixed cell references when referencing the input values in the range B4:B6. Then use the fill handle to copy the formula down, stopping in cell D28. Enter a formula to calculate the percent financed in cell E9. The percent financed is the amount financed/purchase price. Next use the fill handle to copy the formula down, stopping cell E28. Enter a formula in cell F9 to calculate the amount financed. The amount financed is the purchase price-down payment. After entering the formula, use the fill handle to copy the formula down, stopping in cell F28. Use the VLOOKUP function in cell G9 to lookup the mortgage rate based on the customer credit score and the data in the range D4:E6. Be sure to use the appropriate absolute or mixed cell references. Use the fill handle to copy the formula down, stopping in cell G28. Calculate the rate per period in cell H9. All mortgages being financed will be paid monthly. After completing the formula, use the fill handle to copy the formula down, stopping in cell H28. reated On: 11/17/2022 rader-Instructions Use the PMT function to calculate the total monthly payment in cell K10 based on the periodic interest rate, number of periods, and amount financed. Ensure the results are a positive value then use the fill handle to copy the function, down stopping in cell K28. Use Quick Analysis to calculate the total of all payments in cell K29. (Quick Analysis is not available for Mac. If using a Mac, use AutoSum to calculate the SUM of the range K9:K28). Use the appropriate statistical functions in the range B32:B36 to calculate descriptive statistics based on the row headings in the range A32:B36. In cell B32, calculate the total count of loans based on column A, lowest payment in cell B33 based on the data in column K, highest payment in B34, average payment in B35, and median payment in B36. 1 Instructions Excel 2022 Proje 10 Calculate the total number of payment periods in cell J9 based on the years financed in cell 19. After completing the formula, use the fill handle to copy the formula down, stopping in cell J28. Use the XLOOKUP function in cell E32 to lookup the employee number in cell D32 and return the corresponding down payment, % finances, and amount financed. Use the appropriate function to insert the current date and time in cell B2. Save and close the workbook. Submit the file as directed. Points Possible Total Points 0 10 10 10 10 10 10 8 Exp22_ExcelCh02_ML2-Vacation Property 1. 6 Excel 2022 Proje Points Possible 10 6 0 100
Expert Answer:
Answer rating: 100% (QA)
It seems like youre working on a finance project in Excel specifically dealing with mortgage calcula... View the full answer
Related Book For 
Posted Date:
Students also viewed these programming questions
-
Ashley Finn is a new production manager. After a great deal of effort, including considerable market research, she completes her budget and submits it to her boss, Keith Payne. Without even looking...
-
On her 21 st birthday, Rachel deposits $22,500 per year into a 403(b) retirement account with an estimated 12.50% annual rate of return. She will stop making deposits after her 65 th birthday (i.e.,...
-
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...
-
Staircase Equipment Company uses a job order cost system. The following data summarize the operations related to production for April 2014, the first month of operations: Materials purchased on...
-
Determine the moment of inertia for the shaded area about the x axis. dy
-
A US investor bought 2 million lots (1 lot = 100 shares) of BMRI shares for IDR 6,150 per share. After 6 months, BMRI's share price increased to IDR 8,300 per share, and paid a dividend of IDR 100...
-
A stock price is governed by \[\frac{\mathrm{d} S}{S}=\mu \mathrm{d} t+\sigma \mathrm{d} z\] where \(z\) is a standardized Wiener process. Interest is constant at rate \(r\). An investor wishes to...
-
Visic Corporation, a manufacturing company, produces a single product. The following information has been taken from the companys production, sales, and cost records for the just completed year. The...
-
A 10 year bond with 12.5% coupon rate and $1000 face value. YTM is 14.5%. Assuming annual coupon payment, Calculate the price of the bond
-
Prevosti Farms and Sugarhouse pays its employees according to their job classification. The following employees make up Sugarhouse's staff: Employee Number Name and Address Payroll information...
-
Define the composition function f:RnR such that f(x) =h(g(x)). Assume that h is nondecreasing. Prove that if h is convex and g is convex, then f is convex.
-
Frozen Delight, Inc. charges an initial franchise fee of \($75\),000 for the right to operate as a franchisee of Frozen Delight. Of this amount, \($25\),000 is collected immediately. The remainder is...
-
Pengo Company owns land that it purchased at a cost of 400 million in 2013. The company chooses to use revaluation accounting to account for the land. The lands value fluctuates as follows (all...
-
Croatia Company purchased land in 2015 for \($300\),000. The lands fair value at the end of 2015 is \($320\),000; at the end of 2016, \($280\),000; and at the end of 2017, \($305\),000. Assume that...
-
Guillen, Inc. began work on a \($7\),000,000 contract in 2015 to construct an office building. Guillen uses the cost-recovery method. At December 31, 2015, the balances in certain accounts were...
-
Nair Corp. enters into a contract with a customer to build an apartment building for \($1\),000,000. The customer hopes to rent apartments at the beginning of the school year and provides a...
-
You put 50 kg of aluminimum at 180C into 300 kg of water at 20C. The alu- minium and water come to the same equilibrium temperature. Calculate the equilibrium temperature, if no energy is lost....
-
The time to assemble the first unit on a production line is 10 hours. The learning rate is 0.94. Approximately how long will it take for the seventh unit to be assembled? The number of hours needed...
-
A company bought a machine for U.S. $50,000 on January 1, 2013. The machine is to be delivered in one week and is payable upon receipt of the machine. The company has not yet paid for the machine. It...
-
On November 15, 2013, Nizker Ltd., a Canadian company that follows IFRS, entered into a firm commitment to purchase inventory from a European supplier for 250,000, which will be paid in euros upon...
-
Jan Leskewitch has approached you for advice on how to record transactions for her company. She has just started an Internet-based business in her basement and is surprised to see that she has a...
-
If the angle between vectors \(\vec{A}\) and \(\vec{B}\) is \(110^{\circ}\) and if \(A=1.7 \mathrm{~N}\) and \(B=2.0 \mathrm{~m}\), what is the value of \(\vec{A} \cdot \vec{B}\) ?
-
You throw a textbook to a friend who is at their dormitory window \(2.2 \mathrm{~m}\) above you. You are standing \(4.0 \mathrm{~m}\) away from the building and your throw is perfectly made such that...
-
A force \(\vec{F}=F_{x} \hat{\imath}+F_{y} \hat{\jmath}\) with \(F_{x}=50 \mathrm{~N}\) and \(F_{y}=12 \mathrm{~N}\) is exerted on a particle as the particle moves along the \(x\) axis from \(x=1.0...
Study smarter with the SolutionInn App