Excel is much more than a simple application to make tables, although that is the main use that most of us make. Excel is an application with which we can make mathematical, financial, statistical, search and counting, logic and engineering formulas and much more besides allowing formulas to be linked to other spreadsheets. In this article we are going to show you 5 excel financial formulas that every user should know.
And when we say that every user should know, we say it because with them, we can quickly calculate the interest on a loan, the amortization of capital, the profitability of an investment in different Excel sheets… data that we can know without having to resort to a advisor, advisor that will use precisely Excel to calculate this information.
excel financial formulas
PAYMENT
Thanks to this formula we can easily know the payment of any mortgage with a fixed interest. To do so, we only have to establish the interest rate, the term to be paid in years and the value of the property. The result that it shows us is the amount to pay annually, an amount that we must divide by 12 months to know the monthly amount of the mortgage.
If the annual interest is modified, we can recalculate the amount, modifying the years that remain to be paid and the amount of the mortgage that we have already paid the bank.
=PAYMENT (interest, duration in years, value)
PAYMENT
With this formula, we can quickly know the interest paid on an investment for a certain time, as long as the interest is fixed and based on periodic payments. With this formula, we can quickly know the number of interests that we are going to pay for a fixed-interest loan in order to assess whether it is really worth it.
=PAGOINT(rate, period, type)
YIELD
If we like to invest in the stock market, and we want know the performance of a certain value through the periodic interests that it generates, we can use the RENDTO formula. To use this formula, we must include the maturity, rate, settlement, and frequency.
=RENDTO(settlement, expiration, rate, frequency)
NPER
With this formula, we can know the total number of payments of an investment based on a series of fixed constant payments and a fixed interest. Thanks to this function, we can quickly find out how much we are going to pay on a loan based on interest, as long as it is fixed to get an idea of the total interest.
=NPER(rate, payment, type)
DB
Thanks to this formula, we can know the depreciation of an asset over the fixed time period, using the fixed-balance depreciation method. With this formula we can know in advance how much an object (machine, vehicle, among others) depreciates and thus analyze if it is really worth making the investment.
=DB(cost, value, residual value, period)
Other financial formulas
In addition to these 5 formulas, Excel allows us to know the operation of all the formulas that it makes available to us through the application by simply pressing the acronym FX located just after the position of the cell where we are.
In addition, it includes a search engine that allows us to describe what we want the formula to do to offer us all the formulas that allow us to obtain what we are looking for.
