CS130/230 Lecture 2
Functions, Amortization and
What-If Analysis
Friday, January 9, 2004
Save or print a pdf version of this document
Functions are special routines provided by Excel to do simple and complex calculations. There is no way we can cover all of the functions, but we will look at several which will make life much simpler and the rest you can discover on a need basis.
Question: Where do we go to get a feel for the functions that exist in Excel?
The basic syntax required to invoke a function is carried over from algebra and includes the following:
- The function name
- A left parenthesis
- A list of arguments where each argument is separated by a comma
- A right parenthesis
In general, we are looking at something of the form: name(arg1, arg2,...)
As an example, if we have a worksheet that has values in cells A1 to A5 that we want to sum up, we can do this in a couple of ways:
In case 2) above, the name of the function is sum and the argument is A1:A5.
The colon (:) between the cell references indicates a range. So, A1:A5 means A1, A2, A3, A4 and A5.
As you can imagine, this function is invaluable if you want to sum up the contents of 100 cells in a column.
Notice that Excel is not case-sensitive with names of functions or cells.
Question: What does case-sensitive mean?
A couple of other things to note:
- There can be up to 30 arguments in a function. These depend on the function itself, and some of the arguments can be optional.
- Cell entries have a 255 character limit
- The number and type of arguments depend on the function being used
We know of the following types of arguments for various functions:
- literal numbers
- text
- ranges
- other functions
- arrays
- logical values
Excel has over 350 built-in functions divided into 10 categories. There are really two different ways to enter functions into a cell:
Two incredibly useful functions are INT and MOD. You may or may not have heard of these functions, but I would like you to use both of these functions in a worksheet and then if I call on you, I want you to be able to describe what they do. Also, one of these will be useful in an upcoming project.
As was mentioned above, it is possible to have functions as arguments in other functions.
Question: Can you think of an example of when we would do this?
The financial functions can be isolated in Excel. Simply go to the Paste Function option in the toolbar and select Financial. Let's study the PMT function.
If you don't understand exactly what to enter into the fields, then go to the help feature and Excel will give you the following help info:
Calculates the payment for a loan based on constant payments and a constant interest rate.
PMT(rate,nper,pv,fv,type)
Where,
- Rate is the interest rate for the loan.
- Nper is the total number of payments for the loan.
- Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
- Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
- Type is the number 0 (zero) or 1 and indicates when payments are due.
o Set type equal to 0 or omitted if payments are due at the end of the period
o Set type equal to 1 if payments are due at the beginning of the period
The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.
Tip: To find the total amount paid over the duration of the loan, multiply the returned PMT value by nper.
The following formula returns the monthly payment on a $10,000 loan at an annual rate of 8 percent that you must pay off in 10 months:
PMT(8%/12, 10, 10000) equals -$1,037.03
For the same loan, if payments are due at the beginning of the period, the payment is:
PMT(8%/12, 10, 10000, 0, 1) equals -$1,030.16
The following formula returns the amount someone must pay to you each month if you loan that person $5,000 at 12 percent and want to be paid back in five months:
PMT(12%/12, 5, -5000) equals $1,030.20
You can use PMT to determine payments to annuities other than loans. For example, if you want to save $50,000 in 18 years by saving a constant amount each month, you can use PMT to determine how much you must save. If you assume you'll be able to earn 6 percent interest on your savings, you can use PMT to determine how much to save each month.
PMT(6%/12, 18*12, 0, 50000) equals -$129.08
If you pay $129.08 into a 6 percent savings account every month for 18 years, you will have $50,000.
When purchasing large and expensive objects (such as cars, furniture, boats, etc) most of us cannot afford to pay for them right away. Instead, we take out a loan on the object for a certain interest rate and period and pay it back monthly.
Now, letÕs imagine that you want to purchase a car (BMWÉ.) worth $57,899. The car dealer is ready to grant you a 5-year loan at 9.5% annual interest rate. You must put down 10% of the car price as down payment.
Design an Excel spreadsheet to allow the user the ability to input: (a) The price of the car, (b) the interest rate, (c) the period of the loan in years and computes (d) the amount of down payment, (e) the amount of the loan, (f) the monthly payment of the loan.
A payment schedule is a table that lists all the payment periods along with the monthly payment, and the starting balance and the ending balance.
Returning back to the car that you want to purchase. Before you make up your mind, you would like to see a table of the payment schedule. Specifically, you want to see the payment number, starting balance, monthly interest, ending balance.
Add a payment schedule to your spreadsheet. Your spreadsheet should have columns for (1) payment number, (2) starting balance, (3) monthly payment, (4) monthly interest and (5) ending balance.
Question: How can you be sure that your payment schedule is correct?
Using Excel to scrutinize the impact of changing values in cells that are referenced by a formula in another cell is called what-if analysis.
Let us perform what-if analysis on problem 2.
Question: What if the interest rate was 8%? What would the monthly payment be?
Question: What would the monthly payment be if the loan period was 7 years?
If we know the result that we want a formula to produce then we can use goal seeking to determine the value of a cell on which that formula depends.
YouÕve just realised that itÕs your motherÕs birthday in five weeks time. You have found the ideal gift for $100. You donÕt have a penny on you at the moment, but you work at the local grocery store for 12 hours a week and make $4.50 an hour. Since you have been keeping track of your accounts for the last six months you know that your expenses (food, rent, etc.) generally use up 80% of your wages.
Create an Excel worksheet to work out:
- The total amount of money you will make in 6 weeks
- Your total expenses for six weeks
- The amount of money that you will have left over for the gift.
Unfortunately, the amount of money that you have left over will not be enough for that perfect gift.
Use the (Goal Seek) command from the (Tools) menu to figure out:
- The number of extra hours a week you will need to work to reach your goal
- If you choose not to work any extra hours then you will need to lower the percentage of your salary that you use on expenses. What will this percentage now be?