Spreadsheets, Spreadsheet Techniques
CIT105P Rubrics Assignment 3: Spreadsheet 1 Equations 4 Formatting Cells 3 Go to zero (Fill) 3 Total 10
See below, at the bottom of the page, several links to YouTube tutorials on Excel.
A word about Functions
A function could also be thought of as a subroutine or a helper program. Functions are programming units which produce a result (output) based on input without telling you how it works inside. This is also called a "black box" - an engineering term for a machine which takes some sort of input and produces some sort of output based on the operations inside the device. It is "black" not because the device is painted black but because you can't see how it works.
When you see a function you will see a name (such as "PMT") followed by input parameters separated by commas, all within parentheses. Such as PMT(rate, term, principal). When you use this funciton in Excel you start by typing an equal sign (=) then the function name with its arguments.
Side note: This is an extremely common way of writing a function. Probably the most common syntax. It is called prefix notation because the operation name (function name) preceedes the parameters fed into it. The other common forms are infix, such as (functionName, argument1, argument2, argument3) in which both the function name and all arguments are inside parentheses as used in the LISP language and postfix notation such as argument1, argument2, argument3 functionName (no parentheses, as used in the PostScript language, an example of Reverse Polish Notation, common for calculator operations).
A word about the equal sign at the start of a cell entry.
In order to know whether you are writing a formula to be acted on or typing in either given numbers or labels Excel needs to be given a clue. That "clue" is the equal sign as the first character in the cell. Any time you have a formula, something to be calculated from the values in other cells, you start with the equal sign (=). That is the only way Excel has of knowing you are entering a calculation which will produce the value you see displayed in the cell.
Without the equal sign, Excel will not show a calculated value. It will instead show whatever you typed, "thinking" it is just a label.
Calculating the payments for a Loan
PMT (payment) Function
The PMT function in Excel allows you to determine how much a loan will cost you for each payment and how many payments it will take to pay down the loan. The PMT function is the primary tool allowing you to create a Load payment Table. We use this function with some simple formulas to complete the table.
This lesson guides you through the process of creating a payment table with Microsoft Excel. It's a useful example because a payment table can help you make wise decisions about borrowing money or repaying loans. A payment table shows for each payment of a loan how much of the payment is going toward the interest on the unpaid balance and how much is going toward the principal.
Using the PMT (payment) function
The key to creating an payment table is the PMT function.
The PMT function is a built-in function that computes the periodic payment given the principal, rate, and term.
"PMT" merely stands for "PayMenT"
FIRST: Here is the basic syntax for the function:
=PMT(rate, term, principal)
SECOND: Plugging numbers directly into the function to make it easier to see what is going on here is an example:
You borrow $15,000 at 4.5% for 4 years.
The payments are each month so the units for rate and for term are converted to fit a table based on months:
1 - the annual percent has to be divided by 12 because we want the percent per month.
2 - the years have to be multiplied by 12 to get the number of months in three years
3 - the principal is entered as it is because this is the base money to pay back across the entire time. It doesn't change.
=PMT(.045/12, 4*12, 25000) = $-570.09
- The result is negative because it represents money you are paying.
- Because we are setting up a schedule for montly payments we need to convert everything to the same unit (months)
- 4,5% is a yearly interest rate. To get monthly interest divide by 12 (0.045/12)
- Because we are storing the 4.5% interest in cell B4 our calculation will be: B4/12
- The term is given as 4 years. To get the number of months in 4 years multiply by 12 (4*12)
- Because we are storing the 4 years in cell C4 our calculation will be: C4*12
THIRD: Now lets try the same function substituting cell locations in the spreadsheet. We are putting the actual numbers (which are given) into those three cells in the spreadsheet. Then, using the PMT() function we are grabbing those values from the cells, rather than just writing them into the function. This is so that we can try different numbers in the cells giving us room to experiment with various values.
=PMT(B4/12, C4*12, A4) = $-570.09
Again, using the PMT function and a few simple formulas, you can generate a payment table.
Creating our payment table is a 5 step process:
- Open a new spreadsheet and save it with the name "LoanPaymentCalculator.xlsx"
- Enter the heading text and the starting numbers. Be sure to format the cells correctly.
- Calculate the monthly payment using the =PMT function.
- Create the first two lines of your table using formulas with the correct relative and absolute references.
- Use the Fill Down feature in Excel to create the rest of the table.
1 - Start a new spreadsheet and give it a filename
2 - Enter the titles, column headings and the starting numeric values
Here we've entered in the starting information, the title, the column headings, the PMT label and the given numeric values for principal, interest and term.
In the ribbon at the top you will find formatting aids.
You can format any of the cells using the pull down in the upper right in the illustration to the left. You can also tell Excel to format it in a particular way by typing it as you want. Typing a dollar sign ($) in front of the number will cause Excel to regard than cell the same way.
For the next two columns the same. You can pull down "percent" or type "%" at the end of the number.
Typing a number with no specifications gets you a "general" designation for the value.
3 - Enter the PMT function and "arguments" for monthly amount
This is what the spreadsheet looks like as you are typing in the formula using the PMT function. Notice the "=" as the first character which lets Excel know it has a formula to calculate in this cell.
Tip, you can either type in the cell location, such as B4 or C4 or A4 or you can take the mouse and click on the cell which has the value and then click back into the cell were you are writing the formula. That click will enter the cell location name.
This is what the spreadsheet looks like after you stop typing. You still see the formula in the top entry panel but the cell where you typed in the formula is now showing the calculated value of that formula, not the formula itself.
Now you are beginning to see how handy a spreadsheet can be.
In this case the value is showing in red within parentheses which is the manner of showing a negative number in Excel.
Notice that in the above example the parameters to the PMT function are cell references. This allows you to change a value in row 4 and quickly see how your change effects the monthly payment--especially handy when you are doing what-if analysis. Also notice that the value in B6 is displayed in red and in parenthesis. This is the default format for displaying negative numbers.
4 - Adding the top two lines of the table to generate the lines following
In this step you add the first two lines of the table.
The first line sets up the starting points for the item numbers and for the running balance.
The second line will be extended with the Fill Down feature. That second line is the foundation for the table itself.Using the Formulas tab then clicking on Show Formulas button in the top ribbon you can see the spreadsheet revealing all formulas in any calculated cells. This mode will also space out columns so that you can see all of the formulas without having to adjust column widths. Clicking the "Show Formulas" button again will toggle you back to the state you were in before and restore the column widths as they were.
Just to repeat, again, all cells which hold calculation instructions start with an equal (=) sign. That is the only way a spreadsheet knows this is a formula, not a label.
Take a look at the column headings.
1 - starting headings
Principal == the total amount
Interest Rate == yearly interest
Years == years
2 - table headings
payments by number from month one to the last month
The amount of the payment shown in B6 ($570) which goes to pay interest at this point. The interest payment changes as the amount left to pay goes down
The amount of the payment shown in B6 (($570) which goes to pay down the principal (the total amount)
How much of the principal which remains to be paid at each point.
Above is the payment Table almost ready. We have the monthly payment figured and the starter rows for the full table in place.
The rest is gravy, assuming you have the various formulas figured out properly. So take care and check your work. Here is what goes into these two lines:
Payment # (Payment Number) or Month Number or Line Number
Although there is no such thing as a zeroth payment (payments start at number 1), the payment number formula needs a starting value in a previous cell to work from. The formula is very simple, just add 1 to the number in the cell above. So the 0th payment is not a payment, just a preceeding value to get the line numbering started with a 1 in the cell just below, then a 2, then 3 and so forth.
The interest rate in cell B6 is the interest rate per year. We divide by 12 to get the interest owed for each month (for each payment).
Note the absolute reference $B$4. This keeps the reference from changing during the fill down step that's coming up shortly. When you need the same number in each cell, regardless of where that cell is, we use an absolute referemce. In this case we always want the same interest rate, which is located in B4. The dollar signs make sure that only that one number in that one location is used.
Calculated by adding the interest paid for the current period to the value in cell B6 (the monthly payment). It might seem odd that we are adding. Take a close look at the value in cell B6. It's negative. When you add a positive number (B10) to a negative number (B6) you get a less negative number.
The previous balance minus the principal paid. Since the principal paid is a negative number we add it to the balance.
5 - Fill Down from the top line to fill out the table
Relative cell references look like bingo numbers, such as A9 in the formula of A9+1 to get the next line item number. When you do a fill down the cell below will become A10+1, and below that A11+1 and so forth. The relative references in row 10 refer to the relative locations of other terms in the payment table so we can use the Fill Down command to fill out the other rows in the table. The instructions below demonstrate how to use the Fill Down command.
How to do Fill Down command:
1.Click and drag the mouse cursor across row 10 to highlight cells A10 - D10.
2.Now click and drag down the handle at the lower right of the border surrounding the highlighted area.
3.When you release the mouse the row you highlighted will be copied down to fill out your table.Click and drag the mouse cursor across row 10 to highlight cells A10 - D10.Now click and drag down the handle at the lower right of the border surrounding the highlighted area.
3.When you release the mouse the row you highlighted will be copied down to fill out your table.
Full On Assignment
See below for the assignment.
We will make a change in formatting for the "Interest" column in the illustration below. The interest is real money, so it should be formatted as "currency."
And this is what your spreadsheet should show for values. Notice that the 48th payment (on row 57) brings the balance to zero ($0.00).
As an experiment try changing any of the values on the top lines to see what happens.
Two changes to note
1 - We decided to make a change in the first-column heading to "Month #." It could just be "Number" or "Payment" or any number of other possible headings. It merely labels payments by number from one to the last payment.
2 - We formatted the column of "Interest" amounts as currency. Compare the image at left to the way the interest numbers look in the illustration above.
Whatever you do, never make the mistake of simply typing in the numbers in the illustration. (that will mean you don't understand the concept, not a good grade).
The only numbers you should ever supply to a spreadsheet are the starting values. Calculated cells create all the other numbers you see here.
Once you have this spreadsheet working like the example your final assignment, the one you turn in, will use a set of different starting values
Change Principal to $15,000
Change Interest Rate to 6.5%
Change Term (in Years) to 6
You should see the final values change right away.
Hint: You will need to adjust the pull-down from the bottom of the list of payment lines, in order to get the last line to show $0.00. Just repeat the process you've already used. Review the direction above if you need.
At 6 years of monthly payments instead of 4 years you will have more payments. 48 * 1.5 = 72 months (which means 72 payments to pay off the loan)
A Few YouTube links for Microsoft Excel (a starter list only)
Just Google for "Microsoft Excel tutorial"
Excell 2016 tutorial
Excel 2016 Tutorial: Learn Excel in 30 Minutes - Just Right for your New Job Application
Excel Tutorial | 20 Tricks & Shortcuts That Can Make Anyone An Excel Expert