Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 29th 04, 11:07 PM
amalecki
 
Posts: n/a
Default I want the PMT function to calculate using 360 days not 365

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.

  #2   Report Post  
Old December 29th 04, 11:28 PM
Don Guillett
 
Posts: n/a
Default

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.



  #3   Report Post  
Old December 29th 04, 11:29 PM
[email protected]
 
Posts: n/a
Default

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?

  #4   Report Post  
Old December 30th 04, 03:19 PM
amalecki
 
Posts: n/a
Default



" 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.

  #5   Report Post  
Old December 30th 04, 03:51 PM
amalecki
 
Posts: n/a
Default

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.






  #6   Report Post  
Old December 31st 04, 03:25 AM
Harlan Grove
 
Posts: n/a
Default

"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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I calculate EDATE using days not months? pshift Excel Discussion (Misc queries) 3 December 21st 04 10:19 PM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 09:53 PM
How do I use the IF function to calculate date Pulling My Hair Out! Excel Discussion (Misc queries) 1 December 11th 04 12:03 AM
Calculate # of Days from one date to another jscano Excel Worksheet Functions 2 November 11th 04 04:23 AM
how do I make a function to count days? khamsta Excel Worksheet Functions 2 November 1st 04 11:53 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017