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 >,
, 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 >,
.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(
-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