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.
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.
How to Calculate Present Value of Lump Sum in Excel: 3 Easy Ways
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.
- 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.
- 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 Calculate Present Value of Future Cash Flows 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.
- 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.
- 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
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
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
- How to Calculate Future Value of Uneven Cash Flows in Excel
- How to Make a Time Value of Money Calculator in Excel
- How to Apply Future Value of an Annuity Formula in Excel
- How to Calculate Future Value of Growing Annuity in Excel
- How to Calculate Future Value in Excel with Different Payments
- How to Calculate Future Value with Inflation in Excel
- Calculate NPV for Monthly Cash Flows with Formula in Excel
- How to Apply Present Value of Annuity Formula in Excel
<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
