CS130/230 Lecture 4

Financial Functions, Amortization and

What-If Analysis

Thursday, February 12, 2004

Financial Built-in Functions

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:

PMT

Calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax

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

Remarks

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.

Examples

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.

Problem 1.a

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 (say the BMW Z4 roadster) worth $40,900. 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.

Question: How can we calculate the total interest that has been paid on this loan?

Question: How much interest has been paid on this loan?

Payment or Amortization Schedule

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.

Problem 2.b

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[SK1] ?

What-If Analysis and Goal Seeking

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.

Problem 3

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?