When I try to use the function below in my worksheet I get
a "#Value!". If I change it to a sub and define all of the inputs
it works great. I'm sure the problem is some goofy oversight on my
part, possibly how I specify the function output, so any help you
can provide will be greatly appreciated.
For any of you who are interested (finance function), the function
calculates cumulative interest payments or cumulative principal
payments, depending on the intOption chosen, through a specified
payment period during the life of a loan. Actual interest is
accrued using a simple accrual (e.g. auto loan) and projected total
payments are calculated using a monthly interest period.
---------------------------------
Public Function CumulativePmt(ByVal dblOrgBal As Double, _
ByVal OrgDate As Date, ByVal FirstPmtDate As Date, _
ByVal MatDate As Date, ByVal AnnIntRate As Double, _
ByVal SumPeriod As Double, ByVal IntPeriodsYr As Double, _
ByVal MatPeriods As Double, ByVal intOption As Integer)
Dim x, i As Double
x = SumPeriod
Dim dblProjPmt As Double
Dim dblPvFactor As Double
Dim dblPrinPmt(1 To 250) As Double
Dim dblIntPmt(1 To 250) As Double
Dim dblBalance(0 To 250) As Double
Dim dblPmtDate(0 To 250) As Date
Dim dblDaysYr As Double
Dim dblCumPrin As Double
Dim dblCumInt As Double
Dim dblIntCalc As Double
Dim dblYear As Double
Dim dblMonth As Double
dblBalance(0) = dblOrgBal
dblPmtDate(0) = OrgDate
dblPmtDate(1) = FirstPmtDate
i = 0
If Int((Year(dblPmtDate(i)) - 1968) / 4) = _
(Year(dblPmtDate(i)) - 1968) / 4 Then
dblDaysYr = 366
Else
dblDaysYr = 365
End If
dblPvFactor = ((1 - (1 / (1 + AnnIntRate / IntPeriodsYr) ^
(MatPeriods))) / _
(AnnIntRate / IntPeriodsYr))
dblPvFactor = dblPvFactor * (1 + AnnIntRate / IntPeriodsYr)
dblProjPmt = dblBalance(0) / dblPvFactor
dblCumInt = 0
dblCumPrin = 0
dblProjPmt = dblBalance(0) * (1 + (AnnIntRate / dblDaysYr) * _
(DateValue(dblPmtDate(1)) - DateValue(dblPmtDate(0)))) / dblPvFactor
For i = 1 To x
If Month(dblPmtDate(i - 1)) = 12 Then
dblYear = Year(dblPmtDate(i - 1)) + 1
Else
dblYear = Year(dblPmtDate(i - 1))
End If
If Month(dblPmtDate(i - 1)) = 12 Then
dblMonth = 1
Else
dblMonth = Month(dblPmtDate(i - 1)) + 1
End If
dblPmtDate(i) = dblMonth & "/" & Day(dblPmtDate(i - 1)) _
& "/" & dblYear
dblPmtDate(1) = FirstPmtDate
If Int((Year(dblPmtDate(i)) - 1968) / 4) = _
(Year(dblPmtDate(i)) - 1968) / 4 Then
dblDaysYr = 366
Else
dblDaysYr = 365
End If
dblIntPmt(i) = dblBalance(i - 1) * (AnnIntRate / dblDaysYr) * _
(DateValue(dblPmtDate(i)) - DateValue(dblPmtDate(i - 1)))
dblPrinPmt(i) = dblProjPmt - dblIntPmt(i)
dblBalance(i) = dblBalance(i - 1) - dblPrinPmt(i)
dblCumInt = dblCumInt + dblIntPmt(i)
dblCumPrin = dblCumPrin + dblPrinPmt(i)
Next i
If intOption = 1 Then
Cumulativepmt = dblCumInt
Else
Cumulativepmt = dblCumPrin
End If
End Function