"sgl" wrote:
Thank you for your great assistance. This also works verry well. Only
slight
rounding differences when you put the formula in a worksheet. The total
loan
repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In
the VBE it works fine when you run the macro.

I cannot imagine why anyone would resort to such a complex solution,
especially a UDF, when a straight-forward Excel solution exists. (Not to
mention an Excel solution that works for in-arrears as well as in-advance
payments with one obvious, well-documented change to a function argument.)

But FYI, I do not get any such numerical error, whether I use the UDF as
Dana wrote it, or I implement it in an Excel formula. I am using Excel
2003.

Nonetheless, I would not be surprised by a 0.01 difference between an Excel
and VB implementation with some loan parameters. The VB Round function
behaves differently from the Excel ROUND function; specifically, the VB
Round function does "banker's rounding". Unless that is what you want (!),
I would suggest the following change to Dana's UDF:

CumPrin = WorksheetFunction.Round(CumPrin, 2)


----- original message -----

"sgl" wrote in message
...
Dana,

Thank you for your great assistance. This also works verry well. Only
slight
rounding differences when you put the formula in a worksheet. The total
loan
repayments amount to 90,000,000.05 leaving a balloon of 29,999,999.95. In
the
VBE it works fine when you run the macro.

sgl



"Dana DeLouis" wrote:

Hi. Glad you found it useful.
If interested, here is the change in balance formula between two periods.

Function CumPrin(Pv, Fv, IntRate, n, s, e)
' Payment at beginning of period
' n = Number of payments in loan
' s = start
' e = end
Dim k, r
r = IntRate / 1200
k = 1 + r
CumPrin = (Pv - Fv) * (k ^ (e + 1) - k ^ s) / (k * (k ^ n - 1))
CumPrin = Round(CumPrin, 2)
End Function

The balance change from period 1-10

? CumPrin(120000000, 30000000, 8.25, 120, 1, 10)
5004106.32

The balance change from 1 - 120 should be the difference pv - fv

? CumPrin(120000000, 30000000, 8.25, 120, 1, 120)
90000000

The Excel formulas for CumIpmt() and CumPrinc() don't have an option for
Future value. Unfortunately, they therefore assume a 0 future value.

HTH
Dana DeLouis
= = = =


sgl wrote:
Dana,

Thank you vm. This works very well. I have a separate column for the
period
(1,2,3 etc ...120) and therefore declaring the start and end dates in
the
CumInt column as absolute and relative respectively you get the CumInt
to
date. In the interest for a specific period I have both start and end
dates
as relative and therefore I get the interest for that particular
period.

This is very good formula to have and thank you for your assistance/sgl

"Dana DeLouis" wrote:

Hi. If interested, here is a custom function for cumulative interest.
You can bypass the Pmt() function as it is already incorporated into
the
function.
I left the interest rate at 8.25 so as to make it easier to enter.


Function CumInt(Pv, Fv, IntRate, n, s, e)
' n = Number of payments
' s = start
' e = end
Dim k, r
r = IntRate / 1200
k = 1 + r
CumInt = (Fv * (k ^ (e + 1) + r * (-e + s - 1) - k ^ s) + _
Pv * (-k ^ (e + 1) + r * (e - s + 1) * k ^ n + k ^ s)) _
/ (k * (k ^ n - 1))

End Function


Sub TestIt()
'Period 5 only
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 5, 5)
'All periods 1 - 120
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 120)

'Periods 1 - 10
Debug.Print CumInt(120000000, 30000000, 8.25, 120, 1, 10)
End Sub

So, on a worksheet for periods 1-10, you would enter:

=CumInt(120000000, 30000000, 8.25, 120, 1, 10)

The 3 answers above a

802575.291677935
66141363.223381
8007673.94895974

= = =
HTH :)
Dana DeLouis


<snip