Tag: 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.

 

 

 

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 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

 

 

 

The Microsoft Excel Quick Access Toolbar

Microsoft Excel Quick Access Toolbar

Learn how to use the Quick Access Toolbar, showed in the left upper corner of the Excel Window.

In this bar you can put every excel command that you use frequently. The most common are the Save button, Print Preview button, Undo and Redo commands, and so on.

The Microsoft Excel Quick Access Toolbar

But if you right-click you can enter to the customizing menu, and put the Excel command that you use very often. See in the following video how to customize the Quick Access Toolbar:

Using the Excel Counting Functions

Using the Excel Counting Functions

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.

The COUNTA function counts the number of cells that are not empty in a range.

The COUNTBLANK function counts empty cells in a specified range of cells.

Convert Time to Seconds in Microsoft Excel

Convert Time to Seconds in Microsoft Excel

 

Do you ever have had to show a data of time in seconds? In Excel Doing it is really simple and will take only a few seconds.

We are used to express the values of time in hours, minutes and seconds. But probably sometimes you need these values only time expressed in seconds. Suppose the following example.

Convert To Seconds Time Data Initial Input

In this example we have a data equivalent to 1 hour, 35 minutes and 15 seconds. To find quickly the number of seconds is sufficient to modify the format of the cell. Right click on the cell and select Format Cells option and should go to the Custom section.

Format Custom Cells Seconds

In the Type text box you should specify the custom format [ss] and click OK

Convert To Seconds Time Data Result

Excel will show the total of equivalent seconds of the time data in the cell.

 

Concatenate Functions with Microsoft Excel

Concatenate Functions in Microsoft Excel

 

There are some functions in Excel where any arguments allow to specify a condition so that if it is met will perform the action. Such is the case of COUNTIF function or SUMIF function.

Such functions are useful to apply an action a cause of certain condition. For example, if I want to count all the people in the range B1: B20 that are older than 30 years can apply the following formula: =COUNTIF(B1:B20, ">30")

Concatenate Functions with Excel Initial Data

But now, what if I want to count only those who are aged above average? One option would be to get the average in a separate cell and then make the comparison with the obtained value to determine whether or not you can count every item. Rather than make a separate calculation, we can make use a special quality of the COUNTIF function argument condition.

This quality is due to the argument condition is a text string and therefore can make a chain with the result of another function. Observe carefully how to perform this concatenation in the second argument of the function: =COUNTIF(B2:B21, ">"&AVERAGE(B2:B21)) Concatenation in Excel is done with “&” and for this example I’m using the AVERAGE function so that Excel gets the average of the range and then the concatenation to get ready the argument condition.

After making the evaluation of this expression Excel shows the correct result.

Concatenate Functions with Excel COUNTIF and AVERAGE

For this example I used the AVERAGE function, but you can use any function that helps form the argument condition you need. (Source: Excel Total)

 

Goal Seek in Microsoft Excel

Learn About Goal Seek in Microsoft Excel

 

 

Excel includes several tools for data analysis and Goal Seek is a technique used to easily find the number that fulfills the necessary conditions to achieve a goal.
 

This tool will help you do many tests of values in a formula to find the exact value that meets the conditions. That is, if we know the result of a formula, but we are not sure of the value of one of its arguments, then Goal Seek will help us to test various scenarios to find the exact value needed. For example, suppose we have the following problem.
 
Goal Seek Initial Data
 
I have a number multiplied by another number X and this should give me the result shown. Although this is an easy problem to solve mathematically will be useful to show the functionality of Goal Seek. I begin by defining an important formula. In cell C3 will place the formula to be met to obtain the desired result, ie, = B1 * B2 it should result in 689
 
Goal Seek Formula
 
For now the result in C3 is zero, because there is no value in cell B2, however, when you go looking for the appropriate value for B2, the value of cell C3 will become the same as B3 when the correct value is found.
 
Goal Seek Command
 
Now you need to click the Data tab and select If Analysis where an options menu will be displayed and you must choose Goal Seek.
 
Goal Seek Command Button
 
Immediately the dialog box Goal Seek is displayed, where you must fill in the following text boxes.
 
Goal Seek Dialog Box
 
– Set cell: It is the cell that contains the formula – To Value: This is the number we want as a result of the formula. – By changing cell. The cell to be modified to have the desired result. Once the parameters have been set, you must click on the OK button and Excel will begin to enhance the necessary calculations. When have found a suitable result the Goal Seek Status dialog box is displayed.
 
Goal Seek Status Dialog Box
 
This dialog box just notified that it has reached the target by changing the specified cell. When you close the dialog box you can see the result. For our example Excel was doing several tests until the result of 146,60 is the number multiplied by 4,7 will give the expected result.
 
Goal Seek Results
 
(Source: Excel Total)

 

Extract Information from a Date in Microsoft Excel

Learn to Extract Information from a Date in Microsoft Excel

 

 

Using dates in Excel is very common and probably more than once you need to extract information from a specific date as the day, the month or the year. TEXT function offers an alternative to obtain such information.

 

 

Show the month of a date
 
Suppose the next column of date, the first task will be to obtain the month of each and present it as a text string. The function used in column B is: =TEXT(A2, "m")

 
Extract Month Information from a Date – Single Number

 
Indeed I get the month number each date. Now see what happens if I use the same function but in the second argument put two letters “m” as follows: =TEXT(A2, "mm")

 
Extract Month Information from a Date – Two Numbers

 
The difference from the previous result is that now each month are displayed with two digits putting a leading zero if required. In the next picture you can see what happens if in the second parameter of the function TEXT put three, four and five letters “m”.

 
Extract Month Information from a Date – Other Formats

 
Show the day of a date
 
Similarly as in the previous example we can obtain information of the day. Notice how using the TEXT function and specifying the second parameter as the letter “d” we may obtain information about the day.

 
Extract Day Information from a Date

 
TEXT function helps us to specifically identify the name of the day and thus can make an analysis of information according to specific days of the week.
 
Show the year of a date
 
Finally, for the year there are only two possible alternatives that are getting the year with two digits or four-digit format.

 
Extract Year Information from a Date
 
Working with dates is very important and aware of the possibility offered by the TEXT function to work with this type of data will be very useful. (Source: Excel Total)