Question

Hello there. I got question regarding dates. my topic now is BOND, getting the coupon,fv, purchase price,and number of periods (n), when there is a given problem like "maturing on December 1, 2020 then determine the purchase on January 1, 2015 to get the number of periods (n). If I use excel = purchase on date - mature date it will give me total in days, for this date is 2161 days then ill 2161/365 = 5.920547945  then x  4 as compounding quarterly, result is 23.68219178. Now this is my problem, is my number of periods n=23 or 24? shall this kind of details must always rounding off to the next days, months, years? my answer is sometimes arriving at a wrong answer and I suspect this is one of the reasons. if there's other way how to do this perhaps in excel then please guide me. Please help. thank you.

Answer 1

Hello!

Rounding off the value of (n) to the next day, month or year will lead to an approximation error.

Also, the value of (n) is calculated as (mature date - purchase date) and not (purchase date - mature date) as mentioned in the question. The calculation of (n) as given in the question would result in a negative value for (n) which is incorrect. To correct the value of n, we use (mature date - purchase date)

To eliminate the error, do not round off the value of (n). Instead, use Excel to link the value of (n) to the exact amount as determined by the formula (n) = (mature date - purchase date). This way, there is no rounding off and the result will be more accurate.

B3 = B2 - B1

B4 = B3 / 365

B5 = B4 * 4