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