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
- Share on
Free Training & Resources
Webinars
Provided by Yooz
Further Reading
Late or slow payments continue to stall progress for today’s finance teams. These delays often stem from large organizations renegotiatin...
Ever get asked, “What will it take to hit our target?” Instead of guessing and checking, you can make Excel do the hard work for you. G...
CFOs hate to hear about poor financial literacy among workers, especially the folks they count as friends. The latest economic news on that...
Interruptions probably derail you daily. It’s this bad: Leaders can face an average of four interruptions from their staff every h...
How you push one staffer’s buttons might not be the best strategy for another staffer. But there are some motivational “Dos” and “D...
In a typical month-end scenario, you are dealing with hundreds or thousands of rows of transactions. Manually inserting rows to calculate t...
