Using Excel, how could I calculate a cubic spline curve in monthly granularity when my inputs are a combination of months, quarters and years? The quarter and yearly averages of the spline curve need to be exactly the same as the input. An example set of inputs would be:

Period Number

Feb-17 100.00

Mar-17 110.00

Apr-17 115.00

Q2-17 117.00

Q3-17 116.00

cal-18 120.00

cal-19 121.50

Unfortunately, I can only find how to do this if the inputs are all single months.

Any help will be much appreciated.


Dates   Index
01.03.2017  100
01.04.2017  110
01.05.2017  115
01.06.2017  117
01.10.2017  116
01.01.2019  120
01.01.2020  121
  1. I took the end points of the months, quarters, and years that you provided;
  2. Draw the dot plot with smooth lines in Excel;
  3. Right-click on this line and choose "add line trend";
  4. Select "Polynomial" and choose degree "3";
  5. Tick the box that says "show the equation on diagram".

The result is awful, but I think you can play around and make it look better.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.