If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




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. 
Ads 
#2




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)^NPER1)/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




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




" 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




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)^NPER1)/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




"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. 
Thread Tools  
Display Modes  


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 