I am trying to validate the use of a model. I have two streams of NPV calculations, one is the actual return, the other is the projected return. Those streams are turned into deltas from one time period (1 day) to the next to create it's own time series (net from day-to-day)

The current model uses a regression approach to compare the delta results. The comparison is done in Excel with the LINEST formula. I am concerned with that approach because LINEST expects known X and known Y values. But if I am being given two streams, aren't those two Y values? The X values are still unknown and I don't think LINEST would apply in this scenario.

What other approaches can be used to compare two sources of return data?

  • 1
    $\begingroup$ A scatter diagram with actual on one axis and projected on the other could be valuable. It has more information than the regression. Also you could compute the Mean Aboslute Error or the Mean Squared Error. $\endgroup$
    – Alex C
    Mar 22, 2018 at 12:31
  • $\begingroup$ @AlexC Understood. But does that approach make it a candidate for the LINEST formula? Is it acceptable to have one result be your X axis? That just doesn't feel right. $\endgroup$ Mar 23, 2018 at 20:03


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.