Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Payment Calculation
- Thread starter pdwalker
- Start date
Hi!
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.
Hope that helps.
Indu
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.
Hope that helps.
Indu
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)
Regards,
M. Russell
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)
Regards,
M. Russell
- Status
