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.

 I want the PMT function to calculate using 360 days not 365
 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

#1
December 29th 04, 10:07 PM
 amalecki external usenet poster Posts: n/a
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
December 29th 04, 10:28 PM
 Don Guillett external usenet poster Posts: n/a

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
December 29th 04, 10:29 PM
 [email protected] external usenet poster Posts: n/a

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
December 30th 04, 02:19 PM
 amalecki external usenet poster Posts: n/a

" 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
December 30th 04, 02:51 PM
 amalecki external usenet poster Posts: n/a

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
December 31st 04, 02:25 AM
 Harlan Grove external usenet poster Posts: n/a

"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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 08:41 PM.