Excel for Finance: Top 5 Financial Functions in Excel

Excel is an indispensable tool for finance professionals, with a wide range of functions that can help analysts and investors make informed decisions about investments, financing, and budgeting.

In this blog post, we’ll take a comprehensive look at the five most popular Excel functions in finance, with fully worked examples and step-by-step instructions for using each function.

1. =SUM: Adding Up Values in a Range of Cells

The =SUM function is used to add up the values in a range of cells. This is a simple but essential function that is commonly used to calculate totals or sums of values in a financial model or budget.

Here’s the syntax for the =SUM function:

=SUM(range of cells)

For example, if you have a range of cells with values in them (e.g. A1:A10), you can use the =SUM function to add up all of those values.

The formula would be =SUM(A1:A10).

Let’s take a look at a fully worked example of the =SUM function in action. Suppose you have a budget with the following expenses:

You can use the =SUM function to add up all of these expenses to get a total budget.

Here’s how you would do it:

1. In cell D2, enter the formula =SUM(B2:B5).
2. Press Enter to see the result: $1,700.

In this example, the =SUM function added up the values in cells B2 through B5 to give a total budget of $1,700.

2. =PMT: Calculating Payment Amounts for a Loan

The =PMT function is used to calculate the payment amount for a loan based on the loan amount, interest rate, and number of payments. This is a common function that is used to calculate the monthly payment for a mortgage or other loan.

Here’s the syntax for the =PMT function:

=PMT(interest rate, number of payments, loan amount)

For example, if you have a 30-year mortgage with a 4% interest rate and a loan amount of $250,000, your monthly payment would be =PMT(0.04/12, 360, 250000), or $1199.10.

Let’s take a look at a fully worked example of the =PMT function in action.

Suppose you have a 5-year car loan with a 3% annual interest rate and a loan amount of $20,000.

You want to know what your monthly payment will be.

Here’s how you would use the =PMT function to calculate it:

1. In cell B2, enter the formula =PMT(0.03/12, 60, 20000).
2. Press Enter to see the result: $363.32.

In this example, the =PMT function calculated the monthly payment for a 5-year car loan with a 3% annual interest rate and a loan amount of $20,000 to be $363.32. While Excel’s PMT function works well for basic mortgage calculations, you’ll need more specialized tools for complex financial products like lifetime mortgages. If you’re trying to wrap your head around lifetime mortgages, check out various lifetime mortgage calculators available on the web. It’ll help you figure out how much equity you can release from your property and get a grip on those tricky interest rates and costs.

3. =FV: Calculating the Future Value of an Investment

The =FV function is instrumental in computing the future value of an investment. It considers the initial investment, a periodic interest rate, and the total number of periods. This function is crucial for estimating the worth of an investment at a specified future date.

Syntax of the =FV Function:

=FV(interest rate, number of periods, payment, present value, type)

Example 1: Savings Account Projection

• Situation: You have a savings account with a 3% annual interest rate. You wish to calculate the amount accumulated in 5 years with a monthly saving of €500.
• Formula: =FV(0.03/12, 60, -500, 0, 0)
• Result: This formula predicts a future value of €32.323.36.

Example Table:

Example 2: Investment Account Forecast

• Situation: Consider an investment account with a 4% annual interest rate. You aim to find its value in 10 years with an annual contribution of $1,000.
• Formula: =FV(0.04, 10, -1000, 0, 0)
• Result: The computed future value is approximately $12,006.11.

Example Table:

In these examples, the =FV function effectively calculates the future value of investments given different scenarios. The first scenario demonstrates a monthly savings plan in a savings account, while the second scenario shows the growth of an annual contribution in an investment account.

4. =NPV: Calculating the Net Present Value of an Investment

The =NPV function is essential for calculating the net present value of an investment or project. It considers a series of cash flows and a specific discount rate to determine the current value of an investment.

Syntax of the =NPV Function:

=NPV(discount rate, value1, value2, ...)

Example 1: Project Investment Analysis

• Situation: A project requires an initial investment of $100,000 with expected returns of $30,000 annually for three years.
• Assumption: The discount rate is 10%.
• Formula: =NPV(0.1, -100000, 30000, 30000, 30000) or you could write it as =NPV(E2, A2:D2) based on the screenshot below.
• Result: The present value of this investment is recalculated.

Example Table 1:

Example 2: Project with $50,000 Initial Investment

• Situation: Initial investment of $50,000, expecting $20,000 returns per year for five years.
• Formula: =NPV(0.08, -50000, 20000, 20000, 20000, 20000, 20000)
• Discount Rate: 8%.
• Recalculated NPV: $27,642.78. This positive NPV suggests that the present value of future cash flows exceeds the initial investment, indicating a potentially profitable project.

Example Table 2:

These recalculated examples provide a more accurate assessment of the net present value for different investment scenarios using the =NPV function.

5. =IRR: Calculating the Internal Rate of Return for an Investment

The =IRR function is used to calculate the internal rate of return for an investment based on a series of cash flows. This function is used to determine the rate of return on an investment or project.

Here’s the syntax for the =IRR function:

=IRR(values, guess)

For example, if you have an investment with the following cash flows: $10,000 in year 1, $15,000 in year 2, $20,000 in year 3, and an initial investment of $30,000, you can use the =IRR function to determine the internal rate of return.

The formula would be =IRR(–30000, 10000, 15000, 20000), which would give you an internal rate of return of 31.7%.

Let’s take a look at a fully worked example of the =IRR function in action.

Suppose you have an investment with the following cash flows: $5,000 in year 1, $10,000 in year 2, $15,000 in year 3, $20,000 in year 4, and an initial investment of $25,000.

You want to determine the internal rate of return on this investment.

Here’s how you would use the =IRR function to calculate it:

1. In cell B2, enter the formula =IRR(-25000, 5000, 10000, 15000, 20000).
2. Press Enter to see the result: 23.2%.

In this example, the =IRR function calculated the internal rate of return on an investment with the specified cash flows and initial investment to be 23.2%.

These are the five most popular Excel functions in finance, and they are all incredibly useful tools for analysts and investors. Whether you’re calculating loan payments, projecting future investment values, determining the present value of a series of cash flows, or calculating the internal rate of return on an investment, these functions can help you make informed decisions and optimize your financial strategies.

We hope this blog post has been helpful in introducing you to these popular Excel functions and providing fully worked examples and step-by-step instructions for using them. With a little practice and a lot of problem-solving, you’ll be a pro at using Excel for finance in no time!

References

1. Microsoft Office Support: “Excel functions (by category)” https://support.office.com/en-us/article/excel-functions-by-category-bb942a8b-7771-4a97-b07b-c8e1c20f6b57
2. Investopedia: “Net Present Value (NPV)” https://www.investopedia.com/terms/n/npv.asp
3. Corporate Finance Institute: “Internal Rate of Return (IRR)”

Check out our courses on:

• Microsoft Excel: Proficiency in the Basics
• Microsoft Excel: Advanced Business Application
• Microsoft Excel: Spreadsheet Know How
• Microsoft Excel: Formatting Data