How to Calculate the Time Value of Money in Excel

What Is the Time Value of Money?

The core idea behind the time value of money is that money that you have today is worth more than money you will receive in the future.

Parameters to Calculate Time Value of Money

pv → pv the Present Value or the amount of money you currently have.
fv → fv the Future Value of the money that you currently have.
nper → nper represents the Number of Periods: Annually, Semi-Annually, Quarterly, Monthly, Weekly, Daily etc.
rate → rate is the Interest Rate Per Year.
pmt → pmt indicates Periodic Payments.
Note: In the Excel formula, the signs of PV and FV are opposite. PV is negative and FV is positive.

Example 1 – Using the FV Function to Calculate the Future Value of Money in Excel

In the following dataset, initial investments (Present Value), the Annual Rate, and the Number of Years are displayed. To calculate the Future Value, use the FV function.

1.1 Future Value Without a Periodic Payment

Steps:

Enter the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

1.2 Future Value with Periodic Payments

Steps:

Enter the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Example 2 – Computing the Present Value of Money with the PV Function

In the following dataset, Future Value, Annual Rate, and Number of Years are displayed. To calculate the Present Value, use the PV function.

2.1 Present Value Without Periodic Payments

Steps:

Enter the formula below in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

2.2 Present Value with Periodic Payments

Steps:

Use the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Example 3 – Calculating the Interest Rate with the RATE Function in Excel

Use the RATE function. In the dataset given below, Present Value, Future Value, and Number of Years are displayed. To find the Interest Rate:

3.1 Interest Rate Without Periodic Payments

Steps:

Enter the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

3.2 Interest Rate with Periodic Payments

Steps:

Use the formula below in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Example 4 – Computing the Number of Periods with the NPER Function

The following dataset showcases Present Value, Future Value, and Annual Rate.

4.1 Number of Periods Without Periodic Payments

Steps:

Enter the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

4.2 Number of Periods with Periodic Payments

Steps:

Enter the following formula in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

Example 5 – Using the PMT Function to Determine a Payment Per Period

Use the PMT function. In the dataset below, Present Value, Annual Rate, Number of Years, and Future Value are displayed. To find the Payment Per Period:

5.1 Payment Per Period for a Zero Future Value

If after the period of time, you don’t have any money, the Future Value is Zero.

Steps:

Enter the formula below in 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.

Press ENTER.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

5.2 Payment Per Period for a Non-Zero Future Value

The Non-Zero Future Value means that you will have a lump sum at the end of the period of time.

Steps:

Enter the following formula in 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.

Press ENTER.

Note: The negative sign is used before the function, not to be displayed in the output.

This is the output.

Drag down the Fill Handle to see the result in the rest of the cells.

How to Create a Time Value Money Table in Excel

1. Create a PVIF Table

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

Enter your data in the PVIF table.

go to B15 and enter the following formula.

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

Enter the Initial Rate in C15.
Select D5 and enter the following formula to create the third column in the table. Drag the Fill Handle to column 16.

=C15+$C$6