Calculate cumulativeInterest

[dav...@my-deja.com]

Hey All.

I am having a little trouble comming up with a way to (obviously)
calculate the cumulative interest in my A97 DB. It's a credit card
based thing and I am trying to determine that given a fixed BALANCE,
MONTHLY PAYMENT, INTEREST RATE, and the known PAYOFF TIME to find out
how much interest was paid during the life of the loan. Assume that no
other charges will be added.

The interest will be compounded monthly and the payments will be made
each month at the beginning of the period. My accounting is not the
best to begin with and all the formulas I have managed to find are
missing one piece or another.

There is a formula in Excel called CUMIPMT that works fine but I can't
find the corresponding thing in Access or the long math to make it
work.

Any ideas anyone?

TIA as always.

David

Sent via Deja.com http://www.deja.com/
Before you buy.

[Scott McDaniel]

Check online help for keyword "interest" ... there's a couple of functions
that come up (Pmt, Rate, etc etc)

<dav...@my-deja.com> wrote in message news:8nulb5$82f$1...@nnrp1.deja.com...

[dav...@my-deja.com]

Thanks for the thought Scott but I tried that and they don't have
exactly what i am looking for. I think I need some sort of amortization
formula but I'm not entirely sure.

The search continues....

In article <c8Eo5.1554$Z4.3...@news1.rdc1.tn.home.com>,

[Steven R. Zuch]

You can write your own function that loops through the IPmt function
for the number of periods you want to cumulate the interest for -
including the full life of the loan.

The IPmt function returns the interest portion of one payment.

Example of a function that calculates the cumulative interest paid
over the life of a loan, assuming the payments are made at the end of
the period.

Function CumInt(Rate As Double, NPer As Integer, PV As Currency) As
Currency

Dim P As Integer, I As Double
I = 0
For P = 1 To NPer
I = I + IPmt(Rate, P, NPer, PV)
Next P
CumInt = -I

End Function

Steven Zuch
Cogent Management Inc.

[Mike Preston]

David,

The CUMIPMT function in Excel is used to determine the interest that
would be paid between two dates, typically NOT the start date and the
end date. Note that the CUMIPMT function requires you to tell it the
number of payments that are to be made.

If the number of payments is equal to the total number of payments
there would be an easy shortcut formula (i=(PERIODIC_PAYMENT *
NUMBER_OF_PAYMENTS) - PRESENT_VALUE)

If this is what you are looking for you need to determine the number
of payments, as the other two factors are already known to you.

If you give a numerical example of what you are looking for, it should
be a snap to help you come up with the right formula. I suspect that
it merely requires you to determine the number of payments between a
start date (which you didn't indicate was available in the data you
mentioned) and the payoff date, which you called the PAYOFF TIME.

mike preston

On Wed, 23 Aug 2000 15:05:08 GMT, dav...@my-deja.com wrote:

>Thanks for the thought Scott but I tried that and they don't have
>exactly what i am looking for. I think I need some sort of amortization
>formula but I'm not entirely sure.
>
>The search continues....
>
>
>In article <c8Eo5.1554$Z4.3...@news1.rdc1.tn.home.com>,
> "Scott McDaniel" <mcda...@home.com> wrote:
>> Check online help for keyword "interest" ... there's a couple of
>functions
>> that come up (Pmt, Rate, etc etc)
>>
>> <dav...@my-deja.com> wrote in message news:8nulb5
>$82f$1...@nnrp1.deja.com...
>> > Hey All.
>> >

>> > I am having a little trouble comming up with a way to (obviously)
>> > calculate the cumulative interest in my A97 DB. It's a credit card
>> > based thing and I am trying to determine that given a fixed BALANCE,
>> > MONTHLY PAYMENT, INTEREST RATE, and the known PAYOFF TIME to find
>out
>> > how much interest was paid during the life of the loan. Assume
>that no
>> > other charges will be added.
>> >

[dav...@my-deja.com]

Thanks for the insight. I DO know the number of payments it would take
to pay off the loan. I think that's what I meant by PAYOFF TIME. A
nuerical example would be something like:

Balance: 1750.00
Interest: 14.9%
Monthly Payment: 75.00
Payoff in: 28 months
compound int: monthly

At this rate, how much interest would have been paid when the loan is
paid off and how would we calculate that? I could iterate through all
the payments using the IPMT function but that is my second choice. I
have all the variables, just need to know how to put them together.

I appreciate the help.

Thanks

David

In article <39a3fd10...@news.flash.net>,

[Mike Preston]

David,

The answer is determined by multiplying the number of payments (28) by
the monthly payment ($75) and subtracting from the result ($2,100) the
balance/present value ($1,750) resulting in interest of $350 over the
life of the payoff.

mike preston

[dav...@my-deja.com]

OK...that looks like WAY to easy. Something HAS to be missing.<G>
Although the logic of it makes sence even if I do look at it and go DUH!

Thanks for the help Mike

David

In article <39a4154e...@news.flash.net>,