1 / 13

Chapter 13

Chapter 13. Creating Formulas for Financial Applications. Microsoft Office Excel 2003. Using Financial Functions. Excel’s financial functions enable you to confidently use complex financial formulas in your worksheets.

rico
Download Presentation

Chapter 13

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. Chapter 13 Creating Formulas for Financial Applications Microsoft Office Excel 2003

  2. Using Financial Functions • Excel’s financial functions enable you to confidently use complex financial formulas in your worksheets. • Some of the more popular functions are devoted to making investment decisions and working with annuity scenarios. • an annuity is a series of equal cash payments that are made over a given period of time, i.e. a mortgage payment.

  3. The Time Value of Money • Involves calculating the value of money in the past, present and future. • Based on the premise that money increases in value over time • interest earned • Depends on your perspective • As a borrower • As a lender

  4. The Time Value of Money • Present Value (PV) – the principal amount (may be positive or negative) • Future Value (FV) – the principal plus interest (may be positive or negative depending on the perspective as a lender or a borrower) • Payment (PMT) – either the principal or principal plus interest. • Interest Rate (IR) – a percentage of the principal, usually expressed on an annual basis. • Period – represents the point in time when interest is paid or earned. • Term – the amount of time of interest.

  5. Loan Calculations • A loan consists of: • The loan amount • The interest rate • The number of payment periods • The periodic payment amount • If you know any three values, you can create a formula to calculate the unknown component.

  6. Financial Function Arguments • rate – the interest rate per period. • If expressed as an annual interest rate, you must divide it by the number of periods. • nper – the total number of payment periods. • per – a particular period; must be <= nper • pmt – the payment made each period; constant value that doesn’t change. • fv – the future value after the last payment is made. • If omitted, it is assumed to be 0 (as with a loan). • type – indicates when payments are due; if omitted, 0 is assumed. • 0 (due at the end of the period) • 1 (due at the beginning of the period)

  7. Financial Functions • PMT() – returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate. • PMT(rate, nper, pv, fv, type) • PPMT() – returns the principal part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate. • PPMT(rate, per, nper, pv, fv, type) • IPMT() – returns the interest part of a loan payment for a given period, assuming constant payment amounts and a fixed interest rate. • IPMT(rate, per, nper, pv, fv, type)

  8. Calculating Present and Future Values (PV and FV) Function Arguments dialog box for the PV function

  9. Calculating Present and Future Values (PV and FV) Entering the PV and FV functions

  10. Calculating Payments (PMT) Function Arguments dialog box for the PMT function

  11. Calculating Payments (PMT) Completing the Functions 650 workbook

  12. Financial Functions • RATE() – returns the periodic interest rate of a loan, given the number of payment periods, the periodic payment amount, and the loan amount. • RATE(nper, pmt, pv, fv, type, guess) • NPER() – returns the number of payment periods for a loan, given the loan’s amount, interest rate, and periodic payment amount. • NPER(rate, pmt, pv, fv, type) • PV() – returns the present value (the original loan amount) for a loan, given the interest rate, the number of periods, and the periodic payment amount. • PV(rate, nper, pmt, fv, type)

  13. Examples • Pg 274-275 – Loan calculation • Pg 275-277 – Credit card payments • Pg 277-278 – Loan amortization schedule • Pg 279-282 – Summarizing loan options using one-way and two-way data tables • Pg 282-283 – Calculating a loan with irregular payments • Pg 283-290 – Investment calculations • Pg 290-293 – Depreciation calculations

More Related