Car loan-bi weekly payments formula

falcios

Board Regular
How do you calculate car loan using bi-weekly payments with interest of 4.49% or which formula can be used?

Thanks in advance.
 

MickG

MrExcel MVP
Try:-
RateBi_wks for 5 YrsLoanPaymentsFormula
4.49%1201000£9.23=PMT(0.0449/26,120,-1000)
<colgroup><col width="64" style="width: 48pt;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3555;"> <col width="64" style="width: 48pt;" span="2"> <col width="185" style="width: 139pt; mso-width-source: userset; mso-width-alt: 6570;"> <tbody> </tbody>
 

joeu2004

Well-known Member
How do you calculate car loan using bi-weekly payments with interest of 4.49% or which formula can be used?
Very carefully. ;)

First, a 60-month loan is about 261 weeks, to wit: (EDATE(today(),60) - today())/7. That's 130.4 "biweeks".

A 60-month loan is always about 261 weeks (rounded up), regardless of the date of the loan. However, the number of weeks might depend on the date for loans that are not 1 to 5 years.

Second, most lenders determine the biweekly payment by calculating the monthly payment, then dividing by 2, to wit:
PMT(annualRate/12, #months, -loan)/2.

But according to one mortgage lender's online blog, some lenders determine it by calculating 12 times the monthly payment, then dividing by 26, to wit: 12*PMT(annualRate/12, #months, -loan)/26.

I'm not aware of any lender that truly amortizes over 26 weeks per year, to wit: PMT(annualRate/26, #biweeks, -loan).

Finally, the monthly rate is annualRate/12 in most countries.

But in some countries, it might be (1+annualRate)^(1/12)-1 (EU) or (1+annualRate/2)^(1/6)-1 (Canada).

Even in the EU and Canada, they might use annualRate/12 for non-mortgage loans. That is true of the UK, for example. I think it is true of Canada; but I have not been to get reliable confirmation.

In conclusion, according to one US online calculator (Navy Federal Credit Union), the biweekly payment for a $25,000 loan at 7% for 60 months is $247.52, which can be calculated by:

=ROUNDUP(PMT(7%/12, 60,-25000)/2, 2)

Some lenders might simply round. Other lenders might round, round-up or round-down to the dollar or other increment.

Compare with the "true" amortized biweekly payment of $227.56, which can be calculated by:

=ROUNDUP(PMT(7%/26, (EDATE(today(),60) - today())/14, -25000), 2)
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top