Question Details

(Solution Download) EXP ECH02 H3 - Mortgage Calculator 1.3 Project Description: As a financial consultant, you work with


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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 borrowerA????1s 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

 

borrowerA????1s 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 borrowerA????1s 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

 

Tax based 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 PMI based

 

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 functionA????1s 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

 

 

 


Solution details:
STATUS
Answered
QUALITY
Approved
ANSWER RATING

This question was answered on: Dec 08, 2020

PRICE: $15

Solution~000652147654156.zip (25.37 KB)

Buy this answer for only: $15

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Dec 08, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now