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

Calculating present value is a common task in finance. We can easily perform this calculation using Microsoft Excel. In this article, we will demonstrate 3 easy methods to calculate the present value of the lump sum in Excel. If you are also curious about it, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


What Is Present Value?

Present value refers to the idea that the money today is worth more than the worth it will be in the future. To describe it another way, the value of money accepted in the future is less than the value of money received today.

The mathematical expression of present value is:

Here,

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


3 Easy Ways to Calculate Present Value of Lump Sum in Excel

To demonstrate the methods, we consider a simple dataset. Here, the name of the particulars and their notation are in the range of cells B5:B9 and C5:C9, respectively. In addition, the value of the corresponding particulars is in the range of cells D5:D8. As we are estimating the present value of the lump sum, so there will be no payment. As a result, the value of the Payment or PMT is Zero (0). We are going to show the value of Present Value(PV) in cell D9.


1. Using Conventional Formula

In this method, we will use a conventional formula to estimate the value of the present value. Here, we are going to use the general mathematical formula of the present value, which we mentioned earlier. The steps to calculate the present value of the lump sum are given below:

πŸ“Œ Steps:

  • First of all, select cell D9.
  • After that, write down the following formula into cell D9.

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

  • Then, press Enter.

Calculate Present Value of Lump Sum in Excel

  • You will get the present value of the lump sum.

Thus, we can say that our formula worked perfectly, and we are able to evaluate the present value of the lump sum in Excel.

πŸ”Ž 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.

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


2. Applying PV Function for Annual Period

In the following method, we are going to use the PV function to estimate the present value of the lump sum. The steps of calculating the present value are given as follows:

πŸ“Œ Steps:

  • First, select cell D9.
  • Now, write down the following formula into cell D9.

=PV(D6,D5,D8,D7)

  • After that, press Enter.

Calculate Present Value of Lump Sum in Excel

  • You will see the present value of the lump sum at the desired cell.

Hence, we can say that our formula worked precisely, and we are able to determine the present value of the lump sum in Excel.

πŸ”Ž 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.

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


3. Utilizing PV Function for Monthly Period

Like the previous method, we will use the PV function to get the present value of the lump sum. However, instead of the annual time period, we consider the time period in the month. The steps of estimating the present value are shown below:

πŸ“Œ Steps:

  • At first, we have to convert the monthly period into the annual period.
  • For that, select cell D6 and write down the following formula into the cell.

=D5/12

  • Now, press Enter.

Calculate Present Value of Lump Sum in Excel

  • You will get the monthly period into the annual period.
  • Afterward, select cell D9.
  • Then, write down the following formula into cell D9.

=PV(D7,D6,D9,D8)

  • Similarly, press Enter.

Calculate Present Value of Lump Sum in Excel

  • Finally, you will get the present value of the lump sum.

So, we can say that our formula worked successfully, and we are able to determine the present value of the lump sum in Excel.

πŸ”Ž 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.

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


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to calculate the present value of the lump sum in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo