エクセルIRR関数・『推定値』について

質問

エクセルのIRR関数ですが、＝IRR(範囲、推定値)という算式であらわされます。（もし、「推定値」のところを空白にした場合には、10％として認識される）

ここで、マイクロソフトのホームページによると、
http://office.microsoft.com/ja-jp/excel-help/HP005209146.aspx?CTT=1

•IRR 関数の計算には、反復計算の手法が使用されます。推定値を初期値とし、計算結果の誤差が 0.00001% になるまで、利益率の値を変えて反復計算が行われます。反復計算を 20 回行っても、適切な解が見つからない場合は、エラー値 #NUM! が返されます。

とあります。

ここで『利益率の値を変えて』とあるのですが、これはどのように変えていくのでしょうか？

たとえば、推定値に3％とした場合に、1回目の計算は3％で実施されるかと思うのですが、2回目は何％で計算されるのでしょう？3回目は何%で計算されるのでしょう？

回答No.2

xexstyle · ベストアンサー

IRR関数は色々な場面で使われますが、仮にある企業プロジェクトで、最初に100円投資すると、翌年から３年間、毎年50円の利益がある場合を考え、それをIRR関数で計算するとします。
その場合、A列のセルに、
[A1]-100
[A2]50
[A2]50
[A2]50
とデータを入れたあと、どこかのセルで=IRR(A1:A3)と指定すると利益率として23.375％と答えてくれます。

これは内部的に100=50/(1+r)+50/(1+r)^2+50/(1+r)^3という式を組み立て、ここのｒを求めています。
ここで^の記号はべき乗（るい乗）を表すものとします。
この式で仮に(1+r)をXとして、両辺にX^3をかけると、次のように変形できます。
100X^3=50X^2+50X+50
100X^3-50X^2-50X-50=0
この三次方程式を解けば、Xの値が求まりますので、そこからｒを知ることができます。
この三次方程式をどう解くかですが、そこは前の回答者さんが紹介されているニュートン法を使います。

ニュートン法は最初に適当（適切）な数値を推定値（仮の答）として設定し、それを元に同じ計算を何度も繰り返して、少しずつ正解に近づける、力ずくの解法です。
ニュートン法自体については詳しく説明しているサイトがありますので、そちらを参照してください。
ニュートン法では次のような漸化式により、ある答から、次の答を導き出します。
解きたい式をf<x>とし、n番目の答をx(n)、その次の答をx(n+1)とすると、
x(n+1) = x(n) - f<x(n)>/f'<x(n)>

ここでf'<x>は、f<x>を一回微分した導関数です。
先にあげた例では、f<x>=100X^3-50X^2-50X-50ですので、それを微分したf'<x)は、300X^2-100Xとなります。
そのため、
x(n+1) = x(n) - (100x(n)^3-50x(n)^2-50x(n)-50)/(300x(n)^2-100x(n))
という式で、x(1)に推定値として適当（適切）な値を指定して、次々とこの式を計算してゆきます。
この式を使って、0.1を推定値として、Excelを使ってIRR関数のシミュレーションをしてみました。
次のとおり20回程繰り返すと、IRR関数とほぼ同じ値になりました。
n / x(n) / f<x(n)> / f'<x(n)> / r
1回 / 0.10000 / -55.40000 / -7.00000 / -0.90000
2回 / -7.81429 / -50428.85948 / 19100.34694 / -8.81429
3回 / -5.17408 / -14981.42811 / 8548.73675 / -6.17408
4回 / -3.42161 / -4470.09779 / 3854.37798 / -4.42161
5回 / -2.26186 / -1349.87913 / 1760.99057 / -3.26186
6回 / -1.49532 / -421.38054 / 820.32217 / -2.49532
7回 / -0.98164 / -143.69092 / 387.24823 / -1.98164
8回 / -0.61058 / -60.87459 / 172.90147 / -1.61058
9回 / -0.25851 / -42.14344 / 45.89809 / -1.25851
10回 / 0.65969 / -76.03493 / 64.58840 / -0.34031
11回 / 1.83691 / 309.26223 / 828.58364 / 0.83691
12回 / 1.46367 / 83.26690 / 496.33282 / 0.46367
13回 / 1.29591 / 18.86724 / 374.22170 / 0.29591
14回 / 1.24549 / 3.36917 / 340.82434 / 0.24549
15回 / 1.23560 / 0.52580 / 334.45493 / 0.23560
16回 / 1.23403 / 0.07940 / 333.44738 / 0.23403
17回 / 1.23379 / 0.01192 / 333.29490 / 0.23379
18回 / 1.23376 / 0.00179 / 333.27200 / 0.23376
19回 / 1.23375 / 0.00027 / 333.26856 / 0.23375
20回 / 1.23375 / 0.00004 / 333.26804 / 0.23375

回答No.1

keithin

ご質問は，IRRがNPVをゼロにする利益率を繰り返し計算で求めていることは理解した上で，それをどのように計算しているのかについてという事ですね。
エクセルが中でIRRを具体的にどのように計算しているのか，公開された情報はありませんが，多くの研究者（別にエクセル研究者というワケじゃありません）はニュートン法(Newton–Raphson method)だと考えているようです。
＃探してみると，IRRを計算するのに幾つかの繰り返し計算手法の中でやっぱりニュートン法が効率的だねといった研究の報告も見つかります。（あるいは特定の条件下では，間違った結果に収束するといった報告も見つかります）

ニュートン法自体の具体的な説明については，ご質問の主題でもExcelの話題でもありませんので割愛します。ごく一般的な手法ですから，興味があるようなら教科書やネットでさくっと勉強してみて下さい。
ちなみにIRRの「親戚」であるXIRR関数については，マイクロソフトからニュートン法を使っている旨の公開情報があります。
http://support.microsoft.com/kb/214105/en-us