Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •