Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    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. #2
    Bronze Lounger
    Join Date
    Jun 2001
    New York, New York, Lebanon
    Thanked 1 Time in 1 Post

    Re: bi-monthly mortgage payments (Excel 2002)


    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.

    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Salt Lake City, Utah, USA
    Thanked 6 Times in 6 Posts

    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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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