Excel Formulas for Finance: An Easy Guide

In this article, we will learn 18 frequently used Excel formulas for finance that cover a wide range of financial calculations, allowing you to make more informed decisions. The formulas are based on FV, FVSCHEDULE, PV, NPV, XNPV, IRR, MIRR, XIRR, NPER, PMT, IPMT, PPMT, RATE, EFFECT, NOMINAL, SLN, DB, and SLOPE functions.

From determining the future value of an investment using the FV function to analyze the net present value with the NPV function, these Excel formulas for finance help financial professionals, analysts, and even individual investors.

Whether you’re working on financial forecasting, evaluating investment opportunities, or managing your personal finances, understanding these formulas will help you a lot.

This easy-to-follow guide will provide a concise overview of each formula, explaining its purpose and how to use it effectively. By the end of this article, you’ll have a solid foundation in using Excel for financial analysis.

Download Practice Workbook

Download the practice workbook here.

Excel Formula for Finance.xlsx

18 Frequently Used Excel Formulas for Finance

1. FV Function

First, we will explain the FV function. This function will get us the future value of a certain amount.

Suppose you have taken a loan of $200 at a periodic rate of 8%. The repayment of this loan will be semi-annual. So the semi-annual interest rate is 8%. If it were an annual payment, the rate would have been 16%.

The timespan is 3 years. Now, we can get the future value of this $200 by using the following formula in C9.

=FV(C5,C8,C6,-C7)

Please notice that we have a minus (–) sign before C7. That’s because the loan repayment is a cash outflow. We have to give it away eventually. That’s why, a minus sign is used. You could have not used the minus sign. In that case, your future value would have been negative whatsoever.

2. FVSCHEDULE Function

In the previous example, we have considered a constant periodic interest rate. But this may not be the case always. Your periodic interest rate may vary over time. In such cases, if you want to get the future value of a certain amount, you can use the FVSCHEDULE function.

In the following example, we have taken 3 different periodic interest rates. The repayment is annual here. The formula in C11 is,

=FVSCHEDULE(C5,C8:C10)

3. PV Function

Excel also offers you the option to calculate the present value of a certain future value. The function to be used in such cases is the PV function. The formula in C9 is,

=PV(C5,C8,C6,-C7)

4. NPV Function

NPV stands for Net Present Value. As the name suggests, NPV will get you the net present value of a series of cash flows.

This concept is useful when you are evaluating a project. Let’s assume that you have invested $1800 in a project. The return in the 1st year will be $500 and $1700 in the 2nd year.

To understand whether the project is profitable, you can use the NPV function. A positive NPV value will indicate that the project is profitable. The formula in C9 is

=-C6+NPV(C5,C7:C8)

Note:

The initial investment is a cash outflow. That’s why a minus (–) sign has been used before C6.
The initial investment is not inside the NPV function. Because the initial investment is made at present. On the other hand, the returns are to be received in the future. That’s why they are inside the NPV function.
By default, the first value inside the NPV function (in our case it is C7) is the cash flow after the 1st year, the second value inside the NPV function (in our case it is C8) is the cash flow after the 2nd year, and so on.

5. XNPV Function

An advanced version of the previous NPV function is the XNPV function. In this function, you can input the date of the cash flows and Excel will get you the net present value. For example, the formula to calculate NPV in the following example is

=XNPV(C5,C6:C8,D6:D8)

6. IRR Function

IRR stands for Internal Rate of Return. It is a financial metric used to evaluate the potential profitability of an investment or project. The Internal Rate of Return represents the discount rate at which the net present value (NPV) of the investment becomes zero.

In simpler terms, the IRR is the rate at which an investment breaks even, meaning it neither makes a profit nor incurs a loss. It is the interest rate that makes the present value of the investment’s cash inflows equal to the present value of its cash outflows.

In Excel, you can calculate the IRR using the IRR function if you have the cash flows over time. For example, the formula in C9 is

=IRR(C6:C8)

7. MIRR Function

MIRR (Modified Internal Rate of Return) considers the reinvestment of the net present value of the capital inflows at a rate equal to the firm’s cost of capital.

It gives a more realistic estimation of the profitability of an investment compared to the internal rate of return. The MIRR function considers both the finance and reinvest rates to calculate the modified internal rate of return. For example, the formula in C10 is

=MIRR(C5:C7,C8,C9)

8. XIRR Function

In case you want to specify the dates of the cash flows, you can use the XIRR function. This will get you an additional degree of freedom.

The formula in C9 is

=XIRR(C6:C8,D6:D8)

9. NPER Function

The NPER function is used to calculate the payment period of any loan at a specific rate. For our example, we have calculated the number of periods using the following formula in cell C8.

=NPER(C5,C6,-C7)

The payment period is approximately 5 years. Remember that, these payments include both the principal and interest amount.

10. PMT Function

To calculate each payment against a loan, you can use the PMT function. The formula in cell C9 is

=PMT(C5,C8,-C7)

11. IPMT Function

When you pay a loan, you pay both the principal and the interest. So each payment you make has 2 portions, one for the interest and another for the principal. You can get the portion for the interest using the IPMT function. For example, the interest portion in your first payment after 1 year will be

=IPMT(C5,1,C8,-C7)

Similarly, the interest portion for the rest of the payments can be calculated.

12. PPMT Function

Now, if you want to get the principal portion of any of the payments, you can use the PPMT function.

The formula to calculate the principal portion for the 1st payment is

=PPMT(C5,1,C8,-C7)

Similarly, you will get the principal portion of your subsequent payments.

13. RATE Function

If you want to calculate the periodic interest rate of any loan, you can use the RATE function. The formula in cell C8 is

=RATE(C5,C6,-C7)

14. EFFECT Function

The effective interest rate is the actual interest rate you earn or pay on a loan or investment when you consider how often the loan is compounded. It includes both the original amount of money and any interest you’ve already earned or paid.

It helps you understand the real cost or return of your money over time. It’s different from the advertised interest rate, which doesn’t take into account how often the interest is added to your account.

You can use the EFFECT function to get the effective interest rate. For example, the formula in C7 is

=EFFECT(C5,C6)

15. NOMINAL Function

Now, in case you want to get the nominal interest rate from the effective interest rate, you can use the NOMINAL function. The formula in C7 is

=NOMINAL(C5,C6)

16. SLN Function

Depreciation is an important concept in finance. There are different ways to calculate the depreciation for an asset. One of the simplest ones is the Straight Line Method. In this method, we assume that the asset depreciates linearly.

Excel offers the SLN function to measure the depreciation of an asset. For example, let’s assume the cost of an asset is $10000. The life cycle is 7 years with a salvage value of $100.

The formula in C8 is

=SLN(C5,C7,C6)

17. DB Function

The straight-line method is not a very practical approach to calculate the depreciation of an asset. Hence, we often opt to calculate it using the Double Declining Balance Method. It is an accelerated depreciation method, meaning it assigns a higher depreciation expense in the earlier years of the asset’s life and gradually reduces it over time.

The DB function is frequently used to measure depreciation in such cases. The formula for the depreciation in the 1st year is in cell C8.

=DB(C5,C7,C6,1)

18. SLOPE Function

Now, I will show how you can correlate 2 variables in Excel using the SLOPE function. Here, I have 2 variables, Advertising Expense, and Sales Revenue. I will try to find out how advertising expenses (independent variable) are affecting sales revenue (dependent variable). The formula in cell D11 is

=SLOPE(D5:D9,C5:C9)

The result indicates that there is a positive correlation, and for a change in 1 unit of advertising expense, there is an 8.81 unit change in sales revenue.

Things to Remember

Cash outflows are negative and inflows are positive.
Effective interest rates consider compounding factors.
Slopes illustrate relationships between variables.

Frequently Asked Questions

1. How is the IRR different from the ROI?

Ans: The IRR calculates the rate of return based on the timing and magnitude of cash flows, while the return on investment (ROI) measures the profitability of an investment by comparing the net profit to the initial investment.

2. What is the significance of IRR?

Ans: The significance of IRR is below:

If IRR > Required Rate of Return: The project is potentially profitable and meets the desired return expectations.
If IRR = Required Rate of Return: The project meets the minimum financial criteria but may not provide excess returns.
If IRR < Required Rate of Return: The project may not be financially viable or attractive based on desired profitability standards.

3. Can I combine the DB or SLN function with other Excel functions?

Ans: Yes, you can combine the DB or SLN function with other Excel functions to perform more complex calculations or build depreciation schedules.

Conclusion

In conclusion, this article has provided an overview of 18 frequently used Excel formulas for finance. These Excel formulas for finance cover a wide range of financial calculations and are essential tools for financial professionals, analysts, and individual investors alike. By understanding and utilizing these formulas, you can make more informed decisions regarding financial forecasting, investment evaluation, and personal finance management.

Excel Formulas for Finance: Knowledge Hub

<< Go Back to Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!