redranger7018
New member
- Joined
- Jul 15, 2006
- Messages
- 24
Hello, I hope that someone can me help with this. I’m now in my computer programming class and I’m trying to make a program that will amortize monthly payments for a mortgage. Now I’m not having any problems with the programming part, but I am having problems with the mathematical formulas that allows’ me to figure out what a monthly payment would be. Ok here we go then.
If you have a mortgage of $100.00 at a 5.0% for 1 year, or 12 months, what would the monthly payment be?
Now while doing some internet research I found a formula that is for MS-Excel that uses the PMT Function. The PMT Formula is as follows: =-PMT (B2/12,B3*12,B1), ok in an excel work sheet we have this.
In column A: (text only)
Principal
Interest Rate
Term in Years
Monthly Payment
New Balance
In column B:
$100.00
5.0%
1.0, or 12.0 months
$8.56
$91.44
With =-PMT (B2/12,B3*12,B1), this is the formula in cell B4. Now I know that the formula is correct, but when I try to work out the mathematics’ I get a 0.19 cent discrepancy. Ok here is what I mean.
Step one; divide the principal by the term (in months) that is:
$100.00 / 12.0 = $8.33 this the monthly payment with out the interest added
Step two: add the interest rate to the principal that is:
$100.00 + 5.0% = $105.00, so then the total interest is $5.00
Step three: divide the interest by the term or 12 that is:
$5.00 / 12.0 = $0.42, this is the monthly interest for the first payment and the monthly interest is adjusted for each monthly payment
Step four: add the monthly interest to the monthly payment
$8.33 + $0.42 = $8.75
The above excel formula works out the monthly payment of $8.56 which is the correct amount, but I’m coming up with a different monthly payment of $8.75 in my calculations. This is a $0.19 cent difference. I think the problem is with calculating the monthly interest, but I have no idea what I’m doing wrong. Also I can’t see where in the ms-excel formula what is being subtracted from the interest. Is there anyone that can help? Also a complete amortize table with the above amounts can be seen at:
http://www.hsh.com
If you have a mortgage of $100.00 at a 5.0% for 1 year, or 12 months, what would the monthly payment be?
Now while doing some internet research I found a formula that is for MS-Excel that uses the PMT Function. The PMT Formula is as follows: =-PMT (B2/12,B3*12,B1), ok in an excel work sheet we have this.
In column A: (text only)
Principal
Interest Rate
Term in Years
Monthly Payment
New Balance
In column B:
$100.00
5.0%
1.0, or 12.0 months
$8.56
$91.44
With =-PMT (B2/12,B3*12,B1), this is the formula in cell B4. Now I know that the formula is correct, but when I try to work out the mathematics’ I get a 0.19 cent discrepancy. Ok here is what I mean.
Step one; divide the principal by the term (in months) that is:
$100.00 / 12.0 = $8.33 this the monthly payment with out the interest added
Step two: add the interest rate to the principal that is:
$100.00 + 5.0% = $105.00, so then the total interest is $5.00
Step three: divide the interest by the term or 12 that is:
$5.00 / 12.0 = $0.42, this is the monthly interest for the first payment and the monthly interest is adjusted for each monthly payment
Step four: add the monthly interest to the monthly payment
$8.33 + $0.42 = $8.75
The above excel formula works out the monthly payment of $8.56 which is the correct amount, but I’m coming up with a different monthly payment of $8.75 in my calculations. This is a $0.19 cent difference. I think the problem is with calculating the monthly interest, but I have no idea what I’m doing wrong. Also I can’t see where in the ms-excel formula what is being subtracted from the interest. Is there anyone that can help? Also a complete amortize table with the above amounts can be seen at:
http://www.hsh.com