In our daily life, we often need to make some financial decisions and for which we might need to calculate time value of money. Microsoft Excel is quite powerful and versatile software. In Excel, we can calculate time value of money by following some simple steps. In this article, we are going to discuss 10 suitable examples to calculate time value of money in Excel.

**Table of Contents**Expand

## What Is Time Value of Money?

The core idea of the time value of money is that money that you have in your pocket today is worth more than money that you will receive in the future. Let me explain it with an example.

Let’s say you can have $200,000 at once today or you can have $20,000 for the next 10 years. In both cases, the total amount is $200,000. But the first one is worth more than the second one. Because you can reinvest the $200,000 and gain more profit compared to the second option.

## Parameters to Calculate Time Value of Money

Let’s familiarize ourselves with some parameters that we will use to calculate the time value of money in Excel.

**pv****→ pv**denotes the**Present Value**or simply the amount of money that you have right now.**fv →****fv**indicates the**Future Value**of the money that you have now.**nper → nper**represents the**Number of Periods**. It is to be noted that, the**Period**can be**Annually**,**Semi-Annually**,**Quarterly**,**Monthly**,**Weekly**,**Daily**etc.**rate → rate**is the**Rate of Interest Per Year**. You should keep in mind that before utilizing an interest rate in an Excel function, you must convert it to an**Annual Rate of Interest**whether it is given at monthly, quarterly, or any other intervals.**pmt → pmt**indicates the**Periodic Payments**that are made. For example, to repay a loan you need to make periodic payments each month.

In the Excel formula, the signs of*Note:***PV**and**FV**are opposite. Generally, the sign of**PV**is taken as negative and**FV**as positive.

In this section of the article, we will learn 5 examples to calculate time value of money. Not to mention that we have used *Microsoft Excel 365 *version for this article, you can use any other version according to your convenience.

## 1. Using FV Function to Calculate Future Value of Money in Excel

Firstly, we will see how we can calculate Future Value in Excel. Future Value is nothing but the future worth of money that you have currently.

In the following dataset, we have some initial investments (**Present Value**), respective **Annual Rate**, and the **Number of Years** of some **Account Holders**. We will calculate the **Future Value** by using the **FV function** of Excel.

### 1.1 Future Value Without Periodic Payment

If there are no periodic payments to the initial investment, we will calculate the *Future Value *by following the steps mentioned below.

__Steps:__

- Firstly, enter the following formula in cell
**F5**.

`=FV(E5,D5,0,-C5,0)`

Here,

**E5** → **rate
**

**D5**→

**nper**

**0 → pmt**

**-C5 → pv**

**0**→

**0**means payment is timed at the

**end of the period**.

- After that, press
**ENTER**.

Consequently, you will see the following output on your worksheet.

- By using Excel’s
**AutoFill**feature, we can get the rest of the*Future Values*as shown in the following picture.

**Read More:** How to Calculate Periodic Interest Rate in Excel

### 1.2 Future Value with Periodic Payments

On the other hand, if there are periodic payments as marked in the image given below, we will calculate the *Future Value *by following the steps discussed below.

__Steps:__

- Firstly, insert the following formula in cell
**G5**.

`=FV(F5,D5,-E5,-C5,0)`

Here,

**F5 → rate
**

**D5 → nper**

**-E5 → pmt**

**-C5 → pv**

**0 → 0**means payment is timed at the

**end of the period**.

- Following that, hit
**ENTER**.

Subsequently, you will see *Future Value* for Peter as marked in the image given below.

- Now, you can get the rest of the outputs by using the
**AutoFill**feature of Excel.

**Read More: **How to Apply Future Value of an Annuity Formula in Excel

## 2. Computing Present Value of Money with Excel PV Function

In the following dataset, we have **Future Value**, **Annual Rate**, and **Number of Years** data for some **Account Holder**. We need to calculate the **Present Value**. To do this, we will use the** PV function** of Excel.

### 2.1 Present Value Without Periodic Payments

To compute the *Present Value* where there are no periodic payments, we will use the following procedures.

__Steps:__

- Firstly, enter the formula given below in cell
**F5**.

`=PV(E5,D5,0,-C5,0)`

Here,

**E5 → rate
**

**D5 → nper**

**0 → pmt**

**-C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Afterward, press
**ENTER**.

As a result, you will get the *Present Value* for Peter as marked in the following picture.

- Following that, use Excel’s
**AutoFill**option to get the rest of the Present Values.

### 2.2 Present Value with Periodic Payments

If there are periodic payments as shown in the below-given image, we will follow the steps mentioned below to calculate the *Present Value*.

__Steps:__

- Firstly, use the following formula in cell
**G5**.

`=PV(F5,D5,E5,-C5,0)`

Here,

**F5 → rate
**

**D5 → nper**

**E5 → pmt**

**-C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Next, hit
**ENTER**.

Consequently, you will see the following output on your worksheet.

- Following that, use the
**AutoFill**option to obtain the remaining*Present Values*for other*Accounts Holders*.

**Read More: **How to Apply Present Value of Annuity Formula in Excel

## 3. Calculating Interest Rate with the RATE Function in Excel

For determining the Interest Rate, we can use the **RATE function** of Excel. In the dataset given below, we have **Present Value**, **Future Value**, and **Number of Years **data for some **Account Holders**. Now, we will find the *Interest Rate*.

### 3.1 Interest Rate Without Periodic Payments

Firstly, let’s learn the steps to calculate the *Interest Rate *if there are no periodic payments.

__Steps:__

- Firstly, enter the following formula in cell
**F5**.

`=RATE(D5,0,E5,-C5,0)`

Here,

**D5 → nper
**

**0 → pmt**

**E5 → pv**

**-C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Subsequently, press
**ENTER**.

As a result, you the* Annual Rate* for the first set of data as marked in the following picture.

- Following that, you can get the remaining
*Annual Rates*by using the**AutoFill**option.

### 3.2 Interest Rate with Periodic Payments

On the contrary, if periodic payments are included as shown in the image given below, then we will use the following steps to compute the *Interest Rate*.

__Steps:__

- Firstly, use the formula given below in cell
**G5**.

`=RATE(D5,-E5,-F5,C5,0)`

Here,

**D5 → nper
**

**-E5 → pmt**

**-F5 → pv**

**C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Now, press
**ENTER**.

Consequently, you will see the following output on your worksheet.

- Finally, use the
**AutoFill**option to obtain the remaining*Annual Rates*for other*Account Holders*.

**Read More: **How to Calculate Present Value of Future Cash Flows in Excel

## 4. Computing Number of Periods with the NPER Function

We can calculate the Number of Periods quite easily by using the **NPER function**. Here, in the following dataset, we have **Present Value**, **Future Value**, and **Annual Rate** for some **Account Holders**. Now, we will compute the Number of Periods.

### 4.1 Number of Periods Without Periodic Payments

Let’s learn the steps to determine the *Number of Periods* when there are no periodic payments.

__Steps:__

- Firstly, enter the following formula in cell
**F5**.

`=NPER(D5,0,-E5,C5,0)`

Here,

**D5 → rate
**

**0 → pmt**

**-E5 → pv**

**C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Next, hit
**ENTER**.

As a result, we will get the following output as marked in the image given below.

- At this stage, we can get the remaining outputs by using the
**AutoFill**feature of Excel.

### 4.2 Number of Periods with Periodic Payments

On the other hand, if there are periodic payments included as shown in the following picture, we will follow the steps mentioned below to calculate the *Number of Periods*.

__Steps:__

- Firstly, enter the following formula in cell
**G5**.

`=NPER(D5,-E5,-F5,C5,0)`

Here,

**D5 → rate
**

**-E5 → pmt**

**-F5 → pv**

**C5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- After that, press
**ENTER**.

Consequently, you will get the output for the first set of data as shown in the following image.

- Now, by using the
**AutoFill**option of Excel, we can get the remaining outputs for the other Account Holders.

**Read More: **How to Calculate Present Value in Excel with Different Payments

## 5. Using Excel PMT Function to Determine Payment Per Period

In this portion of the article, we will determine the Payment Per Period by using the **PMT function** in Excel. In the dataset given below, we have **Present Value**, **Annual Rate**, **Number of Years**, and **Future Value** for some **Account Holders**. Our aim is to find the *Payment Per Period*.

### 5.1 Payment Per Period for a Zero Future Value

Firstly, we will calculate the* Payment Per Period* for a Zero Future Value. Zero Future Value means that after the time period you will not have any money in your hand. For example, when you repay a loan, you don’t get any money after the completion of the repayment. So, in this case, the Future Value is Zero.

Let’s follow the steps mentioned below to determine the *Payment Per Period* for a Zero Future Value.

__Steps:__

- Firstly, enter the formula given below in cell
**G5**.

`=PMT(D5,F5,-C5,0,0)`

Here,

**D5 → rate
**

**F5 → nper**

**-C5 → pv**

**0 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Following that, hit
**ENTER**.

Subsequently, you will get the *Payment Per Period* for Peter.

- Now, use Excel’s
**AutoFill**option to obtain the remaining outputs as marked in the following image.

### 5.2 Payment Per Period for a Non-Zero Future Value

Now, we will calculate the *Payment Per Period* for a Non-Zero Future Value. Non-Zero Future Value means that you will have a lump sum amount at the end of the time period.

For instance, suppose you want to accumulate $5000 after 3 years at a 5% annual interest rate. You currently have $500. So, you want to calculate the amount of money that you need to save per period. As you are getting $5000 at the end of 3 years, $5000 is the Future Value. In this article, we have used the* Future Value *marked in the following image.

**Steps:**

- Firstly, insert the following formula in cell
**G5**.

`=-PMT(D5,F5,-C5,E5,0)`

Here,

**D5 → rate
**

**F5 → nper**

**-C5 → pv**

**E5 → fv**

**0 → 0**means payment is timed at the

**end of the period**.

- Now, hit
**ENTER**.

** Note:** Here negative sign is used before the function to avoid the negative sign in the output.

Consequently, you will see the following image on your screen.

- Following that, by using the
**AutoFill**option of Excel, you can get the rest of the outputs as marked in the following image.

**Read More: **How to Calculate Future Value in Excel with Different Payments

## How to Create a Time Value Money Table in Excel

You can make custom tables for **PVIF**, **FVIF**, **PVIFA**, and **FVIFA **in Excel too. Now we will walk you through the steps to create them.

### 1. Create a PVIF Table

The **PVIF **table provides a set of values that represent the present value factor which are used to calculate the present worth of the future value of money.

- First of all, enter the necessary inputs for the
**PVIF**table as shown in the following image.

- Then go to cell
**B15**and insert the following formula.

`=PV(C11,C12,0,-1)`

- Enter the
**Initial Rate**in cell**C15**. - Then select cell
**D5**and apply the following formula to create the third column of the table.**AutoFill**up to the sixteenth column.

`=C15+$C$6`

- After that, insert the
**Initial Period**in cell**B16**. - Add a new row by using the following formula in cell
**B17**.

`=B16+$C$8`

- Then
**AutoFill**the rest of the column.

- Once the rows and columns of the tables are created, select the whole table (
**B15:L45**). - Then go to
**Data**>>**What-If Analysis**>>**Data Table**.

- In the Data Table, enter
**$C$11**as the**Row input cell**and**$C$12**as the**Column input cell**.

- Click
**OK**and the**PVIF**table will be created.

### 2. Make an FVIF Table to Calculate Time Value of Money in Excel

The **FVIF **table contains future value interest factors. The **FVIF **table can easily be created following the same procedure as above. Only one change needs to be made to convert the **PVIF **table to the **FVIF **table.

- First, copy the PVIF worksheet to a new worksheet.

- Then select cell
**B15**and enter the formula given below.

`=FV(C11,C12,0,-1)`

- Press
**Enter**and you will have your**FVIF**table.

### 3. Calculating the Time Value of Money in Excel with a PVIFA Table

The Present Value Interest Factor of Annuity (**PVIFA**) is a tool to calculate present worth of future value as annuities.

- Add a new row named
**Type**in**PVIFA**table data. - Then select cell
**C13**and go to,**Data >> Data Validation >> Data Validation**.

- Select
**List**in the**Allow**option. - Then type “
**Regular, Due**” in**Source**.

- As a result, two options will be added in cell
**C13**.

- Now enter the following formula in cell
**B16**and consequently, the**PVIFA**table will be created.

`=IF(C13="Due",PV(C11,C12,-1,0,1),PV(C11,C12,-1,0,0))`

### 4. Create an FVIFA Table to Calculate Time Value of Money in Excel

The **FVIFA **table provides a set of factors to determine the future worth of the present value of money.

- Copy the
**PVIFA**table in a new sheet and change the formula of cell**B16**like the following.

`=IF(C13="Due",FV(C11,C12,-1,0,1),FV(C11,C12,-1,0,0))`

**Download Practice Workbook**

## Conclusion

Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to calculate time value of money in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.

## Related Articles

- How to Calculate Future Value of Growing Annuity in Excel
- Calculate Present Value of Lump Sum in Excel
- How to Calculate Present Value of Uneven Cash Flows in Excel
- Calculate Future Value of Uneven Cash Flows in Excel

## Calculate Time Value of Money : Knowledge Hub

- Calculate NPV for Monthly Cash Flows with Formula in Excel
- How to Make a Time Value of Money Calculator in Excel
- How to Calculate Present Value of Future Cash Flows in Excel
- How to Calculate Future Value with Inflation in Excel

**<< Go Back to** **Excel for Finance**** | ****Learn Excel**