Excel Financial Formulas Quick Guide

Excel Financial Formula-image2Excel financial formulas allows you to quickly and easily process accounting data. Here is a quick guide from Learn Excel Now for using Excel financial formulas.  Using Financial Formulas in ExcelTo use a financial formula in excel, click on the “Formula” tab along the main tool bar. From the function library, click on “Financial” and select the formula you want to use from the drop down menu. After selecting the formula that you want to use, you will be prompted to enter the values needed to calculate the formulas. You can either enter the numeric value directly, or select the cell that contains the value. If you aren’t sure what value you are supposed to enter for a certain part of the formula, click in the empty text box, and the explanation will appear underneath the results. Excel Financial Formula-image1 Formulas for Interest, Cash Flow, Investments, and Annuities

  • FV: Future Value of an investment
  • IPMT: Interest Payment for an investment or loan
  • IRR: Internal Rate of Return
  • NPV: Net Present Value
  • PMT: Periodic Payment for an annuity
  • PPMT: Payment on the Principle for an annuity or loan
  • PV: Present Value of an investment
  • RATE: Interest rate per period

Formulas for Depreciation

  • DD: Fixed-Declining Balance
  • DDB: Double-Declining Balance
  • SLN: Straight-Line Depreciation
  • SYD: Sum-of-Years’ Digits
  • VDB: Variable Declining Balance

Common Financial Formula Examples: Using the Future Value Formula Formula: FV(rate,nper,pmt,pv,type)

  • Rate: the interest rate per period
  • Nper: total number of payment periods in an annuity
  • Pmt: the payment made each period; you MUST enter this number as a negative number
  • PV: present value; you MUST enter this number as a negative number; if you don’t enter a pv it is assumed to be 0
  • Type: indicates when the payments are due (beginning of the period or end of the period); to indicate that payments are due at the end of the period, enter 0 and to indicate that the payments are due at the beginning of the period, enter 1

Excel Financial Formula-image2Using the Net Present Value FormulaFormula: NPV(rate, value#:value#),+cash investments

  • Rate: the rate of discount over the length of one period
  • Value#:Value#: are the periods representing the income
  • Cash investments: cash investments for this project; this must be entered as a negative number

Using the Internal Rate of Return FormulaFormula: IRR(values,guess)

  • Values: a reference to the cells that contain the number for which you want to calculate the IRR; they must contain atleast one positive and one negative value
  • Guess: a number that you guess is close to the IRR; if you don’t enter a guess, it is assumed to be 0.1 or 10%

We here at Learn Excel Now hope you feel comfortable with using Excel financial formulas. It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work.Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.Kevin – Learn Excel Now