## (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

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
QUALITY
Approved

This question was answered on: Dec 08, 2020

Solution~000652147654156.zip (25.37 KB)

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)

STATUS

QUALITY

Approved

Dec 08, 2020

EXPERT

Tutor