computer application homework 2

Project description
Please read everything carefully in advanced. Complete both questions and upload the individual Excel
solution files for each by the due date. Also, be sure to visit your assigned Lab on or before February 27th
to have your assignment graded. Assignments not demonstrated will receive a grade of zero.
Problem 1: [25 marks] Winter is here and you have decided to make the most of it by purchasing a
snowmachine. You started saving up for the purchase last fall, but when you look at your bank balance it is
clear that you need to finance part of the purchase. You have a down payment and now wish to develop an
amortization schedule so you can plan your payments. You hope to borrow no more than $10,000. Interest
rates are low, but may soon be rising. Because the rates will change depending upon the brand of
snowmachine and the dealer, you wish to examine several different payment plans. You hope to pay off the
loan in 5 years.
Develop a worksheet that will show the monthly payment, the beginning and ending balance for each year of
the loan, the annual cost of the loan, and the annual interest paid for each year of the loan.
Instructions: With a blank worksheet on the screen, create the worksheet partially shown in Figure E4A 1.
Perform the following tasks:
1. Select the entire worksheet and change the font size to 12.
2. Change the column widths to the following: A = 11.00; B, C, and E = 20.00; D = 19.00.
3. Change the row heights to the following: 1, 5, and 12 = 39.00.
4. Enter the worksheet title, Snow Machine Loan Analysis, in cell A1. Merge and center cell A1 across the
range A1:E1. Change the font type to Biondi, the font size to 20, the font color to white and the
background to dark blue.
5. Enter the following labels: A2 = Principal; A3 = Rate; A4 = Years; C4 = Payment; E4 = per month; A5 =
Year; B5 = Beginning Balance; C5 = Ending Balance; D5 = Total Paid; E5 = Interest. Change the font color
to dark blue and bold all the labels except E4. Add a thick dark blue line under the titles in A5 through
E5.
6. Enter the =Now() function in cell E2 to display the current date and format as shown in Figure 1. (Note:
The date will be different from that in Figure 1 so you will need to change the number format.)
7. Enter the principal amount of $10,000 in cell B2. Format the cell to currency format with no decimal
places.
8. Enter the interest rate of 4.90% in cell B3. Format the cell as shown in Figure 1.
9. Enter the number 5 in cell B4 for the number of years.
10. Enter the function =PMT in cell D4 to calculate the monthly payment on a loan of $10,000 (cell B2) at
4.90% (cell B3) for 5 years (cell B4). Format it as shown in Figure 2-1.
11. Enter 1 in A6 and 2 in A7, then use the fill handle to fill the range A6:A10 with the numbers 1 through 5.
12. Enter the formula =B2 in cell B6 to reference the principal, which is the beginning balance for year 1.
13. Enter the =PV function in cell C6 to determine the ending balance for year 1.
14. Enter the formula =$D$4 * 12 in cell D6 to determine the annual amount paid on the loan.
15. Enter the formula =D6-(B6-C6) in cell E6 to calculate the amount of interest paid for the year.COSC1702 Winter 2015 Project #2
Due: February 13th, 2015 by 11:55pm
Page 2
16. Enter the formula =C6 in cell B7 to obtain the beginning balance for year 2. Copy this formula to the
range B8:B10.
17. Copy the =PV function entered in cell C6 to the range C7:C10.
18. Copy the formula in cell D6 to the range D7:D10.
19. Copy the formula in cell E6 to the range E7:E10. If all is done properly, the value in cell C10 should be
zero.
20. Use the =SUM function in cells D11 and E11 to sum the payment and interest amounts.
21. Format all cells as shown in Figure 2-1.
22. In cell A12, enter the label, The Effect of Various Interest Rates. Format this entry the same as the
formatted entry in cell A1 (Hint: use format painter).
23. Enter the following labels: B13 = Rate; C13 = Total Paid; D13 = Total Interest.
24. Enter the formula =D11 in cell C14 and the formula =E11 in cell D14.
25. Enter and format the interest rates shown in Figure 2-1 into the range B15:B21.
26. Create a one-variable data table that displays the total amount paid and the total amount of interest for
the 5 year amortization schedule created in this exercise. Format the results.
27. Rename the Sheet 1 tab to Car Loan.
28.

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂