I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

Thank you for your quick response. I'm sorry I haven't replied sooner. You
did an awesome job explaining everything to me, however, when I try your
method, I am getting a monthly payment that is millions of dollars.

For example, I have a loan amount of $250,000. The interest rate is 9% and
it is a 365/365 interest calculation.

So, I used the following formula: =250000*(1+9%/365)*365

The monthly payment I am getting back is $91,272,500.

I did notice that in the function you gave me there were two ** before the
last argument. I wasn't sure if that was a typo - I tried it, though, and
Excel wanted to take it out.

Do you know what I am doing wrong?

Thank you.

"Joel" wrote:

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

=250000*(1+9%/365)^365

** is ^

"sg" wrote:

Thank you for your quick response. I'm sorry I haven't replied sooner. You
did an awesome job explaining everything to me, however, when I try your
method, I am getting a monthly payment that is millions of dollars.

For example, I have a loan amount of $250,000. The interest rate is 9% and
it is a 365/365 interest calculation.

So, I used the following formula: =250000*(1+9%/365)*365

The monthly payment I am getting back is $91,272,500.

I did notice that in the function you gave me there were two ** before the
last argument. I wasn't sure if that was a typo - I tried it, though, and
Excel wanted to take it out.

Do you know what I am doing wrong?

Thank you.

"Joel" wrote:

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

Sorry - I didn't get that ** was ^. Now when I calculate the payment, I am
still getting payments that are very high - more than the original loan
amount. Somehow I always have the knack of making these things more
complicated...

Anyway, this is what I am doing now: =250000*(1+9%/365)^365. The result I
get is 273540.5.

I just can't see what I have messed up.

"Joel" wrote:

=250000*(1+9%/365)^365

** is ^

"sg" wrote:

Thank you for your quick response. I'm sorry I haven't replied sooner. You
did an awesome job explaining everything to me, however, when I try your
method, I am getting a monthly payment that is millions of dollars.

For example, I have a loan amount of $250,000. The interest rate is 9% and
it is a 365/365 interest calculation.

So, I used the following formula: =250000*(1+9%/365)*365

The monthly payment I am getting back is $91,272,500.

I did notice that in the function you gave me there were two ** before the
last argument. I wasn't sure if that was a typo - I tried it, though, and
Excel wanted to take it out.

Do you know what I am doing wrong?

Thank you.

"Joel" wrote:

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

The number you are getting is correct. If you put 1.00 for the dollar amount
you will see the results are 1.094 which is the APR. So a 9% loan APR is
9.4%.

$250,000.00 * 9% is $22,500. $250,000.00 * 9.4% = $23,500. You pay an
extra $1,000.00 a year on the compound interest. $250,000.00 + $23,500 =
$273,500.00. You get some rounding errors with the calculations.

"sg" wrote:

Sorry - I didn't get that ** was ^. Now when I calculate the payment, I am
still getting payments that are very high - more than the original loan
amount. Somehow I always have the knack of making these things more
complicated...

Anyway, this is what I am doing now: =250000*(1+9%/365)^365. The result I
get is 273540.5.

I just can't see what I have messed up.

"Joel" wrote:

=250000*(1+9%/365)^365

** is ^

"sg" wrote:

Thank you for your quick response. I'm sorry I haven't replied sooner. You
did an awesome job explaining everything to me, however, when I try your
method, I am getting a monthly payment that is millions of dollars.

For example, I have a loan amount of $250,000. The interest rate is 9% and
it is a 365/365 interest calculation.

So, I used the following formula: =250000*(1+9%/365)*365

The monthly payment I am getting back is $91,272,500.

I did notice that in the function you gave me there were two ** before the
last argument. I wasn't sure if that was a typo - I tried it, though, and
Excel wanted to take it out.

Do you know what I am doing wrong?

Thank you.

"Joel" wrote:

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.

USA congress enacted years aog the "Truth in leading Laws" that basically
required every back to post APR (Actual proimary rate) because banks were
putting in ine print exception to the interest rate like 365 instead of 360.

I've also answer postings were people wer asking the diference between usa
calculation and europen calculations. Europens were getting errors using PMT
and didn't understand microsoft was a USA company and was defaulting to USA
standards.

"sg" wrote:

Thank you for your quick response. I'm sorry I haven't replied sooner. You
did an awesome job explaining everything to me, however, when I try your
method, I am getting a monthly payment that is millions of dollars.

For example, I have a loan amount of $250,000. The interest rate is 9% and
it is a 365/365 interest calculation.

So, I used the following formula: =250000*(1+9%/365)*365

The monthly payment I am getting back is $91,272,500.

I did notice that in the function you gave me there were two ** before the
last argument. I wasn't sure if that was a typo - I tried it, though, and
Excel wanted to take it out.

Do you know what I am doing wrong?

Thank you.

"Joel" wrote:

The interest formula is

P = A * (1 + I/N)**T

A = Amount
I = Interest
N = number of time periods
T = periods


PMT is just a differnt form of the formula
where rate is interest and nper is the time of the loan

Lets assum interest is 5%

So 365/365 means computing daily interest using 365 days a year for 365 days
I = .05, N = 365, T=365

Rate = .05/365 Nper = 365

So 30/360 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months

Each month the calculation would be
I = .05, N = 360, T=30
assuming interest calculated at 365 days a year. USA rules are 360 days a
year because each month is a constant 30 days. There are 5 or 6 bank
holidays a year

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 360 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


So 30/365 means you have a 30 year loan with monthly payments. The interest
rate formula needs to be recalculated at the end of each month for 360 months
but the yearly interest rate being calculated 365 days a year

Each monthly the calculation would be
I = .05, N = 365, T=30
assuming interest calculated at 365 days a year. Non-USA rules are 365 days
a year.

The nper rate need to be calculate using the Interest rate for monthly period
I = .05 N = 365 T = 30 A = 1
The P (The result of the Interest formula) is the effective interest rate
per month

Rate = P Nper = 360 (payments)


The efective interest rate is the key to the differences in the calculations.

"sg" wrote:

I need to calculate a monthly payment but the interest method will be 365/365
or 365/360, 30/360, or 30/365. My question is - can I use the PMT function
for this? And, if so, what do I put in instead of interest rate/12 to
calculate this?

Thank you in advance.