Tag: Excel function

Move or Copy a Formula in Excel

How to Move or Copy a Formula in Excel

 

 

 

You can restructure or reorganize a worksheet by moving an existing formula to a different part of the worksheet. When you move a formula, Excel preserves the formula’s range references.

Excel also enables you to make a copy of a formula, which is a useful technique if you require a duplicate of the formula elsewhere or if you require a formula that is similar to an existing formula. When you copy a formula, Excel adjusts the range references to the new location.

 

Add a Function to a Formula in Excel

Add a Function to a Formula in Excel

 

 

 

To get the benefit of an Excel function, you need to use it within a formula. You can use a function as the only operand in the formula, or you can include the function as part of a larger formula.

In “Understanding Excel Functions,” you learned that Excel has many functions and that most functions take one or more arguments, but it is often difficult to remember a function’s arguments and the order in which they appear. To make it easy to choose the function you need and to add the appropriate arguments, Excel offers the Insert Function feature.

 

 

Using the ACCRINTM Function in Excel

Learning about the ACCRINTM Function in Excel

 

 

ACCRINTM

Returns the accrued interest for a security that pays interest at maturity.

 

ACCRINTM(issue; settlement; rate; par; [basis])

 

The ACCRINTM function syntax has the following arguments:

  • Issue Required. The security’s issue date.
  • Settlement Required. The security’s maturity date.
  • Rate Required. The security’s annual coupon rate.
  • Par Required. The security’s par value. If you omit par; ACCRINTM uses $1;000.
  • 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.
  • Issue; settlement; and basis are truncated to integers.
  • If issue or settlement is not a valid date; ACCRINTM returns the #VALUE! error value.
  • If rate <= 0 or if par <= 0; ACCRINTM returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ACCRINTM returns the #NUM! error value.
  • If issue = settlement; ACCRINTM returns the #NUM! error value.
  • ACCRINT is calculated as follows:

    • A = Number of accrued days counted according to a monthly basis. For interest at maturity items; the number of days from the issue date to the maturity date is used.
    • D = Annual Year Basis.

 

Interactive Example

 
Change the data in the non-colored cells and see how the result changes in the yellow cells.
 

 

 

ACCRINT Function in Excel

Using the ACCRINT Function in Excel

 

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

 

ACCRINT

Returns the accrued interest for a security that pays periodic interest.

 

ACCRINT(issue; first_interest; settlement; rate; par; frequency; [basis]; [calc_method])

 

The ACCRINT function syntax has the following arguments:

  • Issue Required. The security’s issue date.
  • First_interest Required. The security’s first interest date.
  • 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.
  • Rate Required. The security’s annual coupon rate.
  • Par Required. The security’s par value. If you omit par; ACCRINT uses $1;000.
  • 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
  • Calc_method Optional. A logical value that specifies the way to calculate the total accrued interest when the date of settlement is later than the date of first_interest. A value of TRUE (1) returns the total accrued interest from issue to settlement. A value of FALSE (0) returns the accrued interest from first_interest to settlement. If you do not enter the argument; it defaults to TRUE.

 

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.
  • Issue; first_interest; settlement; frequency; and basis are truncated to integers.
  • If issue; first_interest; or settlement is not a valid date; ACCRINT returns the #VALUE! error value.
  • If rate = 0 or if par = 0; ACCRINT returns the #NUM! error value.
  • If frequency is any number other than 1; 2; or 4; ACCRINT returns the #NUM! error value.
  • If basis < 0 or if basis > 4; ACCRINT returns the #NUM! error value.
  • If issue = settlement; ACCRINT returns the #NUM! error value.
  • ACCRINT is calculated as follows:

    • Ai = number of accrued days for the ith quasi-coupon period within odd period.
    • NC = number of quasi-coupon periods that fit in odd period. If this number contains a fraction; raise it to the next whole number.
    • NLi = normal length in days of the ith quasi-coupon period within odd period.

 

Interactive Example

Change the data in the non-colored cells and see how the result changes in the yellow cells.

 

 

How to make a Supply and Demand Chart

How to Make a Supply and Demand Chart

 

 

One of the basics concepts in the economic class in the high school and in the university were the supply and demand law that is useful to know the price and the product quantity to satisfied a specific market

The supply and demand curve is usually plotted using the product price on the vertical axis and the quantity of the product on the horizontal axis. This time we will learn how to make a supply and demand curve in Excel.

SUPPLY CURVE

In simple words, the law of supply refers to producers and to maximize their profits. Therefore, the higher the price of a product, the greater the amount that the product want to provide. And conversely, the lower the price the lower the quantity supplied. The following table contains the data to graph a supply curve in Excel:

Supply Table

To create the corresponding graph is sufficient to select any cell in the table and go to the Chart tab and select the button Line, then Marked Line. Now you must pay attention because it is very likely to get a graph like this:

Supply Chart 1

To correct this graph we can click right on it and choose the Select data. Then we select the series called Quantity and click the Remove button:

Select Data for Supply Demand Chart

Then you have to put in the label Category (X) axis labels the range A3:A7 choosing the cells. As a final step we can change the name of the series Price by select the data source and set the cell A1 as the name of the series in the Name label. At the end we list changes a graph with the supply curve:

Initial Supply Chart

DEMAND CURVE

The law of demand states: Few people want expensive products. But if we have cheap products (almost given away), many people want them. See the following table for the data to graph a demand curve in Excel:

Demand Table

To include a line in our chart representing the demand curve must right click on the graph and select the Select data and add a new series whose values are in the range E3: E7.

Add a Serie for the Supply Chart

By accepting the changes we have ready our chart with a supply and demand curve in Excel.

Supply and Demand Chart in Excel