Financial Loan calc including monthly fees

[rktect]

How do i calculate monthly loan repayments that incorporate a monthly account keeping fee? I have used the PMT function before but it doesnt suit this application (from i can figure out anyway). I am a bit of a novice user.

Using the following values;
Initial loan amount: $22,757.50
Interest Rate: 7.95%
Length of Loan: 7 years
and a Monthly account keeping fee: $7.50

;i wanted to replicate the formula used to acheive the following results as set out on my contract;
Monthly replayment: $362.11
Total Amount Paid at end of loan: $30,417.24
and total interest paid at end of loan: $7029.74

Any help would be greatly appreciated.

[rktect]

Interest is calculated daily and payments are monthly...
Regards,

[Jim May]

Provide a e-mail address and I will send you my take on this.
Jim

"rktect" <[email protected]> wrote in
message news:[email protected]:

> Interest is calculated daily and payments are monthly...
> Regards,
>
>
> --
> rktect
> ------------------------------------------------------------------------
> rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
> View this thread: http://www.excelforum.com/showthread...hreadid=564999

[rktect]

Thanks Jim
You can contact me on [email protected]

[Fred Smith]

Here is how you get the answer you are looking for. It doesn't match exactly the
bank's numbers, but we'll discuss why at the end.

Moving from the simplest up:

1. Total Amount Paid is always PMT*TERM (or PMT * NPER in Excel terminology)
2. Total Interest charged is always PMT * NPER - PV
3. When you are calculating your payment, you ignore the monthly accounting fee.
Simply add it to the payment after it's been calculated. The formula would be
=PMT(...)+7.50
4. If the compounding and payment period are the same, the calculation becomes:
=PMT(7.95%/12,12*7,-22757.50)+7.50 which is $361.64.
But, this is less than the bank is charging because they are compounding the
interest daily.
5. To calculate the effective annual rate, you ask the question "If I borrowed
$100 at 7.95% compounded daily, how much would I owe at the end of the year?"
Answer: =FV(7.95%/365,365,0,-100) or $108.27. Now you know the effective annual
rate is 8.27%.
6. To get the monthly rate, you ask "What rate, compounded monthly, turns $100
into $108.27 after a year?" Answer: =Rate(12,0,-100,108.27) or 0.66%.
7. Finally, put these all together, and you have:
=PMT(rate(12,0,-100,fv(7.95%/365,365,0,-100)),12*7,-22757.50)+7.50 or $361.93

Now, we are within 18 cents of the bank's calculation. The reasons for the
difference would include:

1. There are one or two leap days in the period which attract extra interest.
2. Payments due on Saturday or Sunday (or a holiday) won't actually be made
until the next business day, so interest will accrue for those days.
3. The bank is not calculating your payment correctly.

Hope this helps,
--
Regards,
Fred


"rktect" <[email protected]> wrote in message
news:[email protected]...
>
> Interest is calculated daily and payments are monthly...
> Regards,
>
>
> --
> rktect
> ------------------------------------------------------------------------
> rktect's Profile:
> http://www.excelforum.com/member.php...o&userid=36786
> View this thread: http://www.excelforum.com/showthread...hreadid=564999
>

[rktect]

Fred
I'd like to think that the numbers differ in the end because of your final points 1 and 2. Makes sense. And would hope that it is not because they are calulating it wrong. The extra 18 cents has to be accounted for somewhere.
I think what you have provided here is great and it will serve my purposes.
Thank you

[Jim May]

Just sent to you;

"rktect" <[email protected]> wrote in
message news:[email protected]:

> Thanks Jim
> You can contact me on [email protected]
>
>
> --
> rktect
> ------------------------------------------------------------------------
> rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
> View this thread: http://www.excelforum.com/showthread...hreadid=564999