Function

In this section we will examine functions and formulas a bit more closely.
Chapter 13 of the book is exceptionally helpful here.
A formula in excel is a mathematical computation entered by the user.
- ```
=q1-1.5*q7
```
A function in excel is a prebuilt computation that is part of excel or an add on package.
- ```
=sum(a1:a10)
```
Formulas and functions can be mixed
- ```
 =average(a1:a10)*3 - min(a1:a10)
```
In general I encourage you to use functions over formulas:
- Debugged
- Generalized
- Uses expert knowledge
Example: monthly payment.
- If I have a loan car for $10,000, an annual interest rate of 4%, I make monthly payments and need to pay this off in 5 years.
- How much must I pay each month?
- From Math 104 (and other places), the monthly payment formula is
- ```
 
         r×P
pmt = --------
      1-(1+r)^-N

      Where 
         P is the principal
	 N is the number of payments
	     for y years
	     at  n payments per year
	     N = n×y
	 r is the periodic interest rate
	     for n payments per year
	     and an annual rate of R
	            R
	        r = -
		    n
```
- In excel, the function is
  - ```
  =pmt(r,N,-P)
```
- Example of Example:
  - Start Excel
  - Enter the following values
    - Notice, when you put 4% in cell B4, excel makes it 0.04
  - Compute r in b6
    - ```
    (=b4/b3)
```
- Compute N in b7
  - ```
  (=b2*b3)
```
  - Use the formula to compute the monthly payment in cell b10
    - ```
    =(b6*b1)/(1-(1+b6)^(-b7))
```
- Note, I could have eliminated one set of ()
  - But I feel it makes it clearer
  - And when in doubt, put the () in the formula.
- Let's do the same computation using the built in function
  - In cell b11
    - =pmt(b6, b7, -b1)
- Just a note, you frequently see this as
  - ```
  =pmt(b4/12, b2*12, -b1)
```
  - Which assumes monthly payments
  - Which is the most common form of payment.
  - Another note, take away the negative sign in the function
    - In cell b12 put
```
=pmt(b6, b7, -b1)
```
Functions frequently take parameters
- This is the input to the function.
- In the payment function
  - There are three required parameters.
  - The first parameter was the periodic interest rate
  - The second was the number of payments
  - The third was the amount financed.
- The order of parameters is important
  - What would happen if we put =pmt(b1, ...)?
- The value is important
  - What would happen if we put =pmt(b4, ...)?
- Required parameters must be present.
- There are also optional parameters
  - These parameters can be provided if you wish.
  - But they also have reasonable default values.
  - Or values that can be assumed in most cases.
- There are two optional parameters for the pmt function.
  - The fourth parameter is the amount in the account after the last payment has been made.
    - The default value is 0.
    - This is reasonable as you probably want the account to be 0 after you have "paid it off"
    - In cell b13 compute the same payment but put a future value of -100
    - ```
    =pmt(b6, b7, -b1, -100)
```
- Why did the monthly payment go up?
- The second optional parameter is the type
  - or when the payment is made
  - A 0 indicates payment in arrears
    - Or the payment is at the end of the payment period.
    - That means you pay interest on all of the money
    - This is the default and it is normal.
  - A 1 indicates that the payment is at the beginning of the payment period.
    - This means you pay the principal and interest on the first day of the period.
    - And you don't have to pay interest on the principal you pay off.
  - In cell B14 compute with a type of 1
    - =pmt(b6, b7, -b1, 0, 1)
    - Notice the 0 in the fourth parameter.
  - Why is this lower than the value in b11?
- I can resist at this point
  - Let's quickly add two more computations
  - In C9,
    - Insert Total Paid
  - In C10
    - Insert
```
=b10*$b$7
```
    - What does this compute?
    - Why the $ in the cell reference $b$7?
    - Are both $ needed
  - Copy this function down from c10 through c14
  - In cell D9
    - Insert Total Cost
  - In cell d10
    - insert
```
=c10-b$1
```
    - What does this compute?
  - Copy this function down from d10 through d14
  - Does this computation tell you anything?
- Some functions take no parameters
  - Put a label Right Now in cell D1
  - In cell e1 put
```
=now()
```
  - in cell e2 put
```
=today()
```
  - What happened to the value in cell E1 when you entered the new function?
  - If you change the format to a more date like one
    - It will still recalculate on every action
    - But you won't have to watch it!
- A few functions take as many parameters as you wish
  - ```
  =sum(p1,p2, ..., pn)
```
  is one of these.
- As we have noted in the past, excel will help you with the parameters
  - - Notice the optional parameters are in []
  - - Notice that it is giving us a clue on how to fill in the field.
    - You can use the arrow keys to select the value if you wish.
  - - Notice the ...
According to Microsoft there are over 400 functions in excel.
- This has been growing with new functions added nearly every release.
- There is no way I expect you to know all of these.
Excel has some tools to assist you with functions.
- On the function toolbar, the insert function button is helpful.
- This is also on the Formulas tab in the Function Library workgroup.
- Go to cell b11 (the first payment function cell)
  - Click on the Insert Function button
  - This brings up the Function Arguments dialog box.
  - Which shows:
  - 1. Which shows the cells and values you have selected.
    2. The final computation value
    3. A description of the function
    4. A description of the current field (and hints)
    5. A further help link
  - Required parameters are in bold.
  - Optional parameters are not in bold.
Click in an empty cell
- And select the Insert Function button
- This brings up the Insert Function dialog box.
- Which provides an interface to finding the function you want.
  - There is a search button
  - Or a category selection button
Or you can go to the Formulas tab
- Where you can browse function categories.