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
Webinars
Provided by Yooz
White Papers
Provided by Anaplan
Further Reading
The IRS is about to put all of the the funding, personnel and technology advantages it now enjoys to use. Count on an upswing in audits of ...
With remote work here to stay, A/P has to work harder to verify crucial information, such as vendor bank account numbers. Unfortunately,...
Believe it or not, there’s still money left on the table for companies that kept workers on their payrolls while struggling to stay a...
You likely work with a good number of departments to get the data you need to run Finance. So you’re bound to see some external mista...
2024 is shaping up as the year of crypto. The trends are unmistakable — and largely positive — for the revolutionary currency. ...
Finance pros know that even one small formula error can cause major reporting headaches. Microsoft Excel includes built-in auditing feature...
