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
------------------------------------------------------------------------
rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
View this thread: http://www.excelforum.com/showthread...hreadid=564999

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

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

"rktect" wrote in
message :

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

Thanks Jim
You can contact me on


--
rktect
------------------------------------------------------------------------
rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
View this thread: http://www.excelforum.com/showthread...hreadid=564999

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" wrote in message
...

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

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


--
rktect
------------------------------------------------------------------------
rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
View this thread: http://www.excelforum.com/showthread...hreadid=564999

Just sent to you;

"rktect" wrote in
message :

Thanks Jim
You can contact me on


--
rktect
------------------------------------------------------------------------
rktect's Profile: http://www.excelforum.com/member.php...o&userid=36786
View this thread: http://www.excelforum.com/showthread...hreadid=564999