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

# How to Select Cells by Value in Microsoft Excel

When you have a lot of data in a table and want only to select a group of cells with a certain value we can use Excel search for quick selection of those cells.

From the following table of data I want to select all the cells that have the value \$ 1,000 and once selected place the cell text in bold

Selecting by the Search Method

An alternative is to use the Excel search box to select the cells that meet the condition set. To do that we open the search bog, then we specify the value we’re looking for and finally click the Find All button.

Excel display all the cells that match the specified value, we select only the results for the worksheet cells are also selected Tip: To select all results can click on the first row of results and then press the Shift key and click the last row. Since you have selected all results, close the search window and observe the cells selected in the worksheet ready to apply the text in bold

## Using a Conditional Formatting Rule

If we make a selection of cells to make a format modification, then we can use an alternative method Once we have selected the range containing the information (b2:E16) I must press the Condittional Format button on the Home tab and select the Highlight cell rules option and then Equal To button.

In the dialog box I have to specify the quantity that I’m looking for and select the Custom Format option.

Then I’m going to select a Bold style for the fonts to format the cells that comply with this rule

Then click OK to close the dialog box and then click OK to apply the conditional formatting rule, we will have the cells of value equal to \$1000 with a bold style format. Excel Total

# How to Use Excel Comments

Excel allows you to insert comments in any cell of your Worksheet in Microsoft Excel. Those Excel Comments are very useful to make a check, give some instructions or simply give a detailed explanation about the cell content.

## Add a comment to a Cell

The first step is to select the cell that you will add the comment and then go to the Review tab and in the group Comments and press the New Comment button (In MAC only New). Clicking on that button, the box necessary to enter the comment is displayed.

Enter your comment in the box and to finish click outside the box. If you need to enlarge the box only positions the mouse over any of the corners and drag the edge to have the desired size. Excel identifies a cell that has a comment through a small triangle in the upper right corner of the cell. To see the comment from a cell only you must position the mouse over that cell and Excel will display the comment.

## Modify or Eliminate a Comment

When you select a cell containing a comment Edit and Delete Comment buttons are enabled.

Clicking on the Edit Comment button will show the comment box allowing you to do the editing again. If instead you want to delete the comment is sufficient to press the Delete button. Now if you have an Excel worksheet that contains many comments, you can use the Previous and Next buttons to move between each. Or conversely, if you want to display all comments sheet at the same time you can click Show All Comments button. You can learn more about Microsoft Excel Comments and Review options here: Excel School

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

## 1. The HOUR function

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

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:

## 3. The SECOND function

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

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

# Excel Visual Basic (VBA) for Beginners to Cleanse a Dataset

This Excel video series helps Excel users just starting out with code in the Visual Basic program, or those who have never coded before, who are curious about it and just heard about it, about its powerful utilisation. In a lot of cases, Excel users find themselves engaged in tricky manual tasks in study subjets or work tasks, that take up time, create stress and lose of time. Data cleansing is one such task.

To explain that, if you have ever gone through a list of data and looked for mistakes (in dates or numbers, for example) then you have done a data cleansing task. With a little coding knowledge, that we are going to see in the following videos, it is possible to dramatically speed up this kind of task, reduce the stress and increase your productivity. You can learn more about Microsoft Excel go on here: Excel School

The 1st Part: Introduction

The 2nd Part: Beginning with VBA

The 3rd Part: Using Messaging Boxes

The 4th Part: Stopping the program

The 5th Part: Finishing and testing the program

# 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

# Dependent Data Validation Lists in Microsoft Excel

This video will walk you through the process of setting up and using dependent data validation lists with Microsoft Excel. You could refer to this as cascading data validation. This is very useful to create templates of databases that you need to be completed by other users. The other users only have a limited option to fill each cell. You can learn more about Microsoft Excel productivity here: Excel School

# Using Navigation Tools in Microsoft Excel

Learn about essential training in Microsoft Excel, one of the first tools that you have to begin to use, the navigation. Save a lot of time, learning basic keyboard combinations, or commands with the mouse, to navigate between worksheets, workbooks, cells, columns, rows, etc. And begin to increase of the productivity in the using of Microsoft Excel You can learn more about Microsoft Excel productivity here: Excel School

# Good Looking Gauges in Microsoft Excel

Show really cool gauges to show indicators in your work. Let’s make good presentations of data learning with this video. Microsoft Excel has a lot of functionalities, not only create charts and tables, with a bit of creativity and learning from experts, take the most out of this powerful tool. You can learn more about Microsoft Excel charts and applications here: Excel Training Videos

# How To Show Negatives In A Chart In A Different Color Using The Invert If Negative Feature In Excel

With this video we are going to use the “Invert If Negative” feature in the charts. This is useful to show the negative values in the chart with another color and you don’t have to manually change the color in each bar. You can learn more about Microsoft Excel charts and applications here: Excel Training Videos