1 SHIMURA Masato polynomial irr.xirr EXCEL irr

Size: px

Start display at page:

Download "1 SHIMURA Masato polynomial irr.xirr EXCEL irr"

しげのぶちゅうか
5 years ago
Views:

1 1 SHIMURA Masato polynomial irr.xirr EXCEL irr d f, pv irr, xirr irr, xirr EXCEL cf cf cf cf calc at once 16 6 References 20 irr, xirr J EXCEL

2 1 2 irr, xirr 1 irr internal rate o f return xirr extended irr xirr 1.1 Example r 100 = r + 5 (1 + r) (1 + r) 3 = 100(1 + r)3 + 5(1 + r) 2 + 5(1 + r) (1 + r) r 295r 2 100r 3 = 0 100(r 3 + 3r 2 + 3r + 1) 5(r 2 + 2r + 1) 5(r + 1) 125 J (p.) ;}. p. 35 _285 _295 _100 _ j _ j_ %( ± ;}. p. _ _ j _ j_

3 1 3 irr, xirr irr _ xirr ,: _ Example r + 305r r 3 = 0 100(r 3 + 3r 2 + 3r + 1) 5(r 2 + 2r + 1) 5(r + 1) 95-5% p. _15 _315 _305 _ _100 _1.5j _1.5j_ _ p. p. p. 35 _285 _295 _ _100 _ j _ j_ xirr xirr ,: _100 _5 _5 95 _0.05

4 polynomial pascal=: 3 : :!/ i. >: y mk_poly_sum=: 3 : 0 NB. make polynominal coefficient. +/ y *."1. pascal <: # y ) pascal # a mk_poly_sum a=. _ _285 _295 _100 mk_poly_sum _100 _5 _5 95 _15 _315 _305 _100 Example mk_poly_sum _ _775 _3900 _10950 _19740 _24150 _20400 _11775 _4450 _995 _ x 3900x x x x x x x 8 995x 9 100x 10 = 0 Example % mk_poly_sum _100 _5 _5 _5 _5 _5 _5 _5 _5 _5 95 _50 _1225 _5100 _13050 _22260 _26250 _21600 _12225 _4550 _1005 _100

5 2 irr.xirr EXCEL =5% 5.0% 5 2 $;}. p. mk_poly_sum _ _2 _ j _ j_ _ j _ j_ _ j _ j_ _ j _ j_ $ ;}.p. mk_poly_sum _100,(9#_5),95 _2 _ j _ j_ _ j _ j_ _ j _ j_ _ j _ j_ _0.05 xirr (i.11),: _100, (9#5), xirr (i.11),: _100, (9#_5),95 _ irr.xirr EXCEL 2.1 irr J irr 3 i f (2 irr=:3 : 0 0 irr y : t=. %>:x cf=.,y tol=. 1e_5 max=. 15 if. 2>#cf do. cash flow must have at least two elements return. end. if. -. *./_1 1 e.*cf do. no sign change in cash flow return. end. cf=. (.cf),:.1.cf*i.#cf while. tol< t-r=. t-%/t#.cf do. t=. r

6 2 irr.xirr EXCEL 6 if. 0=max=. <:max do. iterations exceeded return. end. end. <:%r ) cf=. (.cf),:.1.cf*i.#cf while. tol< t-r=. t-%/t#.cf do. t=. r end. Example a=. _ NB. same Example cf (=a) a1=. (.a),:.1.a*i.#a _ J p. (#.) mk_poly_sum a NB. exact 35 _285 _295 _ x 295x 2 100x 3 1 #. a NB. %/ 1 1- %/ 1 #. a t=1 ) *1 *1 (base) 10 #

7 2 irr.xirr EXCEL d f, pv df xirr df pv df discount factor pv present value (future value) Example data NB. interest.ijs ,:_ NB. time _ NB. pay irr _ xirr ,:_ Present Value pv ,: _ YEAR CF DF PV 0 _100 1 _ _ 100 df (discount factor) 1 (1 + irr) i df ,:_ calc_poly_sum _ mk_poly_sum _ # NB # J Vocaburaly #.y is a weighted sum of the items of y that is, +/w*y where w is the product scan */\.}.x,1

8 2 irr.xirr EXCEL 8 35 _285 _295 _ x 295x 2 100x 3 = Script df=:3 : 0 NB. discount factor NB. 1/(1+irr)ˆi NB. Usage: (df/xirr) ,:_ %(>:xirr y)ˆi. # {.y ) pv_sub0=:3 : 0 NB. Usage: (pv_sub/df/xirr) ,:_ tmp=.(;{: y),. df y tmp=.(i.# tmp),.tmp,. _,}. */"1 tmp tmp, _,_,_,{:+/}.tmp ) 2.3 irr, xirr irr, xirr mk poly sum irr, xirr. irr _100, 9#5 _ xirr (i.10),: _100, 9#5 _ mk poluy sum. mk_poly_sum _100, 9#5 _55 _720 _3180 _7770 _11970 _12180 _8220 _3555 _895 _100,.;}. p. mk_poly_sum _100, 9#5 _ j

9 2 irr.xirr EXCEL 9 _ j_ _ j _ j_ _ j _ j_ _ j _ j_ _ irr, xirr irr, xirr J irr, xirr J irr, xirr xirr (i.11),: _100,10#_5 no sign change in cash flow xirr (i.11),: _100,10#_5 iterations exceeded mk poly sum mk_poly_sum 10 mk_poly_sum _100, 10#_5 _150 _1225 _5100 _13050 _22260 _26250 _21600 _12225 _4550 _1005 _100 p. p.!! 5 2 $ ;}. p. mk_poly_sum _100, 10#_5 _1.6737j _1.6737j_ _ j _ j_ _ j _ j_ _ j _ j_ _ j _ j_ $ ;}. p. mk_poly_sum _100, 4#_5

10 2 irr.xirr EXCEL 10 _ j _ j_ _ j _ j_ irr _100, 9#_5 no sign change in cash flow,.;}. p. mk_poly_sum _100, 9#_5 _ _ j _ j_ _ j _ j_ _ j _ j_ _ j _ j_ Example. 1 + r = n (1 + r 1 )(1 + r 2 )(1 + r 3 ) (1 + r n ) 1 + r = 4 Π(1.2, 0.9, 1.1, 0.8) = r = % 10000( ) 4 = 9504 NB. check Year (%) calc_geom _ _ _ _

11 2 irr.xirr EXCEL _ _ NB. geometric mean of rate 10000* ˆ4 NB. check NB. check OK Example calc_geom2 _100,(9#5) _ _0.05 _90 _ _85 _ _80 _ _75 _ _70 _ _65 _ _60 _ _55 _ irr irr _100, 9#5 _ r = , r = ( calc_geom2 _100,(9#_5) _ _ _ _ _ _ _ _ _ NB. geametric mean 100* _ ˆ9 _ NB. sum up 9 years

12 EXCEL EXCEL IRR EXCEL =IRR(A0:A4) ganri 100;10 3% nr gankin 100;

13 cf Example %10 3.2% (i.11),:_100, (}: {: : 3 gankin 100;10), _ xirr (i.11),:_100, (}: {: : 3 gankin 100;10), cf Example %10 a=._100 _8 _7.7 _7.4 _7.1 _6.8 _6.5 _6.2 _5.9 _ mk_poly_sum a _66.5 _1324 _5397 _ _22953 _26844 _ _12357 _ _1008 _ $ }.; p. mk_poly_sum a _ _ j _ j_ _1.3106j _1.3106j_ _ j _ j_ _ j _ j_ _ xirr (i.11),:_100 _8 _7.7 _7.4 _7.1 _6.8 _6.5 _6.2 _5.9 _ _ ]r0=. xirr (i.11),: a

14 4 14 _ (1+ r0) -> ˆ Example 4.1 cf Example % % (i.11),: _100,( {: : 3 gankin 100;10) _ xirr (i.11),: _100, 5- ({: : 3 gankin 100;10)-10 _ % xirr (i.61),: _100, 5- ({: : 3 gankin 100;60)-100r cf a1=: _100 _5 _5 115 a2=:_100 _8 _7.7 _7.4 _7.1 _6.8 _6.5 _6.2 _5.9 _ a3=:_100 _8 _7.7 _7.4 _7.1 _6.8 _6.5 _6.2 _5.9 _5.6 34

15 df df(discount factor) df ( ),: _ df a1=. ( ),: _100 _5 _ pv PV(present value) pv ,: _ YEAR CF DF PV 0 _100 1 _ _ fv fv ,: _ YEAR CF ReInvest-Rate FV 1 _ _ future value (df) pv pv ( ),: a1 YEAR CF DF PV 0 _100 1 _ 1 _ _ _ _ _ 100 fv a1= ,: _100 _5 _5 115 YEAR CF ReInvest-Rate FV 1 _100 2 _ _ _ _ _ future value d f = 1 (1 + IRR) i

16 5 calc at once 16 fv (i.11),: a2 YEAR CF ReInvest-Rate FV 1 _100 2 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ fv (i.11),: a3 YEAR CF ReInvest-Rate FV 1 _100 2 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ % 10 5% future value 100 0, 5 calc at once y 100 (%) YEAR= NB. years INTEREST= NB. interest rate

17 5 calc at once 17 mk_calc_mat_sub (year rate) mk_calc_mat_sub=: 3 : 0 NB. years interest-rate NB.and profit(except interest rate) YEAR= NB. years INTEREST= NB. interest rate TABLE=. { YEAR;INTEREST ) cf calc_xirr_all=: 3 : 0 NB. Usage:,. calc_xirr_all L:0 {@> NB. y is profit(except interest) per years TABLE=: mk_calc_mat_sub ALL=: }. "1 L:0 ({: L:0 TABLE)gankin (L:0) 100, L:0 {. L:0 TABLE KINRI=: ALL - (L:0) 100 % L:0 {. L:0 TABLE SYUUSI=:_100, L:0 ; L:0 y - L:0 KINRI TABLE2=: (i. L:0 >: L:0 {. L:0 TABLE) ;("1) xirr L:0 TABLE2,: L:0 SYUUSI ) 5% ) calc_xirr_all 5 ( 3% 4% 5% 6%

18 5 calc at once 18 _ _ _ _ NB. 10 years _0.03 _0.04 _0.05 _0.06 NB _ _ _ NB _ _ NB _ NB NB. 60 calc at once %,. calc_xirr_all (L:0){@> ( ) _ _ _ _ NB. 3% _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ NB. 5% _0.03 _0.04 _0.05 _ _ _ _ _ _ _ _ _ _ _ NB. 7% _ _ _ _0.03 _0.04 _0.05 _0.06 NB. 10%

19 5 calc at once

20 6 References 20 6 References 2004 J script Download J602 Script -> APL/J

a b GE(General Erectrics) 9 4 irr (JAPLA 2009/12) Example1 120 P = C r + C 2 (1 + r) C t 1 (1 + r) t 1 + C t + F (1 + r) t 10

a b GE(General Erectrics) 9 4 irr (JAPLA 2009/12) Example1 120 P = C r + C 2 (1 + r) C t 1 (1 + r) t 1 + C t + F (1 + r) t 10 1 SHIMURA Masato 2010 9 27 1 1 2 CF 6 3 10 *1 irr irr irr(inner rate of return)function is able to written only few lines,and it is very powerful and useful for simulate unprofitable business model. 1