Excel for Finance | Excel Financial Formulas Cheat Sheet

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)

 

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. 

 

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? 

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 a financial function in Excel that can easily calculate this amount. This function actually calculates the net present value of an investment project’s future cash flows using a discount rate. Unlike the PV function in Excel, NPV can be used with variable cash flows. The formula is as follows: 

= NPV (Rate, Value 1, [Value 2], [Value 3]…)

• Rate: Discount rate for a period
• Value 1, [Value 2], [Value 3]…: Positive or negative cash flows, which are considered as payment if it is negative and inflows if it is positive. 

Payment (PMT)

One importance of Excel in finance is its ability to calculate different types of payments. Here you can use the PMT function, which stands for “Payment.” Consider paying a charge to pay back a 1000$ loan in 3 years. This payment has a constant interest rate of 10%. PMT helps you calculate the yearly amount of money you should pay with an interest rate of 10%. Remember, in PMT, everything is constant, from the interest rate to the final payment. 

The formula is as follows: 

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

The function is very easy:

• Rate: the interest rate of a loan 
• Nper: Number of periods you need to make the payment (for example 3 years)
• PV: Present Value, the loan you have withdrawn
• FV: Future Value, which is an optional argument, you can leave it empty so it is considered as “0”.
• Type: Another optional argument that tells if the payment is made.(Suppose you enter 0 in this field or do not enter any number. Payment is made at the end of each period. If you enter 1, the payment will be made at the beginning of each period.)

Example of PMT function in Excel 

In our example, we have a loan for 1000$ today. With an interest rate of 20%, we need to pay back this amount annually for 3 years. How much money should be paid each year? 

 

 

One of the useful financial functions in Excel is IPMT, which stands for Interest Payment. When the interest rate of a loan or investment is constant in all periods, the IPMT function calculates the interest amount of the payment in a given period. 

The formula is as follows: 

=IPMT(rate, per, nper, pv, [fv], [type])

• Rate: interest rate 
• Per:  calculates from 1 to nper that shows the period for which we want to find the interest.
• Nper: payment periods (for example 3 years)
• PV: the present value or the amount you have received as a loan.
• FV: future value, it is either 0 or 1. If you want to omit this argument, input 0. This is an optional argument.
• Type: like FV, this argument is also optional and only has two options, 0 shows the end of the payment period, and 1 shows the payments are due at the start. 

A 50,000 $ loan has been received. You must pay it off with a 5% interest rate in 5 years. The payment should be made monthly. Here is the formula we write to calculate the interest amount in period 1.

In this example, the PMT is 943.56 $, and the IPMT is 208.33 $. It means that 208.33 $ of the monthly amount of 943,56$ is the interest payment for month 1. Since the payment should be done per month and the interest rate is mentioned per year, in this calculation, we divide the interest rate into 12 to find the interest rate per month. 

Principal Payment (PPMT)

Each loan payment consists of two parts: the principal payment and the interest payment. In the previous section, we learned to calculate the interest payment using the IPMT function in Excel. Now we use PPMT to measure the Principal Payment. 

Here is the formula for calculating PPMT in Excel:

= PPMT(Rate, Per, Nper, PV, [FV], [Type])

• Rate: the interest rate per period. (it might need to be divided into Per)
• Per: the period that you need to calculate the principal payment. (for example per month) it can be any digit between 1 to Nper. 
• Nper: number of periods (for example if it’s one year, nper should be 12 months) 
• PV: present value 
• FV: Future Value (optional)
• Type: when the payment is made (optional) 

We can check the Principal Payment in the previous example: 

You need to know that the total amount (PMT) is fixed for all the payments, but the “Principal Payment” and “Interest Payment” are different for each payment. With each payment, a lower amount is applied to the interest payment, and a higher amount is applied to the principal payment. For example, in month 6, the PPMT will get higher, but the IPMT will get lower. Check the result below: 

 

Internal Interest Rate (IRR)

IRR stands for Internal Interest Rate; a method to calculate and measure investment profitability. Measuring IRR index is very useful for writing business plans to find the financial justifiability of a project or comparing the investment profitability. It can also help companies decide whether or not to proceed with a project. The IRR function in Excel returns a percentage, which shows how profitable a project will be. The result can be positive, which shows it is profitable, and negative to indicate the investment is not a good idea. 

Here is the formula for calculating IRR in Excel:

= IRR (Values, [Guess])

• Values: can be an array of values that shows cash flows (it can be negative or positive)
• Guess: what you think IRR should be, it’s an assumption. (optional)

 

 

RATE

When you receive a loan from a bank, you must pay it back gradually. Since the value of money changes over time due to currency depreciation and inflation, you must pay an extra amount to the bank, in addition to the loan amount received. To calculate this amount, the RATE function is used in Excel. This function employs the number of payment periods, loan amount, and the total amount payable for each payment as inputs and returns the interest rate as a percentage. 

Here is the RATE formula: 

=RATE (NPER, PMT, PV, [FV], [Type], [Guess])

• Nper: number of periods 
• PMT: payment for each period 
• PV: present value or the received loan
• FV and Type are optional: arguments that can be omitted. 
• Guess another optional argument that is your assumption of what RATE should be.

Here is an example: 

You have received a loan of 1000$ from the bank. You need to pay the annual amount of 200$ for this loan for up to 6 years. Here is how to calculate the RATE for this loan. 

EFFECT

Financial professionals use the EFFECT function in Excel to calculate the effective annual interest rate. However, to measure this amount you need to have the nominal rate and number of compounding per year. Here is the formula:

=EFFECT (Nominal_Rate, NPERY)

Assume that the nominal interest rate of payment is 15% and the number of compounding per year is 12. Here is how to calculate the EFFECT. 

NOMINAL

The NOMINAL function calculates the nominal interest rate based on the effective interest rate and the number of annual compounding periods. Finding the nominal rate is exactly the opposite of the EFFECT calculation. Here is the formula:  

= NOMINAL (Effect_Rate, NPERY))

Here is an example of the NOMINAL function based on the inputs we had on the EFFECT function. 

 

Bottom Line 

Excel has powerful functions for financial analysis and calculations. We mentioned only 10 of these functions, but you can find them all under the Financial category of the Excel Function list. Excel has made it easier for financial experts to do their job with only a few clicks and inputs. You only need to know which function to use, and Excel will do the rest for you.

Need help with financial reports and calculations? Reach out to our Excel Consultant experts for a quote on our Excel services.