0. Quick guide
This is a summary of the simple and compound interest materials used in the xlf presentation series. It incorporates algebraic formulae, examples, and Excel equations and functions where available. Excel FUNCTION syntax is provided, with example specific parameter settings
in the details column.
Symbols
- \(\cdot \; (dot)\): multiplication
- \(FV\): future value
- \(I\): accrued interest
- \(n\): number of periods
- \(pa\): per annum
- \(PV\): present value
- \(r\): interest rate
1. Simple interest
- Amount of accumulated or accrued interest: \(I=PV \cdot r \cdot n\)
- Future value: \(FV=PV(1+r \cdot n)\)
- Present value: \(PV=FV(1 + r \cdot n)^{-1}= FV / (1 + r \cdot n)\)
Example: Bank deposit of $100,000 at 3% pa for 10 years. Interest paid to a non interest bearing (NIB) account.
1.1 Simple interest, accrued: = 100,000 x 0.03 x 10 = 30,000
Excel formula: =100000*3%*10
returns 30000
Excel function: =ACCRINT(DATE(2014,1,1),DATE(2015,1,1),DATE(2024,1,1),3%,100000,1,3,TRUE)
returns 30000. See figures 1 and 2 for the fx view


1.2 Future value (principal plus interest): = 100,000 x (1 + 0.03 x 10) = 130,000
Excel formula: =100000*(1+3%*10)
returns 130000
Excel formula: =100000*(1+0.03*10)
returns 130000
1.3 Present value: = 130,000 / (1 + 0.03 x 10) = 100,000
Excel formula: =130000/(1+3%*10)
returns 100000
Excel formula: =130000*(1+3%*10)^-1
returns 100000
The ACCRINT function returns the accrued interest for a security that pays periodic interest. Syntax: ACCRINT(issue,first_interest,settlement,rate,par,frequency,[basis],[calc_method]) | ||
---|---|---|
argument / parameter | details | |
issue | required | the security’s issue date as a date reference. In this case the deposit is assumed to be made on 1 January 2014. We use the Excel DATE function (Syntax DATE(YEAR,MONTH,DAY)) to return the sequential serial number for that particular, namely 41640. Based on the Excel for Windows 1900 date system . |
first_interest | required | the security’s first interest date as a date reference. One year later, on 1 January 2015; serial number 42005 . |
settlement | required | the security’s settlement date as a date reference. The security settlement date is the date after the issue date when the security is traded to the buyer or the investment matures. In this case the deposit matures 10 years after deposit, 1 January 2024; serial number 45292 . |
rate | required | the security’s annual coupon interest rate. Set at 3% pa . |
par | required | the security’s face value. If you omit par, ACCRINT uses $1,000. Note: par is listed as
|