Google Sheets Accrued Interest Amortization Calculator

This Google Sheets accrued-interest amortization calculator estimates interest earned between payment dates and applies it to a loan balance using a daily-rate method. Enter the principal, annual rate, days in the accrual period, and payment; it returns accrued interest and the balance after interest and payment.

Formula

The calculator uses simple daily accrual on the opening principal, then subtracts the payment:

• Accrued interest = principal × annual rate ÷ 100 × accrual days ÷ 365.
• Ending balance = principal + accrued interest − payment.

Principal (p) and payment (pay) are in dollars. Annual interest rate (apr) is entered as a percentage, such as 8 for 8%, not 0.08. Accrual days (days) is the number of days for which interest accumulates. The calculation assumes a 365-day denominator, including in leap years. The balance formula includes a validation term so a payment greater than the balance after interest is rejected rather than producing a negative balance.

Worked Example

Using the default values—$20,000 principal, 8% annual interest, 30 days, and a $500 payment:

1. Convert 8% to a decimal: 8 ÷ 100 = 0.08.
2. Calculate the daily simple rate: 0.08 ÷ 365 ≈ 0.000219178.
3. Multiply by principal and days: $20,000 × 0.08 × 30 ÷ 365 = $131.5068, which rounds to $131.51.
4. Add interest and subtract the payment: $20,000 + $131.51 − $500 = $19,631.51.

Assumptions

• The annual rate is fixed for the selected accrual period.
• The calculation accrues simple interest daily against the opening principal; it does not compound interest day by day.
• The payment is applied at the end of the period, after the period’s interest is added.
• A 365-day year is used. Actual contract conventions may instead use a 360-day year or actual calendar days.
• Fees, taxes, late charges, payment allocation rules, and changes in principal during the period are not included.

What Accrued Interest Means

Accrued interest is interest that has accumulated over time but may not yet have been paid or posted as a separate charge. On a loan, the outstanding amount can therefore increase between payment dates, even if the borrower has not missed a payment. At a payment date, the lender’s rules determine how the payment is allocated—for example, how much covers interest and how much reduces principal.

Amortization is the process of tracking principal and interest over multiple periods. This calculator handles one period at a time. To model several periods in a Google Sheets table, carry each ending balance forward as the next period’s opening balance, enter the days and payment for that period, and calculate interest again. The method here is not a complete loan schedule: it does not infer payment dates, vary the rate, or account for contract-specific allocation rules.

How to Set It Up in Google Sheets

For a basic spreadsheet, enter the opening principal in A2, annual rate as a percentage in B2, accrual days in C2, and payment in D2. Use these formulas in separate result cells:

• Accrued interest: =ROUND(A2*B2/100*C2/365,2)
• Ending balance: =ROUND(A2+E2-D2,2), assuming the interest result is in E2.

In a multi-period schedule, make each row represent one accrual period. Link the next row’s opening principal to the previous row’s ending balance. Keep unrounded intermediate values if you are reproducing a documented calculation method; round displayed amounts to cents. Some loan contracts round interest at particular stages, so the exact schedule may differ from a spreadsheet that rounds each period.

Choosing the Day-Count Method

The formula above uses actual days divided by 365. That is a common mathematical convention for an estimate, not a claim about the terms of any particular loan. The agreement may specify another method, such as actual days divided by 360 or a 30/360 convention. Those choices can change the interest amount, especially across longer periods.

For example, with $20,000 at 8% for 30 days, the 365-day calculation gives $131.51. Using a 360-day denominator instead gives $133.33 before rounding. To reproduce a statement or contractual schedule, use the day-count convention and dates specified by the lender, and confirm whether the rate is nominal, effective, or otherwise defined.

Common Errors and Limits

• Entering 8% as 0.08: Because the formula divides the entered percentage by 100, enter 8 for 8%. Entering 0.08 would calculate 0.08%.
• Using months as days: The days field expects a day count. Count the actual dates in the period if the applicable method calls for actual days.
• Assuming every loan compounds daily: This calculator uses simple daily accrual on the starting balance. Contractual calculations can use different compounding and posting rules.
• Subtracting payment before interest: The example assumes payment occurs at the end of the period. A payment made earlier can alter the balance on which subsequent interest accrues.
• Expecting a full amortization schedule: One-period results must be repeated with updated balances to build a schedule. Variable rates, irregular payments, extra charges, and payment allocation are not modelled.

Use the calculator to understand the arithmetic or create an illustrative spreadsheet, not as a substitute for the account’s official balance. Check the loan documents or lender statement for the applicable rate, day-count convention, dates, and payment treatment.

Frequently Asked Questions

How do I calculate accrued interest in Google Sheets?
For a 365-day simple-interest estimate, use =ROUND(principal*rate/100*days/365,2). Enter the annual rate as a percentage number, such as 8 for 8%.

Does accrued interest increase the loan balance?
It can, depending on how and when interest is posted and how payments are applied. This calculator adds one period’s interest to principal and then subtracts the payment, but actual account rules may differ.

Should I use 360 or 365 days?
Use the day-count basis stated in the relevant loan terms when reproducing an official calculation. This calculator defaults to 365 and does not decide which convention applies.

Is accrued interest the same as amortization?
No. Accrued interest is the interest accumulated during a period. Amortization describes how payments and interest affect principal over a sequence of periods.

Can Google Sheets calculate a full loan amortization schedule?
Yes. A sheet can repeat the period calculation in rows, carrying each ending balance into the next row. For a reliable match to a real account, it also needs the actual payment dates, rate changes, rounding rules, and payment-allocation method.

This result is an estimate for education; the lender’s disclosure gives the exact figures.

Related

• Accrued Interest Excel Formula and Calculator
• Accrued Interest Entry in TallyPrime: Journal Steps
• Accrued Interest Example and Calculator

in p | Principal balance | $ | 20000 | 0 | 100000000
in apr | Annual interest rate | % | 8 | 0.01 | 100
in days | Accrual period | days | 30 | 0 | 366
in pay | Payment at period end | $ | 500 | 0 | 100000000
out int | Accrued interest | $ | p * apr / 100 * days / 365 | 2
out bal | Balance after payment | $ | p + int - pay + 0 * sqrt(p + int - pay) | 2