Master Financial Functions in MS Excel

Excel financial formulas allow users to process accounting data quickly and easily.

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’ll 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 beneath the results.

Formulas for interest, cash flow & a lot more

Excel provides a wide range of formulas for finance pros. Here are what the abbreviations stand for:

• 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
• DD: fixed-Declining Balance
• DDB: Double-Declining Balance
• SLN: Straight-Line depreciation
• SYD: Sum-of-Years’ Digits
• VDB: Variable Declining Balance

Estimating the future value of an investment

Excel remains the preferred spreadsheet tool for financial analysis and forecasting. One of its most popular financial function is estimating the future value (FV) of an asset or investment such as an annuity.

To quickly find the FV, enter data for:

• Rate: the interest Rate per period
• Nper: total Number of payment Periods in an annuity
• Pmt: the Payment made each period (you MUST enter a negative number)
• PV: Present Value (you MUST enter a negative number or Excel will assume the PV is 0)
• Type: indicates when payments are due. To indicate payments are due at the end of the period, enter 0. To indicate payments are due at the beginning of the period, enter 1