Excel Financial Formulas Quick Guide

June 11, 2014 Excel  No comments

Excel financial formulas allows to quickly and easily process accounting data. Here is a quick guide from Desktop Training Academy for using Excel financial formulas.

Using Financial Formulas in Excel

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

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

Using the Net Present Value Formula

Formula: 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 Formula

Formula: 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 DTA 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. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

email

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>