VBA在Excel中的应用及手工计算净现值

Question

Sub npv()

Dim val As Long
Dim dr As Long
Dim inv As Long
Dim answer As Long
Dim i As Long

dr = Cells(1, 5).Value 'Cells(Row, Column)
inv = Cells(2, 5).Value
val = Cells(1, 1).Value
i = 0

ReDim cashFlow(1 To val) As Long

Do While i < val
    i = i + 1
    cashFlow(i) = Cells((i + 1), 1)
Loop

i = 0
    
Do While i < val
    i = i + 1
    answer = answer + cashFlow(i) / ((1 + dr) ^ i)
Loop

answer = answer - inv

Cells(4, 5).Value = answer

End Sub

你好，我想知道为什么这个不能输出一个合适的NPV。初始投资为500，贴现率为.17，现金流为50,150,175,225，其结果为100.00。应该是-118.35。我知道excel有这样的公式，但需要手动完成。

Answer 1

你得换掉

Dim dr As Long

通过

dim dr as Double

否则，您0.17的折扣率将转换为long，从而转换为0。