PDF Week-1: Homework/Lab Assignments Introduction to Excel ...

WEEK-1: HOMEWORK/LAB ASSIGNMENTS

INTRODUCTION TO EXCEL

LEARNING OBJECTIVES (WEEK-1)

1. To learn basic Excel concepts (various copy/paste options, drag down issues, print screen and create word reports, fixed and relative cell addressing, creating professional looking black/white (not haphazard!) graphs). We will cover this in one or two lectures.

Lab-1: Finish the tutorial and then solve problems 1, 4, 5, 6, and hand solve 7 in your homework then show it to the tutors and get a check mark on the attendance sheet before you leave.. DEVELOPMENT OF LOAN AMORTIZATION TABLE

In this exercise, we will learn how to use spreadsheets to solve simple accounting problems. We will also learn how easy it is to use multiple formulas within Excel. The handout is prepared with instructions to be used in Microsoft's Excel 2007. Some of the instructions are similar if an earlier version of Excel is used; however, it is required that you use Excel 2007 or a later version for all the exercises in this class.

Loan Amortization tables show how much you owe a bank every month when you borrow money from them. This specific problem is a useful practical example adapted from a problem discussed in Engineering with Excel, by R.W Larson. Let us assume that you recently graduated and bought a brand new Ford Mustang (with cool white stripes, of course!). Thanks to government bailouts you got a nice deal of $25,000 at an annual percentage rate (APR) of 6%, with no down payment. You want to pay the loan within 5 years. In this exercise, you will create an amortization table (monthly basis) that shows your payment, how much went as interest, how much principal was paid, and the amount you owe to the bank. The primary concept in preparing this table is that, each time you make a payment, you have to pay interest on the outstanding borrowed amount, called Principal. The amount that exceeds the interest amount paid reduces the principal before the next payment. Before we proceed into the detailed calculations, we have to set up a spreadsheet for your loan. We will design a general layout to organize information and make it easy to understand.

1

Figure 1.0 Setting up the spreadsheet

Figure 1.1 Spreadsheet's layout after setting the column width Click on the cell A1 and type "Loan Amortization Table". It can be observed that the cell width is insufficient to fit the contents of the cell. To adjust the cell size, move the cursor to the area shown in Figure 1.0 and click and drag to resize the cell width. Alternatively, you can double click in the area to automatically adjust the cell width to fit the contents. Make the font bold; this is the heading for the worksheet. Every worksheet should have a heading; this is not a necessity, but it is recommended to keep the spreadsheet organized. Click on cell A3 and type "Loan Amount", "APR" in cell A4, "Loan Term" in cell A5, "Payments/year" in the cell A6. These are called labels. If the worksheet is filled with numbers, it will be difficult for the user to understand it. So, we give names to each number to communicate with the end user what each number means. Always use appropriate labels. Enter the value 25000 in cell B3, 6% in B4, 5 in B5 and finally 12 in B6. We have now entered all input data needed for our calculation. Now we can additionally format the cells to make them more presentable. Since the loan amount is in dollars, we should tell Excel that the number in cell B3 is currency. This can be done by right clicking on the cell B3 and then clicking on the "Format cells" option. Click on the "Numbers" tab and choose "Currency" and click OK.

2

Figure 1.2 Formatting cells to display currency.

Figure 1.3 Loan amount displayed as currency. As observed in Figure 1.3, the loan amount is displayed in currency format and hence has a $ symbol before the value. You could also change the color and make the labels bold. Play with the formatting a little to differentiate the input values from the end results. Since we make the payments every month, it is necessary to calculate the periodic interest rate. The APR is 6%; therefore, the interest rate for each month would be (6/12) = 0.5%. The loan term is 5 years; hence the loan will be repaid in 12*5=60 payments. We should add this additional information into the spreadsheet with appropriate labels. Assign different colors to the cells to differentiate the input, output and titles. To change the color of a cell, select the cell and click on the button highlighted by a red box in the Figure 1.4. Pick an appropriate color from the list.

3

Figure 1.4 After entering additional information (Black box on top shows the cell color button)

Enter "Total payments" in cell A7 and in cell B7 enter the formula "=B5*B6". Type "Periodic Interest Rate" in cell A8 and in cell B8 enter the formula "=B4/B6". To ensure that your number displays as a percentage, Right click on cell B8, select "Format Cells", Click on the "Number" tab, Select "Percentage" and change the "Decimal places" to 1 as shown in Figure 1.5. Finally we calculate the monthly payment amount from the periodic interest rate, principal amount and total number of payments. The payment per month can be calculated using the formula (which you will learn in your engineering economics class):

. .

.

Enter "Payment amount" in cell A9 and enter the formula "=B3*B8/(1-((1+B8)^B7))" in cell B9. This number is currency, so follow the instructions as before and change the cell to display the value as currency. Now the spreadsheet has all the data required to build the amortization table.

4

Figure 1.5 Changing the numbers to percentage. Now set up the spreadsheet to calculate the payment information for every month. This should calculate the interest for each month based on the principal, the amount of money paid towards the principal after subtracting the interest amount and finally the principal amount after the payment. Set up the spreadsheet as shown in Figure 1.6.

Figure 1.6 Setting up the spreadsheet to calculate periodic payments. To ensure that your text fits the cells, you should select the cells and right click on them and choose the "Format cells" option, click on the "Alignment" tab and check the "Wrap text" option. The text you entered into the selected cell will automatically fit into the cell.

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download