Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Bi-weekly mortage payments
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Bi-weekly mortage payments
http://office.microsoft.com/en-us/te...CT101444811033
-- Gary''s Student - gsnu200828 "Alan" wrote: 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Bi-weekly mortage payments
On Jan 23, 1:01*pm, Alan wrote:
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)),eomon th(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 :-). |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Bi-weekly mortage payments
On Jan 24, 2:57*am, Gary''s Student
wrote: http://office.microsoft.com/en-us/te...033.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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart displaying weekly data group in months without weekly labels | Charts and Charting in Excel | |||
Mortage Calculations | Excel Worksheet Functions | |||
Bi weekly payments per month | Excel Worksheet Functions | |||
Optimizing mortage payback | Excel Worksheet Functions | |||
How do I forecast future payments by analyzing past payments? | Excel Worksheet Functions |