Shulou(Shulou.com)11/24 Report--

No matter how busy your work is, you should also look at these five sets of Excel formulas to ensure that you get off work on time! "

In the calculation of financial investment, the payback period is a very important reference index, which refers to the time from investment to principal recovery.

Different from net present value NPV and internal rate of return IRR, there is no special payback period function in Excel.

Therefore, in order to calculate the payback period, financial friends, it can be said that the eight Immortals cross the sea, each showing their magical powers!

One of the most humble methods Xiaohua has ever seen is to use the IF function to build auxiliary lines and then sum up the payback period.

In the case of ▋, the C4 formula for calculating the exact payback period of 1:IF + auxiliary line is as follows:

= IF (C0MAX (- B3C2MAX), 1)

Description of the formula:

Use the IF function to judge the condition. If the cumulative operating cash flow of the current period is less than or equal to 0, 1 is returned.

If the cumulative operating cash flow of the current period is greater than 0, the larger value m between the negative number and 0 of the ratio of the cumulative operating cash flow of the previous period to the current operating cash flow is returned (MAX is used to determine the size).

At this point, m is positive only if the cumulative operating cash flow is positive for the first time in the current period, otherwise the MAX function returns 0. The result of summing the auxiliary rows is the exact payback period.

It is not difficult to find that after the cumulative operating cash flow returns to positive, when the period value is negative or the cumulative value turns negative again, the formula can not be calculated correctly.

The auxiliary line + logic is complex, such a formula is still full of loopholes, is the payback period calculation problem really so difficult to solve?

Actually this is not so!

Learn these formulas that Xiaohua shares so that you can easily handle them.

01, the method of finding the integer payback period in many cases, when we calculate the investment payback period, we do not need to be accurate to the decimal as in example 1, but only integer digits (equivalent to the upward rounding of the results of example 1).

In this case, there are many formulas available. Below, floret shares only three of the more classic methods.

▋ example 2:COUNTIF method to calculate the integer period if the remaining operating period after the cumulative operating cash flow returns to positive will not become negative, then the first positive time is the investment payback period.

In the figure below, after the cumulative operating cash flow is positive in period 4, the remaining periods 5-6 are positive and do not turn negative. At this time, the first positive time is in period 4, and the investment recovery period is 4.

In this case, the problem of calculating the payback period is equivalent to counting the number of negative numbers n in a set of values representing cumulative operating cash flow. if this set of values includes the 0th period that represents the beginning of the first investment period, then n is the investment payback period, otherwise nasty 1 is the investment payback period.

Therefore, it is reasonable to use COUNTIF function to count the number of negative numbers and then calculate the payback period of investment.

The formula is as follows:

= COUNTIB3:H3,0)

Description of the formula:

The COUNTIF function is used to count the number of cells that meet the conditions. Its first parameter (conditional region) B3:H3 is an array of cumulative operating cash flows containing the period 0, and the second parameter is set to "

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.