Financial Functions

Microsoft Excel provides a series of functions to perform various types of financial operations. These functions use some common arguments to solve a particular financial calculation. Basically, these functions deal with investments or loan financing. The table below is showing the arguments commonly used for calculation.

Financial Functions Arguments

The PMT Functions

This function is used to find out regular (periodic) payments of loans or investments. The syntax for the PMT function is =PMT(Rate,Nper,PV,FV,Type). In the following example, EMI (Equal Monthly Installment) has to be calculated for a loan of Rs 5000. The term of the loan is four years and the rate of interest is 6% p.a

The PPMT Functions

This function is used to find out the principal amount within an installment of a loan or an investment. The syntax for the PPMT function is =PPMT(Rate,Nper,PV,FV,Type).  Let us find out the principal amount for the first month of the EMI that we calculated in the earlier example.

The IPMT Functions

Similarly, Interest can be calculated through the IPMT function. The syntax of the function is =IPMT(Rate, Per, Nper, Pv, Fv). Say in the above EMI we have to find out the interest charged in a particular period (say for the first month).

The PV Function

PV stands for present value. This is the present equivalent value of the aggregate amount of all the installments received or paid from time to time. Otherwise, it is the initial amount of the loan being taken or the amount borrowed.

The syntax for the PV function is: PV(rate,nper,pmt,fv,type)

The following formula calculates the original loan amount for a 48-month loan that has a monthly payment amount of Rs 117.43

The FV Function

This Function calculates the future value of an investment. The syntax of the function is = FV(rate,nper,pmt,pv,type)

The Rate Function

The Rate () Function is used to calculate the rate of interest applied on a loan or an investment. Its syntax is:

RATE(Nperiods, Payment, PresentValue, FutureValue, PaymentTYpe, Guess)

The Syntax for the RATE function is: =RATE(nper,pmt,pv,fv,type,guess)

The following formulas calculate the annual interest rate for a 48-month loan of Rs 5,000 that has a monthly payment amount of Rs 117.43

The NPER Function

This determines the term or period within which the money is to be repaid. It can also be said to be the period of usage of the money.

The Syntax for the NPER function is =NPER(rate,pmt,pv,fv,type)

The following formula calculates the number of payment periods for a Rs 5000 loan that has a monthly payment amount of Rs 117.43

MATH AND TRIGONOMETRY FUNCTION

MROUND()

This function rounds the value up or down to the desired multiple depending on the situation. The syntax of the function is =MROUND(Number,Multiple). In the following figure, there is a number of bill amounts that have to be rounded off to the nearest multiple of 5. This can be done in the following way as shown in figure

To explain, let us take the first bill amount, i.e, 265.89. MROUND finds the nearest multiple of 5 as 265.