How to Pricing IRS floating leg in quantlibxl

Question

I'm beginner in quantlibxl and I'm doing Marco Marchioro's lecture 2 IRS-Floating-leg Sheet's exercise.

Here is my question:
When I use the given example, the both npvs(qunatlib calculation and excel calculation ) work. But if I change the pricing date, after the effective date, the qllegNPV gives the error message(#NUM). How do I fix it?

The following is term sheet schedule:

Pricing date: 10/30/2015
Effective date: 07/23/2015
Terminate date: 7/23/2022
Tenor: 3M

I try my best to explain my function in excel
To build the cash flow schedule(irs-float-schedule#0000):

=qlSchedule(irs-float-schedule,7/23/2015,7/23/2022,3M,TARGET,Modified Following,Modified Following,Forwad,TRUE,,,,)  

Forecast curve(swp-forecast#0000)

=qlFlatForward(swp-forecast,0,TARGET,0.29%,Actual/360,Continous,SemiAnnual,,)  

Libor Index(euribor#0000)

=qlEuribor(euribor,3M,swp-forecast#0000,,)

Discount curve(swp-discount#0000):

=qlFlatForward(swp-discount,0,TARGET,4%,Actual/360,Continous,SemiAnnual,,)  

IRS Floating leg(irs-float-leg#0000):

=qlIborLeg(irs-float-leg,Following,5000000,irs-float-schedule#0000,,,Actual/360,0,1,euribor,0,0,,)

legNPV:

=qlLegNPV(irs-float-leg#0000,swp-discount#0000)

Thanks for your help.

Answer 1

You're not reporting the actual error message (which you can find by calling the ohRangeRetrieveError function and passing it the address of the cell containing the error), but I'm guessing it would tell you about a missing fixing.

As long as the pricing date is before the effective date, all the fixings of the floating-rate coupons can be forecast off the interest-rate curve. However, if the pricing date is later than the effective date, some fixings turn out to be in the past. In that case, you can no longer forecast them (the curve only covers the future) so you have to provide them. You can do so by calling the qlIndexAddFixings function; after you do, the calculation will pick them up. To find out what particular fixing you need, you can check the error message as mentioned above.

Answer 2

ohRangeRetrieveError( ) is good when you know which cells are the cause of error. I also use ohLogSetFile( logfile, loglevel), in the first column of the first tab, to switch on log to file for an entire spreadsheet. A log level of 5 produces the most verbose logging.