If you’ve ever borrowed money, you might be familiar with the concept of interest. It’s the fee and expense that we pay the lender for the privilege of borrowing money.
As you move toward making a payment, you have what’s called accrued interest. That means interest that you technically owe, but haven’t paid out yet. Let’s learn how to calculate this amount, called accrued interest in Excel.
What is Accrued Interest?
When you make a payment on a loan, it tends to include two parts: principal and interest. Simply put, principal counts toward the original loan amount and interest is the financing expense you pay the lender.
Every day, a loan accrues interest. That means that interest is building up until the day it’s paid out. On loans that are paid infrequently (like every 6 months or every year) it helps to know how much interest has accrued. That way, you can plan to pay for it. Calculating accrued interest helps you keep an eye on the amount that’s coming due if you don’t have the loan schedule.
Accrued Interest in Excel
It’s easy to calculate accrued interest. We’re going to work with the function labeled =ACCRINT().
This function includes six inputs, so it works best if you type those inputs into the spreadsheet first. Let’s set up a table and make sure that each of these factors are included:
- Issue – when did the loan or interest-paying account begin? Set a date.
- First interest – what date will the instrument pay or charge interest for the first time? Set a date for this one, too.
- Settlement – when does the loan or account finish? Set a date here as well.
- Rate – what is the interest rate chaged or paid? Set this as a percent.
- Par – what is the face value of the loan or account? Set a dollar amount.
- Frequency – how often is the interest calculated? Set 1 for annual, 2 for semiannual (twice per year) or 4 for quarterly (four times per year.)
Type each of those inputs into separate cells as shown below.
Now, create an =ACCRINT() formula, and set each cell above to one of hte inputs, each separated by comma. The format goes in the same order that we laid out our data in.
The final formula is:
=ACCRINT(C2,C3,C4,C5,C6,C7)
Meaning that the format is:
=ACCRINT(ISSUE, FIRST INTEREST, SETTLEMENT, RATE, PAR, FREQUENCY)
That’s all there is to it. You’ve calculated how much interest you’ve accrued with the help of this easy formula.
Stocks in Excel