Microsoft Excel financial formulas What are they, what are they for and what are all the ones I can use?

The financial formulas will help you obtain certain information related to interest rates and current values ​​of investment projectsas well as taking loans.

To be able to use these variants offered by Microsoft Excelyou need to know what the formulas Y how you should use them correctly so that the amount does not return the word “ERROR()”.

If you want to become an expert in managing loans, investments and securitiesit will be convenient for you to read this article until the end so that you can know exactly what your financial return will be.

What are the financial functions in Excel and what are they used for in my spreadsheets?

Financial functions are used in actuarial mathematics and in calculations in the area of ​​money management.. These functions establish payment periods, an internal rate of return, an interest rate for a given period, a present value and a future value.

In this way, an analysis of investment projects can be carried outeither through the current value or IRR, as well as the convenience of taking a loan at a lower rate can also be studied paying in the first period or not.

List of all Excel financial functions and how to use each of them

You must bear in mind that you will have to know basic notions of what a loan or financial investment is, since in these formulas the interest rate, number of periods, amount of payments, present value, future value, and net worth.

Next, we will teach you some concepts so that you more easily understand the use of formulas:

• An investment project is composed of an interest rate that the investor receives for delivering money to the project, which must be equal to or greater than the existing opportunity cost. Each project receives payments in certain periods, which can be monthly, quarterly or annually.
• current value It is the amount of the investment or loan at the beginning of it or at the time the calculation is made. being the future value the inverse valuation, what is the amount that the project will have at the end of it.
• If you take into account income and expenses that are made in the project can be subtracted from each one to obtain the net value of the investment.

Let us then see how the financial formulas should be used:

RATE.INT in Excel

This function returns the interest rate on an investment which is taken as a whole for a certain value that is traded on the stock exchange, be it stocks or bonds.

It is necessary to establish what will be the settlement to be obtained from the investmentthen you must indicate the date of expiration and also the value that the investment will have. On the other hand, you must enter the type of amortization that the investment will suffer and the basis for the calculation.

LETTER.OF.TEST.EQV.A.BONO in Excel

The argument of this function is to return the return that a bond will have when it is equivalent to a treasury bill. The liquidation date that the bill will have, the expiration of the treasury bill and the discount that will be suffered for carrying out this financial operation must be clarified.

The last terms must be placed as a date format.

LETTER.OF.TES.RENDTO in Excel

When you want to determine what is the yield obtained from a treasury bill this formula should be used.

This function requires that you enter the date on which the will liquidate the letterthen you must express the expiration that this bill has and what is the cost or price of the treasury bill, whose nominal value is established in €100.

LETTER.OF.THE.PRICE.in Excel

This function should be used when you want to obtain the price of a face value of €100 for a treasury bill. Therefore, it is necessary to enter the settlement date, the expiration of the treasury bill and the discount rate applied to this instrument.

DB in Excel

This function should be used when you want to obtain the amount of depreciation that an asset has suffered in a certain period. For this it is necessary that the amortization method is by fixed balance.

It’ll need enter the cost that the asset had at the beginning of the period and then the final or residual value that the asset will have at the end of its life.

In addition to this, it must set the periods in which the asset had a useful life, taking into account that the period that must be entered for calculate depreciation it must be expressed in the same units of measurement that have been used in the life of the asset. That is, if it has been counted as a year, month, week or day.

You will have to take into account if the first month is omitted or not in the depreciation. This is necessary to establish if it is a month past due or not.

SYD in Excel

when used annuities and you want to know the value of the depreciation of a given assetthis formula can be used to set the exact amount in a specific period.

For this it will be necessary to enter the value at the beginning of the period, the remainder at the end of life, the time or the number of periods in which the asset was used. and establish the same units in the period that were used when the asset had a useful life.

SLN in Excel

The formula returns the amount of depreciation suffered by an asset in a given period when a direct method is used. For this it is necessary calculate the original value, the residual amount and the time in which the asset had a useful life.

MIRR in Excel

The internal rate of return is the rate that a certain cash flow has when these amounts were not withdrawn from the investment project.. In this case the function returns the interest rate of periodic cash flows.

Needed enter a table with inflows and outflows of money in constant periods, then we must inform what the interest rate will be financing and reinvestment.

IRR.NO.PER in Excel

In case you need to find the IRR for a flow of cash that is not periodic, as in the previous case, this formula can be used to avoid making mistakes.

It must enter the values ​​and each date in which the income or withdrawal of cash flow occurred and then it will be necessary to estimate a number that is approximate to the IRR.

IRR in Excel

This function is generic and It is used to find the return that an investment in cash has during a certain period.

You must enter the values ​​to be obtained in the period and an aliquot in so much for one to reflect what the investor expects. If it is not established, the system assumes that it is a 10%.

NPV.NO.PER in Excel

The non-periodic annual nominal value is the amount that an investor receives and that is the net of the income and expenses of cash flows. they are not newspapers. The discount rate that will have to be applied to the cash is entered, then the values ​​of the flow and the dates of each of these movements.

NPV in Excel

It is a function similar to the previous one, but in this case the flow of funds is obtained in all the analysis periods. Microsoft Excel is informed of the discount rate and each of the the values, whether income or expenses, which must be spaced by “;” It can be up to 254 as a maximum limit.

NOMINAL.RATE

The nominal rate is the interest earned on yearly way for the placement of a financial instrument, whether banking or stock. For this, it is necessary to have at hand the effective rate and the number of periods that must be expressed in years.

VF.PLAN

As we told you at the beginning of this list, the future value It is the amount that will be obtained at the end of the period of an investment taking into account the payments that are made constantly and periodically and the interest rate.

To achieve this calculation effectively, it is necessary to enter the interest rate being expressed in the same unit of period in which the payments are made. Then, you must write the number of periods that the analysis will have, the payment made in each of these periods and the current value of the investment.

Finally, it must be clarified whether payments are made at the beginning or at the end of each period, that is, at expired month.

RATE

This function returns the interest rate that a cash flow will have, either an investment or a loan in a given period. It is necessary to take into account the current value, the amount of each of the payments to be made and their amount.

It should be clarified if disbursements will be made to month past due or at the beginning of each period.

FV

Future value is the amount an investment will have at the end of time. For this it is necessary to incorporate the interest rate, which must be expressed in the same unit as They will have the numbers of the period, the amount that is made in each payment and the value of the investment at the beginning.

In turn, it must be indicated if the Payments will be held at beginning or end of each period.

GOES

For this function, the present value that a cash flow must have in an investment project is analyzed.. It is necessary to incorporate the amount that the investor will have at the end, the interest rate necessary to discount these amounts, the payment made and the number of times or number of investment periods.

In this function it is also necessary to clarify if it will be carried out by month in arrears or in advance.

MAIN.PAYMENT BETWEEN

This financial function allows to obtain the value of the principal payment that was accumulated from an investment or a loan in two periods. It is necessary to enter the interest rate, the number of periods, the current value of the investment and the future value of the loan.

INT.PAYMENT BETWEEN

As with the previous formula, with this financial function you can calculate the interest payment that exists in an investment between two payments. It is necessary to incorporate the interest rate for the discount, the number of periods, the current value of the investment and the first and last period in order to perform the calculation correctly.

PAGOPRIN

When you want to calculate the capital of an investment that has an interest rate, periods and constant payments, you can use this formula. You must bear in mind that the interest rate is registered annuallywhich means that if you need to express a quarterly period, you will have to divide it into four.

Necessary enter the number of periods in which the constant payments, the period, the current value that the investment will have at the time of calculation and the future value are recorded. In case of omitting it, it will be considered that has no salvage value.

PAYMENT

The interest payment is a formula with which you can obtain the value of the rate that is received when an investment is made.as long as there are constant payments on all orders.

must be entered the percentage of the rate of return, specify the period, enter the number of payments to be received, set the present value of investment and future value.

NPER

In case it is necessary to establish the number of periods necessary to obtain a certain rate with a current or future valueyou will need to use this Microsoft Excel financial formula.

must be entered the interest rate, the amount of payments to be made in each period, the current and future value of the investment that has constant payments as well as the interest rate. Finally, It must be established if the payments will be made at the beginning of each period or at the end.

INT.CASH

this formula calculates the effective annual rate of an investment. In order to work with it, you must enter the data of the nominal rate and the number of payments to be made in the year.

PAYMENT

If what you are looking for is to get the value of payments made in a period for a given financial investment This formula can be used in which the discount rate, the number of constant payments, the present value and the future must be entered.

In addition, it must establish if payments will be made by month in arrears or at the beginning of each month.