I am using Excel 2002 I am trying to calculate a fixed monthly payment on a
24 month loan. The problem is the bank uses a 360 day basis when they
calculate the fixed monthly payment. Excel's PMT formula has a 365 day basis.
I have been on the Internet to no avail. I would greatly appreciate
anyone's help in solving this problem.

amalecki wrote...
I am using Excel 2002 I am trying to calculate a fixed monthly payment
on a
24 month loan. The problem is the bank uses a 360 day basis when they

calculate the fixed monthly payment. Excel's PMT formula has a 365 day
basis.
....

If you have 24 identical monthly payments, the only trick is in
calculating the *effective* monthly interest rate. That is, whether you
use 360, 365 or 366 day years, there are always 12 months in a year.
Your effective interest rate is the rate used for compounding, but
banks like to quote *nominal* interest rates which are lower than
annualized effective interest rates. (Truth in lending?!) Anyway, if
your bank quotes nominal interest rates for daily compounding, then
what I suspect is that the bank calculates the monthly effective
interest rate as

(1 + Nominal Rate / 360)^30 - 1

rather than as

(1 + Nominal Rate / 365)^(365/12) - 1

For a 6.0% nominal interest rate, the former returns an effective
monthly interest rate of 0.5012102% (so an annual effective rate of
6.18312%) while the latter gives 0.5012108% monthly (6.18313%
annually).

What's the stated interest rate and the ratio of your monthly payment
to the loan amount?

" wrote:

amalecki wrote...
I am using Excel 2002 I am trying to calculate a fixed monthly payment
on a
24 month loan. The problem is the bank uses a 360 day basis when they

calculate the fixed monthly payment. Excel's PMT formula has a 365 day
basis.
....

If you have 24 identical monthly payments, the only trick is in
calculating the *effective* monthly interest rate. That is, whether you
use 360, 365 or 366 day years, there are always 12 months in a year.
Your effective interest rate is the rate used for compounding, but
banks like to quote *nominal* interest rates which are lower than
annualized effective interest rates. (Truth in lending?!) Anyway, if
your bank quotes nominal interest rates for daily compounding, then
what I suspect is that the bank calculates the monthly effective
interest rate as

(1 + Nominal Rate / 360)^30 - 1

rather than as

(1 + Nominal Rate / 365)^(365/12) - 1

For a 6.0% nominal interest rate, the former returns an effective
monthly interest rate of 0.5012102% (so an annual effective rate of
6.18312%) while the latter gives 0.5012108% monthly (6.18313%
annually).

What's the stated interest rate and the ratio of your monthly payment
to the loan amount?

amalecki writes:
The only interest rate I have from the Bank is 6%; the monthly payment the
bank calculated is $9,465.67, based upon a loan amount of $213,402.24.

"amalecki" wrote...
The only interest rate I have from the Bank is 6%; the monthly payment the
bank calculated is $9,465.67, based upon a loan amount of $213,402.24.

Excel's RATE function, =RATE(24,9465.67,-213402.24), gives 0.00506544 as the
monthly effective interest rate. That gives an annual effictive interest
rate of 0.06250763. 0.00506544/0.06 = 11.84498143, 360/(365/12) =
11.83561644. I have to admit I don't see how the bank comes up with their
monthly loan payment.

try

(Principle * i * (1+i)^n) ) / ((1+i)^n -1)
i = period interest rate (annual rate/12)
n = number of periods
====

or From Norman Harker

PMT = =-(PV*(1+RATE)^NPER+FV)/((1+RATE*TYPE)*(((1+RATE)^NPER-1)/RATE))

--
Don Guillett
SalesAid Software

"amalecki" wrote in message
...
I am using Excel 2002 I am trying to calculate a fixed monthly payment on
a
24 month loan. The problem is the bank uses a 360 day basis when they
calculate the fixed monthly payment. Excel's PMT formula has a 365 day
basis.
I have been on the Internet to no avail. I would greatly appreciate
anyone's help in solving this problem.

Don,
I tried your equations but to no avail; the Bank is using 6% based upon a
360 day year; the loan amount is $213,402.24; the loan will be paid off after
24 monthly payments; the fixed monthly payment the Bank has calculated is
$9,465.67.
Thanks
amalecki

"Don Guillett" wrote:

try

(Principle * i * (1+i)^n) ) / ((1+i)^n -1)
i = period interest rate (annual rate/12)
n = number of periods
====

or From Norman Harker

PMT = =-(PV*(1+RATE)^NPER+FV)/((1+RATE*TYPE)*(((1+RATE)^NPER-1)/RATE))

--
Don Guillett
SalesAid Software

"amalecki" wrote in message
...
I am using Excel 2002 I am trying to calculate a fixed monthly payment on
a
24 month loan. The problem is the bank uses a 360 day basis when they
calculate the fixed monthly payment. Excel's PMT formula has a 365 day
basis.
I have been on the Internet to no avail. I would greatly appreciate
anyone's help in solving this problem.

To calculate the fixed monthly payment on a 24 month loan using a 360 day basis:

1. Open a new Excel worksheet and enter the following information in cells A1 to A3:
   - A1: Loan amount
   - A2: Annual interest rate
   - A3: Loan term in months (24)
   
2. In cell A4, enter the following formula:
   
   Formula:

   =PMT(A2/12, A3, A1, 0, 0) 

   This formula uses the PMT function to calculate the fixed monthly payment based on the loan amount, annual interest rate, and loan term in months. The last two arguments (0, 0) are optional and set to 0 to indicate that there are no additional fees or charges.
   
3. To adjust the formula to use a 360 day basis instead of a 365 day basis, you can multiply the interest rate by 12/360 instead of 12/365. Here's the modified formula:
   
   Formula:

   =PMT(A2*12/360, A3, A1, 0, 0) 

   This formula uses the same PMT function as before, but multiplies the annual interest rate by 12/360 to adjust for the bank's 360 day basis.

That's it! You should now have the fixed monthly payment for your 24 month loan based on a 360 day basis.