current position:Home>Left hand IRR, right hand NPV, master the password of getting rich

Left hand IRR, right hand NPV, master the password of getting rich

2022-01-29 15:24:02 Grape City technical team

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

While all kinds of information explosion appear , All kinds of financial information are closer and closer to our life . Investment is no longer just corporate behavior , For individuals , It is also worthy of attention .

But settle on a small example , If the project A And projects B Can invest 25W, Now the discount rate is 15%( discount rate : It refers to the conversion of future money to the present , Less / The ratio of the extra money to the future money ), Which project do we invest in more cost-effective ?

You who know little about finance , You may be a little flustered to see here .

And this problem is in data analysis , There is a special calculation method , Used to process this data , So as to select the value of maximizing benefits , Its name is ——IRR, Another data concept related to it ,NPV.

Next, let's briefly introduce these two concepts .


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

Suppose tomorrow you get 15 Yuan , discount rate 10%, Converted into... Now 15/1.1=13.63( element ), The accumulated net present value is obtained by subtracting the investment cost after accumulation . The greater the cumulative NPV, the better , In theory, net cash >0, The content of this investment is profitable .

Explain the data content with a table : When A、B All items are 10w Yuan time , The discount rate is 10%, The duration of both projects is 5 year .

( The picture comes from the Internet )

The final calculation is that although the sum of the total amount of the two projects in previous years is 18.52w, But in the process , With B The time to get more returns is earlier .NPV The result of the calculation is to eliminate the depreciation of the currency , The result is B=3.96>A=3.02, obviously B The project is more worthy of investment .

IRR:Internal Rate of Return, Internal rate of return . This value means that the cumulative net present value is 0 The discount rate at the time of . This value represents the maximum currency depreciation rate that the project can bear ( Profit space , Resistance to risk ). To get this value, you need to constantly use different discount rates , find NPV Equal to or near zero .

It is the desired rate of return for an investment , The bigger the index, the better .

Let's use the example just now :

( The picture comes from the Internet )

In this picture A Project NPV zero , The discount rate used at this time is 18.45%, Let's say... At this time IRR by 18.45%

The larger the number , The more risk resistant our choices will be . In today's , Stocks 、 fund 、 gold 、 Real estate 、 Futures and other investment methods have been familiar to many financial managers . But how effective is the investment , Our usual judgment is limited to the amount of income , Lack of more scientific judgment basis . At this time, the internal rate of return (IRR) Indicator is a very effective and intuitive judgment tool .

IRR The calculation of

Scientific Computing IRR

In practice , If we want to get IRR The number , Some calculations are needed .

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

But in fact, Newton's iterative formula is used in this calculation process .

Let the higher-order unknowns IRR Final value for x, Make t = 1/(1+x), We also need to understand (xn )'= nxn-1 ,n≠0. According to Newton's iterative 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]

final x The numerical , Namely IRR.

In the spreadsheet “ A thousand meters have a thousand values ” Of IRR

Because this calculation process is very complex , Therefore, in many data processing tools, it can be obtained directly through the use of formulas IRR Calculated results of . such as Excel It can be realized directly in IRR The calculation of ,GoogleSheet And other spreadsheets also support the calculation of this content .

But because of IRR Is the result of iterative calculation , The calculation result in the spreadsheet is multivalued , By setting the rate of return valuation , To choose close to the rate of return . But different iterative algorithms and the number of iterations are different IRR The result is different , Even if the yield valuation is the same , Different algorithms will calculate different values , At the same time, the number of iterations will also make the accuracy of the calculation results different , These values will not be exactly the same .

As a technical consultant , Always need to deal with all kinds of customer problems . And in a technical support , The customer reported that SpreadJS Of IRR Calculate values and Excel Different .

The customer tried to analyze SpreadJS Calculation IRR The logic of , Find out NPV The negative to positive scenario is normal , however NPV From positive to negative, there will be problems , The following is the idea of customer analysis .

For the problem mentioned by the user , Let's use the data provided by the user for calculation :

( Data content used by customers :IRR Different GUESS value IRR Calculation If you are interested, you can try it together )

In this table, we will find -8.34%, 0.98%, 289% All three results are correct .

The reason for this problem is actually IRR The calculation itself requires a lot of iterations , Different algorithms and values will lead to different final calculation results ; Another reason is because Excel Algorithm in 、 Policies are unknown , At present, there are no documents to explain Excel The strategy of .SpreadJS At this stage, we can only infer from experience Excel The strategy of , But there are still many scenes that cannot be understood .

such as ,A1 Set up -100,A2:A239 Set up 0,A240 Set up 100, It means 240 A cycle earned 200. use Excel Calculation IRR(A1:A240) The result is DIV/0; And the calculation results in the figure above 1% It should be a more reasonable result , Even at a given estimate 0.1 Under the circumstances Excel Still return to 298%, But obviously this number is too optimistic .

These problems are not completely unified in the industry , During the test , We also used GoogleSheet, The calculation results are as follows :

In comparison, our calculation result is the same as Excel Calculate the closest . And our subsequent R & D is also constantly exploring , Trying to provide a better answer to this problem .

In conclusion ,IRR Itself as an estimate , There is uncertainty , And there is no standard to determine which algorithm is correct , For and Excel The inconsistency of the results , We will also try our best to cooperate with... On the premise that the results are reasonable Excel bring into correspondence with . meanwhile , In some cases , We will keep our calculations .


You might want to say IRR There is no unified conclusion on the standard of in spreadsheet , But in the face of this problem SpreadJS Handed in a copy of his answer sheet .

In the follow-up, we will also bring you more about the disclosure of front-end spreadsheets , Think it's good. Give it a compliment ~

Reprint please indicate the source : Grape city official website , Grape city provides professional development tools for developers 、 Solutions and services , Enabling developers .

copyright notice
author[Grape City technical team],Please bring the original link to reprint, thank you.

guess what you like

Random recommended