simple and compound interest

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

accrint 1
Fig 1: Simple interest 1.1 screen 1 – ACCRINT function, parameters 1 to 5. Parameters 1 to 3 (Issue, First_interest, and Settlement) use a nested DATE function to convert yyyy,mm,yy to a date serial number (2014,1,1 >> 41640)
accrint1
Fig 2: Simple interest 1.1 screen 2 – ACCRINT function, parameters 4 to 8. Parameters 7 and 8 are optional as indicated by the non bold name. Basis 3 specifies the Australian Actual/365. calc_method is a Boolean logical value and is set to TRUE. This returns the total accrued interest from Issue date to Settlement date.

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

ACCRINT function
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