Useful Excel formulas for financial control

There are hundreds of online financial planners. All of them are easy to use, but are limited in functionality. MS Excel on their background - a real processor. In it there are 53 financial formulas for all occasions, and for monitoring and budget planning is useful to know three of them.

PMT function

One of the most urgent functions, which can be used to calculate the amount of the payment on the loan with annuity payments, that is, when the loan is paid in equal installments. Full description of the function here.

PMT (rate; nper; ps; BS; type of)

• Rate - the interest rate on the loan.
• nper - the total number of payments on the loan.
• ps - cast to present value, or the total amount, which is currently equivalent to a series of future payments, also known as the principal amount.
• Bs - the desired future value or balance after the final payment. If the argument "bs" is omitted, it is set equal to 0 (zero), r. F. for a loan, for example, the value of "BS" is 0.
• Type (optional) - the number 0 (zero) if you need to pay at the end of the period, or 1 if you need to pay at the beginning of the period.
  instagram viewer

RATE function

It calculates the interest rate on a loan or investment based on estimated future costs. Full description of the function here.

RATE (nper; pmt; ps; BS; type of; forecast)

• nper - The total number of payment periods for an annual payment.
• pmt - payments made in each period; this value can not be changed during the entire period of payments. Usually the argument "PMT" consists of a basic payment and the payment of interest, but does not include other taxes and fees. If omitted, the argument of "ps" is a must.
• ps - The above (current) value, ie.. the total amount, which is currently equivalent to a series of future payments.
• FV (optional) - future value, ie.. the desired balance after the final payment. If the argument is "BS" is omitted, it is assumed that it is equal to 0 (for example, the future value of the loan is equal to 0).
• Type (optional) - the number 0 (zero) if you need to pay at the end of the period, or 1 if you need to pay at the beginning of the period.
• Forecast (optional) - the estimated value of the bet. If the argument is "prediction" is omitted, it is assumed that the value is 10%. If RATE function does not converge, try changing the value of the argument "forecast". RATE function usually converges if this argument is between 0 and 1.

EFFECT function

Returns the effective (actual) the annual interest rate, if we are given the nominal annual interest rate and the number of periods in a year, for the compounding interest. Full description of the function here.

EFFECT (ns; nper)

• ns - the nominal interest rate.
• nper - the number of periods in a year, for the compounding interest.

There are many ways simplify and accelerate work to Excel, and we are pleased to extend these lists your advice.