The Most Popular Financial Functions In Excel

Functions in Excel are always something that amazes everyone who uses them. Especially in the financial field, Excel can surprise us. Let’s review the most popular financial functions in Excel with Buffcom.net!

FV Financial Function

The FV function helps calculate the future value of an investment based on a fixed interest rate.

Formula: =FV(rate,nper,pmt,[pv],[type])
Where:

• Rate: Interest rate per period.
• Nper: Total number of payment periods in an annuity.
• Pmt: Payment made each period and does not change over the life of the annuity.
  You can leave out pmt and enter 0. In this case, you must enter the Pv value afterward.
• Pv: Present value, or the amount of a single sum that is worth the same as a series of future payments.
• Type: Type of calculation of interest.
  Enter 0 to calculate interest at the end of each period (default) or 1 to calculate interest at the beginning of each subsequent period.
  Example: You have two options for receiving money:

First, invest 300 million at the current time and receive 500 million after 5 years.

Second, buy a 5-year bond with 300 million at a 12% interest rate per year.
Which option should you choose?

We will enter =FV(12%,5,,,-300,0) and the result is 528.7 million. So clearly option 2 generates more profit than option 1. Note that in the formula, you enter pv as -300 because this is the amount of money you invest at the present time.

PV Financial Function

The PV function helps calculate the present value of a loan or investment based on a fixed interest rate.

Formula: =PV(rate,nper,pmt,[fv],[type])
Where:

• Rate: Interest rate per period.
• Nper: Total number of payment periods in an annuity.
• Pmt: Payment made each period and does not change over the life of the annuity.
  You can leave out pmt and enter 0. In this case, you must enter the Fv value afterward.
• FV: Future value or cash balance you want to receive after the final payment. This is optional, if not available, you can select 0 and skip.
• Type: Interest calculation method

You enter 0 to calculate interest at the end of each period (default) or 1 to calculate interest at the beginning of each next period.

• Example: If you want to receive 100 million VND after 3 years by depositing at a bank with an interest rate of 10%/year.

How much money do you need to invest now?

You will need to enter: =PV(10%;3;0;100;0) then you will get the result that you need to invest about 75.13 million VND. The result is negative, implying the amount of money you need to invest for saving.

The NPV Financial Function

The NPV function helps to calculate the present value of an investment by using a discount rate and a series of future cash flows (negative values) and income (positive values).

Formula: =NPV(rate,value1,[value2],…)
Where:

Rate: The discount rate for the entire period.
Value1 is required, while the following values are optional. Value1, value2, etc. must have equal time intervals and occur at the end of each period.
Example: You have a capital of 500 million and plan to invest in a project with revenue over 3 years of 100, 200, and 300 million respectively. Should you invest in this project?
We enter: =NPV(6%;100;200;300) and get a result of 524.22 million. Therefore, we should invest because this amount is greater than 500 million.

The IRR Financial Function

The IRR function helps to calculate the internal rate of return (IRR) for a series of cash flows.

Formula: =IRR(value1,[guess])
Where:

• Values: A range of cells or references to cells that contain the numbers for which you want to calculate the IRR. You can input by selecting those cells.
• Guess: A number that you guess is close to the IRR result.
  Example: You have a capital of 500 million and plan to invest in a project with revenue over 3 years of 100, 200, and 300 million respectively. Should you invest in this project?
  We enter into cells A1 to A4, -500 (investment amount), 100, 200, 300 respectively. In cell A5, we enter =IRR(A1:A4;10%) and get a result of 8.21%. When compared to the IRR of other projects, if it is higher, we should invest.

Summary of Financial Functions

Financial functions are a fundamental group of functions that any finance or accounting candidate should know. We hope this article has provided useful Excel function knowledge for you.

In addition, if your job is closely related to Excel but the software you are using does not meet your needs, contact Buffcom.net for support!