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