bi-monthly mortgage payments (Excel 2002)

Topic

New Reply

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.

Reply | Quote

Replies

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.

if they refuse then you can ask your accountant and he will know about these things.

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

Reply | Quote

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.

Reply | Quote