Using Spreadsheets

This assignment consists of two real-world scenarios which are solved using Excel or a similar spreadsheet program. It illustrates the use of spreadsheets to approximate and model solutions to mathematically-oriented problems.

If you are not already an experienced spreadsheet user, make sure you have gone through the Excel for Noobs tutorials indicated in the in-class assignment.

Part 1 — Calculating future value

Remember the computer you priced for your classmate in a previous in-class assignment? It’s a sizeable investment now, but think about how much it will be worth (or not worth) in the future. We can calculate its actual worth through its useful life cycle with the help of Excel functions. Begin by entering the data below into the first three rows of your spreadsheet, with the text values in column A and the numeric values in column B:

  • Row 1: Initial cost, 1500
  • Row 2: Salvage value, 300
  • Row 3: Useful life in years, 3

(Note that three years is a reasonable expectation for a computer’s useful life.)

Format cells B1 and B2 as currency . To do this, right-click on the cell, choose Format Cells from the pop-up menu, choose Currency, select an appropriate format from those shown.

Skip row 4, and place the following heading values as text in the indicated cells:

  • A5: End of year
  • B5: Double-declining depreciation
  • C5: Value of computer
  • D5: Straight-line depreciation
  • E5: Value of computer
  • F5: Sum-of-years depreciation
  • G5: Value of computer

Place the numbers 1, 2, and 3 in cells A6 through A8 respectively. Format cells B6 through G8 as currency just like you did above. The numbers in cells B1 through B3 and A6 through A8 will be the ONLY actual numbers you will enter on this spreadsheet. All remaining values will be calculated using formulas.

Now come the formulas. Note that ALL formulas will begin with an equals sign (=) as the first character you type in the cell. You will use three different methods to calculate depreciation and value. These three methods of calculation are completely independent of one another; you should not use any data from one method’s calculations to calculate values for another values.

The first method is the DDB (double-declining balance) financial formula. Select cell B6 by clicking on it, then use the menu bar at the top of your window to locate the list of functions. You will find the DDB function in the Financial category. Select it; as you do so, fill in the appropriate cell addresses (NOT the actual values, but the address of the cells containing those values), for the cost (B1), salvage (B2), and life (B3) values. For the period value, use A6. Repeat this for cells B7 and B8, using the same values for cost, salvage, and life, but using A7 for the period value in B7 and A8 for the period value in B8. Then, in cells C6 through C8, calculate the end-of-year value by subtracting the year’s depreciation (value in column B of this row) from the previous year’s value (B1 the first year, C6 the second year, C7 the third year). It is NOT necessary to use a built-in function for this calculation — spreadsheet programs understand the arithmetic operators +, -, *, and / just fine. As an example, you can calculate the value at the end of the first year with the formula =B1-B6. Make sure you use the cell addresses where the values are stored, not the actual data values.

Repeat the process in the above paragraph for cells D6 through D8, but use the SLN (straight-line depreciation) formula. Calculate the end-of-year values for this method in cells E6 through E8. Note that you do not need to specify a period for this method. Make sure you use the depreciation amounts in column D, not column B.

Finally, repeat the process in cells F6 through F8 using the SYD (sum-of-years depreciation) formula, calculating the corresponding end-of-year value in cells G6 through G8.

As a final check for each method, does the value of the computer after the third year equal the salvage value for all three calculations? It should! What else do you notice about the decline in value of your computer?

Note that the normal view of your spreadsheet will show the results of your calculations instead of the formulas in those cells containing formulas. You can toggle back and forth between the Results view and the Formulas view if you need to do so in order to correct any errors. Look under the Help menu if you cannot figure out how to do this.

Submit your spreadsheet file in the iCollege dropbox folder, along with a brief paragraph stating what you conclude about how depreciation of an asset behaves with different methods of calculating it.

Part 2 — Analyzing a loan

Let’s now look at loan analysis. What if you want to see the effect of interest rate and length of loan on a car payment? Begin by moving to a clean worksheet. As you work through this problem, look back at the previous problem’s instructions if you do not remember how to do a particular step.

In cell A1, enter the label “Loan principle,” and in cell B1 enter the amount of the loan you want to analyze. Format B1 as currency. Skip row 2. In row 3, enter the text values “Interest Rate”, “Payment”, “Total”, “Payment”,  “Total”, “Payment”, “Total”, “Payment”, and “Total” in columns A through I respectively. In row 4, enter in the values 3, 3, 4, 4, 5, 5, 6, and 6 in columns B through I respectively, once under Payment and once under Total for each value. This indicates the number of years for the loan.

Format cells A5 through A21 as percentage (CRITICAL). In A5, enter the value 8.00%, WITH the percent sign. In A6, enter the formula A5 + 0.25%. Move your mouse so that it points to the bottom right corner of A6; your cursor should look like a +. Click and hold the left mouse button, and drag it down until the cells in column A through row 20 are highlighted. When you release the button, you should see percentage values, increasing by 0.25% each row, up to 12.00 %, in column A. If the values increase by 25%, you have entered the formula incorrectly and you must correct it to get the right results from this point on.

NOTE: From this point on, ALL values are to be calculated using formulas, NOT hard-coded.

In cells B5, D5, F5, and H5, use the PMT formula to calculate the amount of the payment for 3, 4, 5, and 6 years respectively, using the values you already entered into the spreadsheet. Note that your principal remains constant, therefore you should enter the principal using dollar signs (i.e. $B$1). Your interest rate is annual, but you are making monthly payments. Therefore, your interest rate should refer to the cell in column A of the same row, and be divided by 12 (i.e. =A5/12 for the first one). Since you will be making monthly payments, there will be 12 payments per year; thus the number of payments should be the number of years (row 4 of the corresponding column) multiplied by 12. This, too, will be a constant value so should be entered with dollar signs in front of the column letter and row number (e.g. $B$3). Make sure that, once again, you use cell references and not actual values in your formulas!

Do you get a number, and does it make sense? If not, check your formula. I’m serious. You will save yourself a lot of grief if you check as you go along to see if your results make sense at each step.

Once you are confident of your formulas, select the four cells containing the payment formulas, one by one. Put your mouse on the bottom right corner of the group, so that your cursor again looks like a +. Click and hold the left mouse button, and drag it down until the cells in that column through row 20 are highlighted. Do you have payment values in each cell, and are they all different? Do payments go up as interest rates go up? Do they go down as the length of the loan increases? Make sure that all your cells indicating payment amounts are formatted as accounting and that they show the $.

For cells C5, E5, F5, and H5, use formulas to calculate the total amount that you will pay back on the loan: the payment amount you calculated times the total number of payments you will make. Remember, you are making monthly payments, not yearly payments. Once you have the totals calculated, repeat the process in the previous paragraph to copy the formulas to the remaining rows in the columns. What do you notice about the total payment amount as the length of the loan decreases?

Submit your spreadsheet file in the dropbox folder, along with a brief paragraph describing your observations about the effect of interest rate and of term of a loan on payments and on total payback.