WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » PV Function in Excel

PV Function in Excel

PV is also known as the present value and this function is used to calculate the current present value for any investment done and this present value is dependent on the rate of the investment and the number of periods for the payment with the future value as an input, this function is available in the financial category of the formulas tab in excel.

PV Function in Excel

PV Function in Excel (or Present Value) is a financial function, which calculates the PV Function of a future sum of money or fixed cashflows at a constant rate of interest. PV in excel is based on the concept of the time value of money. For example, receiving Rs. 5,000 now is worth more than Rs. 5,000 earned next year because the money received now could be invested to get an additional return till next year. PV in excel function is commonly used to compare investment alternatives, as in stock valuation, bond pricing, financial modeling, insurance, banking, and pension plans, etc.

For an investment to be made today, investors calculate the PV in excel of expected cash flows to decide upon the investment. Suppose you have an amount of Rs. 10,00,000 today to invest, and you have two alternate plans, which is expected to give you

  • 30,000 monthly for the next 5 years (which is Rs. 18,00,000 in total).
  • 25,000 quarterly for the next 20 years (which is Rs. 25,00,000 in total)

Both of the investment plans seem to be giving a good profit. Rs. 25,00,000 (case 2) is more than Rs. 18,00,000 (case 1), and both of them are more than the current investment of Rs. 10,00,000. However, not in terms of time. In this, you would like to know the present value of these regular cashflows to decide upon if this investment is worth making and to compare it between the two investment alternatives. By the end of this article, you will realize that plan 1 is much better than plan 2.

PV in Excel Formula

In Excel, there is a built-in function to calculate PV in Excel. The PV excel formula is given as:

PV Excel Formula

The arguments in the PV excel formula are as follows:

rate* – The rate of interest or return per period. Also termed as discount rate
nper* – The number of periods for the lifetime of the annuity or investment.
pmt – The payment is made per period. It includes both principle amount and interest.
fv – It specifies the future value of the annuity at the end of nper payments.

( default value: 0).

type – Optional. Value: 0 or 1. It defines whether the payment is made at the start or the end of the period.

0:     the payment is made at the end of the period;
1:     the payment is made at the start of the period.

(default value : 0 denoting payments made at the end of the period).

If pmt is omitted, the fv argument should be provided.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

PV in Excel – Assumptions

There are two assumptions of PV in Excel function:

  1. Constant and Periodic payment
  2. Constant rate of interest or return

A series of cash flows that include a similar amount of cash flow (outflow or inflow) each period is called an annuity. For example, a car loan is an annuity. When each period’s interest rate is the same, an annuity can be valued using the PV function in excel. In the case of annuity functions, a general convention of cash flow is followed- cash outflows are represented as negative, and cash inflows are expressed as positive. So, the pmt is negative if it is an outflow.

You can use PV formula excel with i) periodic and constant payments and ii) future value. If you opt for a car loan, you are supposed to pay a fixed amount of money periodically, say Rs. 20,000 monthly for two years. In this case, you use the pmt option as Rs. 20,000 to calculate the present value. You can also use the PV function in excel with a fixed future value. Suppose you plan to attain a sum of Rs. 5,00,000 after 5 years for your child’s education, you can calculate PV formula in excel using the fv option.

How to Use the PV Function in Excel?

Let understand the working of PV Function in excel with a few PV in excel function examples.

You can download this PV Function Excel Template here – PV Function Excel Template

PV in Excel Function Example #1

With an interest rate of 7% per annum, payment of Rs. 5,00,000 is made on a yearly basis for five years.

PV Function Excel (Example - 1)

The present value of an annuity can be calculated using the PV function in Excel as PV(7%, 5, -500000), as shown in the example below.

PV Function Excel Example - 1-1

The present value in the above case is Rs. 20,50,099.

It may be noted that in this case, the interest rate is the interest rate per period, which is different from the interest rate per annum used commonly.

PV in Excel Function Example #2

Suppose you make quarterly payments of Rs 1,25,000 per period for five years, having an interest rate per annum of 7%. The interest rate per period will be counted as 7%*4/12 quarterly.

PV Function Excel Example - 2

The PV Function Excel will be given as (rate=7%*4/12, nper=4*5, pmt=-125000).

PV Function Excel Example - 2-1

PV in Excel Function Example #3

Suppose you have a future value goal of Rs 25,00,000 to attain from an investment in 20 periods having an interest rate of 2.333%. If the payment is made at the end of each period, the present value can be calculated in such cases using this function as PV(rate=2.333%, nper=20, fv=2500000, type=0).

PV Function Excel Example - 3

PV in Excel Example #4

Going back to the previous case where you need to compare two alternative investment plans

  • 30,000 monthly for the next 5 years (which is Rs. 18,00,000 in total).
  • 25,000 quarterly for the next 20 years (which is Rs. 25,00,000 in total)

PV Function Excel Example - 4

Assuming a rate of 6% per annum, the rate per period (1) 6%/12 = 0.5%, (2) 6%*4/12 = 2%.

PV Function Excel Example - 5

You get the present value of (1) Rs. 15,51,767 (2) Rs. 10,77,459.

So, you would like to choose the first plan as the present value from the first plan is much larger than the second.

Things to Remember About The PV Function in Excel

  • The PV function excel uses a specific order of values (which is the rate, nper, pmt, fv, type) and is separated by “,” If any of the arguments are not provided, pv in excel function can be left blank. As in example 3, it is PV(B4,B5,,B6,0).
  • The rate is the interest/return rate per period, which is different from the annual rate.
  • PV in excel function allows cash flows either in the beginning or at the end of the period.
  • There is constant cash flow and a constant interest rate in the pv excel function.

Recommended Articles

This has been a guide to PV Function in Excel. Here we discuss the PV Formula in Excel and how to use PV in Excel along with practical examples and downloadable excel templates.-

  • Examples of NPV
  • Excel NPER Function
  • Excel Index Match Function
  • Evaluate Formula in Excel
3 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download PV Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More