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
White Papers
Provided by Anaplan
Webinars
Provided by Insightsoftware
Further Reading
A big reason employers are still allowing hybrid work is business benefits like reduced operating costs. But it’s also harder to ensu...
Companies are now complying with the Securities & Exchange Commission’s (SEC) cybersecurity breach rule. Or maybe over-complying ...
In the world of data, raw numbers are just the beginning. The real power lies in turning those numbers into actionable insights. While an E...
CFOs hate to hear about poor financial literacy among workers, especially the folks they count as friends. The latest economic news on that...
Exporting ERP data into Excel and manually building financial reporting processes and reports is costing your team more than just time. Man...
Sales and use tax shouldn’t just be the responsibility of your A/P and A/R departments. Although they’re the ones who are ...
