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

Small partners who are familiar with finance and investment must be familiar with NPV. NPV, that is, net present value, is an important index to measure investment income. Generally speaking, the greater the net present value of an investment, the better the return.

The NPV function in Excel is tailored for calculating NPV.

However, it is such a seemingly insipid and serious function, unexpectedly let the Excel bosses play tricks!

Want to know the alternative use of the NPV function?

Keep up with the rhythm of Xiaohua, take you to appreciate the different NPV function!

1. The cross-difference connoisseur Xiao K manages four stores and needs to calculate the total profit through the income and expenditure statement of each store every day. The income and expenditure of each store is expressed by a positive number. As shown in the figure below, how to set the calculation formula of C10 cell is the most concise?

The conventional idea must be to use the SUMIF function to deal with it, and the formula is as follows:

= SUM (C2:C9)-2*SUMIF (B2:B9, "expenditure", C2:C9)

Although this formula is much simpler than the formula in which two SUMIF functions are subtracted, if you use the NPV function to deal with this problem, the cleanliness of the formula will definitely surprise your chin!

Why don't you take a look at it?

NPV interlaced difference formula:

=-NPV (- 2meme C2RU C9)

Description of the formula:

The formula is based on the calculation principle and mathematical logic of NPV function.

First of all, we need to understand how the NPV function is calculated, that is, each value of parameter 2, value, is multiplied by the specified secondary of 1 / (1+rate) (the ordinal value of the current value) before summing.

In the formula, we set rate to-2, so 1 / (1+rate) equals-1.

Based on the principle that the odd power of-1 is-1 and the even power is 1, each odd term of the second parameter of the NPV function takes its opposite number and the even term takes itself, thus the cross difference operation is realized.

Since the result of this calculation is that the income is negative and the expenditure is positive, you need to add a negative sign before the formula.

At the same time, because the NPV output defaults to currency, you need to reset the cell number format to [regular].

2, mixed text fetch expert mixed text fetch is an Excel formula problem, you must have seen many different solutions, but you may not know, NPV function can also solve this problem, especially from a pile of chaotic data to extract all the values and combination, NPV is handy.

As in the following example, due to the hasty time of the recording process, the customer information and the customer phone number are mixed, and the format is random, there are no clear rules, the only thing that can be determined is that the customer's phone number is recorded completely and in the right order.

In this case, how to extract the customer's phone number?

The formula is as follows:

{= (--MID (NPV (9 Magi ifer) (- MID (A2 recorder row ($1), 1), ""), 1), 3100)-1) / 10}

Description of the formula:

① ROW ($1 100): returns an ordered array of 1 100

② MID (A2, ①, 1): extract each character in A2 one by one

③-- ②: converts numbers extracted by MID into numeric values

④ IFERROR (③, "): non-digits extracted by MID are converted to numeric values by double negative signs or spelling errors # NAME?, is converted to empty using IFERROR here, so that of all the characters proposed by MID, only all digits are retained and the rest are empty.

⑤ NPV (9, ④, 1): set rate to 9, so that 1 / (1+rate) equals 1 to 10, and all the numbers in ④ {1 / 7, 7, 8, 9, 3, 2, 4, 5} are divided by 10, 100, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, .}, added to get 0.17789123245, that is, to achieve the effect of arranging all the numbers after the decimal point. In order to avoid the error of subsequent calculation caused by the last digit 0, NPV increases a value value of 1, thus adding the number 0.17789123245 after the last bit of the number string is converted into 0.177891232451.

⑥ MID (⑤, 3100): all digits after the decimal point are extracted

⑦-- ⑥: same as ③, converts the numbers extracted by MID into numerical values

⑧ {= (⑦-1) / 10}: remove the extra Mantissa 1 added in ⑤. The outermost curly braces indicate that this is an array formula and you need to press Ctrl+Shift+Enter at the same time to calculate correctly.

3. A friend of the digital inversion master asked Xiaohua how to arrange the code composed of 10 numbers in reverse order.

The answer given by Xiaohua shocked him! Yes, it's the NPV function.

The formula is as follows:

{= NPV (- 0.9 mai muri mid (A2Mae row ($1), 1)) / 10}

Description of the formula:

MID (A2Graver row ($1) extracts the numbers in A2 cells one by one, and converts text numbers into numeric numbers by double minus sign "-" for NPV to calculate.

Set the parameter rate of the NPV function to-0.9, so that 1 / (1+rate) is equal to 10 all the numbers in the A2 are multiplied by 10, 10, 100, and then added, so that the first number is filled in the ten digits, the second number in the hundred, the third number in the thousand, and so on. Finally, divide the whole of the formula by 10 to complete the digital inversion.

These are the three alternative uses of the NPV function shared by Xiaohua. Have you learned it? Take a look back:

❶ rate is set to-2 to achieve cross-cut.

❷ rate is set to 9 to complete mixed text fetch.

❸ rate is set to-0.9, easy to take down the digital inversion.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua

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.