How to Calculate Present Value of Lump Sum in Excel (3 Ways)

What Is the Present Value?

Present value refers to the idea that the money today is worth more than the worth it will be in the future.

The mathematical expression of present value is:

FV represents the future value,
i stands for the annual rate of interest and
n is the total number of the annual period.

What Is Lump Sum?

Payments made in lump sums are often large sums that are paid in one go rather than as installments. In the context of loans, it is also called bullet repayment.

How to Calculate the Present Value of a Lump Sum in Excel: 3 Easy Ways

Consider a simple dataset with a future value of an investment depending on a fixed interest rate over a certain period. As we are estimating the present value of the lump sum, there will be no payment. The value of the Payment or PMT is Zero (0). We are going to show the value of Present Value (PV) in cell D9.

Method 1 – Using a Conventional Formula

Steps:

Insert the following formula in cell D9.

=D7/((1+D6)^D5)

Press Enter.

You will get the present value of the lump sum.

Interpretation of the Result

Our determined present value of the lump sum is $7,835.26. It states that if today we deposit $7,835.26 in any bank and if the bank will provide us with an annual rate of 5% interest, then after 5 years, we will get $10,000 from the return of the deposit.

Method 2 – Applying the PV Function for an Annual Period

Steps:

Use the following formula in cell D9.

=PV(D6,D5,D8,D7)

Press Enter.

You will see the present value of the lump sum.

Interpretation of the Result

The PV function returns us the present value of the lump sum -$7,835.26. As the function calculating the previous value of our future value, the present value shows a negative sign. The value states that if today we deposit $7,835.26 in any bank and if the bank will provide us with an annual rate of 5% interest, then after 5 years, we will get $10,000 from the return of the deposit.

Method 3 – Utilizing the PV Function for a Monthly Period

Steps:

Convert the monthly period into the annual period.
Select cell D6 and use the following formula into the cell.

=D5/12

Press Enter.

You will get the monthly period into the annual period.
Insert the following formula in cell D9.

=PV(D7,D6,D9,D8)

Press Enter.

You will get the present value of the lump sum.

Interpretation of the Result

The PV function returns us the present value of the lump sum -$8,499.02. As the function calculating the previous value of our future value, the present value shows a negative sign. The value states that if today we deposit $$8,499.02 in any bank and if the bank will provide us with an annual rate of 5% interest, then after 40 months or 3.33 years, we will get $10,000 from the return of the deposit.