# Excel Financial Functions

- by Puneet

Contents

sample files

## 1. FV Function

FV function returns the future value of an investment using constant payments and a constant interest rate. In simple words, it will return a future value of an investment where you have constant payments and a constant interest rate throughout the investment period.

### Syntax

FV(rate,nper,pmt,[pv],[type])

### Arguments

• rate: A constant interest rate that you want to use in the calculation.
• nper: Number of payments.
• pmt: A constant payment amount to pay periodically throughout the investment time.
• [pv]: The present value of future payments. It must be entered as a negative value. 0 if omitted.
• [type]: A number to specify when payment is due. 0 = at the end of the period, 1 = at the beginning of the period.

## 2. PMT

PMT function returns a periodic payment of loan which you need to pay. In simple words, it calculates the loan payment based on fixed monthly payments and a constant rate of interest (loan payment based on fixed monthly payments and a constant rate of interest).

### Syntax

PMT(rate, nper, pv, [fv], [type])

### Arguments

• rate: The rate of interest for the loan. This rate of interest should be constant.
• nper: The total number of payments.
• pv: The present value or the total amount of loan.
• [fv]: The future value or the cash balance which you want after the last payment. The default value is 0.
• [type]: Use 0 or 1 to specify the due time of payment. You can use 0 when payment is due at the end of each payment or 1 if payment is due at the start of each period. If you omit to specify the type, it will assume 0.

### Notes

• The amount of payment return by PMT only includes payment and interest but not include taxes and other fees which are related to the loan.
• You have to be sure while specifying the value for rate and nper arguments. If you want to pay monthly installments on a five-year loan at an annual interest rate of 8 percent, use 8%/12 for rate and 5*12 for nper. For annual payments on the same loan, use 8 percent for rate and 5 for nper.

### Example

Let’s say you want to take a 20 years mortgage loan for \$250000 by assuming 2.5% as an interest rate. Now here we can use PMT to calculate your monthly installments.

In the above calculation, we have converted the annual interest rate into monthly by dividing with 12 and years into months by multiplying with 12.

But we have not mentioned any future value, and the payment type is a default. So as a result, we have got a negative value, because the amount of \$987.80 is what we have to pay every month for 30 years.

## 3. PV

PV Function returns the present value of a financial investment or a loan. In simple words, with the PV function you can calculate the present value of an investment or a loan where you can check is that.

### Syntax

PV(rate, nper, pmt, [fv], [type])

### Arguments

• rate: The rate of interest for the payment of the loan.
• nper: Total number of payment periods
• pmt: A constant amount of payment you have to make after every period.
• [FV]: The future value or a cash balance of a loan or investment you want to attain after the last payment is made. If omitted, it will be assumed as 0.
• [type]: Time of the payment. Beginning of the period (use “0”) or the end of the period (Use “1”).

### Notes

1. The units you use as arguments should be consistent. For example, If you are using periods in months (36 Months = 3 Years) then you have to convert the annual interest rate into a monthly interest rate (6%/12 = 0.5%).
2. PV function is an annuity function. In annuity functions, the cash payments by you are represented by negative numbers and the payments you receive are represented by positive numbers.

### Example

Let’s say you want to invest \$4000 in an investment plan and in return, you’ll get \$1000 at the end of each year for the next 5 years.

That means you’ll get a total of \$5000 in the next 5 years.

Now the thing is, you have to evaluate that this investment is profitable or not. You are investing \$4000 today and the return will come to you in the next 5 years.

In the above calculation, it has returned -4329. The present value of your investment is \$4329 and you are investing \$4000 for it. Hence, your investment is profitable.