A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

I want the PMT function to calculate using 360 days not 365



 
 
Thread Tools Display Modes
  #1  
Old December 29th 04, 10:07 PM
amalecki
external usenet poster
 
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.
Ads
  #2  
Old December 29th 04, 10:28 PM
Don Guillett
external usenet poster
 
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  
Old December 29th 04, 10:29 PM
[email protected]
external usenet poster
 
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  
Old December 30th 04, 02:19 PM
amalecki
external usenet poster
 
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  
Old December 30th 04, 02:51 PM
amalecki
external usenet poster
 
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  
Old December 31st 04, 02:25 AM
Harlan Grove
external usenet poster
 
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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 09:19 PM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 08:53 PM
How do I use the IF function to calculate date Pulling My Hair Out! Excel Discussion (Misc queries) 1 December 10th 04 11:03 PM
Calculate # of Days from one date to another jscano Excel Worksheet Functions 2 November 11th 04 03:23 AM
how do I make a function to count days? khamsta Excel Worksheet Functions 2 November 1st 04 10:53 PM


All times are GMT +1. The time now is 07:52 PM.


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