用于IRR计算的填充阵列

Question

我试图创建一个函数来计算退休基金所需的回报率。情况是这样的:退休人员在退休开始时有一个退休帐户的目标数额。他们也有一个目标提取金额，他们希望每年提取一个目标金额的年份。

例如:退休基金数额为1 000 000美元。每年提取10万美元，目标为15年。

我所写的代码如下：

Function RequiredReturn(retire_amnt, annual_spending, n_year)

    Dim arr()

    ReDim arr(0)

    arr(0) = -retire_amnt

    ReDim arr(1 To n_year)
    For i = 1 To n_year
        arr(i) = annual_spending
    Next i

    RequireReturn = Application.WorksheetFunction.IRR(arr)

End Function

这一职能的目标是：

1. 创建第一个值(0) =负退休金额的数组
2. 用annual_spending值填充从(1到n)的数组
3. 调用IRR函数对此，以找到最低要求的年度回报，以能够实现这一现金流。

如果要将此数组输入单元格，然后使用IRR函数，则所需的返回率为5.56%。

很明显这不管用。

如何将其更改为正确填充数组？如果正确填充，IRR函数是否被正确应用？

Answer 1

分配数组两次，第二个赋值覆盖第一个--即数组逻辑中的错误。

通过将数组定义为：

ReDim arr(0 To n_year)

这将给它n_year +1插槽，其中第0位可以接受一次总付(retire_amnt)，而从1-n_year获得年金支付(annual_spending)。然后，您就有了正确的IRR计算数据结构。

下面的代码起作用，并给出了5.56%的预期结果

Option Explicit

Sub Test()

    MsgBox Format(RequiredReturn(1000000, 100000, 15), "#.0000%")

End Sub

Function RequiredReturn(retire_amnt, annual_spending, n_year)

    Dim arr() As Variant
    Dim i As Long

    ReDim arr(0 To n_year)

    arr(0) = -retire_amnt

    For i = 1 To n_year
        arr(i) = annual_spending
    Next i

    RequiredReturn = Application.WorksheetFunction.IRR(arr)

End Function

我主要是在资本外流的情况下使用IRR，然后是一系列相同时间的现金流入。但就年金回报率而言，我的理解是，IRR也可以使用，因为无论起始金额是贷方还是借方，只要随后的现金流入/流出流量是相反的，这都无关紧要。不过，你可能不想冒养老金的风险，尽管如此:)

Answer 2

或者更直接地使用RATE公式

这将初始金额称为负数.

Sub Test()
   MsgBox Format(RequiredReturn(-1000000, 100000, 15), "#.0000%")
End Sub

选项

1. RequiredReturn = Application.Evaluate("=RATE(" & n_year & "," & annual_spending & "," & retire_amnt & ")")
2. RequiredReturn = Application.Rate(n_year, annual_spending, retire_amnt)