Using the simple PMT formula, with an annual interest rate og 7%(cell A5), on a loan of $10,000 (cell A6) for 12 months (cell A7), the formula
=PMT(A5/12,A7,A6)
yields a monthly payment of $865.27.
The PMT formula does not take into account whether the year is a 365 or 366 or 360 days, it just takes into account the interest rate per payment period for the length of the period for a given principal amount.
Having said that, with a 365 day year would get a daily effective interest rate of
=(1+A5/A7)^(A7/365)-1
or 0.019124%
The same with a 360 day year will get you a daily effective rate of
=(1+A5/A7)^(12/360)-1
or 0.019390%
As a result, both would produce an annual effective rate of
7.2229 and 7.3330 respectively ((1+B15)^365-1 for both)
So, finally, based on a 360 day year the payment on the loan would be
=PMT(((1+(1+(1+A5/A7)^(12/360)-1)^365-1)^(1/12)-1)*12/A7,A7,A6)
or $865.72
A difference of $0.45
I am hoping the math is all good here, but anyway that's the concept.
Hello. Interesting question about 360 day years. FYI- when I was a temp employee at a bank I discovered that banks work with a 360 Day year, but the employees have 365 days in their work year. Excel's default is the 365 day year and every time I made a payment calculation with Excel's PMT function, it matched the Bank's calculation EXACTLY.
Now, Excel does have the function "DAYS360" which uses the 360 day year for date calculations. However, I think the the question about monthly payment is moot because no matter how many "days" are in the "year" a "month" is 1/12 of the number of days.
Now, the DAILY interest rate will be slightly higher for a year with 360 days when compared to a 365 day year but there are slightly less days in the 360 day year month to compensate.
The formula I came up with to calculate a monthly payment based on ANY NUMBER OF DAYS in a year is as follows:
=PMT(RATE/DAYS IN YEAR, DAYS IN YEAR * # OF YEARS,PRESENT VALUE)*(DAYS IN YEAR/12)
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.