Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
=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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
Ok. So this isn't calculating the payment amount - its the total interest
paid on the loan? Is there a way to also calculate the monthly payment? Sorry to keep bothering you - this is all new to me! "Joel" wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
I first calculated the monthly interest rate using the formula below
=1*(1+(9%/360))^30 360 days a year for 30 days. Depending on the banki these number may be different. I used 1 as the principal amount to get the interest rate for one month. The result is the principal plus interest. =1*(1+(9%/360))^30-1 I have to subtract 1 from the formula to get the actual interest rate. This number gives 0.007527251 I now used the PMT to get monthly payments =PMT(D9,360,250000) which result in ($2,017.44) where D9 is the 0.007527251. 360 is 30 years at 12 payments a year. Not 360 days like above. You may want to calcualte with 15, 20, or 25 years. "sg" wrote: Ok. So this isn't calculating the payment amount - its the total interest paid on the loan? Is there a way to also calculate the monthly payment? Sorry to keep bothering you - this is all new to me! "Joel" wrote: 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
You can build your own morgage payment schedule to help understand the numbers
Put in E1 the monthly payment from the IPT formula 2017.44172623422 A1 = put the amount of loan 250000 B1 =A1*(1+(9%/360))^30 the principal plus interest after 30 days C1 = B1 - A1 the interest paid at the end of the month D1 = E$1-C1 which is the principal paid the 1st month A2 = A1 - D1 the new principal at the end of the 1st month Then copy B1:D1 to B2:D2 Now copy row 2 to rows 3 - 360 You will see that the last payment will bring the principal to zero. "Joel" wrote: I first calculated the monthly interest rate using the formula below =1*(1+(9%/360))^30 360 days a year for 30 days. Depending on the banki these number may be different. I used 1 as the principal amount to get the interest rate for one month. The result is the principal plus interest. =1*(1+(9%/360))^30-1 I have to subtract 1 from the formula to get the actual interest rate. This number gives 0.007527251 I now used the PMT to get monthly payments =PMT(D9,360,250000) which result in ($2,017.44) where D9 is the 0.007527251. 360 is 30 years at 12 payments a year. Not 360 days like above. You may want to calcualte with 15, 20, or 25 years. "sg" wrote: Ok. So this isn't calculating the payment amount - its the total interest paid on the loan? Is there a way to also calculate the monthly payment? Sorry to keep bothering you - this is all new to me! "Joel" wrote: 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
Well, I must say - you have been very helpful. Thank you for going above and
beyond in your explanations. I think I will be fine from now on. I truly do appreciate your help - this forum is a great place to get help! "Joel" wrote: You can build your own morgage payment schedule to help understand the numbers Put in E1 the monthly payment from the IPT formula 2017.44172623422 A1 = put the amount of loan 250000 B1 =A1*(1+(9%/360))^30 the principal plus interest after 30 days C1 = B1 - A1 the interest paid at the end of the month D1 = E$1-C1 which is the principal paid the 1st month A2 = A1 - D1 the new principal at the end of the 1st month Then copy B1:D1 to B2:D2 Now copy row 2 to rows 3 - 360 You will see that the last payment will bring the principal to zero. "Joel" wrote: I first calculated the monthly interest rate using the formula below =1*(1+(9%/360))^30 360 days a year for 30 days. Depending on the banki these number may be different. I used 1 as the principal amount to get the interest rate for one month. The result is the principal plus interest. =1*(1+(9%/360))^30-1 I have to subtract 1 from the formula to get the actual interest rate. This number gives 0.007527251 I now used the PMT to get monthly payments =PMT(D9,360,250000) which result in ($2,017.44) where D9 is the 0.007527251. 360 is 30 years at 12 payments a year. Not 360 days like above. You may want to calcualte with 15, 20, or 25 years. "sg" wrote: Ok. So this isn't calculating the payment amount - its the total interest paid on the loan? Is there a way to also calculate the monthly payment? Sorry to keep bothering you - this is all new to me! "Joel" wrote: 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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
360/365, etc.
Here is a simple mortgage calcuator they give out for free and banks and
morgage brokers. It shows the monthly payments on different type loans. The top row is the number of years and the left column is the percentage rate based on a loan of $1000.00. For a 30 year loan at 9% for $250.00 dollars simply multiply 8.07 * 250 = $2017.50 which is the same thing I got. Usually I use as a simple rule of thumb that a 6% loan for 30 years is $6.00. I calculated the table using my formulas and new my calculations were correct because I got the $6.00 for a 6% loan. 10 15 20 25 30 5.00% 10.61 7.91 6.61 5.85 5.37 5.25% 10.73 8.04 6.74 6.00 5.53 5.50% 10.86 8.18 6.89 6.15 5.69 5.75% 10.98 8.31 7.03 6.30 5.84 6.00% 11.11 8.45 7.17 6.45 6.00 6.25% 11.24 8.58 7.32 6.61 6.17 6.50% 11.36 8.72 7.47 6.76 6.33 6.75% 11.49 8.86 7.61 6.92 6.50 7.00% 11.62 9.00 7.76 7.08 6.67 7.25% 11.75 9.14 7.92 7.24 6.84 7.50% 11.88 9.28 8.07 7.40 7.01 7.75% 12.01 9.43 8.22 7.57 7.18 8.00% 12.15 9.57 8.38 7.74 7.36 8.25% 12.28 9.72 8.54 7.90 7.53 8.50% 12.41 9.86 8.70 8.07 7.71 8.75% 12.55 10.01 8.86 8.24 7.89 9.00% 12.69 10.16 9.02 8.41 8.07 9.25% 12.82 10.31 9.18 8.59 8.25 9.50% 12.96 10.46 9.35 8.76 8.44 9.75% 13.10 10.62 9.51 8.94 8.62 10.00% 13.24 10.77 9.68 9.12 8.81 "sg" wrote: Well, I must say - you have been very helpful. Thank you for going above and beyond in your explanations. I think I will be fine from now on. I truly do appreciate your help - this forum is a great place to get help! "Joel" wrote: You can build your own morgage payment schedule to help understand the numbers Put in E1 the monthly payment from the IPT formula 2017.44172623422 A1 = put the amount of loan 250000 B1 =A1*(1+(9%/360))^30 the principal plus interest after 30 days C1 = B1 - A1 the interest paid at the end of the month D1 = E$1-C1 which is the principal paid the 1st month A2 = A1 - D1 the new principal at the end of the 1st month Then copy B1:D1 to B2:D2 Now copy row 2 to rows 3 - 360 You will see that the last payment will bring the principal to zero. "Joel" wrote: I first calculated the monthly interest rate using the formula below =1*(1+(9%/360))^30 360 days a year for 30 days. Depending on the banki these number may be different. I used 1 as the principal amount to get the interest rate for one month. The result is the principal plus interest. =1*(1+(9%/360))^30-1 I have to subtract 1 from the formula to get the actual interest rate. This number gives 0.007527251 I now used the PMT to get monthly payments =PMT(D9,360,250000) which result in ($2,017.44) where D9 is the 0.007527251. 360 is 30 years at 12 payments a year. Not 360 days like above. You may want to calcualte with 15, 20, or 25 years. "sg" wrote: Ok. So this isn't calculating the payment amount - its the total interest paid on the loan? Is there a way to also calculate the monthly payment? Sorry to keep bothering you - this is all new to me! "Joel" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|