Problem conc. VBA function - NPV & date

[bompa]

I have been trying to writa a npv calculator calculator.

The problem is however that I would like the function to work regardless of what time periods the user specifies between the cash-flows. NPV = Discounting of cashflows..


The function takes three arguments the annual discount rate, the range that contains the dates of the cash-flows and the range that contains the magnitudes of the cash-flows.

Do you have any clues

Thank you in advance

 

[SkipVought]

Well, bompa,

I can tell you how to get the Date/Value sets. You're gonna have to figure out what to do with them. I imagine that your function might look something like this...

Function CashFlow(dblRate As Double, rngData As Range)
    Dim rngCell As Range, lRow As Long, iCol As Integer, lNbrOfValues As Long
    Dim dteDate As Date, curValue As Currency, curNPV As Currency
    With rngData
        If .Columns.Count <> 2 Then Exit Function
        curNPV = 0
        lNbrOfValues = .Rows.Count
        For lRow = .Row To lNbrOfValues + .Row - 1
            dbldate = Cells(lRow, .Column).Value
            curValue = Cells(lRow, .Column + 1).Value
            'now do yer thing for this set of data
            
            curNPV = curNPV + something
        Next
    End With
    'now return a value
    CashFlow = curNPV
End Function

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Are you using Excel? If so, you can use the NPV function.

 

[SkipVought]

euskadi,
I think that the PROBLEM is multiple sets of data. Certainly the NPV function could be used for each set. Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Bompa,

Are you suggesting the payments will be at varying intervals? Or are you suggesting the payments may be yearly, monthly, weekly?

If the payments are of a period shorter than a year, can't you use the normal NPV calculation, but adjust the interest rate because of the shorter term?