Cubic curve fitting - two screencasts

[jyothi1965]

David

Greetings after a long time.

As I had mentioned, I continue to visit BT regularly and find your chalk talk most useful. You had recently screencast twp movies on the above (with respect to the Treasury yield curve)

would be really be grateful if you could also provide the editgrid sheets for both. In particular I find it very useful to teach CFA level 2 - Fixed income, as an excel based approach is hands on and appreciated by students.

Many Many thanks for an excellent website. soon enough BT will be like wikipedia, (bitipedia.com should be your website's next avatar)!!

best regards

Jyothi

 

[David Harper CFA FRM]

Hi Jyothi,

Thanks for coming back! (I like that avatar suggestion...)

Here is the EditGrid version (two tabs: spline + single cubic polynomial)

In case you'd prefer the Excel version, here is the XLS..

...because I *think* the Solver-type action (solving for best fit with OLS) requires Excel but please let me know if you find a better way. The example btw is straight out of Tuckman. Talk to you soon!

David

 

[jyothi1965]

David

As always many thanks.

My curiosity was aroused because of an additional reason: the understated power of Excel. What you have tried to do is fit a non linear (defined as when the dependent variable has a power more than 1, like t^2, t^3, etc) using the OLS method. Does that work? When you have a non linear equation, one needs to use logistic regression. So does solver actually fit the the output data from the NS model with the time series on a linear basis- I suspect something along these lines, as the degree of fit, as evidenced by a visual examination seems to be quite high!. This is because the minimizing the sum of squared residuals is the basis for all linear regression models, even if they are multivariate. (correct me if I am wrong here)

Excel has many advantages in regression at least there are five different ways of doing a linear regression:

(1) through Excel function - Slope and intercept
(2) through data analysis - menu tools
(3) creating a scatter plot and adding a trendline - this will also give the eqn
(4) generalised inverse matrix
(5) estimating the coffecients of regression by creating the formulas in excel

But till date there is no method of doing a non linear regression analysis in excel (I could be wrong again).

By the method developed by you seems to suggest that there is a way out, provided that the OLS method can be used even in non linear regression. This is because, essentially you have estimated the coeffecients of a non linear regression fit!

Also the reason why I asked for the excel was for a reason. Solver seems to work very well when the guess is somewhere near the actual values which minimises the SSR. I found it quite annoying trying to put in values (in the worksheet developed by myself) and running solver each time. It seems to be quite an iterative process and quite dependent on the value put in.

Is that the case?

Jyothi

 

[David Harper CFA FRM]

Glad you find it useful. I totally agree with you about the understated power of Excel for this!

I can't take credit: I found this application of Solver (to fit data to a function) in Bourg's Excel Scientfic & Engineering Cookbook. Then I just thought to try it for Tuckman's cubic spline and I was suprised how nicely it works.

In regard to OLS, absolutely "least squares" can be used to fit non-linear functions, and is commonly used to get a "least-square parabola" or "least square curve." Experts on this (i.e., not me) will point to flaws with OLS (not so good for outliers) and prefer other methods; e.g., weighted least squares. I've never had the practical need...but in regard to OLS, that is absolutely a common, acceptable approach for both multivariate and non-linear regression (it's just that clearly specialists will cite superior methods).

In regard to Excel, agreed and i would add one more powerful function: =LINEST() returns an array. This is my favorite because it handles multiple regression and, unlike the tools, is dynamically linked...

Re: solver, me too with sometimes annoying problems. I'd like to learn more about solver, am not expert. Did you know they have a pro version, the site looks like a good resource. I wish i had a problem that required purchase so i could play with it

David

 

[GGOPI0119]

Hi Jyothi,

Thanks for coming back! (I like that avatar suggestion...)

Here is the EditGrid version (two tabs: spline + single cubic polynomial)

In case you'd prefer the Excel version, here is the XLS..

...because I *think* the Solver-type action (solving for best fit with OLS) requires Excel but please let me know if you find a better way. The example btw is straight out of Tuckman. Talk to you soon!

David

Hi David, can you please share the excel version again . Somehow not able to access this. Also, completely agree on the comments in the section. The forum is a treasure trove and I keep visiting time and again , to learn more from the interactions.
Regards
Gopi