Fixed Income Bond Duration – Monthly Coupon Payment Frequency - EXCEL
About the Course
EXCEL’s built-in functionality allows for calculations of price and duration for non-callable fixed income bonds with payment frequencies of annual, semi-annual & quarterly. However, it does not cater for bonds with monthly frequency.
This course includes one EXCEL file that calculates the price & Macaulay, modified and effective durations for non-callable bonds having monthly coupon payment frequencies.
The worksheet is built from first principles and assumes an Actual/365 day count convention. It includes separate tabs for 10-, 20-, 30-, 40- and 50-year monthly coupon payment bonds, where prices and durations are derived.
The “Summary – Inputs & Results” tab allows the user to change formula variables, such as settlement date, issue date, coupon rate, yield rate, face value & interest rate shock for effective duration calculation, and view the price & durations results from the individual tabs.
The “Freq 1-2-4 – check1”, “5y - semiannual - check2” & “10y - semiannual - check3” tabs are quality assurance tabs, that allows the user to compare the results obtained from first principles and those generated using EXCEL’s price functionality for annual, semi-annual & quarterly coupon payment bonds of varying tenors and specifically for 5 year semiannual frequency & 10 year semiannual frequency bonds. These serve as checks for the first principles methodology applied in the other tabs to calculate values for monthly coupon payment bonds.
After taking this course you will be able to:
- Build a pricing sheet for a fixed income bond with monthly frequency
- Calculate the price of a fixed income bond with monthly frequency from first principles
- Calculate the Macaulay Duration of a fixed income bond with monthly frequency from first principles
- Calculate the Modified Duration of a fixed income bond with monthly frequency
- Calculate the Effective Duration of a fixed income bond with monthly frequency from first principles
Comfortable with basic mathematics and EXCEL and some familiarity with money market instruments.
The course is aimed primarily at banking professionals and individuals responsible for asset liability management, portfolio management and risk management within banks, insurance companies and mutual funds who need to review or refresh their understanding of ALM concepts and fundamentals for work, professional review, audit or personal development.