Category: Functions

PRICE Excel Function

What is the PRICE Excel Function Used for?

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

 

PRICE

Returns the price per $100 face value of a security that pays periodic interest.

 

PRICE(settlement; maturity; rate; yld; redemption; frequency; [basis])

 

The PRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Rate Required. The security’s annual coupon rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; frequency; and basis are truncated to integers.
  • If settlement or maturity is not a valid date; PRICE returns the #VALUE! error value.
  • If yld < 0 or if rate < 0; PRICE returns the #NUM! error value.
  • If redemption <= 0; PRICE returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; PRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; PRICE returns the #NUM! error value.
  • If settlement => maturity; PRICE returns the #NUM! error value.
  • PRICE is calculated as follows:where:
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in coupon period in which the settlement date falls.
    • N = number of coupons payable between settlement date and redemption date.
    • A = number of days from beginning of coupon period to settlement date.

 

Interactive Example

Change the white cells data and see the result in the yellow cell.

 

 

 

ODDFPRICE in Microsoft Excel

Using the ODDFPRICE Function in Excel

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

ODDFPRICE

Returns the price per $100 face value of a security having an odd (short or long) first period.

 

ODDFPRICE(settlement; maturity; issue; first_coupon; rate; yld; redemption; frequency; [basis])

 

The ODDFPRICE function syntax has the following arguments:

  • Settlement Required. The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer.
  • Maturity Required. The security’s maturity date. The maturity date is the date when the security expires.
  • Issue Required. The security’s issue date.
  • First_coupon Required. The security’s first coupon date.
  • Rate Required. The security’s interest rate.
  • Yld Required. The security’s annual yield.
  • Redemption Required. The security’s redemption value per $100 face value.
  • Frequency Required. The number of coupon payments per year. For annual payments; frequency = 1; for semiannual; frequency = 2; for quarterly; frequency = 4.
  • Basis Optional. The type of day count basis to use.
    BASIS DAY COUNT BASIS
    0 or omitted US (NASD) 30/360
    1 Actual/actual
    2 Actual/360
    3 Actual/365
    4 European 30/360

 

Remarks

 

  • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
  • The settlement date is the date a buyer purchases a coupon; such as a bond. The maturity date is the date when a coupon expires. For example; suppose a 30-year bond is issued on January 1; 2008; and is purchased by a buyer six months later. The issue date would be January 1; 2008; the settlement date would be July 1; 2008; and the maturity date would be January 1; 2038; which is 30 years after the January 1; 2008; issue date.
  • Settlement; maturity; issue; first_coupon; and basis are truncated to integers.
  • If settlement; maturity; issue; or first_coupon is not a valid date; ODDFPRICE returns the #VALUE! error value.
  • If rate < 0 or if yld < 0; ODDFPRICE returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ODDFPRICE returns the #NUM! error value.
  • The following date condition must be satisfied; otherwise; ODDFPRICE returns the #NUM! error value:maturity > first_coupon > settlement > issue
  • ODDFPRICE is calculated as follows:Odd short first coupon:

    where:

      • A = number of days from the beginning of the coupon period to the settlement date (accrued days).
      • DSC = number of days from the settlement to the next coupon date.
      • DFC = number of days from the beginning of the odd first coupon to the first coupon date.
      • E = number of days in the coupon period.
      • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)

    Odd long first coupon:

    where:

    • Ai = number of days from the beginning of the ith; or last; quasi-coupon period within odd period.
    • DCi = number of days from dated date (or issue date) to first quasi-coupon (i = 1) or number of days in quasi-coupon (i = 2;…; i = NC).
    • DSC = number of days from settlement to next coupon date.
    • E = number of days in the coupon period.
    • N = number of coupons payable between the settlement date and the redemption date. (If this number contains a fraction; it is raised to the next whole number.)
    • NC = number of quasi-coupon periods that fit in odd period. (If this number contains a fraction; it is raised to the next whole number.)
    • NLi = normal length in days of the full ith; or last; quasi-coupon period within odd period.
    • Nq = number of whole quasi-coupon periods between settlement date and first coupon.

 

Interactive Example

 

 

 

The 3 Time Functions Most Used in Microsoft Excel

The 3 Time Functions Most Used in Microsoft Excel

 

 
Excel provides three functions that help us work with time information in a Worksheet. These functions are useful for extracting information specific about the hour, the minute and the second of a cell containing data with a Number category of Time.

 

Each function, according to its name, extracts a specific part of the data information (hour, minute or second) with only specifying the cell containing the data you want to analyze. For example, suppose that cell B2 has the following information:

Time Type Data in Microsoft Excel

 

1. The HOUR function

To get only the hour number of this cell you can use the HOUR function as follows:

The Hour Function in Microsoft Excel

Just see that Microsoft Excel returns the 17 value because the hours by default is showed with the 24 hours format.

2. The MINUTE function

To get the result of the minutes we use the MINUTE function as follows:

The Minute Function in Microsoft Excel

 

3. The SECOND function

Finally the last one is the SECOND function to get the value of the seconds of the cell information:

The Second Function in Microsoft Excel

We learn in this post that when you need to extract specific information from a cell value related to time, you can use those 3 functions to get the specific value of hour, minute and second that you can use in later calculations. You can learn more about Microsoft Excel Time functions here: Excel School
 

 

PMT Function Calculating a loan payment

PMT Function Calculating a Loan Payment

 

 

In this video, learn how to calculate the payment for a loan based on constant payments and a constant interest rate. You need to know: the interest rate, the number of periods and the present value of the loan. You can learn more about Microsoft Excel financial functions here: Excel School

 

 

VLOOKUP to 36 Different Tables: CHOOSE, OFFSET or INDIRECT function

VLOOKUP to 36 Different Tables with CHOOSE or OFFSET or INDIRECT function

Learn how to: CHOOSE Function Solution, with VLOOKUP and MATCH functions and the asterisk wild card (zero or more characters) OFFSET Function Solution, with VLOOKUP and MATCH functions and the asterisk wild card (zero or more characters) Check is the results for two columns are equivalent using a logical formula and the COUNTIFS function with FALSE Criteria. INDIRECT function Solution, with VLOOKUP and Defined Names You can learn more about Microsoft Excel functions and applications here: Excel Training Videos

Using OFFSET Function in Databases in Microsoft Excel

Using OFFSET Function in Microsoft Excel

Now we are going to see how works the OFFSET function. With the OFFSET function you can returns a reference that is a number of rows and a number of columns from a cell or range of cells selected With this function you can get the specific value in a database, that allows you to search information in big lists of data Let’s see the video and learn how to use the OFFSET function, you can use it with the MATCH function explained in the last video and search specific values in a Database. You can learn more about Microsoft Excel functions and applications here: Excel Training Videos

Using MATCH Function to Manage Databases in Excel

Using MATCH Function to Manage Databases in Excel

In the following video, we are going to use the MATCH function, this functions used with another useful function the OFFSET function, allows you to search specific values in a database

These two functions are really easy to use, and when you learn to use it very well you can get the most out of the manipulation of data in large lists Let’s see the video and learn how to use initially the MATCH function and let’s begin to get data of big lists and databases.

Excel has more and less 300 functions and you can make a lot of formulas to simplify your work. Learn how to construct formulas with the following tool: Excel Formula Helper E-Book

 

Thank you to Tiger Spreadsheet Solutions for the below video
PV function and FV function in Microsoft Excel

Present Value and Future Value in Microsoft Excel

One of the most commonly used financial functions in Excel are Present value (PV) and Future Value (FV). Both functions use similar arguments so it is only necessary to understand the terminology to use them properly.

The PV() and FV() functions in Excel The arguments for those functions are:

– Nper: The total number of periods. For example, if a monthly payment is made for one year periods total will be 12. If a payment is made monthly for 3 years we will have a total of 36 periods.

– PMT: The payment made each period.

– Rate: The constant interest rate for each period. When we use financial functions must remember that the values can be positive or negative depending on whether you are receiving money or if you are paying. Another important thing to note is that generally interest rates are expressed on an annual basis, so if you’re doing a monthly calculation we divide the interest rate by 12.

The basic sintax for both functions is the following: PV (Rate, Nper, PMT) FV (Rate, Nper, PMT) Now, see the following calculations:

The FV function and the PV function in Microsoft Excel

In this example I have used the same parameters for both the VA function and for the VF function. The future value helps us know how much money we will have in a future date if you start investing today the amount specified with a fixed interest rate. The present value is the value today that we will be making an investment in future payments at a fixed interest rate. Excel Total

The AMORDEGRC Function in Microsoft Excel

 

 

Now we are going to learn all about formulas and functions. This is the best way to learn them:

AMORDEGRC Function

Returns the depreciation for each accounting period. This function is provided for the French accounting system. If an asset is purchased in the middle of the accounting period; the prorated depreciation is taken into account. The function is similar to AMORLINC; except that a depreciation coefficient is applied in the calculation depending on the life of the assets.

 

AMORDEGRC(cost; date_purchased; first_period; salvage; period; rate; [basis])

 

The AMORDEGRC function syntax has the following arguments:

  • Cost Required. The cost of the asset.
  • Date_purchased Required. The date of the purchase of the asset.
  • First_period Required. The date of the end of the first period.
  • Salvage Required. The salvage value at the end of the life of the asset.
  • Period Required. The period.
  • Rate Required. The rate of depreciation.
  • Basis Optional. The year basis to be used.
    BASIS DATE SYSTEM
    0 or omitted 360 days (NASD method)
    1 Actual
    3 365 days in a year
    4 360 days in a year (European method)

 

Remarks

 

    • Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default; January 1; 1900 is serial number 1; and January 1; 2008 is serial number 39448 because it is 39;448 days after January 1; 1900.
    • The depreciation coefficients are:
LIFE OF ASSETS (1/RATE) DEPRECIATION COEFFICIENT
Between 3 and 4 years 1.5
Between 5 and 6 years 2
More than 6 years 2.5
  • The depreciation rate will grow to 50 percent for the period preceding the last period and will grow to 100 percent for the last period.
  • If the life of assets is between 0 (zero) and 1; 1 and 2; 2 and 3; or 4 and 5; the #NUM! error value is returned.

 

Interactive Example