Download
1 / 18
180 likes | 278 Views
Computer Science & Engineering 2111. Lecture 6 Financial Functions. Financial Functions. Functions that can be used to calculate values based on compounded interest Taking a loan Investing in a savings account. Simple Interest vs. Compound Interest.
E N D
Computer Science & Engineering 2111 Lecture 6 Financial Functions CSE 2111 Lecture 6-Financial Functions
Financial Functions • Functions that can be used to calculate values based on compounded interest • Taking a loan • Investing in a savings account CSE 2111 Lecture 6-Financial Functions
Simple Interest vs. Compound Interest • Simple interest always calculates interest based on the original amount. So$1,000 at 4% per year for 2 years • Year 1:$1000 * 4% $40 in interest for the 1st year. • Year 2:$1000 * 4% $40 in interest for the 2nd year. After 2 years you would have: $1,000 * 4% = $80 interest For a total of $1,080 CSE 2111 Lecture 6-Financial Functions
Simple Interest vs. Compound Interest • Compound interest always calculates interest based on the “latest amount”. So$1,000 at 4% per year for 2 years compounded Yearly • Year 1:$1,000 * 4% $40 in interest for the 1st year. • Year 2:$1,040 * 4% $41.60 in interest for the 2nd year. After 2 years you would have: $1,000 * 4% = $81.60 interest For a total of $1,081.60 CSE 2111 Lecture 6-Financial Functions
Compounding Periods • Compounded Yearly • Compounded Quarterly • Compounded Semi-Annually • Compounded Monthly • The total amount of your financial transaction will be different based on when the interest is compounded. CSE 2111 Lecture 6-Financial Functions
Compounding Interest Quarterly What if we compound our 4% interest quarterly for the $1,000. This would be four separate calculations CSE 2111 Lecture 6-Financial Functions
Financial Functions • Present 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 transaction • Payment (PMT) • Payment made each period. It remains constant over life of annuity • RATE • Interest rate per period • NPER • Number of payment periods CSE 2111 Lecture 6-Financial Functions
Financial Functions-Syntax =PV(rate, nper, pmt, [fv], [type]) =FV(rate, nper, pmt, [pv], [type]) =PMT(rate, nper, pv, [fv], [type]) =RATE(nper, pmt, pv, [fv], [type], [guess])*Compounding Periods =NPER(rate, pmt, pv, [fv], [type]) / Compounding Periods CSE 2111 Lecture 6-Financial Functions
Arguments in Financial Functions CSE 2111 Lecture 6-Financial Functions
Using Financial Functions Arguments • Use consistent signs • Outgoing 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) CSE 2111 Lecture 6-Financial Functions
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) CSE 2111 Lecture 6-Financial Functions
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 Note: Divide the function by the number of compounding periods to calculate the number of years for the annuity CSE 2111 Lecture 6-Financial Functions
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 Note: Multiply the function by the number of compounding periods to calculate the annual interest rate CSE 2111 Lecture 6-Financial Functions
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) CSE 2111 Lecture 6-Financial Functions
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) CSE 2111 Lecture 6-Financial Functions
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 . CSE 2111 Lecture 6-Financial Functions
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. CSE 2111 Lecture 6-Financial Functions
