EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Excel Excel Resources Financial Functions in Excel Excel PRICE Function

Excel PRICE Function

Manisha Sharma
Article byManisha Sharma
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 10, 2023

PRICE Function in Excel

PRICE in Excel

The price function in excel is used to calculating what would be the price need to pay off a bond per 100 units (mostly in Dollars) which also pays the periodic interest.

The price function is financial in Excel. This is mostly used when an investor borrows money by selling bonds instead of stocks. It requires a few more attributes, usually more than other financial functions such as settlement, maturity, yield, redemption, and frequency as mandatory attributes.

ADVERTISEMENT
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests

Start Your Free Excel Course

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

PRICE Formula in Excel:

Below is the PRICE Formula:

PRICE Formula in Excel

Explanation of Price Function in Excel

The PRICE Formula in Excel has 7 segments:

  1. Settlement: This refers to the calendar day on which the deal is settled. The argument passed to this bracket is the date following the date of issue when the security or bond is traded on the market to the entity that is the buyer of said security bond.
  2. Maturity: This bracket accepts a date as a valid argument. It is the calendar day on which the security or bond reaches its expiration, and the principal amount is paid back to a person or entity holding the bond.
  3. Rate: This bracket refers to the annual interest rate of the security or bond at which coupon payments are processed or made.
  4. Yld: This bracket refers to the annual yield of the bond or security in question, i.e. the yearly rate of interest archetypal of any adverse probability projection of the bond.
  5. Redemption: This section refers to the security value; every $100 face value is reimbursed to the bond owner on the redemption date.
  6. Frequency: This bracket refers to the rate of occurrence of coupon payments made every year.

Let us understand the Frequency segment a little more:

The payments can be made Monthly, Annually, Semi-annually, or Quarterly. In these cases, the Frequency would be as follows:

Frequency in PRICE Function

  1. Basis: This bracket is optional and refers to any integer argument which specifies the financial day counting basis.

We shall see the possible values for “Basis” in the table below:

Basis in PRICE Function

How to Use the PRICE Function in Excel?

PRICE Function in Excel is very simple and easy to use. Let’s understand the working of the PRICE Function in Excel with some examples.

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

Example #1

Suppose we are given the following data to calculate the price function in Excel.

Example 1-1

The following screenshot shows us how the PRICE Excel function prices a bond.

Example 1-2

Example 1-3

Example 1-4

So the Final Result will be :

Final Result

Things to Remember

  • For computation, Excel’s Date format is linear or sequential. That means the default value 1 refers to 1st January 1900, so 2 would ideally be the following day, i.e. 2nd January 1900.
  • All the variables passed as Settlement, frequency, maturity, and basis value should be valid integers, i.e., floating-point numbers.
  • If the value passed as maturity or the day of settlement is not a rational date, in that case, the formula of PRICE will result in the #VALUE! error.
  • If rate < 0 or if Yld < 0 or redemption ≤ 0, then PRICE would return a #NUM! error.
  • If the value passed as a frequency in the formula of the PRICE function is anything other than 4, 2, or 1, then the PRICE function would return the #NUM! error as a result.
  • If the basis is greater than 4 or If the basis is less than 0, then the PRICE function will return the #NUM! error.
  • If maturity value ≤ settlement value, in that case, a #NUM! the PRICE function would return the error.

Thus, it might also be wise to enclose the PRICE function with an IFERROR function, i.e., use the PRICE function inside an IFERROR function to handle the various error cases that might arise in the Frequency, Basis, Settlement Value, etc.

IFERROR With PRICE Formula

So the result will be :

Result

Recommended Articles

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

  1. SUBSTITUTE Function in Excel
  2. Excel CORREL Function
  3. YIELD Excel Function
  4. CEILING Function in Excel
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
ADVERTISEMENT
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

EDUCBA

Download PRICE Function Excel Template

Let’s Get Started

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

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?

EDUCBA

Download PRICE Function Excel Template

EDUCBA

डाउनलोड PRICE Function 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

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW