EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Financial Functions in Excel Excel PV Formula

Excel PV Formula

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 8, 2023

PV Formula in Excel

PV Formula in Excel

PV Formula or Present Value formula in Excel calculates any loan amount’s present value. By this, we can calculate the amount of loan required to purchase anything or the asset’s present value when we have taken the loan. This is a complete financial formula not seen in any operation calculations.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

PV Formula in Excel can be used from Insert Function, beside the formula bar, by clicking the icon.

PV Formula

PV Formula in Excel has the following arguments:

  • Rate: It is the rate of interest per compounding period. We can directly use the whole rinterest rate or we can divide this by the total compounding period, lay a month, ,and use the interest applied for a single month.
  • Nper: It is the total number of payments or installments paid in the tenure. Payment can be made monthly, quarterly, or yearly.
  • Pmt: It is a fixed amount we must pay in a period.

The above shown are mandatory arguments required in PV Formula. Below are some optional arguments as well;

  • Fv: The future value of a borrowed asset at the end of all payments of all periods is made. If we do not keep this value, it will automatically be considered 0.
  • Type: Use 1 if the EMI is paid at the start of the month, and 0 if the EMI is paid at the end of the month. For this, if we do not keep them in Excel automatically, it will be considered as 0.

How to Use PV Formula in Excel?

PV formula in Excel is very simple and easy to use. Let’s understand how to use PV Formula in Excel with some examples.

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

Excel PV Formula – Example #1

We have data where a person wants to borrow a loan for an item he wants to buy. A bank is offering a loan with an interest rate of 8% yearly. And that person can pay the loan for 10 years with a fixed monthly payment of near about Rs. 1000/-

If we summarize the above data, then we will have;

Example 1-1

As we have discussed in the argument above, we can k the followingeep a rate of interest as it is, or we can divide it within the formula, or else we can first scrub the argument separately and then use it in the formula. For a better understanding, let’s scrub the data separately first. For that, we need a rate of interest applicable per month which will be our Rate, and the total number of payments that will be made in the whole tenure of the loan, which will,l be our Nper.

Now, as per the Input data we have seen above, our Rate is calculated by using the formula =B2/B4

PV Formula Example 1-2

The Result w.ill be as given below.

PV Formula Example 1-3

Nper is calculated by using the formula =B3*B4

PV Formula Example 1-4

The Result will be as shown below.

PV Formula Example 1-5

Now we can directly use these parameters in our PV Formula. First, go to the cell where we need the output. Then go to the Insert function option located beside the formula bar, as shown below.

PV Formula Example 1-6

Once we do that, we will get the Insert function box. From there, under the Financial category Or select a category option,, search PV function from the list. Or else, we can keep the Or select a category as ALL and look for the required function as shown below. And click on Ok.

PV Formula Example 1-7

Now for the selected cell for output E2, we will get an argument box for PV Function. Now from there, select the Rate, Nper, and Pmt as calculated above.

PV Formula Example 1-8

As we can see for the function argument of PV Formula, we have selected all the necessary cells in excel. At the bottom left of the argument box, we will get the result of the applied function. Here we are getting the result is negative, which means the borrowed value of the loan amount.

PV Formula Example 1-9

If we need to use this data in another form, then we can apply a negative sign (“-“) in the Pmt argument box to get a positive result. And once we apply the negative signs, we will see the formula result is positive in the bottom left of the argument box. Once done, click on Ok.

PV Formula Example 1-10

Once we click on Ok, we will get the result in the selected cell, as shown below.

Result of Example 1-11

The calculated present value loan amount in INR (Indian Rupees) Rs. 82,421.48/- is the final amount that can be sanctioned to that person for 10-year tenure with an interest rate of 8 if that person pays Rs. 1000/- monthly EMI.

Excel PV Formula – Example #2

Below is another example for which we need to calculate a Present Value or a Discounted value.

Example 2-1

For this, go to the edit mode of the cell, where we need to see the output by typing the “=” sign (Equal). And search and select PV Formula in Excel as shown below.

PV Formula Example 2-2

Here we will directly use the data and crunch it in the formula, as shown below. Here we have divided the Rate by the total number of months of the loan period. And Nper is kept at 12 because the loan is only for 12 months. And we have considered the compounding period as 30 Years, which is our FV.

Calculation of Example 2-3

Now press the Enter key to get the result, as shown below.

Result of Example 2-4

As the obtained value PV is Rs. 4067.80/- which means the present value of the loan amount for 30 years of compounding will be Rs. 4067.80/-

Things to Remember

  • A negative sign used in Example 1 shows the borrowed loan amount.
  • Keeping “0” for Type tells us that the payment is made at the end of the month.
  • FV is optional, but in some cases where the compound interest rate is applicable, it is recommended to consider FV (Future Value).

Recommended Articles

This has been a guide to PV Formula in Excel. Here we discuss how to use the PV formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. PV Function in Excel
  2. FV Function in Excel
  3. Excel Match Function
  4. PROPER Function in Excel
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download PV Formula Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download PV Formula Excel Template

EDUCBA

डाउनलोड PV Formula Excel Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more