Financial Functions in Excel

HomeExcel FunctionsFinancial Functions


In Excel, multiple financial functions can help you to deal with financial calculations. These functions can help you know the periodic payment of loan if an investment is profitable or not, and the present value of financial investment or a mortgage. In this post, we have covered the top three financial functions.

1. FV

Excel 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.

Notes

  • If pmt is the cash which you have paid (i.e deposits to saving, etc), the value must be negative; and if it is the cash received (income, dividends), the value must be positive.
  • Make sure you have a specified rate and number of payments in a consistent manner. If the rate is for an annual basis then you have to specify payment periods on an annual basis as well and if you want to specify payments on a monthly basis you have to convert interest rate on a monthly basis by dividing by 12. Same for a quarterly and half-yearly basis.

Example + Sample File

In the below example, we have used 10% interest rate, 5 payments on a yearly basis, $1000 payment amount, no PV amount and payment type at the beginning of the period. And the function has return 6716 in the result.

excel-fv-function-example-1

Let me explain to you the phenomena working behind the FV Function.

At the beginning of each period, it will calculate the interest on payment and carry forward that amount (Actual Amount + Interest)to the next period. And in the next period again the same calculation will be done and so on. The best part of FV is that it can do this step by step calculation for you in a single cell.

In the below example, we have used monthly payments.

excel-fv-function-example-2

For this, we have converted the annual interest rate into a month by dividing by 12 and 60 months instead of 5 years and then, specified 10000 for payment. Note: You can also calculate compound interest with FV.

2. PMT

Excel 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 + Sample File

We need to try it out in an example and below is one which you can try out:

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.

excel-pmt-function-example-1

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

Excel PV Function returns the present value of financial investment or a loan. In simple words, with 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 + Sample File

We need to try it out in an example and below is one which you can try out:

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.

excel-pv-function-example-1

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.

More Excel Functions

More Excel Tutorials