# Thread: Trend Analysis (2003)

1. ## Trend Analysis (2003)

Perhaps this is more of a math's question than an excel one.
I want to trend some data but I need something a little more advanced than the usual trend line.
I am looking at a relatively new banking product - High interest rate loans.
Most loans have redeemed early and it is easy to calculate the average life of a redeemed loan. However, I need to predict the average life of the whole portfolio and show the redemption trend. This involves making a prediction of when all the loans will redeem.
The trend line is expected to look like a "waterfall" in it's shape i.e initial steep fall and then curving straight to run out to an almost horizontal trend line.

I have been looking at the data analysis tools and the different trend line alternatives in excel but I cannot seem to find the right thing.
Has anyone got any ideas as to how I could go about this.

Many thanks

Robert

2. ## Re: Trend Analysis (2003)

This is probably more of a mathematics question indeed. You'd have to find a function that can model the data. Once you have that, you can use the function to transform the data, find a linear trend line, and transform this back.
If you post a workbook with some sample data, someone may be able to come up with a suggestion for a function.

3. ## Re: Trend Analysis (2003)

I agree with Hans.

Your comment "The trend line is expected to look like a "waterfall" in it's shape i.e initial steep fall and then curving straight to run out to an almost horizontal trend line." sounds like exponential decay. If the "horizontal" asymptote is at a value of 0 then it can be "Linearized" using a logarithmic transformation. If the asmymptote is not zero then it is a non-linear function and a solution is less directly found and you must make guesses at the what the final value will be to be able to solve it. Often it can be calculated iteratively.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•