• 締切済み

IRRの履歴データを作りたい

以下の投資成績Excel シートがあります。 - A列: 日付 - B列: 投資額[JPY] 　投資した場合(株を買った場合など)に負数で、決済した場合に正数で書いています。 - C列: 投資残高[JPY] 　投資してる商品の(株など)当日末残高です。 　ここで、D列にIRR(内部収益率)を書きたいと思います。D列に入れるべき関数等を教えてください。要件は以下の通りです。IRRの起算日はA1の日付です。IRRの最終日は同行A列の日付です。利率は、日複利の年利換算で算定します。 - 日々の作業は、最終行をコピー&ペーストして、B列とC列の数字を入力するだけにしたい。 表ははこんなイメージです。 2001/01/01, -10000, 10000, 0.00[%] 2001/05/01, 0, 10200, 6.21[%] 2002/01/01, -10000, 20500, 5.00[%] 2003/01/01, -10000, 31216, 4.00[%] 2004/01/01, +10000, 22000, *.**[%] 　2004/01/01に、1万円出金して残高が22000でした。このときの作業は、3行目をコピペして4行目に張り付け、B4とC4を書きこむことを想定します。その時にD4にIRRが出るような関数等を知りたいと思います。多分、Excelに詳しいだけの人には答えられないと思いますので、数学カテゴリで質問しました。

  • q_yy
  • お礼率79% (146/184)

みんなの回答

  • xexstyle
  • ベストアンサー率72% (72/99)
回答No.1

IRRは均等な時間間隔における利益率を計算するので今回のケースの場合は使えません。 この場合は日付を指定できるXIRRを使います。 XIRRの使い方は、 =XIRR(CFのセル範囲, 日付のセル範囲, 推定値<省略可>) です。 CF（キャッシュフロー）のセル範囲には、お金の出入れ額が入ったセル範囲を指定します。 その最後のセルには残高（投資成果）を入れることになります。 今回のケースの場合、CFのセル列（B列)と、残高のセル列(C列）が異なるので、どう指定していいか私には分かりません。 推定値は、省略した場合の規定値は10％です。 これが大きく異なるとエラーになりますので、エラーの場合は利益率を自分で大まかに計算して指定しなければなりません。 XIRRの結果は年利で返ってきます。

q_yy
質問者

お礼

　ありがとうございます。そこまでは、自力で到達しています。 　おっしゃる通り、「CFのセル列（B列)と、残高のセル列(C列）が異なる」ので、 XIRR関数はが通常の方法では利用できません。 　その上で、XIRR関数と同様の効果を発生させることを考える質問です。私の勘では、質問にも書いたとおり、Excelの質問ですがも数学に明るい人じゃないと解決できないだろうと思っています。 　次善の策として、D列の値はその1行上の数値と大幅には乖離しないという前提を置いて推定値として利用することは許容範囲です。

  1. カテゴリ
  2. 学問・教育
  3. 数学・算数

関連するQ&A

  • IRRの履歴データを作りたい

    以下の投資成績Excel シートがあります。 - A列: 日付 - B列: 投資額[JPY] 　投資した場合(株を買った場合など)に負数で、決裁した場合に正数で書いています。 - C列: 投資残高[JPY] 　投資してる商品の(株など)当日待つ残高です。 　ここで、D列にIRR(内部収益率)を書きたいと思います。D列に入れるべき関数等を教えてください。要件は以下の通りです。IRRの起算日はA1の日付です。IRRの最終日は同行A列の日付です。 - 日々の作業は、最終行をコピー&ペーストして、B列とC列の数字を入力するだけにしたい。

  • IRRがマイナスになるとは？また、エクセルでの計算方法を教えてください。

    投資の評価をする際に、IRR（内部収益率）を指標とすることがありますが、IRRがマイナスとなることはあるのでしょうか？ たとえば、1年目に100投資して、2年目に30のリターン、3年目に50のリターンがあり、そこで投資が終了となった場合、IRRはどうなるのでしょうか？ また、上記例をエクセルで計算させようとした場合、どうすればいいでしょうか？ （私のエクセル2003のIRR関数では、＃NUMとなり計算できません） ご存知の方、ぜひご回答のほどよろしくお願い申し上げます。

  • エクセル関数　支出管理のやり方

    エクセルの関数について質問です。 支出に関する表を作成しているのですが 例えば シート１のA行に１か月分の日付を入れて、B行に預金額の流れを管理します。 シート2のA行に預金額、B行に個々の支払期限日、C行に残高を入れて管理をします。 支払期限日は毎月違うので、シート2のB行に入れた日付がシート1のA行の日付と同じ日付だった場合 シート1の同じ日付になる列に、シート2の同じ日付のC行の残高が自動入力されるようにしたいのですが いくら調べても、関数がわかりません。 分かる方が居たら教えてください。 お願いします。

  • EXCEL：ゼロ以外のデータを詰めて抽出する方法

    初めて投稿します。よろしくお願いいたします。 EXCEL2000を使用しています。 オートフィルタを使わずに 関数でデータを抽出する方法で悩んでいます。 下記＜データ＞が存在しています。 2行目がゼロ以外のものを、 別シート＜抽出＞に上から詰めて抽出したいのです。 ＜データ＞ 　　A列　B列　C列　D列　E列 1行　1　　2　　3　　4　　5　　←日付 2行　5　　3　　0　　2　　0　　←数字 ＜抽出＞別シート 　　A列　B列　 1行　1　　5 2行　2　　3 3行　4　　2 　　↑　　↑ 　　日付　ゼロ以外 関数については中級レベルだと思います。 いろいろ試したのですが、関数を組み合わせてもなかなかうまくいきません。 どなたかご教授いただけないものでしょうか？ よろしくお願いいたします。

  • EXCEL2003出納帳残高欄の、計算結果がある最終行の数値を取り出し

    EXCEL2003出納帳残高欄の、計算結果がある最終行の数値を取り出したいのですが 　　　　Ａ　　　　　Ｂ　　　　　Ｃ　　　　　Ｄ １　　日付　　　入出金　　　残高 ２　　繰越　　　　　　　　　　３０ ３　８／１　　　１００　　　１３０ ４　８／２　　－１３０　　　　　０ ５　８／２　　　　４０　　　　４０ ６　　　　　　　　　　　　　　　－ ７　　　　　　　　　　　　　　　－ ８　　　　　　　　　　　　　　　－ ９　　　　　　　　　　　　　　　　現在残高　４０　　　　　　　 途中には空白行はなく順に入力します 入力する行数は未確定です 残高は"０"　の場合もあります Ｃ列２行目は　数式「=B2」　 Ｃ列３行目から８行目までは　数式「=IF(B3=0,0,C2+B3)」 現在残高を表示したいのですが、 ここＤ列９行目には数式　「=LOOKUP(10^5,C3:C8)」　としてますが、 Ｃ列の６～８行目までは計算式が入ってるので、 その値”０”となってしまうようです。 また、 数式「=INDIRECT(ADDRESS(COUNT(C2:C8)+1,3))」 という式も入れてみましたが、”０”となってしまいます。 計算結果のある最終行の値　"40" にするにはどうしたらよいでしょうか？

  • エクセルの計算式で

    利息の計算をしたいのですが 関数の使い方を教えてください。 債務の残高がAの列に入ります。 　　　　　　Ａ(残高)　　Ｂ(利率) 1(日付)　　100000 　　　　　　10000 　　　　　　　… というような形です。 Ｂ列に入る利率を計算する式を教えてください。 条件 1.直前の行の残高がマイナスだった場合、5%になる 2.直前の行の残高が10万から100万の間だったら18%になる 3.直前の行の残高が100万以上なら15%になる 4.一度条件3で15%になったら、以降はずっと15%となるが、マイナスになったとき(条件1)の場合は条件1が優先される わかりにくいかも知れませんが、よろしくお願いします。

  • エクセル表→セル内データ更新→同じ行の最終更新日欄の日付を自動更新　し

    エクセル表→セル内データ更新→同じ行の最終更新日欄の日付を自動更新　したい。 エクセルで作成した表で、表の中のデータを更新した場合に、その行の最後の列の「最終更新日」の日付を自動的に更新する方法を探しています。 具体的には、エクセルで1行目のA列に「データのキーワード」、B列に「データのハイパーリンク」、C列に「データ記入者」、D列に「最終更新日」の項目を作成し、2行目以降に作成した各データをA～Cの項目に従って記入してデータ一覧表を作成しています。 その際に、A，C列の項目を更新した時と、B列項目のリンクを開いた時に、同じ行D列の最終更新日の日付を自動で入力及び更新する方法はないでしょうか？ 恐らくマクロを組む必要があると思うのですが、全くの素人ですので是非教えて頂けないでしょうか？ すみませんが、宜しくお願い致します。

  • Excelデータ上の抽出ワザは？

    　　　　　日付A　番号　日付B １行目　　２日　　01　　２日 ２行目　　２日　　02　　１日 ３行目　　３日　　03　　２日 ４行目　　４日　　03　　２日 ５行目　　５日　　03　　３日 ６行目　　５日　　04　　３日 ７行目　　６日　　04　　３日 いま、上記のようなエクセルデータがあったときに、 「番号列」が等しく、かつ「日付B列」も等しい場合に 「日付A列」で一番大きい日付以外の行を削除したい （上記例の場合、３行目と６行目を削除したい） のですが、実行するにはどうすればいいのでしょうか？ 初心者で申し訳ないのですが、 マクロとかいう代物を使用すれば解決するのでしょうか？ それともAccessが絡むのでしょうか？ アドバイスいただければ幸いです。 よろしくお願いいたします。

  • IRR関数の変動について

    IRR関数の変動について IRR関数を使って10年間投資した場合のIRRの変動を見ようと思っております 下記コードを使って作成しても、10年年後のIRRの結果が10個作成されるだけです Dim Values(i) as Doubleとしても、定数式を要求されてしまいます rate = IRR(Values(i))としても型が一致しませんと出てしまいます また、Values(3)くらいで止めたところ「インデックスが有効範囲にありません」 と出てしまいます。恐らくIRRの値がマイナスになっているからだと思います これらを回避しつつ、10年分のIRRの変動をさせるやり方を教えて下さい お願いします Dim i As Integer Dim rate As Double Dim Values(10) As Double Dim L As Integer Dim equity As Currency equity = Range("A8").Value - Range("H10").Value) * -1 Values(0) = equity Values(1) = Range("C13").Value Values(2) = Range("D13").Value Values(3) = Range("E13").Value Values(4) = Range("F13").Value Values(5) = Range("G13").Value Values(6) = Range("H13").Value Values(7) = Range("I13").Value Values(8) = Range("J13").Value Values(9) = Range("K13").Value Values(10) = Range("L13").Value For i = 1 To 10 rate = IRR(Values()) L = i + 9 Cells(L, 12).Value = rate Next i End Sub

  • エクセルVBA、数式の入ったセルのコピーについて質問です。よろしくお願

    エクセルVBA、数式の入ったセルのコピーについて質問です。よろしくお願いします。 シートのA列は日付の入ったセルがあり、データを更新する度に行が追加されていきます。 数式(1)～(3)は、それぞれ異なる計算式が入っており、日付データを参照して計算を行っています。 　 A　　　 B　　　 C　　　 D 1 日付データ 数式(1)　数式(2)　数式(3) 2 日付データ　数式(1)　数式(2) 数式(3) 3 日付データ　　 4 日付データ　　 5 日付データ　　 A列にデータが追加したときに、B～D列の数式をA列の最終行までコピーしたいのですが、 どのようなコードを書けばよいでしょうか？ ちなみに、１行目のB～D列には、データ更新の有無に関わらず、必ず数式が入っているものとします。 何かよいアドバイスがあればよろしくお願いします。 【補足】 単純に考えると、B1～D1をA列の最終行までAUTOFILLすれば良いのでしょうが、データ数がかなり多く、 この方法だと時間がかかってしまいます。A列のデータ追加前の数式セルの最終行から、追加後の最終行までとすると、処理は早くなるのでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する