Results 1 to 15 of 18
Thread: mo'ly vs biweekly pmts (2002)

20050205, 22:20 #1
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,612
 Thanks
 45
 Thanked 79 Times in 74 Posts
mo'ly vs biweekly 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 biweekly payments, etc.).

20050205, 22:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: mo'ly vs biweekly pmts (2002)
The number of terms obviously doubles for twicemonthly payments. Dividing the interest rate by two is a good approximation; if you want to be more accurate, use =(1+B3)^0.51 where B3 is the cell containing the monthly interest rate.

20050205, 22:36 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,612
 Thanks
 45
 Thanked 79 Times in 74 Posts
Re: mo'ly vs biweekly 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 biweekly and the payment every two weeks is half the payment from the traditional (above).

20050205, 22:43 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: mo'ly vs biweekly 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 biweekly payment is half that of the monthly payment? Is that a given?

20050205, 22:47 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,612
 Thanks
 45
 Thanked 79 Times in 74 Posts
Re: mo'ly vs biweekly 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 biweekly payment is have the traditional (i.e., 254.95), I want to know the total interest paid on the biweekly, the principal balance each year it is outstanding, and the length of the loan payments under the biweekly plan. It's something slightly less than the 5 years.

20050205, 23:03 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: mo'ly vs biweekly 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.

20050206, 00:39 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: mo'ly vs biweekly pmts (2002)
<P ID="edit" class=small>(Edited by sdckapr on 05Feb05 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).

20050206, 01:03 #8
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: mo'ly vs biweekly 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

20050206, 01:04 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: mo'ly vs biweekly pmts (2002)
Here is an amortization schedule based on your info. Given a halfmonthly 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

20050206, 01:08 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: mo'ly vs biweekly 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

20050206, 01:17 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: mo'ly vs biweekly 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.

20050206, 02:22 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: mo'ly vs biweekly 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

20050206, 04:01 #13
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,612
 Thanks
 45
 Thanked 79 Times in 74 Posts
Re: mo'ly vs biweekly pmts (2002)
What's wrong with my logic in this calculation on the biweekly?
ROUND(PMT(int/26,term*26,amt),2)
Using this, with $25000 amt, 8.25% int, and a 5 year term, I get the biweekly payment to be $234.99.

20050206, 12:08 #14
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: mo'ly vs biweekly 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 "nonpayments"
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

20050206, 12:10 #15
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: mo'ly vs biweekly 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