How to Make Calculations Using Excel Financial Functions

44
24912
Excel Spreadsheets

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:

Excel Spreadsheets

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:

FF_1

• 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.

FF_2

• Rate

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.

FF_3

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%:

FF_4

• 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%.

FF_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!

Excel Spreadsheets

44 COMMENTS

  1. Good night. I can not find anything that shows the CET of an operation based on the SAC system. every example I see speaks in constant performance which is not the reality of the SAC system. Can you help me?

  2. Hi Alessandra, I find negative flows in consecutive months, which may facilitate calculation. I think you will need to put this annual rate in monthly and then make the calculation of the debt values ​​in their corresponding term according to that rate, using the future value formula probably

  3. Wave, bom dia!

    First of all congratulations for the blog and I would like some help in the NPer function when the flows are different. I'll give you an example exercise:
    One person has accumulated 3 debits: the first with 30 days for 20.000, the second from 10.000 to 60 days and the third from 30.000 to 90 days.
    If she wishes to make the payment in a single installment of 60.000, what is the maximum time that this payment should be made considering a rate of 42,58% pa. Consider the business year 360 days.

    Could you please exemplify how to use in excel or hp?

    Thank you

  4. Hi Alexander, I do not know if I understood very well, but are the plots always the same value? If it is, to find the outstanding balance taking the total amount and subtracting from 40 plots of 2.269. With the rest, you will follow the same procedure. There are automatic ways to create SAC and Price tables to view payments of a certain value

  5. Hi Rafael,
    I begin by thanking attention in the explanations and in the answer to the questions.
    Well I have a doubt here.
    From a 90.850,14 loan carried out on 60 plots of 2.296,09, I found the monthly fee for 1,4815867. After the payment of the 40 portion what would be the balance and how can I compose the composition of these installments? EX .: 60 Parcel - 2.296,09 - Main Value 958,84 - Value Charges 1.337,25
    These values ​​are only exemplary.
    I thank you.

  6. Hi Kelwyn, you can have one cell for the total amount, another for the amount of installments and then a series of columns with the installments according to the dates filled

  7. Hello good afternoon! I saw several formulas and would like something similar to calculate the card's invoice. Showing for future months adding up the parcels of purchases made. Could you give me some hints? Thank you in advance.

  8. Hi Zoraide, you can use the RATE function in Excel to get this result

  9. If I make a financing of R $ 2000,00 in 12 times and the value of the parcel is of R $ 350,95, what is the amount of the rate that is being charged?

  10. Hi Alysson, I do not know what function you're talking about specifically, but overall, if you have a 1,89% rate per month, you'll still need other data depending on the financial function. For example, the PGTO function asks for the present or future value and the number of periods to make its calculation.

  11. HOW CAN I USE THAT FOR ANTICIPATION? USING THE 1.89 RATE% TO THE MONTH

  12. Normally it just takes the interest rate to be in the same pattern of the period, so if your interest rate is 3% am, but you want to put in for days you need to do your conversion

  13. Good night!
    I really liked the financial calculations that the blog teaches in excel.
    The calculations in the excel spreadsheet examples are in the month period.
    How do I calculate in days ???

  14. Make a daily investment simulation of R $ 850.000,00 for 30 days, verifying your final value after this deadline. For a daily and loyalty application with banks, a fee of up to 3% am is applied. The bank effectively pays 80% of the nominal rate. Please note that 20 days must be considered for each application.

  15. I'm forced to take the holidays after 1 year and 11 months and what does it mean if I do not carry the wallet and do not sign any document? Does the company have to prove it together with justice?

  16. the company discounted on the day 10 / 10 / X1 a nominal value of R $ 5.000,00. Knowing that the maturity date of the security is 5 / 11 / X1 and the compound interest rate is 12% pa, what is the value of the rational discount?

  17. Vanessa,

    This is quite relevant in the problem. I will try to help you in the way I interpreted, but I would ask a teacher or the one who formulated this problem.

    Considering that the 3,5% rate affects every 31 days, we need to turn that rate into daily:

    = POWER (1 + 0,035; 1 / 31)

    Then just calculate the future value:

    = VF (power output; 90; 0; -100000) = R $ 110.503,29

  18. So the rate is 3,5% to 31 Days. I also found the information confusing but that's exactly how it is in the statement of the exercise

  19. Good evening! Congratulations on the excellent blog helped me a lot.

    Let's question, a loan of $ 100. 000, 00, for 90 days at an interest rate of 3,5% at 31 days. What is the redemption value of this operation?

  20. Filippo, it worked.
    Brother, I do not know how to thank you. Thank you very much for the effort and sorry for the work.
    It helped a lot.

    Big hug.

  21. I finally found it! Haha ha!

    You have to use the formula: = VP (0,75%; 131; 3810,61; 0; 1)

    The explanation is that at HP, you put the interest rate without the% symbol and it reads with. Therefore 0,75 = 0,75% or 0,0075, which is the interest rate per month.

    I hope I helped!

    Hugs

  22. 131 is for months.
    already the 0,75 I do not know, corresponds to an index that the company that I work informs to use to calculate.
    I'll try to find out.

    well, I'll explain step by step what I do in hp 12c to get the result.

    digit:

    131 n
    0,75 i
    3810.61 pmt
    0 fv

    pv -319549.06 (output already output result)

  23. The rate was 75% same? Is it annual?
    And the 131 periods, are they months or years?

  24. Filippo, thank you.
    Brother, in Excel gave (-) R $ 5.080,82 while in HP12c gave (-) R $ 319.549,06

    Hug

  25. Alexandre, thank you!
    I'll try to help you ... what values ​​did you find in Excel?
    Hugs

  26. Good afternoon, first I want to congratulate you on the blog.
    Well, I've followed the steps I've taken to execute the present value formula.
    At first it was very easy.
    Only when the value appeared, did it differ from the value compared to the value that appears in the HP12C calculation.
    Am I making a mistake in the formula?

    I used 0,75 - nper 131 - pgto 3810,61 - vf 0 - type 0) Note: I used 1 and 0.

    on 319.549,06 hp

    in excel of values ​​that do not beat. can you help me please

  27. I got to see André now,

    I created an extra column with the Present Value. You see, I used a formula to calculate how much that future value would be worth if it were paid today using the interest rate that you had already filled in the spreadsheet.

    Otherwise, the way of deducting the values ​​of the debit balance seemed to me correct

  28. friend ,,, if I send you to the table q montei ,,, table price ,,,, would you have cm take a look ?? the only thing now that I was in the doubt was qto to the balance due, if I should go discounting the already paid or not !!!!

  29. I thought you had already set the price table. If you set the price table it should have a structure similar to this here - https://robsonlsoares.wordpress.com/2012/12/20/tabela-price/

    with portion number, interest, amortization, amount payable (sum of the previous ones) and debit balance. In that case, you would pick up the amount payable and play to present value by adding one more column to that table.

    in the price table the values ​​are already individualized per parcel ok?

  30. can be "ignorance of me", but I could not individualize the values ​​,,,,,

  31. I had understood that, André.

    Simply create a column on the side of your table with the present value heading and use the present value formula.

    By doing this, you will have the present value of each of your plots. If you want you can see individually or together, as you prefer

    Did you understand logic?

  32. in fact, I need to bring to present value only ONE installment !!! and not all, I need to bring to value the value of each of these plots !!! individually!

  33. Hi André,

    to know the value of a portion if you anticipate it, you can use the present value (PV) function, where you will put the data referring to that amount in the future, the number of installments and the interest that exists to analyze the present value her.

    In the case of the total value, I would use the same function, drag it to all portions of the table and see the sum. That would be a simulation of throwing all future values ​​into present values, understand?

  34. I got the following question: I put a table with the following data:

    Value of the EnterpriseR $ 94.033,80EntradaR $ 9.403,38 Financed ValueR $ 84.630,42Prazo120Taxa Interest Monthly1% Initial InstallmentR $ 1.214,20

    Once the Price Table is done, my doubts are as follows: how do I know the amount of a parcel if I want to make an advance payment for some of it?
    I know I should bring it to the initial time, but I could not find a formula for it.
    And how do I find the amount for a full settlement of the amount yet to be paid?
    Is it possible to help me? thank you

LEAVE AN ANSWER

Please, write your comment
Please enter your name