Excel has several formulas that allow the interaction and calculation of the information inserted in its spreadsheets. The financial functions are an important part of this application, since in a simple way they allow the visualization of rates, terms, payment terms among others. We will now know some of the most used financial functions:
Most common financial functions in Excel
• Future Value
The future value formula is used to return the redemption value of a given application. His sentence is presented as follows:
VF (rate; np; pg; vp; type) where:
- Rate: It is the interest rate per period;
- Nper: It is the total number of payments;
- Pgto: It is the payment made each period. In general this argument is composed of capital and juts, when it is omitted it becomes mandatory to include the argument vp;
- Vp: It is the present value, that is, the total sum corresponding to the present value of a series of future payments. When this argument is omitted it is considered 0 and the inclusion of the argument pgto in this case will be mandatory;
- Type: Always being the number 1 or 0 this argument indicates the due dates of the payments being 0 the end of the period and 1 the beginning of the period.
In this and other financial functions, in the payment argument the representation of deposits in savings and withdrawals will be represented by negative numbers. Here's an example:
• Present value
This function returns the investment value of an investment. His sentence is presented as follows:
VP = (rate; nper; pgto; vf; type).
Note that the arguments are the same as those detailed in the previous function.
This function shows the interest rate of a certain period. His sentence is written as:
RATE = (nper; pgto; vp; vf; type; estimate)
Note that the only argument not yet known is the estimate. This argument is nothing more than your estimate for the rate, if this information is omitted the argument will consider 10%. But in most cases this information is not necessary.
• Number of Periods - NPER
This function returns the number of periods for a given constant interest investment. His sentence is given by:
NPER = (rate; pg; vp; vf; type)
Note that in this case arguments are already known. In the following figure we see an application example in which we want to find out how long we will have an amount of R $ 10.000,00 if we apply R $ 2.000,00 at a rate of 20%:
• Payment - PTO
The PTO function calculates the payment of a loan or financing, for example, based on payments and a constant interest rate. His sentence is given by:
PGTO = (rate; nper; vp; vf; type)
For this function we also notice that the arguments have already been written previously.
In the next figure we will see an example in which you want to know the installment amount for a real estate financing of R $ 70.000,00 in 30 times at a monthly rate of 0,5%.
Want to delve deeper into Excel? Check out our Excel online courses! You will find the right course for your need, from the most basic to the most advanced.
And you, do you have any doubts about Excel's financial formulas? Tell us in the comments below!