One of the key user groups for excel is the financial community.
And they are well catered for by excel, especially in a set of financial functions that simplify one of their key uses of excel: net present value calculations.
For those that don’t know what that is here’s a simple primer:
Net Present Value
Net Present Value (NPV) is the value of all future cash flows (positive and negative) over the entire life of an investment discounted to the present.
…………………………………
Excel’s Financial Functions
Here, then, are the key excel functions, and what they are used for:
- PV: The present value of a set of cashflow payments given a known interest rate and number of payments
- FV: The final value of a set of cashflow payments given a known interest rate and number of payments
- PMT: The (equal) payments required to be made for a given interest rate, number of payments and present value. Very useful for calculating the regular payments on a loan.
- NPER: Similar to PMT but returns the number of payments required given the payment amount.
- RATE: Gives the interest (or discount) rate for a set of cashflows when the number of payments and present value are known.
Here’s more on how these functions work:
Financial Functions
To illustrate Excel’s most popular financial functions, we consider a loan with monthly payments, an annual interest rate of 6%, a 20-year duration, a present value of $150,000 (amount borrowed) and a future value of 0 (that’s what you hope to achieve when you pay off a loan).