 Excel Financial Formulas Quick Guide
June 11, 2014 Excel

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: FixedDeclining Balance
 DDB: DoubleDeclining Balance
 SLN: StraightLine Depreciation
 SYD: SumofYears’ 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 indepth training check out our upcoming instructorled, 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
Leave a reply