Left-handed IRR, right-handed NPV, master the password of the road to wealth

The popularity of smart phones has made the world a square inch under our fingertips.

At the same time that all kinds of information explode, all kinds of financial information are getting closer and closer to our lives. Investment is no longer just a corporate behavior, it is also a content worthy of attention for individuals.

But to settle on a very small example, if project A and project B can invest 25W, and the current discount rate is 15% (discount rate: refers to the future money converted to the present, the less / more part The ratio of money to future money), which project is more cost-effective for us to invest?

If you know very little about finance, you may be a little panicked when you see this.

In data analysis, there is a special calculation method for this problem, which is used to process this kind of data, so as to select the value that maximizes the benefit. Its name is IRR, and another data concept related to it, NPV.

Next, we will briefly introduce these two concepts for everyone.

IRR and NPV

NPV: Net Present Value, net present value. Converting the amount you will get in the future into the amount you get now is similar to interest, but the process of calculating interest in reverse.

Suppose you get 15 yuan tomorrow and the discount rate is 10%, which is converted to 15/1.1=13.63 (yuan), and then subtract the investment cost to get the cumulative net present value. The larger the accumulated net present value, the better. Theoretically speaking, the net present value is \>0, and the content of this investment is profitable.

Use a table to explain the content of this data: when both A and B projects are 10w yuan, the discount rate is 10%, and the duration of both projects is 5 years.

(The picture comes from the Internet)

The final calculation is that although the sum of the final total amount of the two projects in the previous years is 18.52w, in this process, the time for obtaining more returns with B is even earlier. The calculation result of NPV is to remove the part of currency depreciation, the result is B=3.96>A=3.02, obviously B project is more worthy of investment.

IRR: Internal Rate of Return, internal rate of return. This value refers to the discount rate when the cumulative net present value is zero. This value represents the maximum currency depreciation ratio that the project can withstand (profit space, risk resistance). To get this value, you need to constantly use different discount rates to calculate, and find the time when the NPV is equal to zero or close to zero.

It is the rate of return that an investment aspires to achieve. The larger the indicator, the better.

Let's take a look at the example from Gang:

(The picture comes from the Internet)

In this picture, the NPV of Project A is zero, and the discount rate used at this time is 18.45%. Let's say that the IRR at this time is 18.45%

The larger the value, the stronger our choice to resist risks. Today, investment methods such as stocks, funds, gold, real estate, and futures are familiar to many financial planners. But how effective the investment is, our usual judgment is only limited to the amount of income, lacking a more scientific basis for judgment. At this time, the internal rate of return (IRR) indicator is a very effective and intuitive judgment tool.

IRR calculation

Scientific Computing IRR

In actual use, if we want to obtain the IRR value, we need to perform certain calculations.

Before understanding the relevant content, I thought it was such a calculation process:

However, in this calculation process, Newton's iterative formula is used for calculation.

Let the final value of the high-order unknown IRR be x, let t = 1/(1+x), we also need to understand (xn)'= nxn-1, n≠0. According to the Newton iteration formula x n+1 = xn-f(xn)/f'(xn)

Then bring in: t n+1 = tn – [a(tn +tn2 +tn3 +tn4)+b tn5 – c]/ [a(1 +2tn +3tn2 +4tn3)+b*5tn4]

The final value of x is IRR.

IRR of "Thousand Meters and Thousand Values" in the spreadsheet

Due to the complexity of this calculation process, the IRR calculation results can be directly obtained through the use of formulas in many data processing tools. For example, Excel can directly realize the calculation of IRR, and electronic forms such as Google Sheet also support the calculation of this content.

However, since IRR is the result of iterative calculation, the calculation result in the spreadsheet is multi-valued, and the return rate is selected to be close to the return rate by setting the rate of return estimate. However, the IRR results calculated by different iterative algorithms and the number of iterations are different. Even if the rate of return is the same, different algorithms will calculate different values. At the same time, the number of iterations will make the results of the calculations different in precision, and these values will not Exactly the same.

As a technical consultant, you always need to deal with a variety of problems from customers. In a certain technical support, I encountered a customer complaining that the IRR calculation value of SpreadJS is different from that of Excel.

The customer tried to analyze the logic of SpreadJS to calculate the IRR and found that the scenario where the NPV changes from negative to positive is normal, but there will be problems with the NPV from positive to negative. The following is the customer's analysis ideas.

For this question mentioned by the user, let us use the data provided by the user together to calculate:

(Data content used by the customer: IRR different GUESS values IRR calculation you are interested, you can try it together)

In this table, we will find that the three results of -8.34%, 0.98%, and 289% are correct.

The reason for this problem is actually that the calculation of IRR itself requires a lot of iterations, and different algorithms and values will lead to different final calculation results; another reason is that the algorithms and strategies in Excel are unknown, and there is currently no information document to explain Excel's strategy. SpreadJS can only speculate on Excel's strategy based on experience at this stage, but there are still many scenarios that cannot be understood.

For example, A1 is set to -100, A2: A239 is set to 0, and A240 is set to 100, which means that 240 cycles have earned 200. The result of calculating IRR (A1:A240) with Excel is DIV/0; and the calculation result of 1% in the above figure should be a more reasonable result. Even if the estimated value is given 0.1, Excel still returns 298%. But obviously this number is too optimistic.

These problems are not completely unified in the industry. In the test process, we have also used GoogleSheet, and the calculation results are as follows:

In comparison, our calculation results are the closest to Excel calculations currently in use. And our follow-up research and development are also constantly exploring, trying to provide a better answer to this question.

In summary, as an estimated value, IRR itself has uncertainty, and there is no standard to determine which algorithm is the correct algorithm. For inconsistencies with Excel results, we will try our best to maintain with Excel under the premise of ensuring that the results are reasonable. Unanimous. At the same time, in some scenarios, we will keep our calculation results.

Summarize

Seeing this, you may want to say that the IRR standard is not uniform in the electronic form, but SpreadJS handed over a copy of its own answer sheet in the face of this problem.

In the follow-up, we will also bring you more related content about the front-end spreadsheet revealing the secret, think it’s good, please like it~

Please indicate the source for reprinting: Grape City official website , Grape City provides developers with professional development tools, solutions and services, and empowers developers.