Probability - TREND

[fireal5000]

Hi, I'm new to the board hoping for some friendly insight..

I'm trying to find a means to calculate a probability of default trend line over a certain period using the TREND function in Excel 10. My issue is the probability figure cannot be less than zero, however I'm not sure how to configure the function so that the TREND period reflects this parameter.

Year Pd Risk
10 2.44
9 2.13
8 1.84
7 1.52
6 1.23
5 0.96
4 0.71
3 0.5
2 0.26
1 0.1
0.75 x
0.5 x
0.25 x
0.08333 x

The periods < 1 year reflect 9 month (0.75), 6 months (0.5), 3 months (0.25) and 1 month (0.8333). "x" are the periods I cannot properly capture.

Thanks in advance for any assistance!

Jay

 

[Dendro]

Hi,

First of all, probability can never be negative and this is in all cases.
As you see the data points (use scatterplot) you can see that you need to set it to power. You can always make it reflect your datapoints even more, but it would make your trend-line function useless. To measure how could the trend-line fits your data you have to calculate and sum up the residual values( the absolute value of the distance of your data points to the average (=trendline) to the power of 2) and compare this to the total variation²(=without the regression model). This will give you the R², this number will show you how much the regression model has reduced your variation. We want a value close to 1.
You are lucky you only have to click the option of calculating R² in excel properties so all this is done for you
You could try different kinds of models, and check each R² keeping in mind the cost of added constants and pick out the best. If you want to do this i suggest you install "R" and do a stepwise regression.

I checked the data with the power regression model ( y = 0,1021*10^1,3888) and i must say you excel gives a good model: R² = 0,9993!
The extrapolation gives the following values:
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0,75[/TD]
[TD="width: 64, align: right"]0,068472
[/TD]
[/TR]
[TR]
[TD="align: right"]0,5[/TD]
[TD="align: right"]0,03899[/TD]
[/TR]
[TR]
[TD="align: right"]0,25[/TD]
[TD="align: right"]0,01489[/TD]
[/TR]
[TR]
[TD="align: right"]0,08333[/TD]
[TD="align: right"]0,003238[/TD]
[/TR]
</tbody>[/TABLE]

The only things you would need to do, depending on how precise you want your regression and if you want a credible probability, collect more data and adjust( your R will slightly decrease though).

Good luck!

 

[fireal5000]

Thanks, Dendro!

 

[fireal5000]

Hi Dendro -

Sorry to persist, but could you elaborate how you were able to extrapolate the probability figures for 0.75, 0.5, 0.25, and 0.8333 using the R2 factor?

Thank you!

 

[fireal5000]

Dendro - I was able to read through your note a little closer now and see the power equation used - thanks again for your help!