Tutorial 9: Working with Financial Tools and Functions

1. Tutorial 9:Working with Financial Toolsand Functions

2. Objectives Work with financial functions to analyze loans and investments Create an amortization schedule Calculate a conditional sum Interpolate and extrapolate a series of values Calculate a depreciation schedule New Perspectives on Microsoft Excel 2010 2 2 2

3. Objectives • Determine a payback period • Calculate a net present value • Calculate an internal rate of return • Trace a formula error to its source New Perspectives on Microsoft Excel 2010

4. Visual Overview New Perspectives on Microsoft Excel 2010

5. Loan and Investment Functions New Perspectives on Microsoft Excel 2010

6. Can be used with either investments or loans Difference is based on direction of cash flow Excel Financial Functions New Perspectives on Microsoft Excel 2010

7. Evaluating Investment Options New Perspectives on Microsoft Excel 2010

8. Calculating a Periodic Payment with the PMT Function • Optional type argument specifies whether payments are made at end (type=0) or beginning (type=1) of each period • Default is type=0 • Interest rate and payment period must use same time unit New Perspectives on Microsoft Excel 2010

9. Calculating a Periodic Payment with the PMT Function Financial functions automatically format calculated values as currency Negative cash flows appear in red within parentheses New Perspectives on Microsoft Excel 2010

10. Calculating a Future Value with the FV Function New Perspectives on Microsoft Excel 2010

11. Calculating an Investment’s Length with the NPER Function • Returns the number of payment periods, not necessarily the number of years New Perspectives on Microsoft Excel 2010

12. Calculating an Investment’s Present Value with the PV Function New Perspectives on Microsoft Excel 2010

13. Calculating an Investment’s Interest Rate with the RATE Function • Value returned is the interest rate per period, not the interest rate per year New Perspectives on Microsoft Excel 2010

14. Working with Loans and Mortgages • Use PMT function to calculate a quarterly loan payment New Perspectives on Microsoft Excel 2010

15. Creating an Amortization Schedule • Amortization schedule specifies how much of each loan payment is devoted toward interest and toward repaying the principal • Principal is the amount of the loan that is still unpaid New Perspectives on Microsoft Excel 2010

16. To calculate the amount of a loan payment devoted to interest and to principal IPMT function returns the amount of a payment that is used to pay the interest on the loan PPMT function calculates the amount used to repay the principal Creating an Amortization Schedule New Perspectives on Microsoft Excel 2010

17. Creating an Amortization Schedule • Initial payment in the amortization schedule New Perspectives on Microsoft Excel 2010

18. Creating an Amortization Schedule • Total amount paid each month doesn’t change, only how that amount is allocated between paying interest and paying off principal New Perspectives on Microsoft Excel 2010

19. Calculating Cumulative Interest and Principal Payments • To calculate cumulative payments on interest and principal • CUMIPMT function calculates the sum of several interest payments • CUMPRINC function calculates the cumulative total of payments made toward the principal New Perspectives on Microsoft Excel 2010

20. Calculating Cumulative Interest and Principal Payments New Perspectives on Microsoft Excel 2010

21. Visual Overview New Perspectives on Microsoft Excel 2010

22. Income Statement and Depreciation New Perspectives on Microsoft Excel 2010

23. Projecting Future Income and Expenses • Income Statement worksheet New Perspectives on Microsoft Excel 2010

24. Exploring Linear and Growth Trends • Linear trend • Values change by a constant amount • Appears as a straight line • Growth trend • Values change by a constant percentage • Appears as a curve; greatest increases occur near end of series New Perspectives on Microsoft Excel 2010

25. Interpolating within a Series of Values • If you know beginning and ending values in a series and whether they constitute a linear or growth trend, AutoFill can fill in missing values New Perspectives on Microsoft Excel 2010

26. Projecting Future Expenses • Gross profit • Difference between sales revenue and cost of goods sold New Perspectives on Microsoft Excel 2010

27. Extrapolating from a Series of Values • Use extrapolation to extend a series from one or more beginning values • Step value represents the amount that each value is increased or multiplied as the series is extended New Perspectives on Microsoft Excel 2010

28. Calculating Depreciation of Assets • Depreciation • Process of allocating original cost of an investment over the years of use • What you need to know to calculate the depreciation of a tangible asset: • Asset’s original cost • Asset’s useful life • Salvage value (value at the end of its useful life) • Rate at which the asset is depreciated over time New Perspectives on Microsoft Excel 2010

29. Depreciation Functions New Perspectives on Microsoft Excel 2010

30. Calculating Depreciation New Perspectives on Microsoft Excel 2010

31. Straight-Line Depreciation • Asset depreciates by equal amounts each year of its lifetime until it reaches the salvage value New Perspectives on Microsoft Excel 2010

32. Declining Balance Depreciation • Asset depreciates by a constant percentage each year • Depreciation value is highest early in its lifetime; also when highest declines occur • As asset loses value, depreciation amounts steadily decrease, though the percentage decrease remains the same • An example of a negative growth trend New Perspectives on Microsoft Excel 2010

33. Declining Balance Depreciation • Asset depreciates more quickly initially under declining balance model than straight-line model New Perspectives on Microsoft Excel 2010

34. Completing the Income Statement • Final income statement projections New Perspectives on Microsoft Excel 2010

35. Visual Overview New Perspectives on Microsoft Excel 2010

36. NPV, IRR , and Auditing New Perspectives on Microsoft Excel 2010

37. Calculating the Payback Period of an Investment • Payback period • Length of time required for an investment to recover its initial cost • Quickly projects the value of an investment • Does not take into account the time value of money New Perspectives on Microsoft Excel 2010

38. Calculating Net Present Value • Time value of money • Money received today is worth more than same amount received later (invest and earn interest) • Rate of return (or discount rate) • Interest rate applied to present funds • Defines time value of money by measuring future dollars in terms of current dollars New Perspectives on Microsoft Excel 2010

39. Calculating Net Present Value • Use PV (present value) function to calculate time value of money under different rates of return • Returns a negative value • Use NPV (net present value) function to determine what would constitute a fair exchange if future payments are not equal • Returns a positive value New Perspectives on Microsoft Excel 2010

40. Choosing a Rate of Return • Related to concept of risk—possibility that entire transaction will fail, resulting in loss of initial investment • Investments with higher risks generally have higher rates of return • At higher rates of return, net present value of investment goes down New Perspectives on Microsoft Excel 2010

41. Calculating the Internal Rate of Return • Internal rate of return (IRR) • Point at which net present value of an investment equals 0 • Forms a basis of comparison between two investments • Investments with higher IRRs are usuallypreferred to those with lower IRRs New Perspectives on Microsoft Excel 2010

42. Calculating the Internal Rate of Return • Use IRR function to calculate internal rate of return for an investment • Like NPV function, it assumes that payments and payoffs occur at evenly spaced intervals • Unlike NPV function, include initial cost of the investment in the values list • Use XNPV and XIRR functions for cash flows that appear at unevenly spaced intervals New Perspectives on Microsoft Excel 2010

43. Auditing a Workbook • Errors will spread throughout a workbook from a precedent cell down through all of its descendents • Use Excel auditing tools to trace an error back to its source New Perspectives on Microsoft Excel 2010

44. Tracing an Error • Error code • Begins with # followed by error name, which indicates type of error • Does not specify where mistake is located • Error indicator (green triangle in upper-left corner of cell) • Flags cells with an error or potential error • Tracer arrows • Provide visual clue to relationship between two cells; point from precedent cell to dependent cell New Perspectives on Microsoft Excel 2010

45. Tracing an Error • Error values traced across the worksheet New Perspectives on Microsoft Excel 2010

46. Tracing an Error • Source of the error value New Perspectives on Microsoft Excel 2010

47. Evaluating a Formula • Use Evaluate Formula dialog box to display value of different parts of the formula or “drill down” through cell references in the formula to discover the source of formula’s value New Perspectives on Microsoft Excel 2010

48. Using the Watch Window • Watch Window • Dialog box that displays values of cells located throughout the workbook • Allows user to view impact of changing a cell’s value on widely scattered dependent cells New Perspectives on Microsoft Excel 2010