Compound interest is used in mortgages, loans, investments, and anything else that pays interest multiple times. It basically means that interest is paid on both the principal AND the past interest. Microsoft Excel Compound Interest can run these computations for you.
When a percentage rate compounds a set number of times per year (for instance monthly or quarterly), the algebraic compound interest formula is fairly straight forward.
Compound interest in Excel
If algebra isn’t your cup of tea, you can use our template here, use visual basic, or use one of the Excel financial functions.
ACCRINT
The ACCRINT functions returns interest tha pays periodic interest. Use the formula by plugging in these numbers or cell references.
=ACCRINT(issue, first_interest, settlement, rate, par, frequency, [basis], [calc_methoed])
- Issue – The security’s issue date.
- First_interest- The security’s first interest date
- Settlement- The security’s settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer/
- Rate- The security’s annual coupon rate
- Par- The security’s par value.
- Frequency- The number of coupon payments per year
- Basis – The type of day count basis to use
ACCRINTM
The ACCRINTM function returns the accrued interest for a security that pays interest at maturity. Use the definitions of each reference from above.
=ACCRINTM(issue, settlement, rate, par, [basis])
Excel financial formulas
Excel also offers a number of financial specific formulas. For instructions in using any of these formulas, click on the help menu (in Excel 2003) or the help button in the top right hand corner (for Excel 2007, 2010, and 2013) and search “Financial Formulas.” There are some examples below.
AMORDEGRC – Returns the depreciation for each accounting period by using a depreciation coefficient
AMORLINC – Returns the depreciation for each accounting period
COUPDAYSNC – Returns the number of days from the settlement date to the next coupon date
