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
.
