Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    mo'ly vs bi-weekly pmts (2002)

    I'm trying to compare making a loan payments on a monthly basis to the benefit(s) of paying every two weeks.
    I've found the traditional formulas like CUMIPMT and CUMPRINC that work for the standard monthly payments.
    However, I'm stumped on how to apply the same principal, term (because it would obviously change), and int rate and get the various amounts (e.g., payment which is half the monthly, but the portion of the payment that is interest and principal, the cum of the interest and princ for the bi-weekly payments, etc.).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    The number of terms obviously doubles for twice-monthly payments. Dividing the interest rate by two is a good approximation; if you want to be more accurate, use =(1+B3)^0.5-1 where B3 is the cell containing the monthly interest rate.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    I'm still confused. Let's shoot for one result (or generalized).
    Example: $25000 principal; monthly payments; 5 years. Calculating with the formulas in Excel is "obvious"...I know how to calc PMT (509.91), CUMINT and CUMPRINC.
    But, I don't know what to do to compare the results of the traditional (above) with what would happen if the loan is paid bi-weekly and the payment every two weeks is half the payment from the traditional (above).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    Without knowing the interest rate, the PMT is not obvious at all. I assume you used 8%/year.

    Why do you assume that the bi-weekly payment is half that of the monthly payment? Is that a given?

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    The given is (user provided): Princ=25000; Annual Rate=8.25%; Term=5 years.
    Then, PMT yields 509.91.
    If we consider it given that the bi-weekly payment is have the traditional (i.e., 254.95), I want to know the total interest paid on the bi-weekly, the principal balance each year it is outstanding, and the length of the loan payments under the bi-weekly plan. It's something slightly less than the 5 years.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    If you assume that the yearly interest is the same, and that the payment is halved, the number of terms is not an integer any more.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    <P ID="edit" class=small>(Edited by sdckapr on 05-Feb-05 19:39. Added PS)</P>I calculate the payment for biweekly payments should be:
    =-PMT(8.25%/24,10*12,25000,1) =$254.61

    which is a little less thant half of the monthly:
    =-PMT(8.25%/12,5*12,25000,1)/2 = $254.96

    The Cumulative interest for the monthly case is:
    =-CUMIPMT(8.25%/12,5*12,25000,1,60,0) = $5,594.38

    For the biweekly case:
    =-CUMIPMT(8.25%/24,10*12,25000,1,120,0) = $5,552.08

    So you should save:
    $42.30 over the course of the 5 years.

    Steve
    PS if you want to pay more than the "payment amount" I think you need to create an amortization schedule to give additional payments per month against the principle. I do not think there is a formula to do this (but then I am chemist and not a financial expert of any kind).

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    Hans: Many US banks are now pushing making a payment every two weeks (NOT twice a month since it comes out to 26 per year not 24) and the payment is usually half the monthly amount. So, not only are you paying a little earlier each month, you make the equivalent of one extra payment per year. This allows the loan to be paid off earlier and reduces total interest cost. I'm not enough of a Finance person to know how you calculate the periodic interest rate in this case.
    Legare Coleman

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    Here is an amortization schedule based on your info. Given a half-monthly payment instead of the calculated would mean paying $0.35 at each biweekly payment.

    You would still require the 5 years, but the lat payment will be smaller than the others ($202.71 instead of the $254.95) . If you gave $1.73 extra every biweekly payment, you would knock out 1 payment exactly.

    I have attached an amortization workbook to play with.

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    Based on 26 payments a year and actually paying half the monthly will give almost $20 every other week in money from the principal which will knock off almost 2.5 months in payments.

    Steve

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    Thanks, I didn't know that. I'm not a Finance person either <img src=/S/grin.gif border=0 alt=grin width=15 height=15> so I'll leave this thread to the experts.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    I'm not sure where you got that 2.5 months from, but making an extra payment every year for five years should knock off five months plus the compounded interest savings. It should be considerably more than 5 months, not 2.5.
    Legare Coleman

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,451
    Thanks
    29
    Thanked 61 Times in 57 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    What's wrong with my logic in this calculation on the bi-weekly?

    ROUND(PMT(int/26,term*26,-amt),2)

    Using this, with $25000 amt, 8.25% int, and a 5 year term, I get the bi-weekly payment to be $234.99.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> OOOPs. I forgot to add more payments. I still only listed 120 (5*24) instead of the 130 (5*26). So I missed 10 of the "non-payments"

    If I got the requisite payments, I get 117 full biweekly of half the monthly and 1 partial. Which means you will be done in a little over 4.5 years instead of 5 years.

    Steve

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: mo'ly vs bi-weekly pmts (2002)

    That calculation is correct. But if you make payments at this schedule, it will require the full 5 years to pay it off since you do not pay any "extra" against the principle.

    I thought your goal was paying off to be shorter than the 5 years?
    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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