To calculate per diem interest in Excel, multiply the outstanding loan balance by the annual interest rate and divide by the number of days in the lender’s interest year. This calculator estimates one day’s interest from your balance and APR; enter the amount and rate to see the daily cost.
Formula
For a 365-day basis, per diem interest equals the outstanding balance multiplied by the annual percentage rate as a decimal, divided by 365:
Daily interest = balance × (APR ÷ 100) ÷ 365
- Balance is the principal or other amount on which interest is being calculated, in dollars.
- APR is the annual rate expressed as a percentage, such as 8 for 8%.
- 365 is the assumed number of days in the interest year for this estimate.
In Excel, if the balance is in cell A2 and the APR percentage is in B2 as the number 8, enter =A2*(B2/100)/365. If B2 is entered as a percentage-formatted value, such as 8%, use =A2*B2/365 instead. Do not divide a percentage-formatted 8% by 100 again.
Worked Example
Using the calculator’s default values, the balance is $20,000 and the APR is 8%. The calculation is:
- Convert the annual rate to a decimal: 8 ÷ 100 = 0.08.
- Calculate annual interest for this simple estimate: $20,000 × 0.08 = $1,600.
- Divide by 365: $1,600 ÷ 365 = $4.38356… per day.
- Rounded to cents, per diem interest is $4.38. A 30-day estimate using the rounded daily figure is $4.38 × 30 = $131.40.
The 30-day output is an approximation based on the displayed, rounded daily amount. A lender may calculate interest at greater precision and round only when posting a charge, so its statement may differ slightly.
Assumptions
- The balance stays unchanged throughout the days being estimated.
- The APR is treated as a fixed annual rate for this calculation.
- Interest is estimated using a 365-day year and simple daily accrual; no daily compounding is applied.
- The result excludes payments, fees, taxes, insurance, and other charges.
When Per Diem Interest Is Useful
“Per diem” means “per day.” A per diem figure expresses interest as a daily amount, making it useful when estimating the interest that accrues between two dates. For example, a payoff quote may include interest through a stated date, and a later payment can require additional daily interest. The calculator gives a basic estimate of that daily amount, not an official payoff balance.
Daily interest can also help explain why the interest portion of a loan balance changes as principal is paid down. If the rate remains fixed and interest is based on the outstanding principal, a lower balance produces a lower daily interest estimate. Actual account calculations depend on the loan’s terms and how the lender applies payments.
365-Day and 360-Day Methods
There is no single denominator that applies to every account. Some interest calculations use actual calendar days with a 365-day or 366-day year; some contracts use a 360-day convention, sometimes alongside the actual number of days in a period. The contract or lender’s disclosures determine the applicable method.
To estimate using a 360-day basis instead, use =A2*(B2/100)/360 when B2 contains a whole-number percentage such as 8. With the default balance and rate, this gives $20,000 × 0.08 ÷ 360, or about $4.44 per day. That is higher than the 365-day estimate because the same annual rate is divided across fewer days. Do not switch conventions merely to make a result match; confirm the stated method first.
Using the Result and Avoiding Errors
Per diem interest is a daily estimate, not the total interest over a loan’s life and not necessarily the exact amount that will appear on a bill. To estimate interest over several days with an unchanged balance, multiply the daily amount by the number of days. If the balance changes during that interval, calculate each period using its applicable balance and day count, then add the amounts.
- Check percentage entry: A cell containing 8% has the decimal value 0.08. A cell containing the number 8 needs division by 100 in the formula.
- Use the right balance: Interest may be based on a current principal balance, a daily balance, or another contract-defined amount. A starting loan amount may not be the current interest-bearing balance.
- Count dates carefully: The number of accrued days depends on the relevant dates and the account’s counting convention. A one-day difference changes the estimate by approximately one per diem amount.
- Keep precision until the end: Avoid rounding intermediate values in Excel. Format the result to two decimal places for display, but let Excel retain the underlying precision for further calculations.
For a more detailed estimate when the balance changes, make a table with one row per period: record the balance, annual rate, day count, and denominator, then calculate =balance*(rate/100)*days/denominator for each row. Sum the period results. This approach still cannot reproduce contract-specific adjustments unless those rules are known and included.
Frequently Asked Questions
What is the Excel formula for per diem interest?
For a whole-number APR such as 8 in B2, use =A2*(B2/100)/365, where A2 is the balance. If B2 stores 8% as a percentage, use =A2*B2/365.
Is per diem interest always divided by 365?
No. The applicable interest convention may use 365, 366, 360, or a method based on actual days and a defined year basis. Check the account agreement or lender’s calculation details.
How do I calculate per diem interest for a payoff?
Multiply the relevant balance by the annual rate and the applicable number of days, divided by the contract’s day-count basis. A formal payoff amount can also include other amounts or adjustments, so request an official figure from the lender.
Does per diem interest change when I make a payment?
If the payment reduces the balance on which interest accrues, later daily interest may be lower. The precise effect depends on when the payment is credited and the account’s calculation terms.
Why does my Excel result differ from a lender’s amount?
Possible causes include a different balance, day-count convention, date range, payment-credit timing, rate, or rounding procedure. Compare those inputs and the lender’s stated method before drawing conclusions.
This result is an estimate; the lender’s disclosure gives the exact figures.
Related
Formula used
in bal | Outstanding balance | $ | 20000 | 0 | 100000000 in apr | Interest rate (APR) | % | 8 | 0.01 | 100 out daily | Per diem interest | $ | bal * (apr / 100) / 365 | 2 out monthly | 30-day interest estimate | $ | daily * 30 | 2
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard loan formula for this calculation, fixed rate and monthly compounding unless stated, rounded as each result states. Not financial advice; no lender, rate or product is recommended. See formulas and assumptions, the disclaimer or report an error.
More in this section
- Plan 2 Repayment Amount? Calculate Your Monthly Payment243.45 £
- Plan 2 Repayment Calculator: Estimate Monthly Deductions20.29 £
- New Standard Repayment Plan: Estimate Monthly Payments$405.53
- Pay As You Earn Repayment Calculator and Guide$405.53
- Federal Loan Repayment Plan Calculator$405.53
- Loan Repayment Comparison Calculator: Compare Payments$405.53