interpolation in a query

[Mark]

Need to interpolate the date that PV10 of each ID equals Cumulative
Cash Flow for each ID from the following query:

ID Date CashFlow Cum_CashFlow PV10
003920072010 8/31/2003 17244.62 17244.62 275190.13
003920072010 12/31/2003 65953.59 83198.21 275190.13
003920072010 12/31/2004 150768.41 233966.62 275190.13
003920072010 12/31/2005 64825.41 298792.03 275190.13
003920072010 12/31/2006 237.68 299029.71 275190.13
003920075010 8/31/2003 28706.83 28706.83 515429.34
003920075010 12/31/2003 112337.47 141044.30 515429.34
003920075010 12/31/2004 277419.97 418464.27 515429.34
003920075010 12/31/2005 138962.25 557426.52 515429.34
003920075010 12/31/2006 6509.51 563936.03 515429.34

The interpolated date for ID 003920072010 would lie 64% of the time
between 12/31/2004 and 12/31/2005, or 8/20/2005. If the PV10 exceeds
the value shown in the table, I need to use the slope from the last
two points to extrapolate.

The query SQL is:

SELECT a.ID, a.TF, a.CashFlow, Sum(b.CashFlow) AS Cum_CashFlow,
First(a.PV10) AS PV10
FROM po4 AS a, po4 AS b
WHERE (((b.ID)=[a].[id]) AND ((b.TF)<=[a].[tf]))
GROUP BY a.ID, a.TF, a.CashFlow
ORDER BY a.ID, a.TF;

I'd really appreciate any suggestions and feedback. Perhaps this is
too complex for Access.

Thanks, Mark

 

[Michel Walsh]

Hi,


I will simplify your case to:

ds ' table name
x y ' fields
1 10
2 20
4 26
8 30 ' data


and we want y-value, yc, for paramX:


SELECT bb.x AS x2, aa.x AS x1, bb.y AS y2, aa.y AS y1, y1+(y2-y1)*(paramX-x1)/(x2-x1) AS yc
FROM Ds AS aa, Ds AS bb
WHERE bb.x=(SELECT MAX(x) FROM Ds WHERE x<=ParamX)
AND aa.x=(SELECT MAX(x) FROM Ds WHERE x<bb.x);



Which will work jusssssssssssst fine for Jet, the toy. On the other hand, that won't for MS SQL
Server, the AllMighty, but there, you just have to substitute y2, y1, x2 and x1 by their values, in
the expression for yc. (x1, y1) and (x2, y2) are the two points we used for the
interpolation/extrapollation.


Hoping it may help,
Vanderghast, Access MVP