Excel for Finance | Excel Financial Formulas Cheat Sheet

10.4 min read|Last Updated: November 30th, 2023|Categories: excel|
table of content

As a financial analyst or a business accountant, Excel will be your best friend throughout your career. It has innumerous functions for creating financial formulas, formulating assumptions, capital budgeting, risk analyzing, and preparing financial strategies. However, the basic functions and knowledge of Excel will not be enough for this purpose. That’s when you need to learn more about using Excel for finance. To help you write financial formulas in Excel, there are some frequently used functions that will be practical to start with. 

How is Excel used in finance?

Considering the large number of functions Excel has for different data types, it is fairly easy to use Excel for finance. Whether you are an accountant, investor, or corporation, you can perform different technical calculations and prepare the most complex reports and analyses using Excel. 

What Excel formulas are used in finance?

Here is the list of top 10 Excel functions for financial and accounting uses: 

Future Value (FV)

One important thing to financial planners and investors is the Future Value (FV) of an investment, which is the expected value of the current investment in the future with an assumed growth rate. Knowing the concept of this function, you can easily write the formula in Excel using the FV function. The syntax is as follows: 

=FV (Rate, Nper, [Pmt], PV, [Type])

Regarding writing this formula, you need to know a little more about each value:

  • Rate: as its name indicates, it is the growth rate that is expected in the future. 
  • Nper: Number of periods means how many years or months you want this investment to continue. For example, if you make monthly payments on a four-year loan, Nper should be 4. 
  • [Pmt]:  Payment/period (Payments in each period are entered with a negative number and receipts with a positive number.)
  • PV: Present Value is the current investment money. 
  • [Type]: When the payment is made, its value can be 0 or 1. Since this value is optional, it can be omitted and considered 0. 

Let’s see how this function works in an Example: 

Consider you invested 200$ in 2018; the payment has been made annually with an interest rate of 10%. What will be the Future Value of this investment in 2022? 

The formula is written as follows: 

= FV (10%, 4, 1, – 200)

= FVSCHEDULE (Principal, Schedule)

 

If you've invested 200$ in 2018; with 10% interest rate on the annual payment; the Future value of this investment in 4 years is calculated by FV Function
Figure 1- Example of FV Function in Excel

What if the interest rate changes each year? For example, assume that the growth rate of 2019 is 20%, 2020 is 25%, and 2021 is %30. How is the Future Value of this investment calculated for 2022? That’s when FVSCHEDULE is used. 

FVSCHEDULE is a financial function in Excel that calculates the future value with variable interest rate. The formula is written as follows: 

  • Principal is the present value. 
  • Schedule is the series of interest rates which is selected within a range. 

 

FVSCHEDULE function calculates the future value with variable interest rates. It asks for the present value, and a series of interest rates.
Figure 2- Example of FVSchedule function in Excel.

Present Value (PV)

Calculating the present value is as effortless as finding the future value (FV). The method and the syntax are all the same, and only the financial function changes to PV. The formula is as follows: 

= PV (Rate, Nper, [Pmt], FV, [Type])

Let’s explain it with an example. Assume that the future value of an investment is 400$ in 2024. With a 10% interest rate that has been made yearly for 2 years, how do you expect the PV of this amount to be as of now? 

In this example the future value is 400$. With a yearly 10% interest rate for 2 years, the Present value is calculated 328.84 $ with PV function.
Figure 3- Example of PV Function in Excel

Net Present Value (NPV)

For investors, it’s critical to have a preview of the current value of a future stream of payments, known as Net Present Value (NPV). A positive NPV is a sign that shows the project or investment will have a positive result. NPV is also