Weekly Loan Repayment Schedule Excel: Formula and Example

A weekly loan repayment schedule in Excel lists each payment date, payment amount, interest, principal, and remaining balance. This calculator estimates a level weekly payment from the loan amount, APR, and term in years, then calculates total interest; you can use the same figures to build a row-by-row spreadsheet schedule.

Formula

The calculator uses a standard amortizing-payment formula with weekly periods. It treats a year as 52 payment periods and converts the annual percentage rate to a weekly rate by dividing by 5,200.

• p is the amount borrowed, in dollars.
• apr is the annual percentage rate, entered as a percentage.
• r is the weekly rate, equal to apr ÷ 5,200.
• n is the number of weekly payments, equal to the term in years × 52.
• pmt is the estimated weekly payment, in dollars.

The payment formula is pmt = p × r ÷ [1 − (1 + r)⁻ⁿ]. Total interest is estimated as pmt × n − p. Because the displayed payment is rounded to cents, total interest uses that rounded weekly payment; an actual lender’s final payment may be adjusted to clear a small remaining balance.

Worked Example

Using the calculator’s defaults—$20,000 principal, 8% APR, and five years—the weekly rate is 0.08 ÷ 52 = 0.00153846, and the schedule has 5 × 52 = 260 payments.

1. Calculate the weekly payment: $20,000 × 0.00153846 ÷ [1 − (1.00153846)⁻²⁶⁰].
2. The result rounds to $95.93 per week.
3. Multiply the rounded payment by the number of payments: $95.93 × 260 = $24,941.80.
4. Subtract the original principal: $24,941.80 − $20,000 = $4,941.80 total estimated interest.

Assumptions

• The APR is fixed throughout the schedule.
• Payments are equal and made at the end of each weekly period.
• Interest accrues weekly at APR ÷ 52, equivalent in this model to monthly-style nominal APR conversion using 52 periods per year.
• The estimate excludes fees, taxes, insurance, and other charges because they are not inputs.

Build the Schedule in Excel

A spreadsheet schedule expands the payment estimate into one line per installment. Put the starting balance in a cell, then calculate the interest portion for each week, subtract that from the payment to get principal paid, and subtract principal from the prior balance. A practical column layout is:

• Payment number or date
• Beginning balance
• Payment
• Interest
• Principal
• Ending balance

For a period-based model, the core calculations are:

• Interest: beginning balance × weekly rate.
• Principal: payment − interest.
• Ending balance: beginning balance − principal.
• Next beginning balance: prior row’s ending balance.

For example, if the first row’s beginning balance is in B2, weekly rate in a fixed cell such as $B$1, and payment in $B$2, Excel formulas could be =B2*$B$1 for interest, =$B$2-C2 for principal, and =B2-D2 for the ending balance, assuming interest is in C and principal in D. The next row’s beginning balance should reference the preceding ending balance. Use absolute references for inputs so they do not shift when you copy formulas down.

To generate dates, enter the first due date and add seven days in each following row, such as =A2+7. Calendar due dates and lender processing conventions may not follow a simple every-seven-days pattern, so use the actual payment dates and accrual method from the loan documents when reproducing a lender statement.

How to Read and Check the Results

In a fully amortizing schedule, the interest portion generally falls as the balance declines, while the principal portion rises when the payment remains level. The first payment’s interest is calculated on the largest balance; later interest calculations use the successively reduced balance. The payment itself does not normally change in this simplified fixed-rate model.

Check that each row’s payment equals its interest plus principal, and that the ending balance equals the beginning balance minus principal. The final balance should be zero or a small rounding difference. If it is not, review the rate conversion, number of periods, cell references, and whether the spreadsheet carries unrounded values or rounds each period. Lenders can use daily accrual, different payment timing, or a final adjusted installment, so a spreadsheet may not match a statement exactly.

Common Mistakes and Limits

• Using the wrong period rate: APR entered as 8 means 8%, not 0.08 in a formula that already divides by 100. For weekly periods, this model divides by 52; dividing by 12 would produce a monthly calculation.
• Mixing periods: a five-year weekly schedule has 260 payments under the 52-weeks-per-year convention, not 60 monthly payments.
• Rounding too early: rounding interest and principal at each row can create a different ending balance from keeping full precision and displaying cents. Match the method you intend to reproduce.
• Confusing APR with the accrual method: the calculator uses a nominal APR divided into equal weekly periods. A contract may calculate interest using actual days, so the lender’s figures can differ.

This is an estimate for a fixed-rate, level-payment loan. It does not model variable rates, skipped or late payments, extra payments, fees, payment-date changes, or lender-specific daily interest calculations. A weekly schedule also does not mean that every loan contract permits weekly installments; confirm the actual payment frequency and terms with the lender.

Frequently Asked Questions

How do I calculate a weekly loan payment in Excel?
Use the weekly rate APR ÷ 52 and the number of payments years × 52 in the amortizing-payment formula. The calculator above provides the estimated payment; spreadsheet rows can then split each installment into interest and principal.

Is a weekly payment the same as a monthly payment divided by four?
No. A year has 52 weekly periods but only 12 months, and interest accrues over different periods. Dividing a monthly payment by four ignores those differences and can produce a misleading schedule.

Why does my Excel balance not reach exactly zero?
Small residual balances commonly result from rounding, using a different weekly-rate convention, or using a payment that differs by a cent. Check the final installment and the lender’s accrual method rather than forcing an unexplained adjustment into every row.

Does the weekly schedule include fees?
No. This calculation uses only principal, APR, and term. Add fees only if you have a clear, applicable loan figure and know how the lender treats it; otherwise, the result will not represent the complete cost.

Can I use this for extra weekly payments?
The fixed-payment calculator does not include extra payments. In a spreadsheet, record each extra amount separately and subtract it from principal according to the contract’s payment-allocation rules; then recompute the remaining schedule as needed.

The result is an estimate, and the lender’s disclosure gives the exact figures.

Related

• Personal Loan Repayment Schedule Calculator
• PayPal Business Loan Repayment Schedule Calculator
• Online Personal Loan Repayment Schedule Calculator

in p | Loan amount | $ | 20000 | 1 | 100000000
in apr | Interest rate (APR) | % | 8 | 0.01 | 100
in yrs | Loan term | years | 5 | 0.0833 | 50
out pmt | Weekly payment | $ | p * (apr / 5200) / (1 - (1 + apr / 5200) ^ -(yrs * 52)) | 2
out int | Total interest | $ | pmt * yrs * 52 - p | 2