# Thread: bi-monthly mortgage payments (Excel 2002)

1. ## bi-monthly mortgage payments (Excel 2002)

I need an Excel formula that will calculate a mortgage payment that is made twice a month vs. the standard one.

I can make it run a normal amortization schedule with monthly payments, but I can't get it to calculate one correctly for bi-monthly payments. The borrowers payment is \$845.31. However he makes the payment bi-monthly so on the 15th he pays \$422.66 then on the 30th he pays another \$422.66. What happens is that he is reducing principal on the 15th so he is paying less interest from then until the 30th. For that month especially in the beginning of a loan it's not that much but in the long run you can shave about 7 years off of your mortgage.

So the main thing is I need the principal to adjust in the middle of the month amortization.

Any help would be greatly appreciated.

2. ## Re: bi-monthly mortgage payments (Excel 2002)

crossmamy

Why don't you ask your bank, to give you the formula for bi-monthly mortgage payments. They will be the ones to adjust the principal and Interest rate and all that.

Or you can install Excel's Analysis ToolPak, or others, Add-In and see what kind of formulas you get from it.

Hope this helps.

Wassim

3. ## Re: bi-monthly mortgage payments (Excel 2002)

Edit in Italics

I don't do this often, but I think =PMT() will derive the amount of the payment due; be sure to adjust the interest rate to a semi-monthly rate as I did in this example. See if the attached helps. Use =CUMPRINC if you need a schedule of remining principal.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•