Present value (PV) is a crucial financial concept that helps us determine the current worth of an anticipated stream of future payments or cash inflows. In this article, we will explore how to calculate the present value in Microsoft Excel, specifically with different payment amounts.

Step 1: Open a New Worksheet in Excel

Launch Microsoft Excel and open a new blank worksheet.

Step 2: Label Your Columns

Label the columns in your worksheet with the following headers: “Year”, “Payment”, “Discount Rate”, and “Present Value”.

Step 3: Enter the Data

In the first column (Year), input the years when you expect to receive each payment. In the second column (Payment), enter your estimated payment amounts for each corresponding year. In the third column (Discount Rate), enter the annual discount rate you wish to apply to these payments.

Step 4: Apply Present Value Formula

Now it’s time to apply the present value formula. In the first row of the “Present Value” column, type the following formula:

=PV(Discount_Rate, Year, Payment)

Replace “Discount_Rate” with your cell reference containing the discount rate for that row. Replace “Year” with your cell reference containing the year of that specific payment, and replace “Payment” with your cell reference containing that year’s payment amount.

Step 5: Drag Down Formula for All Rows

After entering your formula in Step 4, click on the cell containing the result and drag it down along with your data rows, so it calculates present values for all expected payments.

Step 6: Find Total Present Value

To find out the total present value of all payments for easy interpretation, create a new row below your data set and label it “Total.” Underneath “Present Value” header and in front of “Total,” input this formula:

=SUM(your_PV_range)

Replace “your_PV_range” with the actual range containing your present value calculations in the “Present Value” column. Once entered correctly, the result will show the total present value for all payments.

Conclusion:

Calculating present value in Microsoft Excel with different payment amounts is a valuable skill for various financial planning purposes. By following these simple steps, you can quickly assess the current worth of cash inflows or investments based on changing payment streams. Use this guide to make informed decisions and optimize your financial planning effectively.