4194 Day 9 Financial Functions 1 Financial Functions Functions that can be used to calculate values based on compounded interest Taking a loan Investing in a savings account 2 Simple Interest vs Compound Interest ID: 449392
Download Presentation The PPT/PDF document "Computer Science & Engineering" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Computer Science & Engineering 4194
Day 9Financial Functions
1Slide2
Financial FunctionsFunctions that can be used to calculate values based on compounded interestTaking a loanInvesting in a savings account2Slide3
Simple Interest vs. Compound InterestSimple interest always calculates interest based on the original amount. So $1,000 at 4% per year for 2 yearsYear 1:
$1000 * 4% $40 in interest for the 1st year.Year 2: $1000 * 4% $40 in interest for the 2
nd year.3
After 2 years you would have:
$1,000 * 4% = $80 interest
For a total of $1,080Slide4
Simple Interest vs. Compound InterestCompound interest always calculates interest based on the “latest amount”. So $1,000 at 4% per year for 2 years compounded YearlyYear 1:
$1,000 * 4% $40 in interest for the 1st year.Year 2: $1,040 * 4% $41.60 in interest for the 2
nd year.4
After 2 years you would have:
$1,000 * 4% = $81.60 interest
For a total of $1,081.60Slide5
Compounding PeriodsCompounded YearlyCompounded Quarterly4 quarters per year and 3 months per quarterCompounded Semi-Annually6 months per half yearCompounded Monthly12 months per yearYou *cannot* assume 30 days per month
The total amount of your financial transaction will be different based on when the interest is compounded.5Slide6
Compounding Interest QuarterlyWhat if we compound our 4% interest quarterly for the $1,000.This would be four separate calculations6
QuarterPrincipal
Interest1st Quarter$1,000 * 1%= $10.002nd
Quarter
$1,010
* 1%
= $10.10
3
rd
Quarter
$1,020.10
* 1%
= $10.201
4
th
Quarter
$1,030.301
* 1%
≈ $10.30Slide7
7Slide8
Financial FunctionsPresent Value (PV)What you get/pay at the beginning of the financial transaction
Future Value (FV)What you are going to get OR what you will have to pay at the end of the financial transactionPayment (PMT)
Payment made each period. It remains constant over life of annuityRATEInterest rate per periodNPERN
umber
of payment periods
8Slide9
Financial Functions-Syntax9
=
PV(rate,
nper
,
pmt
, [
fv
], [type])
=FV(rate,
nper
,
pmt
, [
pv
], [type])
=PMT(rate,
nper
,
pv
, [
fv
], [type])
=RATE(
nper
,
pmt
,
pv
, [
fv
], [type])=NPER(rate, pmt, pv, [fv], [type])
NOTE
: rate,
nper
, and
pmt
*must*
be in the same unit in order for the Excel calculations in the background to work correctly; AND that unit must be the compound unit.Slide10
Arguments in Financial Functions10
ArgumentDescriptionArgument RulesrateInterest
rate per compounding periodnperNumber of compounding periodspmtPeriodic payments to the initial sum
Payment
amount cannot vary
pv
Original value of financial transaction
fv
Value at the end of the financial transaction
type
Designates
when payments are made
0: Payments are made at the end of the period
1:
Payments are made at the beginning of the period
(0 is the default and is implied)Slide11
Using Financial Functions ArgumentsUse consistent signsOutgoing cash ( - )Incoming cash ( + )For arguments that are zero, at least a comma must be put in the function to maintain the argument order, unless no other non-zero arguments follow.=PV(.03, 2, 0, 5000, 0)
same as=PV(.03, 2, , 5000)11Slide12
Write an excel formula in cell D2 to calculate the payment for a loan amount of $15,000 at 9% interest rate for a period of 5 years. Assume the loan is compounded monthly.=PMT(rate, nper, pv, [fv], [type]) ----Returns periodic payment
=PMT(.09/12,5*12,15000,0,0) OR =PMT(.09/12,5*12,15000) 12Slide13
Write an excel formula in cell B2 to determine how many years it will take to save $12,000 if you put $10,000 into a savings account paying 4% annual interest compounded quarterly.=NPER(rate, pmt, pv, [fv], [type]) ----Returns # of Payment periods
=NPER(.04/4,0,-10000,12000,0) /4 OR =NPER(.04/4,,-10000,12000)/4
13
Note:
Divide the function by the number of compounding periods to calculate the number of years for the annuitySlide14
Write an excel formula in cell A2 to calculate the annual interest rate of a new Chevy Cruz. The cost of the car is $18,999, and you will put down $2,000. You will pay $350 per month for five years. The annual interest rate is compounded monthly.=RATE(nper, pmt, pv, [fv], [type]) ----Returns the rate per period
=RATE(5*12,-350,16999,0,0)*12 OR =RATE(5*12,-350,16999)*12
14Note: Multiply the function by the number of compounding periods to calculate the annual interest rateSlide15
Write an excel formula in cell E2 to determine how much money you would have to put into a CD now to have a $5,000 down payment on a car when you graduate in 2 years. The CD pays 3% annual interest rate compounded yearly.=PV(rate, nper, pmt, [fv], [type]) - Returns the present value of an investment=PV(.03,2,0,5000,0)
OR =PV(.03,2,,5000) 15Slide16
Write an excel formula in cell F2 to determine the value of a CD in 2 years. You plan on an initial investment of $5,000 and you will add an additional $50 per month. The CD pays an annual interest rate of 3% compounded monthly.=FV(rate, nper, pmt, [pv], [type]) - Returns the future value of an investment=FV(.03/12,2*12,-50,-5000,0)
OR =FV(.03/12,2*12,-50,-5000) 16Slide17
Write an Excel formula in cell G2 to calculate the monthly mortgage payment for a $100,000 home with a balloon payment of $10,000. The annual interest rate is 4% compounded monthly with a loan duration of 30 years. Note: A balloon payment is an amount due at the end of the loan and is indicated in the fv argument as a negative value .
17Slide18
Five years ago you won $75,000 on the game show, “I Wanna Win A lot of Money”! At that time, you invested the money in a CD that paid 6% per year compounded monthly. Write a formula in cell C9, to determine T/F if you have enough money to purchase a $100,000 house without financing.
18