1 / 17

Microsoft Excel Financial Functions

Microsoft Excel Financial Functions. Objectives:. Understanding and using Financial Functions the time value of money PV, FV, Rate, NPER, PMT problem solving. Simple Interest vs. Compound Interest. Simple interest always calculates interest based on the original amount.

lars-mendoza
Download Presentation

Microsoft Excel Financial Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Excel Financial Functions Objectives: • Understanding and using Financial Functions • the time value of money • PV, FV, Rate, NPER, PMT • problem solving

  2. 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:$1000 * 4%  $40 in interest for the first year. • Year 2:$1000 * 4%  $40 in interest for the second year. So after 2 years you would have $1000 * 4% *2  $80 interest For a total of $1080

  3. 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

  4. Compounding Interest Quarterly What if we compound our interest quarterly instead of yearly?$1000 at 4% per year compounded quarterly for one year is actually4 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

  5. 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

  6. The Basics • PV: present value, what you get/pay at the beginning of the financial transaction • FV: future value, what you are going to get OR what you will have to pay at theend 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

  7. $100 Loan for 2 Years Compounded Quarterly at 8% per year End FV $0 Beginning PV $100 PMT PMT PMT PMT PMT PMT PMT PMT 2% RATE for each of 8 Quarters $13.65 PMT for each of 8 Quarters Interest RATEper compounding period(8% per yr/4 qtr per year)forNPERperiods (2yrs * 4 Qtr/yr) withPayments PMT($13.65) - In/Out at Equal Intervals

  8. $0 5000 ? $0 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>, <type>) 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) 3% RATE per period

  9. 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)

  10. 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>, <type>) 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 $50 ? $5000 $50 .025% RATE per period for 24 periods

  11. 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 paymentneeded to completely pay off the loan at the end of the 5 years? =PMT(<rate>, <nper>, <pv>, <fv>, <type>) =PMT(B3/B5, B4*B5, B1, B2) : Will your payment be a positive or negative value?

  12. Rate( ): Returns Rate per Period $18,999 for a new Chevy Cruze $2000 down and $350/month For 5 years What is the annual rateof interest of this loan – assuming it is compounded monthly. =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.

  13. 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 NPER( ) : # Payment Periods Remember to get the correct compounding - calculate the number of periods (quarterly) and then convert to years.

  14. The “type” argument: TypeIf 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

  15. 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? Make Payments at the Beginning of Each Quarter: =FV(.03/4, 4*10, -100,0,1) Make Payments at the End of Each Quarter: =FV(.03/4, 4*10, -100,0,0)

  16. Another problem…… Write an Excel formula in cell D4 that can be copied down the column to calculate the monthly payment 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(<rate>, <nper>, <pv>, <fv>, <type>) =PMT(B$1/12, B4*12, A4, -C4)

  17. A Summary of Financial Functions • Financial Function can be used to calculate financial transactions with compound interest. • PV, FV, PMT, NPER, RATE are all dependent on the values of the other four • 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

More Related