How to calculate the excess cumulative rate of return of individual stocks in a period of time in excel?

You can use IRR function or ask excel for help yourself.

The IRR function shows all and hides all.

This paper introduces the IRR function in Microsoft Excel (function: a function is a pre-written formula that can operate on one or more values and return one or more values. Functions can simplify and shorten formulas in worksheets, especially when formulas are used to perform long or complex calculations. ) Formula syntax and usage.

explain

Returns the internal rate of return of a set of cash flows expressed in numerical values. These cash flows do not need to be balanced, but as annuities, they must be generated periodically, such as monthly or yearly. Internal rate of return is the rate of return on investment, including regular payment (negative value) and regular return (positive value).

grammar

IRR(values, [guess])IRR function syntax has the following parameters (parameters: values that provide information for operations, events, methods, properties, functions or procedures. ):

Value is required. A reference to an array or cell that contains numbers used to calculate the internal rate of return.

The value must contain at least one positive value and one negative value to calculate the returned internal rate of return.

The function IRR explains the order of cash flow according to the order of values. Therefore, you should ensure that the values of payment and income are entered in the required order.

If an array or reference contains text, logical values or blank cells, these values are ignored.

Guess is optional. Estimated value of IRR calculation result.

Microsoft Excel uses iterative method to calculate the function IRR. Starting from guess, the function IRR performs cyclic calculation until the accuracy of the result reaches 0.0000 1%. If the function IRR does not find the result after 20 iterations, the error value is #NUM! .

In most cases, it is not necessary to provide a guess for the calculation of the function IRR. If guess is omitted, it is assumed to be 0. 1 (10%).

If the function IRR returns the error value #NUM! Or the result is not close to the expected value, you can try again with another guess.

explain

The function IRR is closely related to the function NPV (net present value function). The rate of return calculated by IRR is the interest rate when the net present value is 0. The following formula shows the relationship between function NPV and function IRR:

NPV (IRR (B 1: B6), B 1: B6) is equal to 3.60E-08 (in the accuracy requirements of IRR calculation, the value of 3.60E-08 can be regarded as a valid value of 0).

example

It may be easier to understand if you copy the example into a blank worksheet.

How to copy an example?

Choose the examples in this article. Important: Do not select row or column headings.

Select an example from Help, and then press CTRL+C. In Excel, create a blank workbook or worksheet. In the worksheet, select the cell A 1 and press CTRL+V. Important In order for this example to work properly, it must be pasted into the cell A 1 on the worksheet.

To switch between viewing the results and viewing the formula that returns the results, press Ctrl+ ` (accent), or on the Formula tab, in the Formula Review group, click the Show Formula button. After copying the sample to a blank worksheet, you can modify the sample as needed.

three

four

five

six

seven

eight

nine

1 1

A b

data description

-The initial cost of starting a business is 70,000.

12,000 net income for the first year