Transcript Document

Microsoft Excel Financial Functions

Objectives:

Understanding and using Financial Functions •the time value of money •PV, FV, Rate, NPER, PMT •problem solving

CS&E 1111 ExFin

Simple Interest vs. Compound Interest

Simple interest always calculates interest based on the original amount.

So $1000 at 4% per year for 2 years 

Year 1:

first year.

$1000

* 4%  $40 in interest for the 

Year 2: $1000

second year.

* 4%  $40 in interest for the So after 2 years you would have $1000 * 4% *2  $80 interest For a total of

$1080 CS&E 1111 ExFin

Simple Interest vs. Compound Interest

Compound interest - always calculate interest based on “

latest amount

” –Year 1:

$1000

at 4%/yr for 1 year is

$40

– Year 2:

$1040

* 4%

=$41.60

so now after 2 years you have

$1081.60

T ot al I nt er es t $82.00

$81.00

$80.00

$79.00

Simple Int erest Si mpl e I nt er es t Compound Int erest Compound I nt er es t

CS&E 1111 ExFin

Compounding Interest Quarterly

What if we compound our interest

quarterly

instead of

yearly

? $1000 at 4% per year compounded quarterly for one year is actually

4 separate calculations

– Each quarter updating the

principa1

and using the rate 1% per quarter.

Principal Interest

1st quarter

$1000.00

*1% =

$10.00

2nd quarter

$1010.00

*1% =

$10.10

3rd quarter

$1020.10

*1% =

$10.201

4th quarter

$1030.301

*1% ≈

$10.30

Total interest for year 1 ≈ $40.60 vs. $40 for simple interest CS&E 1111 ExFin

Financial Functions

 Functions that can be used to calculate values based on compounded interest Taking a loan - Investing in a savings account  The basic financial functions use these 5 basic variables :

PV, FV, RATE, PMT, NPER

 Other functions are also available: NPV, PPMT, IPMT

CS&E 1111 ExFin

    

The Basics

PV : present value, what you get/pay at the beginning of the financial transaction FV end : future value, what you are going to get OR what you will have to pay at the of the financial transaction PMT : payment made each period. It remains constant over life of annuity RATE : interest rate per period NPER : number of payment periods CS&E 1111 ExFin

$100 Loan for 2 Years Compounded Quarterly at 8% per year

Beginning

PV $100 End FV $0

2% RATE for each of 8 Quarters $13.65 PMT for each of 8 Quarters Interest

RATE

per compounding period (8% per yr/4 qtr per year) for

NPER

periods (2yrs * 4 Qtr/yr) with Payments

PMT

($13.65) - In/Out at Equal Intervals

CS&E 1111 ExFin

PV ( ): Present Value - What I have at the

beginning

How much money would I have to set aside now to have a $5000 down payment on a car when I graduate in 2 years ? I plan to put the money in a CD that pays 3% annual interest compounded yearly .

= PV (< rate >, < nper >, < pmt >, < fv >, ) RATE = 3% (per year) – interest per period NPER = 2 (years) – number of periods PMT = 0 (per year) – payment per period FV = $5000 - amount at the end of the transaction =PV( 0.03

, 2 , 0 , 5000 ) $0

?

$0

5000

3% RATE per period CS&E 1111 ExFin

When using Financial Functions remember to..

   Use consistent units of time  RATE per quarter, NPER number of quarters and PMT payment per quarter.

Use consistent signs 

outgoing cash: (- ), incoming cash: (+ )

For arguments that are

zero

at least a comma must be put into the function to maintain the argument order, unless

no other non-zero arguments follow

– then it many be deleted.

=PV(0.03, 2,

0 ,

5000

,0

) same as =PV(0.03, 2,

,

5000) CS&E 1111 ExFin

FV ( ): Future Value - What I have at the end

I plan on depositing $5000 into a CD that pays 3% annual interest compounded monthly . I plan to add an additional $50 each month. How much will I have at the

end

of 2 years ?

= FV (< rate >, < nper >, < pmt >, < pv >, ) RATE = 3%/12

.025% (per month) – interest per period NPER = 2 * 12

(months) – number of periods PMT = -50 (per month) – payment per period PV = -$5000 - amount at the beginning of the transaction = FV ( 0.03/12 , 2*12 , -50 , -5000 ) $50 $5000 $50 $50 ?

.025% RATE per period for 24 periods CS&E 1111 ExFin

PMT( ): Returns the periodic payment

I have been offered a 5 year car loan of $15,000 at 9% annual interest rate compounded monthly. What is the monthly

payment

needed to completely pay off the loan at the end of the 5 years?

= PMT (< rate >, < nper >, < pv >, < fv >, )

:

= PMT ( B3/B5 , B4*B5 , B1 , B2 )

1 2 3 4 5 6 A

Original Loan Value Ending Loan Value Yearly Interest Rate Number of Years Compounding Periods per Year Monthly Payment

B

$ 15,000 $ 9% 5 12 ($311.38)

Will your payment be a positive or negative value?

CS&E 1111 ExFin

Rate( ): Returns Rate per Period

What is the

annual rate of interest

of this loan – assuming it is compounded monthly.

$18,999 for a new Chevy Cruze $2000 down and $350/month For 5 years = RATE ( < nper >, < pmt >, < pv >, < fv >, < type > ) = RATE ( 5*12 , -350 , 18999-2000 ) * 12 months per yr

Remember to get the correct compounding - calculate rate per period (month)

then convert it to rate per year.

CS&E 1111 ExFin

NPER( ) : # Payment Periods

Write an Excel formula to determine how many

years

will it take to save $12,000 if I put $10,000 into a savings account paying 4% annual interest compounded quarterly.

= NPER (< rate >, < pmt >, < pv >, < fv >, < type >) = NPER ( 4%/4 , , -10000, 12000 ) /4 quarters per yr

Remember to get the correct compounding - calculate the number of periods (quarterly) and then convert to years.

CS&E 1111 ExFin

Type

The “type” argument:

If payments are made:

0 (default) At the end of the period 1 At the beginning of the period

Example:  Type 0: You make a car payment to the bank at the

end

of each month to pay down the principal  Type 1: An annuity pays you a set amount each month at the

beginning

of the month

Unless specifically mentioned assume type 0

CS&E 1111 ExFin

The “type” argument:

I have been putting $100 per quarter in the bank for the past 10 years in an effort to save money for my child’s college education. How much money is currently in this account assuming the bank has paid a 3% annual interest rate compounded quarterly? 1 A B

Value in 10 Years

2 3

Payment at Payment at Beginning of End of the the Month $4,679.48

Month $4,644.65

Make Payments at the Beginning of Each Quarter: Make Payments at the End of Each Quarter: =FV(.03/4, 4*10, -100,0, 1 ) =FV(.03/4, 4*10, -100,0, 0 )

CS&E 1111 ExFin

Another problem……

Write an Excel formula in cell D4 that can be

copied

column to calculate the

monthly payment

down the for each of the mortgages listed. The annual interest rate is 4% compounded monthly. Note: A

balloon payment

is an amount due at the end of the loan.

=PMT(, , , , ) = PMT (B $ 1/12, B4*12, A4,

-C4

)

3 4 5 6 7 1 2 A

Interest Rate

B

4%

C D

Loan Amount # Years 100000 100000 30 30 100000 100000 15 15 Balloon Payment 0 10000 0 10000 Monthly Payment ($477.42) ($463.01) ($739.69) ($699.05) CS&E 1111 ExFin

A Summary of Financial Functions

 Financial Function can be used to calculate financial transactions with

compound interest

.

 PV, FV, PMT, NPER, RATE on the values of the other four are all dependent  Use positive values for cash flow back to you, and negative values for cash flow from you to a financial institution..

 Use correct compounding periods for your values of NPER, PMT and RATE.

 Use the correct type argument

CS&E 1111 ExFin