Automating Partial Period Bond Valuation With Excels Day Counting Functions

Abstract

An Excel model for calculating the actual price of bonds under a 30 day/month, 360 day/year day counting assumption by nesting the DAYS360 function within the PV function is developed. When programmed into an Excel spreadsheet, the model can accommodate annual and semiannual payment bonds sold on or between interest dates using six fundamental inputs, including the bond’s par value, maturity date, annual stated rate of interest, and payment frequency, the annual market rate of interest, and the settlement date. Thus, the model can be applied across the business curriculum in a variety of introductory and more advanced level courses.