Bi-weekly mortage payments

[Alan]

I have a amortization schedule based on months. I would like one that I
could use bi-weekly payment schedule 26 half month payments over the year.

 

[Gary''s Student]

http://office.microsoft.com/en-us/templates/TC010566181033.aspx?pid=CT101444811033

 

[joeu2004]

I have a amortization schedule based on months.  I
would like one that I could use bi-weekly payment
schedule 26 half month payments over the year.

That depends on the payment option for your loan agreement. There are
two common approaches:

(1) Your loan is reduced and interest is computed with each biweekly
payment.

(2) You make 26 payments (each equal to half a monthly payment), but
your loan is reduced and interest is computed on a monthly basis, with
your 12th-month payment equal to two monthly payments.

Option #1 (biweekly interest) is easier to set up. For example (bare
bones):

B2: biweekly payment amount [1]
C2: annual interest rate
E2: loan amount
A3: first payment due date
B3: payment: =min(roundup(E2+C3,2),$B$2)
C3: interest: =E2*$C$2/26
E3: principal: =B3-C3
F3: remaining balance: =E2-(B3-C3)
A4: next payment due date: =A3+14

Copy B3:E3 to B4:E4. Then copy down A4:E4 until the last payment date
or until the remaining balance is zero or negative. (Should be the
same for a fully-amortized loan with no balloon payment.)

[1] If you are computing the biweekly payment, use:
=roundup(pmt(C2/26, 26*yrs, -E2), 2)

Option #2 (monthly interest) is complicated by the fact that the
payment schedule and loan reduction schedule are different. For
example (bare bones):

B2: biweekly payment amount: =D2/2
E2: monthly payment amount [2]
F2: annual interest rate
H2: loan amount
A3: first biweekly payment due date
B3: biweekly payment: =$B$2
(column C is blank)
D14: 12th monthly payment date [3]: =A3+14*25
E14: monthly payment: =min(2*$E$2,roundup(H13+F14,2))
F14: interest: =H13*$F$2/12
G14: principal: =E14-F14
H14: remaining balance: =H13-(E14-F14)
D13: previous monthly payment date [4]:
=min(date(year(D14),month(D14)-1,day($D$14)),eomonth(D14,-1))
E13: =min($E$2,roundup(H13+F14,2))
D15: next monthly payment date [5]:
=min(date(year(D14),month(D14)+1,day($D$14)),eomonth(D14,1))

[2] If you are computing the monthly payment, use (ensuring that it is
divisible by 2):
=ceiling(pmt(F2/12,12*yrs,-H2),0.02)

[3] You might need to reformat the D14 as Date.

[4] If you do not have EOMONTH, you could write:
date(year(D14),month(D14),0)

[5] If you do not have EOMONTH, you could write:
date(year(D14),month(D14)+2,0)

Complete the monthly loan reduction schedule first.

Copy F14:H14 to F13:H13, and copy D13:H13 up through D3:H3. Copy
E3:H14 (12 rows without the dates) down until the remaining balance is
zero or negative. Then select D15 and double-click the lower-right
handle to propagate D15 through the last row. Suppose the last row is
290. Then:

E291: sum of monthly payments: =round(sum(E3:E290),2)

Now finish the biweekly payment schedule.

A4: next biweekly payment date: =A3+14
B4: biweekly payment:
=min($B$2,$E$291-round(sum($B$3:B3),2))

Copy A4:B4 down until the next biweekly payment would be zero or
negative. That should be about 2.2 times the number of rows in the
loan reduction schedule.

Whew! Well, you asked .

 

[joeu2004]

http://office.microsoft.com/en-us/templates/TC010566181033.aspx?pid=C....

Caveat: That might not apply to the OP's loan. In some biweekly
payment plans, the loan reduction and interest computation is on a
monthly basis.