How to Calculate Time Value of Money in Excel (5 Examples)

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.


Download Practice Workbook


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.
    Note: In the Excel formula, the signs of PV and FV are opposite. Generally, the sign of PV is taken as negative and FV as positive.

5 Examples to Calculate Time Value of Money in Excel

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. Calculating Future Value

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.

how to calculate time value of money in excel Calculating Future Value


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,
E5rate
D5nper
0 → pmt
-C5 → pv
00 means payment is timed at the end of the period.

  • After that, press ENTER.

how to calculate time value of money in excel Calculating Future Value

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.

how to calculate time value of money in excel Calculating Future Value


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.

how to calculate time value of money in excel Calculating Future Value

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.

how to calculate time value of money in excel Calculating Future Value

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

how to calculate time value of money in excel Calculating Future Value

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


2. Computing Present Value

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.

how to calculate time value of money in excel

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

how to calculate time value of money in excel Computing Present Value

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

how to calculate time value of money in excel Computing Present Value

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

how to calculate time value of money in excel Computing Present Value

  • 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

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.

how to calculate time value of money in excel Calculating 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.

how to calculate time value of money in excel Calculating Interest Rate

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.

how to calculate time value of money in excel Calculating 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.

how to calculate time value of money in excel Calculating Interest Rate

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.

how to calculate time value of money in excel Calculating Interest Rate

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


4. Computing Number of Periods

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.

how to calculate time value of money in excel Computing 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.

how to calculate time value of money in excel Computing Number of Periods

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

how to calculate time value of money in excel Computing Number of Periods


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.

how to calculate time value of money in excel Computing Number of Periods

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.

how to calculate time value of money in excel Computing Number of Periods

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


5. Determining 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.

how to calculate time value of money in excel


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.

how to calculate time value of money in excel Determining Payment Per Period

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

how to calculate time value of money in excel Determining Payment Per Period

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

how to calculate time value of money in excel Determining Payment Per Period

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.

how to calculate time value of money in excel Determining Payment Per Period

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.

how to calculate time value of money in excel Determining Payment Per Period

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


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. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo