Top 5 Financial Functions in Excel

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.

#1: Future Value (FV)

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:

1. Rate – Interest rate per period
2. Nper – Number of periods
3. Pmt – Payment per period
4. PV – Optional. Present value of the future amount.
5. Type – Optional. When the payment is made. 0 or blank indicates end of period. 1 indicates beginning of period.

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.

#2: Present Value (PV)

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:

1. Rate – Interest rate per period
2. Nper – Number of periods
3. Pmt – Payment per period
4. FV – Optional. Future value of the future amount.
5. Type – Optional. When the payment is made. 0 or blank indicates end of period. 1 indicates beginning of period.

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.

#3: Net Present Value (NPV)

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:

1. Rate – Discount rate per period
2. Value1 – Cash flow #1
3. Value2, Value3… – Optional. Additional cash flows

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).

#4: PMT

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:

1. Rate – Interest rate per period
2. Nper – Number of periods
3. PV – Present value of the future amount
4. FV – Optional. Future value of the future amount
5. Type – Optional. When the payment is made. 0 or blank indicates end of period. 1 indicates beginning of period.

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.

#5: Internal Rate of Return (IRR)

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:

1. Values – Cash flows. Can be positive or negative, but must contain at least one of each
2. Guess – Optional. A guess of what the IRR should be

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.