Excel VBAで複利計算する方法【投資信託・積立シミュレーション対応】

複利計算とは

複利とは、元本に対して発生した利息を次期の元本に組み入れ、その合計額に対して再び利息が計算される仕組みです。

「利息が利息を生む」効果により、単利と比較して資産が加速度的に増加します。投資や貯蓄のシミュレーションに欠かせない計算手法です。

ExcelにはPMT関数やFV関数などの金融関数がありますが、引数の設定方法が分かりにくく、本当に正しく計算できているのか不安になるデメリットがあります。

また、「自分でVBAコードを書いて複利を計算したい」というニーズも少なくありません。そこでこの記事では、Excel VBAで複利計算する方法を、基本の数式からサンプルコードまで解説します。

複利計算の基本公式

複利計算の基本公式は以下の通りです。

元本のみの場合（年利で計算）

元利合計 = 元本 × (1 + 年間利率) ^ 年数

例えば「元本100万円、年利5%、10年間、積み立てなし」の場合は以下のようになります。

元利合計 = 1,000,000×(1 + 0.05) ^ 10 = 1,628,894円

なお、ExcelのFV関数であれば、「=FV(0.05, 10, 0, -1000000)」と入力します。

元本＋毎月の積立を考慮する場合（月利で計算）

元利合計 = 元本 × (1 + 1か月の利率) ^ 月数 + 積立額 × (((1 + 1か月の利率) ^ 月数 – 1) ÷ 1か月の利率)

例えば「元本100万円、年利5%、10年間、毎月1万円積み立て」の場合は以下のようになります。

元利合計 =1000000*(1+0.05/12)^(10*12)+10000*(((1+0.05/12)^(10*12)-1)/(0.05/12)) = 3,199,832円

なお、ExcelのFV関数であれば、「=FV(0.05/12, 10*12, -10000, -1000000, 0)」と入力します。

このように、年利計算を月利換算にすると12で割ったり掛けたりがあり、これがとてもわかりにくいです。

引数の理屈が分からないと、ごちゃごちゃしてますよね。

一般的に複利の計算を行う場合は投資信託の毎月積み立てを指すことが多く、元本と、毎月の積立額と、年率と、運用年数、で、複利でどれだけ増えるのか、「元本が100万円で、毎月1万円積み立てをして、年率5%で、10年運用したらいくらになる？」といった内容を知りたいわけです。

それをVBAで実装します。

複利計算関数のコード

この関数は、元本、年利、年数、毎月の積立額を渡すと、将来いくらになるのかを返します。

'// PV : 元本（100万円なら 1000000）
'// Rate : 年利（例：5%なら 0.05）
'// Years : 年数（例：10年なら 10）
'// MonthlyAdd: 毎月の積立額（例：1万円なら 10000）
'// 戻り値 : 複利計算結果。小数点以下は切り捨て。
'// 注意：この関数は「期初払い」ではなく「期末払い」を想定しています。
'// つまり、毎月の積立は月末に行われる前提です。
Function CompoundFutureValueMonthly(PV As Double, Rate As Double, Years As Long, MonthlyAdd As Double) As Double
    Dim n As Long
    Dim r As Double
    Dim result  As Double
    
    '// 月数と月利を算出
    n = Years * 12
    r = Rate / 12
    
    '// 初期投資の複利計算
    CompoundFutureValueMonthly = PV * (1 + r) ^ n
    
    '// 毎月積立の計算（金利がゼロでない場合のみ）
    If MonthlyAdd <> 0 Then
        If r <> 0 Then
            result = CompoundFutureValueMonthly + MonthlyAdd * ((1 + r) ^ n - 1) / r
        Else
            '// 金利0%の場合は単純合計
            result = CompoundFutureValueMonthly + MonthlyAdd * n
        End If
    End If
    
    CompoundFutureValueMonthly = Int(result)
End Function

'// PV : 元本（100万円なら 1000000）

'// Rate : 年利（例：5%なら 0.05）

'// Years : 年数（例：10年なら 10）

'// MonthlyAdd: 毎月の積立額（例：1万円なら 10000）

'// 戻り値 : 複利計算結果。小数点以下は切り捨て。

'// 注意：この関数は「期初払い」ではなく「期末払い」を想定しています。

'// つまり、毎月の積立は月末に行われる前提です。

Function CompoundFutureValueMonthly(PV As Double, Rate As Double, Years As Long, MonthlyAdd As Double) As Double

Dim n As Long

Dim r As Double

Dim result As Double

'// 月数と月利を算出

n = Years * 12

r = Rate / 12

'// 初期投資の複利計算

CompoundFutureValueMonthly = PV * (1 + r) ^ n

'// 毎月積立の計算（金利がゼロでない場合のみ）

If MonthlyAdd <> 0 Then

If r <> 0 Then

result = CompoundFutureValueMonthly + MonthlyAdd * ((1 + r) ^ n - 1) / r

Else

'// 金利0%の場合は単純合計

result = CompoundFutureValueMonthly + MonthlyAdd * n

End If

CompoundFutureValueMonthly = Int(result)

End Function

この関数では常に月利で計算しています。その理由は、年利で計算すると複利の頻度が少ないため最終的な金額が小さくなり、月利の計算結果と比べると0.4～3%程度の差で、100万円元本であれば長期になると数万円～10数万円の差が出る可能性があります。これを避けるために月利にしています。

正確にするのであれば日利で計算した方がいいのですが、一気にロジックが複雑になるわりに、月利と日利での差はわずか0.1%程度で100万円元本であれば1000円程度の差で、実用上は月利で十分のため、月利での処理にしています。

金利0%の場合の処理を入れていますが、これは0%がどうのこうのというよりも、0割でエラーになるのを避ける目的で入れてます。

なお、この関数は「期初払い」ではなく「期末払い」を想定しています。「期初払い」（月初に入金）の場合との差はありますが実用上の差は誤差範囲のため無視しています。

使い方（例1：積立なし）

例えば、

元本：1,000,000円
年利：5%
年数：10年
毎月積立額：0円

のケースでは次のように入力します。

Sub CompoundFutureValueTest()
    Dim fv  As Double
    
    fv = CompoundFutureValueMonthly(1000000, 0.05, 10, 0)
    Debug.Print Format(fv, "#,##0")
End Sub

Sub CompoundFutureValueTest()

Dim fv As Double

fv = CompoundFutureValueMonthly(1000000, 0.05, 10, 0)

Debug.Print Format(fv, "#,##0")

End Sub

結果は 1,647,009円となります。

これは元本100万円を10年間、年5%を月利計算で複利運用した場合の金額です。

なお、ExcelのFV関数「=FV(0.05, 10, 0, -1000000)」では1,628,895円となります。なぜずれているかというと、ここでのFV関数は月利ではなく年利で計算しているためで、より実態に近い月利計算での1,647,009円とは2万円ほどずれています。

使い方（例2：毎月の積立あり）

次に、

元本：1,000,000円
年利：5%
年数：10年
毎月積立額：10,000円

とすると、

Sub CompoundFutureValueTest()
    Dim fv  As Double
    
    fv = CompoundFutureValueMonthly(1000000, 0.05, 10, 10000)
    Debug.Print Format(fv, "#,##0")
End Sub

Sub CompoundFutureValueTest()

Dim fv As Double

fv = CompoundFutureValueMonthly(1000000, 0.05, 10, 10000)

Debug.Print Format(fv, "#,##0")

End Sub

結果は 3,199,832円となります。元本100万円に毎月1万円を積み立てて10年間、年5%で複利運用した場合の金額です。

なお、ExcelのFV関数「=FV(0.05/12, 10*12, -10000, -1000000, 0)」でも同じ3,199,832円となります。これは引数を月利換算にして計算しているためで、マクロとFV関数とで計算結果が一致しています。

途中で積立額を変更する場合

例えば、次のように途中で積立額を変えることもあります。

最初の5年間は「毎月1万円」
その後の5年間は「毎月2万円」

このようなケースでは関数を2回呼び出せばOK です。

Sub TestCompound()
    Dim fv1 As Double
    Dim fv2 As Double
    
    '// 最初の5年（毎月1万円）
    fv1 = CompoundFutureValueMonthly(1000000, 0.05, 5, 10000)
    
    '// 次の5年（毎月2万円）→ fv1を元本として再計算
    fv2 = CompoundFutureValueMonthly(fv1, 0.05, 5, 20000)
    
    Debug.Print "10年後の金額は " & Format(fv2, "#,##0") & " 円です"
End Sub

Sub TestCompound()

Dim fv1 As Double

Dim fv2 As Double

'// 最初の5年（毎月1万円）

fv1 = CompoundFutureValueMonthly(1000000, 0.05, 5, 10000)

'// 次の5年（毎月2万円）→ fv1を元本として再計算

fv2 = CompoundFutureValueMonthly(fv1, 0.05, 5, 20000)

Debug.Print "10年後の金額は " & Format(fv2, "#,##0") & " 円です"

End Sub

実行結果
10年後の金額は 3,879,893 円です

ExcelのFV関数で同じことをするならば、
A1セルに「=FV(0.05/12, 5*12, -10000, -1000000, 0)」→ \1,963,420
A2セルに「=FV(0.05/12, 5*12, -20000, A1*-1, 0)」→ \3,879,893

のようにセルを分けて書くとマクロでの計算結果と同じになります。

なぜ複数回の関数呼び出しで十分なのか？

途中で積立額を変える場合、引数を追加して「開始年」「金額」を設定できる関数を作ることも可能です。しかしその場合、関数の使い方が複雑になり、理解しにくくなります。

複数回コールの仕組みなら、

途中の結果を確認できる。
何度でも柔軟に積立額を変えられる。
関数の引数は常に4つでシンプル。
実装が単純になる。
使う側も単純で分かりやすい。

というメリットがあります。

まとめ

Excel VBAで複利計算をするには、月利専用の自作関数を使うとFV関数よりも、より直感的に利用できるようになります。

引数は「元本」「年利」「年数」「毎月の積立額」の4つだけでシンプルにすることで、FV関数のように月利換算にするなどのややこしい入力は不要です。

途中で積立額を変更したい場合は、関数を複数回呼び出して区切りごとに計算すれば対応可能です。

この仕組みを使えば、教育資金・老後資金・積立投資など、さまざまな資産形成シナリオをExcelで簡単にシミュレーションできます。

複利計算とは

複利計算の基本公式

元本のみの場合（年利で計算）

元本＋毎月の積立を考慮する場合（月利で計算）

複利計算関数のコード

使い方（例1：積立なし）

使い方（例2：毎月の積立あり）

途中で積立額を変更する場合

なぜ複数回の関数呼び出しで十分なのか？

まとめ

関連記事