Excel formula assignment need it in 2 hours
In today’s fast-paced academic environment, students often find themselves juggling multiple responsibilities, from attending classes and working part-time jobs to participating in extracurricular activities. The demands of modern education can be overwhelming, leaving students in search of effective solutions to manage their academic workload efficiently. One such solution is the use of Nursingpapertutors.com academic writing website. Nursingpapertutors.com academic writing website employ a cadre of experienced and knowledgeable writers who are well-versed in various academic subjects and writing styles. Nursingpapertutors.com professionals bring a wealth of expertise to the table, enabling them to produce well-researched and well-structured papers that meet the academic standards and expectations of students. Their familiarity with diverse subjects ensures that students can receive assistance across a wide spectrum of disciplines, from mathematics to literature, and from nursing to engineering.
EXP ECH02 H3 – Mortgage Calculator 1.3
Project Description:
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 payments in one year). Borrowers pay 0.5% private mortgage insurance (PMI) of 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 per-son 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.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
|
Step |
Instructions |
Points Possible |
|
1 |
Start Excel. Download and open the file named e02_grader_h3.xlsx. |
0.000 |
|
2 |
On the Payment worksheet, assign the range name Table to the range A17:C20. |
0.000 |
|
3 |
In cell F4, enter a lookup function to calculate the APR Based on Credit Rating. Include the range_lookup argument to ensure an exact match. Use range names for the first two arguments in the function.
|
10.000 |
|
4 |
In cell F5, enter a lookup function and formula to calculate the Minimum Down Payment Required amount. Include the range_lookup argument to ensure an exact match. Use range names for the first two arguments in the function and for calculation after the closing parenthesis. 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 |
10.000 |
|
5 |
In cell F6, enter a formula to calculate the Annual Property Tax based on the negotiated cost of the house and the annual property tax rate. Use range names in the formula. |
6.000 |
|
6 |
In cell F10, enter a formula to calculate the Total Down Payment, which is sum of the required minimum down payment (calculated previously) and any additional down payment entered in the Inputs section. Use range names in the formula. |
4.000 |
|
7 |
In cell F11, enter a formula to calculate the Amount of the Loan, which is the difference between the negotiated cost of the house and the total down payment. Use range names in the formula. |
5.000 |
|
8 |
In cell F7, enter an IF function to calculate the Annual PMI. If the borrower’s total down payment (required and additional) is 20% or more 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 calculated by multiplying the amount of the loan by the PMI rate. Use range names whenever possible in the function. |
10.000 |
|
9 |
In cell F12, enter the PMT function to calculate the Monthly Payment. Use range names in the function. |
10.000 |
|
10 |
In cell F13, enter a formula to calculate the Monthly Property Taxbased on the annual amount (calculated previously). Use range names in the formula. |
5.000 |
|
11 |
In cell F14, enter a formula to calculate the Monthly PMIbased on the annual amount (calculated previously). Use range names in the formula. |
5.000 |
|
12 |
In cell F15, enter a function to calculate the Total Monthly Payment. |
5.000 |
|
13 |
In cell F16, enter the EDATE function to calculate the Date of Last Payment. 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 payment 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.
|
10.000 |
|
14 |
Display the Range Names sheet and paste a list of range names starting in cell A2. |
10.000 |
|
15 |
Set the width of column A to 12 and the width of column B to 22. |
4.000 |
|
16 |
Create a footer on each worksheet with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side of both worksheets. |
6.000 |
|
17 |
Ensure that the worksheets are placed in the following order in the workbook: Payment, Range Names. Save the workbook. Close the workbook, and then exit Excel. Submit the workbook as directed. |
0.000 |
|
|
Total Points |
100.000 |
One of the significant challenges students face is time management. The demands of coursework, assignments, and exams can create immense pressure, leading to stress and a potential drop in academic performance. Nursingpapertutors.com academic writing website can be a valuable resource for students seeking to balance their responsibilities effectively. By outsourcing some of their academic tasks, students can free up time to focus on studying, participating in meaningful extracurricular activities, or even addressing personal obligations. This improved time management can alleviate the stress associated with academic overload. The ultimate goal of education is to enhance one’s knowledge and skills. Nursingpapertutors.com academic writing websites can play a vital role in achieving this goal by providing students with well-crafted papers that serve as models for their own work. These papers can offer valuable insights into effective research, organization, and writing, thereby empowering students to improve their academic performance over time.
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!
NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
Have any questions? Talk to us…