Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you invest in a security that pays interest, you can receive your interest payments in one of two ways: either periodically or all at once when the security matures. Excel has a function for each of these cases. The ACCRINT function lets you find accrued Interest that's paid periodically, and of the ACCRINTM function calculates interest that's paid in a lump sum when the security matures. You can remember the difference between the two functions by remembering that the M at the end stands for at maturity.
In this workbook, I have two worksheets, one for periodic interest calculation and the other for calculating interest at maturity. On the Periodic worksheet, I have all of the arguments I need to calculate accrued interest that's paid periodically. So for example, I have the Issue Date, which is the date the security is first issued, not the date that you bought it. Then we have the First Interest Date and that is the date that it first pays interest to its investors. Settlement Date, that's the date you bought it, and then the Annual Rate, which is the annual rate that the investment pays.
Par Value is the face value of the investment and that's listed in the prospectus. Frequency is the number of payments per month. That can either be 1, 2, or 4, so annual, semiannual, or quarterly. Then we have Basis and a basis is the number of days in a month and in a year. So for example, some accounting systems use 30-day months and assumed a 360-day year. Others use actual days and so on.
Basically, you're going to have to know which one you're using based on your jurisdiction's accounting rules. And then finally, we have the Calculation method. The calculation method can either be true or false. If it is true, then Excel starts calculating interest from the issue date to the settlement date. If you set it to false, then it calculates interest from the settlement date to the first interest date. In most cases the value will be true and again that's something you'll just have to know. So let's go ahead and create our first formula.
So we're in cell C12, type =ACCRINT for accrued interest, and then a left parenthesis, and then the issue date is in C3, first interest date is in C4, type a comma, then we have the settlement date in C5, comma, annual rate is in cell C6, comma, par value C7, comma, then we have our frequency which is quarterly, that's in cell C8, comma, the basis we'll use the standard US which is 30-day months and 360 day a year, that's in cell C9, and then our calculation method, we're going to use the default which is true. That is in cell C10. Type a right parenthesis.
Take a quick look to make sure I haven't mistyped any numbers. Everything appears to be correct and press Enter. When we do, we see that the accrued interest paid quarterly will be $66.67. Now let's switch over to the At Maturity worksheet by clicking its sheet tab and you'll see that the calculation is a bit simpler. That's because we only need to know the interest that accumulates throughout the life of the investment. So here we have the Issue Date, the Settlement Date, the Annual Rate, the Par Value, and the Basis.
So in cell C10, type =ACCRINTM because we're calculating interest at maturity. Then a left parenthesis and we have the issue date which is C3, settlement date C4, comma, and then the annual rate is in C5, comma,par value C6, comma, and the basis is in cell C7. So I type a right parenthesis. Quick look, everything is right, and I'm going to press the Tab key so I don't scroll down.
And when we do, we see that the interest at maturity is $800 and that makes sense. There is a $10,000 investment at an annual rate of 4% over two years. Depending upon when you receive interest on an investment, whether it's periodically or when the investment matures, you can use these functions in Excel to calculate how much you're due and when.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.