The time has come for you to purchase a home. After years of saving you are ready to find the
home of your dreams. You have a down payment and now wish to develop an amortization
schedule so you can plan your payments. Interest rates are low but may soon be rising. Because
you are not sure how long it will take to find just the right home, you wish to examine several
different payment plans. You hope to pay off the loan in 20 years.
Develop a worksheet that will show the monthly payment as well as the beginning balance for
each year of the loan, the ending balance each year of the loan, the annual cost of the loan, and
the annual interest paid for each year of the loan.
Choose a home from www.riliving.com. Enter in the price of the home and what you will be
paying for the home. Insert a picture of your home on your spreadsheet.
Perform the following tasks:
Enter the worksheet title, Home Loan Schedule, in cell A1. Merge and center cell A1 across
the range A1:E1. Change the font type to Bookman Old Style, the font color to red, and the
background to yellow.
Enter the following labels: B2 = Principal; B3 = Rate; B4 = Years; B5 = Payment; D5 = per
month; A6 = Year; B6 = Beginning Balance; C6 = Ending Balance; D6 = Total Paid; E6 =
Interest. Change the font color to blue, except for cell D5.
Enter the =Now() function in cell E2 to display the current date and format as illustrated in
Enter the principal amount of your home (What you paid for it) in cell C2. Format to
currency format with no decimal places.
Enter the interest rate that you obtained based on your credit score (explain)in cell C3.
Enter the number of years you got your loan for in cell C4 for the number of years.
Enter the function =PMT in cell C5 to calculate the monthly payment on a loan of 450,000
(cell C2) at 7.2% (cell C3) for 20 years (cell C4).
Use the fill handle to fill the range A7:A26 with the numbers 1 - 20.
Enter the formula =C2 in cell B7 to reference the principal, which is the beginning balance
for year 1. Enter =C7 in B8
Enter the =PV function in cell C7 to determine the ending balance for year 1. The formula
is =PV($C$3/12,12*($C$4-A7),-$C$5) autofill the range from B8 straight down to number
of years. Autofill the row straight down in row C.
Enter the formula =$C$5 * 12 in cell D7 to determine the annual amount paid on the loan.
Enter the formula = D7 - (B7 - C7) in cell E7 to calculate the amount of interest paid for the
Copy the =PV function entered in cell C7 to the range C8:C26.
Copy the formula in cell D7 to the range D8:D26.
Copy the formula in cell E7 to the range E8:E26. If all is done properly, the value in cell 20
should be zero.
Use the =SUM function in cells D27 and E27 to sum the payment and interest amounts.
Format all cells with designs of your choice.
Enter your name in cell A40. In the cells directly beneath your name, enter your course
identification, computer lab assignment (Lab Test A – Excel Project 4), date, and instructor
Rename the Sheet 1 tab to Home Loan.
Save the workbook using the file name Yourname - Excel Project 4 - Lab Test A, where
Yourname is your last name.
Upload the sheet to ULEARN and Present to your Class Next week.
Purchase answer to see full