Shelly Cashman Excel 2019 | Module 4: End of Module Project 1 Conyers Law Offices CREATE A LOAN ANALYSIS GETTING STARTED ? Open the file SC_EX19_EOM4-1_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought7

Share With

Shelly Cashman Excel 2019 | Module 4: End of Module Project 1

Conyers Law Offices

CREATE A LOAN ANALYSIS

GETTING STARTED

?

Open the file

SC_EX19_EOM4-1_

FirstLastName

_1.xlsx

, available for download from

the SAM website.

?

Save the file as

SC_EX19_EOM4-1_

FirstLastName

_2.xlsx

by changing the “1” to a

“2”.

?

If you do not see the

.xlsx

file extension in the Save As dialog box, do not type it. The

program will add the file extension for you automatically.

?

With the file

SC_EX19_EOM4-1_

FirstLastName

_2.xlsx

still open, ensure that your first

and last name is displayed in cell B6 of the Documentation sheet.

?

If cell B6 does not display your name, delete the file and download a new copy from the

SAM website.

PROJECT STEPS

1.

Nadia Khalif is the office manager for the Conyers Law Offices in Dallas, Texas. The firm

is planning to move its computer network into the cloud so that everyone working on a

case can access information at any time and from anywhere. Nadia is in charge of

securing a loan to pay for the network transition and asks for your help in creating a loan

analysis.

Go to the

Loan Payment Calculator

worksheet. Resize row 1 to a height of 8.25 to reduce

the blank space at the top of the worksheet.

2.

Cell D4 has a defined name, which is unnecessary for a cell that will not be used in a

formula. Delete the name for cell D4 from the worksheet.

3.

Nadia wants to assign names to other cells to help her interpret the loan calculations. In

the range D5:D7, define names based on the values in the range C5:C7. Define names

for the range F5:F7 based on the values in the range E5:E7.

4.

Nadia needs to calculate the monthly payment for the loan to fund the law firm's

transition to the cloud. In cell F5, enter a formula using the

PMT

function. Insert a

negative sign (

-

) after the equal sign in the formula to display the result as a positive

amount. Use defined names for the rate, nper, and pv arguments as follows:

· rate argument: Divide the

Rate

by

12

to use the monthly interest rate.

· nper argument: Multiply the

Term

by

12

to specify the number of months as the

periods.

· pv argument: Use the

Loan_Amount

as the present value of the loan.

5.

Nadia wants to calculate the total interest, which is the total amount of the payments

minus the loan amount. In cell F6, enter a formula without using a function that

multiplies

12

by the

Term

and the

Monthly_Payment

, and then subtracts the

Loan_Amount

to determine the total interest.

6.

In cell F7, enter a formula without using a function that adds the

Loan_Amount

to the

Total_Interest

to determine the total cost of the loan.

7.

Nadia wants to compare monthly payments, total interest, and total cost for interest

rates that vary from 7.725% to 8.075%. She has already entered formulas to insert the

monthly payment in cell D11, the total interest in cell E11, and the total cost in cell F11.

Based on the range C11:F26, create a one-variable data table that uses the rate in cell

D6

as the column input cell to provide the comparison that Nadia requests.

8.

Cell D6 includes the rate a bank quoted Nadia for the business loan. In the list of interest

rates (range C12:C26), create a Conditional Formatting Highlight Cells Rule to highlight

the matching rate in Green Fill with Dark Green Text.

9.

Nadia has set up the structure for an amortization schedule in the range H4:L15. Finish

the amortization schedule by completing the formula in cell J5, which already contains an

IFfunction that checks whether the year in column H is less than or equal to the term in cell D7

Between the commas in the formula in cell J5, enter another formula using the PV

function. Use defined cell names for the rate, nper, and pmt arguments as follows:

· rate argument: Divide the

Rate

by

12

to use the monthly interest rate.

· nper argument: Subtract the year value in cell

H5

from the

Term

, and then multiply the

result by

12

to specify the number of months remaining to pay off the loan.

· pmt argument: Use the

Monthly_Payment

as a negative value to specify the payment

amount per period.

10.

Fill the range J6:J14 with the formula in cell J5 to complete the amortization schedule.

11.

The line chart in the range H16:L33 compares the amount of principal and interest paid

each year of the loan. Nadia wants to make the chart more prominent on the worksheet

and easier to interpret.

Add a border to the chart using the Lavender, Accent 1 shape outline color and a 1½

point weight. Add the default data table (with legend keys) to the chart to show the

principal and interest values for each year.

12.

Nadia was planning to list cost estimates for hardware and software purchases on the

Cost Estimates

worksheet, but has decided not to. Delete the

Cost Estimates

worksheet.

13.

Go to the

Retirement Projections

worksheet, if necessary, which compares details for

three retirement plans Nadia is evaluating for the law firm. The options show the amount

the firm would contribute to an employee's retirement plan per month for 10 years and

the monthly rate of return. Nadia wants to determine the future value of the investments

for each plan.

In cell C10, insert a formula with the

FV

function that uses the monthly rate of return

(cell

C6

), the number of payments (cell

C8

), and the monthly payment (cell

C7

) to

calculate the future value of Plan 1. Fill the range D10:E10 with the formula in cell C10 to

calculate the future value of Plans 2 and 3.

Your workbook should look like the Final Figures on the following pages. Save your changes, close

the workbook, and then exit Excel. Follow the directions on the SAM website to submit your

completed project.

Shelly Cashman Excel 2019 | Module 4: End of Module Project 1

Final Figure 1: Loan Payment Calculator Worksheet

Final Figure 2: Retirement Projections Worksheet