Assignment – MS Excel Loan Payment

You will create a spreadsheet that will calculate monthly payments on a loan. The user – that’s you! -- enters the amount of the loan, the annual interest rate, and the number of years for the loan. You use an Excel function to calculate the monthly payment and a formula to calculate the total amount of interest paid. You also need to format the cells to display the proper currency and percentage formats. Remember that, in general, you must enter formulas or functions using cell references, not numbers. You must use Microsoft Excel for this assignment.

1. Create a new spreadsheet with the following label entries:

 Cell A1: Payment on a loan                                       

Cell B2: Amount:  

Cell B3: Annual interest rate:

Cell B4: Term in years:

Cell B6: Monthly payment:

Cell B7: Total interest:

 2. Enter $8000 in cell C2, 12.8% in C3, and 4 in C4. Don’t forget the $ sign in cell C2 and the % sign in C3.

 3. Format cells as follows:

                Cell A1: Underlined, 16 point font size, centered across Columns A, B, and C

                Cells B2 through B7: right aligned

                Cells C2, C6, and C7: Currency, two decimal places

                Cell C3: Percentage, one decimal place

                Widen columns as necessary to display all information.

 4. You enter an Excel function in Cell C6 that will calculate the monthly payment on a loan.

The PMT function requires three arguments, i.e. values that are separated by commas:

                                =PMT(Rate, Term, Amount)

                Since we want to get a monthly payment, the rate and the term must be expressed in months. You need to divide the Rate from cell C3, which is entered as an annual percentage, by 12 to change it to a monthly percentage. You need to multiply the Term from cell C4, which is entered as years, by 12 to get the number of months required. Finally, put a minus sign in front of the Amount from cell C2 so that your monthly payment will be a positive number(trust me!) Use cell addresses rather than the actual numbers to indicate the values entered.. Do not put any numbers other than 12 into the function. To get you started, the entry for the Rate argument would be C3/12, i.e. take the number in C3 and divide it by 12.

 5. You enter a formula in Cell C7 that calculates the total interest payment.

                The total interest is equal to the total of all the monthly payments(Payment times years times 12) minus the original Amount of the loan. Again, do not enter any numbers in this formula other than 12. Use cell addresses instead.

 6. The last two digits (cents) of the calculated monthly payment are 83. The last two digits of the total interest are 68.

 7. Place your name centered in the header of your worksheet. Save your worksheet as Your Last Name  Your First Name.xls.