Investment bankers, financial analysts, and financial modeling experts all have a few things in common. Besides the often discussed pay and benefits, they also spend a good deal of their working day using Excel. Conducting valuations, modeling risks, creating graphs, and building macros to model investment returns are but a few of the tasks that Excel can help you tackle. However, not every person with a career in finance is an Excel wizard. This article presents the top 5 Excel financial functions that you must know. Master these, and maybe our Excel for finance course, and you will be prepared for any task that is sent your way.
How do you find the future value of an investment that has a constant interest rate and periodic payments? You can use your handy Texas Instrument BA II calculator or you can turn to Excel’s FV function.
Excel’s future value function requires the following inputs:
Future Value Example
An individual invests $1,000 in 2010 with a 5% annual interest rate. The investment is held for 3 years. The equation in Excel is as follows:
USAGE NOTE: When using the FV function the PV input must be negative. Entering PV as a positive figure will result in inaccurate results. In the above example had the PV of $1,000 been positive, as opposed to negative, the output would be ($1,160.78) – clearly not the correct answer.
Let’s assume you know the future value of an investment. How much should you pay for it in today’s dollars? If you were able to use the future value function without issue then Excel’s present value function will be a cake walk.
The present value (PV) function requires the following inputs:
Present Value Example
We know the future value of a given investment is $1,500. The interest rate is 8% annually. We could quickly find the PV as follows:
USAGE NOTE: Similar to the FV function it is necessary to ensure that the FV argument is entered as a negative figure.
The Net Present Value (NPV) function receives a series of cash flows and a discount rate to determine if an investment makes sense. Essentially, NPV discounts each cash inflow and outflow to the present day and then sums the values. If the result comes out positive then the investment should receive further review. If negative, then the investment should be passed.
The net present value (NPV) function requires the following inputs:
Net Present Value Example
We have an investment opportunity that requires an initial cash outflow of -$1,000. The investment is expected to return 8% per year with cash flows as follows:
Year 1: $800
Year 2: $1,250
Year 3: -$150
Should we pursue this investment or take a pass?
Given the NPV is positive we should continue to review this investment opportunity.
USAGE NOTE: Negative values represent payments (cash outflows) and positive values represent revenue (cash inflows).
Loans come in all different shapes and sizes. Auto, home, and student are just a few examples most people are familiar with. If you have a loan with a constant interest rate the PMT function will tell you the amount of your periodic payments.
The PMT function requires the following inputs:
PMT Example
A loan of $10,000 with an interest rate of 5% per year needs to be repaid in 5 years. Payments are monthly. How much is each monthly payment?
USAGE NOTE: The interest rate (rate) and number of period (nper) are assumed to be annualized in our example. To find a monthly rate divide the rate by 12. To get monthly periods multiple nper by 12 (12 months = 1 year). Alternatively, you can enter the rate and nper arguments already in monthly format. Keeping units consistent between rate and nper is necessary to produce an accurate result.
PRO TIP: Looking to calculate only the principal or interest portion of a payment on a loan? Check out the PPMT (principal only) and IPMT (interest only) functions.
The internal rate of return (IRR) is used to estimate the profitability of potential investments. If the IRR is greater than the hurdle rate then the investment may be profitable for a firm. The greater the IRR the greater the potential return on an investment, generally speaking.
The IRR function requires the following inputs:
IRR Example
Let’s assume we have an investment opportunity that requires an initial cash outflow of $100,000. The cash flows are projected as follows:
Year 1: $25,000
Year 2: $50,000
Year 3: $75,000
Year 4: $85,000
Find the IRR.
USAGE NOTE: The initial investment is a cash outflow and is thus entered as a negative figure. Subsequent values can be either positive or negative.
Reached the end yet still hungry for more Excel financial functions? Check out our complete list of functions or our Excel financial modeling courses.