PV Function in Excel Tutorial

Excel Tutorial

The PV function in Excel is used to calculate the present value of an investment or loan based on a fixed interest rate and regular payments. It helps businesses and individuals understand how much a series of future payments is worth today.

In this tutorial, you will learn the PV function from the basics, understand its arguments, and calculate the present value using practical financial examples.

What Is the PV Function in Excel?

PV Function stands for Present Value.

Present Value means the value of money today compared with money that will be received or paid in the future.

For example, suppose someone promises to pay you ₹1,00,000 after one year. If the annual interest rate is 10%, that future amount is not worth exactly ₹1,00,000 today.

Why? Because money available today can earn interest.

The PV function helps calculate the value today of future payments or a future amount.

PV Function Syntax

=PV(rate, nper, pmt, [fv], [type])

Understanding the Arguments

ArgumentMeaning
rateInterest rate for each payment period.
nperTotal number of payment periods.
pmtRegular payment made or received in each period.
[fv]Optional future value after the final payment. Default is 0.
[type]Optional payment timing: 0 = end of period, 1 = beginning of period. Default is 0.

1. rate – Interest Rate Per Period

This is the interest rate applicable to each payment period.

For example, if the annual interest rate is 12% and payments are monthly:

=12%/12

The monthly interest rate is 1%.

Always match the interest rate period with the payment frequency.

2. nper – Total Number of Periods

This represents the total number of payment periods.

For example, if an investment pays monthly for 5 years:

=5*12

The total number of periods is 60 months.

3. pmt – Regular Payment

This is the fixed amount paid or received in every period.

For example, if you receive ₹10,000 every month, the payment amount is ₹10,000.

If you pay ₹10,000 every month, the payment is generally entered as a negative value.

4. fv – Future Value

This is an optional argument.

It represents the amount you want to have remaining after the final payment.

For example, if an investment will provide an additional ₹50,000 at the end of the investment period, enter 50000 as the future value when the regular payments are entered as negative cash flows.

If omitted, Excel assumes 0.

5. type – Payment Timing

This argument specifies when payments happen.

TypeMeaning
0Payment at the end of each period
1Payment at the beginning of each period

If you omit this argument, Excel assumes 0.

Important Concept: Why Does PV Return a Negative Value?

Excel follows a cash-flow sign convention.

  • Money you receive is positive.
  • Money you pay is negative.

For example, if you receive regular payments in the future, Excel may return a negative present value because the present value represents the amount you would pay today to receive those future payments.

The sign does not mean the calculation is wrong. It shows the direction of money movement.

If you want to display the present value as positive, you can enter future payments as negative cash flows.

Practical Examples of PV Function in Excel

Question 1: Calculate the Present Value of a Future Amount

Problem Statement

A person wants to receive ₹1,10,000 after one year. If the annual interest rate is 10%, calculate the present value of this amount today.

Given Data

ParticularsValue
Future Value₹1,10,000
Annual Interest Rate10%
Time Period1 Year
Regular Payment₹0

Excel Formula

=PV(10%,1,0,-110000)

Expected Result

₹1,00,000

Explanation

  • 10% is the annual interest rate.
  • 1 represents one year.
  • 0 means there are no regular payments.
  • -110000 represents the future amount as a negative cash flow.

Excel calculates that ₹1,10,000 received after one year is equivalent to ₹1,00,000 today at a 10% annual rate.

Concept Covered

PV can calculate today’s value of a single future amount using the fv argument.

Question 2: Calculate the Present Value of Monthly Payments

Problem Statement

A person will receive ₹10,000 every month for 3 years. The annual interest rate is 12%. Calculate the present value of these monthly payments.

Given Data

ParticularsValue
Monthly Payment₹10,000
Annual Interest Rate12%
Period3 Years
Payment FrequencyMonthly

Excel Formula

=PV(12%/12,3*12,-10000)

Expected Result

Approximately ₹3,01,906.60

Explanation

  • 12%/12 converts the annual interest rate into a monthly rate.
  • 3*12 calculates 36 monthly payments.
  • -10000 represents the monthly payment received as a positive cash inflow.

The result shows the value today of receiving ₹10,000 every month for 36 months, assuming a 12% annual rate and end-of-month payments.

Concept Covered

PV can calculate the current value of a series of regular payments.

Question 3: Calculate the Present Value of an Investment

Problem Statement

An investor expects to receive ₹15,000 every year for 5 years. The annual interest rate is 8%. Calculate the present value of these payments.

Given Data

ParticularsValue
Annual Payment₹15,000
Annual Interest Rate8%
Investment Period5 Years
Payment FrequencyYearly

Excel Formula

=PV(8%,5,-15000)

Expected Result

Approximately ₹59,890.00

Explanation

  • 8% is the annual interest rate.
  • 5 represents five annual payments.
  • -15000 represents the annual payment received.

Excel calculates the value today of receiving ₹15,000 at the end of each year for five years.

Concept Covered

PV helps compare a series of future payments with their equivalent value today.

Question 4: Calculate Present Value with a Future Balance

Problem Statement

A person will receive ₹20,000 every year for 4 years. At the end of the fourth year, an additional ₹50,000 will also be received. The annual interest rate is 10%. Calculate the present value.

Given Data

ParticularsValue
Annual Payment₹20,000
Annual Interest Rate10%
Period4 Years
Future Value₹50,000

Excel Formula

=PV(10%,4,-20000,-50000)

Expected Result

Approximately ₹97,037.74

Explanation

  • 10% is the annual interest rate.
  • 4 represents four annual periods.
  • -20000 represents the regular annual payment.
  • -50000 represents the additional amount received at the end of the fourth year.

Excel calculates today’s value of both the regular payments and the final amount.

Concept Covered

The fv argument allows you to include an additional future amount along with regular payments.

Question 5: Calculate Present Value of Quarterly Payments

Problem Statement

A business expects to receive ₹25,000 every quarter for 3 years. The annual interest rate is 8%. Calculate the present value of these payments.

Given Data

ParticularsValue
Quarterly Payment₹25,000
Annual Interest Rate8%
Period3 Years
Payment FrequencyQuarterly

Excel Formula

=PV(8%/4,3*4,-25000)

Expected Result

Approximately ₹2,51,824.90

Explanation

  • 8%/4 calculates the quarterly interest rate.
  • 3*4 calculates 12 quarterly payments.
  • -25000 represents the quarterly payment received.

Excel calculates the present value of all 12 quarterly payments.

Concept Covered

For quarterly payments, use the quarterly interest rate and total number of quarters.

Question 6: Calculate Present Value for Payments at the Beginning of Each Month

Problem Statement

A person will receive ₹5,000 at the beginning of every month for 2 years. The annual interest rate is 12%. Calculate the present value.

Given Data

ParticularsValue
Monthly Payment₹5,000
Annual Interest Rate12%
Period2 Years
Payment TimingBeginning of Month

Excel Formula

=PV(12%/12,2*12,-5000,0,1)

Expected Result

Approximately ₹1,06,449.97

Explanation

  • 12%/12 calculates the monthly interest rate.
  • 2*12 calculates 24 monthly payments.
  • -5000 represents the monthly payment received.
  • 0 means there is no additional future value.
  • 1 means payments happen at the beginning of each month.

Because payments are received earlier, their present value is higher than the equivalent end-of-month payment series.

Concept Covered

The type argument changes the timing of regular payments and therefore changes the present value.

Question 7: Calculate Present Value Using Cell References

Problem Statement

A company expects to receive ₹30,000 every year for 5 years. The finance department wants to calculate the present value using cell references.

Given Data

CellParticularsValue
B2Annual Payment₹30,000
B3Annual Interest Rate10%
B4Period in Years5
B5Future Value₹0

Excel Formula

=PV(B3,B4,-B2,-B5)

Expected Result

Approximately ₹1,13,723.60

Explanation

  • B3 contains the annual interest rate.
  • B4 contains the total number of annual payments.
  • -B2 represents the annual payment received.
  • -B5 represents the future value.

If you change the interest rate, annual payment, or period, Excel recalculates the present value.

Concept Covered

Cell references make financial calculations flexible and help build reusable investment calculators.

Student Practice Tasks

Try these calculations using the PV function.

  1. Calculate the present value of ₹50,000 received after 2 years at an annual interest rate of 8%. There are no regular payments.
  2. Calculate the present value of receiving ₹8,000 every month for 2 years at an annual interest rate of 10%.
  3. Calculate the present value of receiving ₹20,000 every year for 6 years at an annual interest rate of 9%.
  4. Calculate the present value of receiving ₹15,000 every quarter for 2 years at an annual interest rate of 8%.
  5. Calculate the present value of receiving ₹12,000 every year for 5 years, with an additional ₹30,000 at the end of the fifth year, at an annual interest rate of 10%.

Final Student Challenge: Build a Present Value Calculator

A company expects to receive fixed payments from a customer over the next 5 years. The finance department wants to calculate the present value of those payments.

Given Data

ParticularsValue
Annual Payment₹40,000
Annual Interest Rate9%
Period5 Years
Future Value₹0

Your Task

  1. Enter the annual payment in B2.
  2. Enter the annual interest rate in B3.
  3. Enter the period in years in B4.
  4. Enter the future value in B5.
  5. Calculate the present value using the PV function.

Excel Formula

=PV(B3,B4,-B2,-B5)

Expected Result

Approximately ₹1,55,670.00

Your calculator should update the present value whenever you change the payment amount, interest rate, or period.

Key Takeaways

  • PV stands for Present Value.
  • PV calculates the current value of future payments or a future amount.
  • rate represents the interest rate per payment period.
  • nper represents the total number of payment periods.
  • pmt represents the regular payment made or received.
  • fv represents the additional future value after the final payment.
  • type controls whether payments happen at the beginning or end of each period.
  • Match the interest rate period with the payment frequency.
  • Excel uses positive and negative signs to represent cash-flow direction.

Frequently Asked Questions

1. What is the PV function in Excel?

The PV function calculates the present value of an investment or loan based on a constant interest rate and regular payments.

2. What is the full form of PV?

PV stands for Present Value. It represents the value of future money in today’s terms.

3. What is the syntax of the PV function?

=PV(rate,nper,pmt,[fv],[type])

4. What is the difference between PV and FV?

PV calculates the value of money today, while FV calculates the value of money at a future date.

5. Can PV calculate the present value of a single future amount?

Yes. Use zero for regular payments and enter the future amount in the fv argument.

6. Why does PV Function in Excel return a negative value?

Excel follows a cash-flow sign convention. The result may be negative when future receipts are entered as positive amounts.

7. What is the difference between type 0 and type 1?

Type 0 means payments occur at the end of each period. Type 1 means payments occur at the beginning of each period.

8. Can PV calculate the present value of monthly payments?

Yes. Divide the annual interest rate by 12 and multiply the number of years by 12.

9. What happens if the interest rate is zero?

If the interest rate is zero, PV calculates the sum of the regular payments and future value, adjusted for their cash-flow signs.

10. Can PV be used for business financial planning?

Yes. Businesses can use PV to evaluate future customer payments, investment income, and other regular cash flows.

11. What is the difference between PMT and PV Function?

PV calculates the present value of future payments, while PMT calculates the regular payment amount required for a loan or investment.

12. Is the PV function available in Microsoft 365?

Yes. PV is a standard Excel financial function available in Microsoft 365 and commonly used desktop versions of Excel.