Formula to calculate continuous compounding interest rate in Excel

Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Home

Stock Options

Continuous Compounding Interest Rate Conversion

Converts any periodically compounded (monthly, semiannually, etc.) interest rate into the continuously compounded rate. (The Black-Scholes formula uses the continuously compounded interest rate as an input.)

Syntax (Values in boldface are required; others are optional)

ContCompRate(PeriodicRate, PeriodsPerYear)

PeriodicRate is the nominal interest rate you wish to convert
PeriodsPerYear is the number of annual compounding periods associated with the PeriodicRate.

Examples

To convert a semiannual coupon bond rate of 5% to a continuous compounding rate, enter "=ContCompRate(5%, 2)".
Dividend yield for a security which is priced at $100 and pays quarterly dividends of $1.00 is calculated by the formula "=ContCompRate(4%, 4)".

Code

To install this function, copy the entire contents of the window below into your power4XL code module in Excel's Visual Basic editor.

For detailed instructions, see the "Individual Function Setup" page.

To copy the contents to your clipboard, place your cursor inside the window below, then press "CTRL+C".

NOTE: By copying the code below, you are agreeing to the power4XL license terms. You agree to use the code only on your personal and/or business computer(s) for your own personal use. You agree not to distribute, publish, alter, or edit the code in any way. You may freely refer others to this website if you wish to make the code available to them.

Public Function ContCompRate(PeriodicRate, PeriodsPerYear) 'Copyright 2006 by power4XL. 'This software is licensed to the individual user who copied it from the power4XL.com website, 'for use only by that individiual on his/her personal and/or business computer. 'The software may not be distributed, published, emailed, posted on another website, edited, 'altered, or used in any other way without the express written permission of power4XL.com. 'The software is provided "as is" with no warranty of suitability for any purpose. 'No warranty is made with respect to the software's ability to provide correct and accurate results. 'The user of this code agrees to hold power4XL and its principals harmless against any 'loss or claim arising from the use of this software. 'The user agrees not to delete these provisions from the code. 'For further information, visit www.power4XL.com. If PeriodsPerYear > 10000 Then ContCompRate = CVErr(2015) Exit Function ElseIf PeriodsPerYear <= 0 Then ContCompRate = CVErr(2015) Exit Function ElseIf Not IsNumeric(PeriodicRate) Then ContCompRate = CVErr(2015) Exit Function ElseIf PeriodicRate < 0 Then ContCompRate = CVErr(2015) Exit Function End If ContCompRate = Application.WorksheetFunction.Ln((1 + (PeriodicRate / PeriodsPerYear)) ^ PeriodsPerYear) End Function

Copyright notice: This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley. License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files. Users may not redistribute code in any way. Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden. "Microsoft" is a registered trademark of Microsoft Corporation. Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.