Question
Question:In cell C5, enter the function =PMT to calcul ate the monthly payment on a loan of $96,500 (cell C2) at 2.9% (cell C3) for
Question:In cell C5, enter the function =PMT to calcul
ate the monthly payment on a
loan of $96,500 (cell C2) at 2.9% (cell C3) for 72 months (cell C4). Format it
as shown in Figure E1. Note that you will have to divide the rate by 12
because the formula takes years and not months.
Perform the following
tasks:
[1]
Select the entire Excel workbook and change the font size to 12.
[2]
Change the Column Widths of: A to 10.00; B, C to 18.00; D to 17 and E to
16.00.
[3]
Change the Row He
ights of
1 and 6 to 30.00 and E79 to 20.00.
[4]
In cell A1, enter the workbook title, Car
Loan Schedule, Merge and Center
cell
.
[5]
A1 across the Range A1:E1. Change the font type to Verdana, the font size to
18, bold, the font color to Blue Accent 1 -
Darker 50%, and the background
to Blue Accent 1 -
Lighter 60%.
[6]
Enter the following text labels: In cell B2 type Principal; cell B3 type Rate;
cell B4 type Months; cell B5 type Payment; cell D5 type per month; cell A6
type Month; cell B6 type Beginning Balance; cell C6 type Ending Balance; cell
D6 type Total Paid; cell E6 type Interest. Note: bold the
text labels. Change
the font color to Blue Accent 1 -
Darker 50%.
[7]
In cell E2, enter the function =Now() to display the current date and format
as shown in Figure 1. Note: The date will be different from that in Figure 1.
[8]
In cell C2, enter the loan amount of $96,500. Format the cell to currency
format with no decimal places.
[9]
In cell C3, enter the Interest Rate of 2.9%. Format the cell as shown in
Figure 1.
[10]
In cell C4, enter the number 72 for the number of Years.
[11]
In cell C5, enter the function =PMT to calcul
ate the monthly payment on a
loan of $96,500 (cell C2) at 2.9% (cell C3) for 72 months (cell C4). Format it
as shown in Figure E1. Note that you will have to divide the rate by 12
because the formula takes years and not months.
[12]
Use the fill series (under H
ome
Editing
Fill) to fill the range A7:A78
with the numbers 1 through 72. Make sure that you click on Columns and the
Stop Value is 72.
CGS2100C: Computer Fundamentals and Applications
Final Project
4
[13]
In cell B7, enter the formula =C2 to reference the Loan Amount, which is
the beginning balance for Month 1.
[14]
In cell C7, enter the function =FV to determine the ending balance for year
1. Note that you will need to do =
-FV to make it positive. Also make sure you
have your absolute and relative reference correct. Also note that since we are
doing months the nper in the fo
rmula will be 1.
[15]
In cell D7, enter the formula =
-$C$5 to determine the Annual Amount Paid
on the loan.
[16]
In cell E7, enter the formula =D7 -
(B7
- C7) to calculate the amount of
Interest paid for the month.
[17]
In cell B8, enter the formula =C7 to obtain the Beg
inning Balance for month
2.
[18]
Copy this formula to the range B9:B78.
[19]
Copy the function =FV entered in cell C7 to the range C8:C78.
[20]
Copy the formula in cell D7 to the range D8:D78.
[21]
Copy the formula in cell E7 to the range E8:E78. If all is done properly, the
value in cell C78 should be zero.
[22]
Use the function =SUM in cells D79 and E79 to sum the payment and
interest amounts.
[23]
Put a double border on cells D78 and E78 and increase the font of D79 and
E79 to 14
[24]
In cell A81, enter your first and last n
ame. In the cells directly beneath your
name, enter your course CRN number, assignment name (Excel Module 4 -
[25]
Analyzing and Charting Financial Data), date, and instructor's name (Prof.
Sapijaszko).
[26]
Rename the Sheet 1 tab to Car Loan.
[27]
Save the workbook usin
g the file name (your last name -
Excel Module 4 -
Financial Functions).
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started