Lesson 7: Functions in Excel

Opening Story

Lesson 7 - Opening story
(3:11 mins, L7 Opening Story Transcript)

Introduction

Like Craig, there are many reasons why you may need to borrow money at some point in your lifetime. A loan can provide funds for you to purchase a home, buy a car, get an education, or start a small business without the delay that would be caused by saving the necessary money. However, planning ahead and saving money prior to a purchase can help us avoid borrowing money unnecessarily.

Our church leaders have counseled us to be wise in how we manage our personal finances and to avoid debt.

“Look to the condition of your finances. Discipline yourself in your purchases, avoiding debt to the extent you can. In most cases, you can avoid debt by managing your resources wisely. If you do incur debt, such as a reasonable amount in order to purchase a modest home or complete your education, work to repay it as quickly as possible and free yourself from bondage. When you have paid your debts and accumulated some savings, you will be prepared for financial storms that may come your way. You will have shelter for your family and peace in your heart.” 1

The Quantitative Reasoning Process can be a great tool to help us follow this advice and manage our resources wisely. Contributing money to a savings account can help us acquire finances we need to make purchases. However, if you do need to borrow money, it is essential that you understand the calculations necessary to make an informed decision. In this lesson we will learn about functions and how they can be used as part of the Quantitative Reasoning Process to make informed decisions about savings and loans.

In the following video, Henry J. Eyring, President of BYU-Idaho shares a personal experience he had in following the council of church leaders to get out of debt.

L07 Eyring
(2:44 mins, L7 Eyring Transcript)

Functions

Mathematical functions are a useful mathematical tool. They provide a way to think about complex situations, such as savings and loans.

A function is a rule that assigns the input value(s) to a single output value.

It is important to understand that a function deals with inputs and outputs. The function itself provides a way to determine which output corresponds with a given input. Here is an analogy to help explain functions:

L07 - Interactive 1: Functions

[This interactive has 6 pages that outline and give practice to functions in Excel.]

Page 1 of 6

At the grocery store, items have barcodes. This is similar to the INPUT of a function.

Page 2 of 6

When you check out of a grocery store, they use a barcode scanner to determine the price. The barcode scanner acts as a FUNCTION.

Page 3 of 6

The scanner reads the barcode and gives a price for the item. The price is the OUTPUT of the function.

Page 4 of 6

Notice that many different inputs can have the same output.

Page 5 of 6

However, any given input has to correspond to a single output. Imagine the confusion of the scanner gave several possible prices for one barcode!

[End of activity.]

Although functions always assign a single output once the input has been chosen, it is possible for a function to have several inputs. For example, the Body Mass Index (BMI) is used to determine if an individual is has healthy body weight. The BMI is calculated using a function with two inputs. If you put your body weight and height into the function as inputs, the output is your BMI.

“This” is a function of “That”

In English, we often say that one thing is a function of something else. Or one variable depends on another variable. These phrases relate to the mathematical concept of a function. For example, suppose someone said one of the following:

• “How well you do on the test is a function of how much time you spend studying.”
• “Your starting salary when you graduate depends on your major.”
• “The price of a used car depends on the make and model of the car, as well as its age and condition.”
• “Your monthly cash flow is a function of your income and expenses.”
• “The amount you can borrow for the purchase of a home is a function of your net monthly income.”
• “Your Body Mass Index (BMI) depends on your height and weight.”
• “What you get out of sacrament meeting is a function of the amount of preparation you do.”

These statements all correspond to the mathematical definition of a function. Each situation includes at least one input and an output. The inputs and outputs of each of these statements are summarized in the following table:

L07 - Interactive 2: “This” is a function of “That”

[This interactive has 3 pages that outline the factor “this is a function of that.”]

Function Notation

In addition to knowing the inputs and outputs, you need to know the rule associated with each function. In mathematics, we use function notation to write the rules for functions. For example:

This function is named ff. The input is xx and the rule to find the output is 5x−25x−2. If we used an input of x=1x=1, we would get

We write this as f(1)=3f(1)=3. If we used an input of x=2x=2, we would get

This would be written as f(2)=8f(2)=8. And so on.

We could use this method to list some of the inputs and outputs for this function.

From the table we see that f(4)=18. This means if there is an input of 4, the output is 18.

L07 - Interactive 3: Function Notation

[This interactive has 1 page that outlines function notation.]

 [End of activity.]

Examples of Functions

Check Your Understanding

L07 - Interactive 4:Check Your Understanding

[This interactive has 1 page that gives practice for functions in Excel.]

[End of activity.]

Functions in Excel

Throughout the semester we have been using Excel to do computations. Excel uses the function concept to carry out these calculations. Open Excel and create a spreadsheet that matches the spreadsheet shown in the following screenshot:

In this case we will create a function in Excel with several inputs and a single output. We want the inputs to be: rent, car payment, car insurance, groceries, clothes, and entertainment. We want the output to be Total Expenses.

We could say that the Total Expenses are a function of the amount spent on rent, car payment, car insurance, groceries, clothes, and entertainment.

We can create a function in Excel that will calculate the output for us. In this case, we will use the Sum function. Notice that Excel uses function notation similar to what we would use in mathematics. As shown in the red circle in the following image, we can see that the function f(x) is equal to the sum of the entries in cells B2 through B7. The inputs of the function are $350, $125, $30, $150, $40 and $40. The rule used for this function is a sum, so we add these numbers together to get the output: $735.00.

This semester we have already seen several other functions that are built into Excel. For example, we have computed the average, the minimum, the maximum, and the standard deviation. These are all examples of functions that are built into Excel.

One purpose in learning about functions is to help us better understand loan calculations. Excel has several built-in functions that will be beneficial to us. But in order to use them, we have to understand how to enter the inputs of the function into Excel so it can compute the output.

Some Special Excel Functions

There are a few functions in Excel that will be useful as we work on loan calculations. Some of these are functions we have seen before and some of them are new. Here is a summary of some helpful functions.

Notice for each of these functions the syntax is the same. They always start with an equal sign, which tells Excel you want to use a function. They then have the name of the function, this tells Excel which rule to use for the function. They then have a list of inputs. This allows Excel to use the correct rule for the inputs you give and find the appropriate output. Notice how well this corresponds to our definition of a mathematical function:

A function is a rule that assigns each input value(s) to a unique output value.

Important Note: In Excel, positive and negative signs are used to distinguish between money you get and money you pay. Any money that figuratively leaves your wallet would be entered in Excel as a negative number. Any money figuratively entering your wallet would be entered in Excel as a positive number. Here are some examples:

Savings and Loans

When making a purchase, we generally have two options. We can save the money and pay at the time of purchase; or we can get a loan to pay for the item. For most purchases, it is wise to save the money first. For some very large purchases, such as a home, it is worthwhile to get a loan because it allows us to make the purchase without having to save a large amount of money.

The Future Value Function

The future value (FV) function can be very helpful when you want to find out how much money you will have in an investment (like a savings account or a retirement fund) after a certain amount of time. It computes the balance of the investment account given an interest rate, the number of periods, and the payment.

Example 3 – Savings Account

Anya is saving money for emergencies. After creating a budget, she realizes she can contribute $210 to her savings account every month. Her savings account earns 0.95% interest annually. How much money will she have in her emergency savings account after 5 years?

Solution

In this situation, Anya needs to know the interest rate, the number of periods, and the monthly payment in order to use the future value (FV) command in Excel.

Because she gives the monthly payment to the bank every month, it should be a negative number (Excel used parentheses to show a negative number in the image below).

After entering the information shown below, she finds that the balance of her savings account will be $12,898.82 after she saves for 5 years.

	A	B
1	Inputs	Amount Spent
2	Interest Rate	0.95%
3	Years	5
4	Payment	$(210.00)
5	Output	=FV(B2/12,B*12,B4)
6	Total Saved	$12,898.82

Example 4 – Retirement Savings

Doug is currently 42 years old and earns $42,000 per year. He just decided to start saving for retirement. He starts contributing 8% of his salary to his 401K retirement account every month. His employer also contributes by paying 4% of Doug’s salary to his 401K account each month. Assuming Doug’s salary stays the same for the rest of his career and that he retires at age 65, how much money will Doug have when he retires if his 401K has an average annual interest rate of 5.2%?

Solution

In order to use the future value (FV) function in Excel to find the future value of his 401K retirement account, Doug needs to know how much he and his employer contributes each month.

Doug contributes 8% of his salary: 0.08×$42000=$3360
His employer contributes 4% of Doug’s salary: 0.04×$42000=$1680
Total annual contribution: $3360+$1680=$5040
Total monthly contribution: $$ \frac{$5040}{12} = $420 $$

See the Excel input below to see how this information was used in Excel to compute the future value of Doug’s retirement account.

Using the formula shown below shows us that Doug will have $222,761.17 when he retires.

Blank Cell	A	B
1	Inputs	Amount Spent
2	Interest Rate	5.20%
3	Years	23
4	Payment	$(420.00)
5	Output	=FV(B2/12,B*12,B4)
6	Total Saved	$222,761.17

The Payment Function

When we do need to borrow money, it is important to make sure that the loan payment amount fits within your monthly budget. The payment function (PMT) in Excel helps determine what the monthly payment would be on a loan. Here are a few examples:

Example 5 – Student Loan

Emilee is an online student who needs to take out a student loan to finish her degree. She needs to borrow $2800 and will pay it back after she graduates. She can pay it off over 10 years with an interest rate of 4.2%. What would her monthly payments be once she graduates?

Solution

The payment function in Excel can help Emilee find her monthly payment. She needs to enter the interest rate, years, and loan amount. Because she wants to know a monthly payment, all inputs in the formula must be in months. We have to divide the annual interest rate by 12 to get a monthly interest rate. We also have to multiply the number of years by 12 to find the number of months.

Using the formula shown below tells us Emilee would pay $28.62 per month to pay off her student loan.

Notice the loan amount is a positive number, because when Emilee gets the loan she will receive that money. But the monthly payment will be negative, because that is money she has to pay each month. Excel uses a negative sign, a red number, or a number in parentheses to indicate a negative value.

	A	B
1	Inputs	Amount Spent
2	Interest Rate	4.20%
3	Years	10
4	Payment	$2,800.00
5	Output	=PMT(B2/12,B3*12,B4)
6	Payment	$28.62

Example 6 – Small Business Example

Andrew is starting a construction company and needs to purchase tools and supplies to begin his business. His calculations show he needs $45,000 to get his business started. He has $9,000 saved to put toward his start-up expenses, so he only needs to borrow $36,000. His bank will give him a 5-year small-business loan with an interest rate of 3.5%. What will his monthly payment be?

Solution

The payment function in Excel would help Andrew answer this question. He needs to input the interest rate, the number of terms, and the total loan amount. Excel will then give the monthly payment as the output.

We entered the following formula into Excel to get the answer. Notice that because we want a monthly payment, we have to divide the annual interest rate by 12 to get a monthly interest rate. We also have to multiply the number of years by 12 to find the number of months.

Notice the loan amount is a positive number and the monthly payment is negative.

Andrew’s monthly payment would be $654.90.

	A	B
1	Inputs	Amount Spent
2	Interest Rate	3.50%
3	Years	5
4	Payment	$36,000.00
5	Output	=PMT(B2/12,B3*12,B4)
6	Payment	$28.62

Check Your Understanding

L07 - Interactive 5: The Payment Function

[This interactive has 1 page that checks your understanding of the payment function in Excel.]

[End of activity.]

Craig’s Car Loan

In the opening video, we saw that Craig needed a loan to purchase a new car. We will use the Quantitative Reasoning Process to find which loan option Craig should choose. As we go through the process we will see the usefulness of the functions in Excel.

Understand the Problem

The real-world problem in this situation is that Craig needs to choose a loan for a new car. The loan is for $5000 with an annual interest rate of 4%. Craig knows he will pay less interest if he finances the car for a shorter time period. But he can only afford to pay up to $100 per month on a car payment. The bank offers 3, 4, 5, 6 or 7 year loans.

Identify Variables & Assumptions

The variables in this situation are:

• His monthly payment: PP
• The length of the loan: nn

His monthly payment depends on the length of the loan. Using the vocabulary of functions we would say that the monthly payment is a function of the length of the loan. The length of the loan is the input and the monthly payment is the output.

We are making the following assumptions:

• The cost of the car is $5000.
• The APR is 4%.
• Craig wants to finance the car for the shortest time period possible.
• Craig can only pay $100 per month for his car payment.

Apply Quantitative Tools

Click on each of the following steps to see Excel calculations that help determine the length of the loan Craig should get.

Excel Instructions

Step 1

We will use the payment function in Excel, so we know our inputs are the interest rate, the number of years, and the loan amount. Our output will be the monthly payment.

Step 2

The rate is 4% per year, we will start by using 3 years as the length of the loan, and the loan amount is $5000. We have to be sure to convert all of our inputs to be in monthly units.

Blank Cell	A	B
1	Inputs	Amount Spent
2	Interest Rate	4%
3	Years	3
4	Payment	$5,000
5	Output
6	Payment	=PMT(B2/12,B3*12,B4)

 

Step 3

This Excel calculation tells us that if Craig gets a 3-year loan his monthly payment would be $147.62. This is more than the $100 that Craig knows he can afford, so he should consider other loan lengths.

Blank Cell	A	B
1	Inputs	Amount Spent
2	Interest Rate	4%
3	Years	3
4	Payment	$5,000
5	Output
6	Payment	=PMT(B2/12,B3*12,B4)

We will look at different inputs for this variable to determine which length of loan to choose.

Using the formulas we entered into Excel you should get the following values as you change the number of years for the loan:

Number of Years	Payment
3	$147.62
4	$112.90
5	$92.08
6	$78.23
7	$68.34

Make an Informed Decision

Since Craig can only afford a $100 loan payment per month, he should choose the 5 year loan with a car payment of $92.08.

Evaluate Your Reasoning

When we reflect on the decision, we should consider whether we have made the best decision. Some people might argue that Craig should finance the car for 7 years because that makes the payment lower. However, we have to realize that means he has to pay interest for a longer period of time, so the entire loan will cost more money. We can find the total cost of the loan by multiplying the payment by 12 (to find out how much you pay per year) and then multiplying that by the number of years the loan is financed. Doing so for this problem tells us the total cost of the loan for each of the 5 options.

This shows if Craig finances the car loan for 7 years he would pay $5740.90 for the loan, but if he finances it for 5 years he will only pay $5524.96. So Craig will save $215.94 by selecting the shortest loan he can afford.

Excel - Absolute and Relative Cell References

One of the benefits of using Excel is that it allows you to use formulas. Additionally, you can copy and paste the formulas to help make it easier to create a useful spreadsheet.

Cell References are used within Excel formulas to refer to information located in another cell in the spreadsheet. There are two types of cell references: relative cell references and absolute cell references.

Relative cell references refer to a cell by giving its row and column (for example: A1). Absolute cell references use dollar signs when referring to a cell (for example: $A$1).

The difference between these two types of cell references is how they behave when they are copied and pasted to another cell. Relative cell references change when they are copied and pasted to a new cell. Absolute cell references remain constant when they are copied and pasted. The following animation demonstrates how relative and absolute cell references work:

L07 - Interactive 6: Absolute and Relative Cell References

[This interactive is an animation to further show absolute and relative cell references in Excel.]

Page 1 of 1

Absolute References (with dollar signs)	Relative References (without dollar signs)

Absolute References (with dollar signs)

Before the animation runs:

A	B
100	=$2*$A$1(or equals absolute two multiply absolute A1)

After the animation runs:

A	B
100	200
	200
	200
	200
	=$2*$A$1(or equals absolute two multiply absolute A1)

Absolute cell references (with dollar signs) remain constant when they are copied and pasted to a new cell.

Absolute References (with dollar signs)	Relative References (without dollar signs)

Relative References (without dollar signs)

Before the animation runs:

A	B
100	=2*A1(or equals absolute two multiply absolute A1)

After the animation runs:

A	B
100	200
200	400
300	600
400	800
500	=2*A1(or equals absolute two multiply absolute A1)

Relative cell references (without dollar signs) change when they are copied and pasted to a new cell.

[An arrow indicates the 100 that has changed to 200, 300, 400, and 500 in column A.]

[End of activity.]

Excel Instructions - Amortization Tables

Now that we know how to use the Payment function in Excel to compute a monthly payment for a loan, we can learn how to create an amortization table. We will continue with the same example used in the previous problem to create an amortization schedule. Remember we are financing a $5000 car for 5 years at 4% interest.

Follow these steps to create an amortization table for Craig’s loan. The spreadsheet was intentionally left very plain in order to make it easier for you to reproduce. The last optional step will give instructions on how to change the colors in order to make the spreadsheet look more professional and interesting.

Step 1

An Amortization Table breaks down your payment for each month and shows how much of the payment goes toward paying the loan principal and how much goes toward interest. We will add to the Excel spreadsheet we have been working on by creating headings:

Step 2

Now, we use formulas to fill in the Month 1 row. Pay attention to the dollar signs. They help the formulas copy correctly. Cell references with dollar signs are absolute cell references and cell references without dollar signs are relative cell references.

Here are the formulas you should type in for the first row:

Cell Number	Formula	Explanation
D5	1	Type the number 1 to indicate Month 1.
E5	=$B$6	The Beginning Balance for Month 1 is the Loan Amount listed in cell B6. The dollar signs indicate an absolute cell reference.
F5	=$B$4/12*E5	Divide the interest rate from cell B4 by 12 to make it a monthly rate, then multiply by beginning balance in cell E5 to compute the interest accrued this month
G5	=$G$3-F5	This subtracts the interest from the monthly payment to determine how much of the payment goes to paying down the loan balance.
H5	=E5-G5	This subtracts the amount of principle paid from the beginning balance to find the monthly ending balance.

 

Step 3

If you entered the formulas for the first row correctly, your spreadsheet should look like this:

Blank Cell	A	B	C	D	E	F	G	H
1	Loan Payment Calculator			Amortization Schedule
2	Inputs					Payment = $92.08
3	Interest Rate	4%		Month	Beginning Balance	To Interest	To Principal	Ending Balance
4	Years	5		1	$5,000	$16.67	$75.42	$4,924.58
5	Loan Amount	$5,000.00
6	Output
7	Payment	$92.08

Step 4

Now we will fill in the formulas for Month 2. After filling in this row, we will be able to copy these formulas to fill in the rest of the table.

Cell Number	Formula	Explanation
D6	=D5+1	This adds 1 to the month number from the previous cell to give us the new month number.
E6	=H5	The ending balance from the previous month becomes this month’s beginning balance.
F6	Copy and paste the formula from cell F5	The formula should become =$B$4/12*E6
G6	Copy and paste the formula from cell G5	The formula should become =$G$3-F6
H6	Copy and paste the formula from cell H5	The formula should become =E6-G6

Note that absolute cell references (the ones with dollar signs) did not change when you copied and pasted them. But the relative cell references (the ones without dollar signs) changed to refer to the new row. This is the difference between absolute and relative cell references.

Step 5

If you entered the formulas for the second row correctly, your spreadsheet should look like this:

Blank Cell	A	B	C	D	E	F	G	H
1	Loan Payment Calculator			Amortization Schedule
2	Inputs					Payment = $92.08
3	Interest Rate	4%		Month	Beginning Balance	To Interest	To Principal	Ending Balance
4	Years	5		1	$5,000	$16.67	$75.42	$4,924.58
5	Loan Amount	$5,000.00		2	$4,924.58	$16.42	$75.67	$4,848.92
6	Output
7	Payment	$92.08

Step 6

Copy the entries in the Month 2 row and paste them in the remaining rows to fill in the rest of the table. The final row should be month 60 because it is a 5-year loan (5 years = 60 months). Only about 11 months are shown in the screenshot, but the table should continue until Month 60. If everything is done correctly, the Ending Balance in Month 60 should be $0.00.

We can use the Amortization Table to find useful information. For example, we see that after 2 years (24 months), Craig will still owe $3118.91 on the loan. We also see that in the first month he paid $16.67 in interest, but in the 24th month he only paid $10.67 in interest. This is because his account balance is lower and he is paying interest on a smaller amount of money.

We could also use the SUM function in Excel to add up the interest payments for all 60 months of the loan. This tells us Craig paid a total of $524.96 in interest for the loan.

Because we used formulas to create the entire table, we could go back and change some of the inputs (interest rate, number of years, and original loan balance) and the entire table would update to reflect the new information.

Lesson Checklist

By the end of this lesson you should be able to do the following:

• Identify relevant variables used in a given model of a real-world situation.
• Identify input and output variables used in a given model of a real-world situation.
• Understand the definition of a function.
• Use function notation to define specific functions.
• Given a function and an input value(s), find the output value.
• Use the payment function in Excel.
• Use the future value function in Excel.
• Create an amortization schedule in Excel.

Optional Resources

The following video gives some good advice from the church on business loans:

• How do I know if I should use a loan to grow my business?

The following videos show the talk about mortgage debt by President Hinckley that was referenced by Henry J. Eyring in the video included in this lesson.

• Link to short video segment (1 min 45 sec)
• Link to entire talk (11 min 21 sec)

© 2020 Brigham Young University - Idaho

1. “Debt”, True to the Faith (2004), p. 48-49↩

[End of Lesson]

Formatted for accessibility by the BYU-Idaho Accessibility Resource Center, January 2021.