Calendar functions in Excel: NxEDATE and NxAdjust

Scene 1:

Welcome to the date and holiday calculation tutorial. In this video we'll demonstrate the use of NumXL's and NxEDATE and NxAdjust functions to compute a value date for a US Treasury bond. For a sample, we'll be using a hypothetical 20-year bond issued on January 15th, 2010.

First let's compute the coupon date. A principle at T-bond pays a coupon every six months. Type NxEDATE in the formula bar and click on the FX button.

Scene 2:

In the function dialog box enter six months for the period and specify the first coupon start date, which is the issue date.

Scene 3:

The function returns a date that falls exactly six months after the issue date. Now copy the formula to the cell below then change the start date of the function from issue date to previous coupon date. Now click OK.

Scene 4:

Copy the formula to all the rows below it.

Next let's compute the payment value date which shows when the actual payment takes place. Type NxAdjust function in the equation bar, then click on the FX button to invoke the function wizard.

Scene 5:

For the date, select the coupon date. For the convention field let's choose following which tells the function to choose thenext working day for the date. For holidays type USA to designate the US federal holidays or leave the field blank. You may also leave the weekends field blank or type 1 for the Western calendar.

Scene 6:

NxAdjust only affects the date that falls on a weekend or holiday.

Now copy the formula to the rows below it. In the second row the coupon date falls on a Saturday but the function returns Tuesday because Monday happens to be Martin Luther King Day, a US holiday.

Scene 7:

That is it for now, thank you for watching!