1 / 21

Excel Functions for Financial and Statistical Analysis

Learn how to use PMT, FV functions, decision-making with IF, statistical functions, and more in Excel for financial and statistical analysis.

kalia-houston
Télécharger la présentation

Excel Functions for Financial and Statistical Analysis

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 Office 2003 Excel Functions By Hani Almohair

  2. Objectives • Describe the PMT (payment) and FV (future value) functions • Use the PMT and FV functions • Use the IF function to help in the decision-making process: provide decision making • Define and use the absolute and mixed reference • Use the Statistical functions , AVERAGE, MAX, MIN, COUNT and COUNTA functions • Use the logical functions AND & OR

  3. Overview • Learn to use spreadsheets as a tool in decision making • Use financial functions • Use statistical functions • Use absolute, relative, and mixed cell references

  4. Analysis of a Car Loan • Can I afford it? • How do I calculate for: • rebates • Down payments • interest rates • the number of payments

  5. Analysis of a Car Loan • Set up a worksheet template with initial conditions • Use a PMT function which requires interest rate/period, number of periods, and amount of loan • Payment amounts and the number of payments are usually expressed in months, while interest rates are annual rates. Divide the interest rate by 12 to come up with a monthly rate • The amount of the loan (present value) should be expressed as a negative number.

  6. PMT Function =PMT(B8/12;B9*12;-B7) A mount of loan ( as a negative amount) Number of periods( 4 years x 12 months/year Interest rate per period ( annual rate divided by 12) Remember : A function is a predefined formula

  7. How Much Money Will I Have at Retirement? • The Future Value (FV) function to return the future value of a series of payments • Use the FV function which requires the expected rate of return, the number of periods, and the investment each period.

  8. FV Function (the rate of return) • Use the FV function which requires the expected rate of return, the number of periods, and the investment each period. Amount at retirement = FV (rate of return, Term, Periodic payment) B14 B16 B15 B17 = FV (A1, A2, -A3)

  9. Inserting a Function • Use the Insert Function command from the Insert menu • Use the list box to select the name of the function • functions categorized by function • Let the Wizard help you enter the arguments

  10. Isolate Your Assumptions • Enter your assumptions (the arguments needed for the function) into cells and use those cells for your arguments • For example, in the PMT function, enter the loan amount, number of payments, and interest rate into cells, then use those cells in the PMT function • Change the values in those cells to test different scenarios • Easier than editing the formula when you want to change on or more of your variables

  11. Getting the most from Excel • Relative versus absolute addressing in a worksheet • Knowing the difference and when to use each when copying makes setting up your worksheet more efficient and more accurate • Mixed references • Either the row or the column is absolute; the other is relative • Don’t forget to isolate your assumptions!!

  12. Relative versus absolute addressing in a worksheet PMT Function =PMT(B8/12;B9*12;-$B$7) A mount of loan ( as a negative amount) Number of periods( 4 years x 12 months/year Interest rate per period ( annual rate divided by 12)

  13. Mixed referencesEither the row or the column is absolute; the other is relative FV function =FV(C$5;$B6;-$D$3) Absolute reference to cell D3 Mixed reference to cell $B6 Mixed reference to cell C$5.

  14. Using Functions in ExcelStatistical Functions • Statistical Functions: MAX,MIN, AVERAGE, COUNT and COUNTA • Use functions instead of arithmetic expressions • IF function enhances decision making • allows for different results based on different conditions

  15. Average Function • =(A1+A2+A3)/3 • =AVERAGE(A1:A3) • =AVERAGE(A1:A3;200) • =AVERAGE(A1:A3;C2) MAX,MIN Functions =MAX(A1:A3) =MIN(A1:A3)

  16. COUNT and COUNTA Functions • =COUNT(A1:A3) Returns the number of numeric entries. • =COUNTA(A1:A3) • Return the number of numeric and text entries.

  17. The IF function • Allows for different results, based on a condition • for example, if you work over forty hours in a week, you will receive overtime pay • Requires three arguments: • a condition, which Excel must be able to evaluate as true or false • a value if true • a value if false • The value if true and value if false may contain additional (nested) IF functions for more complex decisions.

  18. IF functions • Enables decision making to be implemented within a worksheet. • =IF(condition,value-if-true, value-if-false) Value returned for a true condition Value returned for a true condition Condition is either true or false

  19. Operators that used with IF Function Operator Description • = Equal to • <> not equal to • < less than • > Greater than • <= less than or equal to • >= Greater than or equal to

  20. AND & OR • And: return true if all its arguments are true, return false if any argument are false. • OR: returns true if any arguments are true, return false if all arguments are false

  21. Summary • Financial functions (PMT and FV) • Statistical functions (MAX, MIN, AVERAGE, and COUNT) • Decision making functions (IF ) • Isolate and clearly label initial assumptions

More Related