CSIS 110 Grossmont College Loan Analysis Excel Worksheet Idont know how to do the project and it is diffeclt to be done by my self also there are many proj

CSIS 110 Grossmont College Loan Analysis Excel Worksheet Idont know how to do the project and it is diffeclt to be done by my self also there are many projects to be done within short time. 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”.
o
•
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.
o
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
Shelly Cashman Excel 2019 | Module 4: End of Module Project 1
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 IF function 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
Shelly Cashman Excel 2019 | Module 4: End of Module Project 1
Final Figure 2: Retirement Projections Worksheet
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
the SAM website.
Shelly Cashman Excel 2019 | Module 4: End of Module Project 1
Conyers Law Offices
CREATE A LOAN ANALYSIS
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from
the SAM website.
f the file from
Loan Payment Calculator
Date
Loan Amount
Rate
Term
Amortization Schedule
4/3/2021
$327,000.00 Monthly Payment
7.825% Total Interest
10 Total Cost
Varying Interest Rate Schedule
Rate
7.725%
7.750%
7.775%
7.800%
7.825%
7.850%
7.875%
7.900%
7.925%
7.950%
7.975%
8.000%
8.025%
8.050%
8.075%
Monthly Payment
$0.00
Total Interest
$0.00
Total Cost
$0.00
Year Beginning Balance
1
$327,000.00
2
0.00
3
0.00
4
0.00
5
0.00
6
0.00
7
0.00
8
0.00
9
0.00
10
0.00
Ending Balance
$0.00
Subtotal
Paid on Principal
$327,000.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
$327,000.00
Interest Paid
($327,000.00)
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
($327,000.00)
Principal and Interest Paid
$400,000.00
$300,000.00
$200,000.00
$100,000.00
$0.00
1
($100,000.00)
($200,000.00)
($300,000.00)
($400,000.00)
2
3
4
5
6
7
8
9
10
Retirement Plan Projections
Return on balance
Monthly rate of return
Monthly payment
Number of payments
Future value
Plan 1
7.00%
0.583%
-$200
120
Plan 2
6.75%
0.563%
-$250
120
Plan 3
7.25%
0.604%
-$175
120

Purchase answer to see full
attachment